Monday, March 30, 2020

Mapping in Informatica

There are four component in Informatica

  • Designer
  • Workflow Manager
  • Workflow Monitor
  • Repository
Designer is the component that are used by Developer and it is used by developer to design the mapping.Mapping is nothing but the creation of transformation rule i.e. how to load the data from source to target and the rule that has to be applied on the source.
Also in designer, what we do is identify the source tables and import the source data to informatica, then, connect to target database and create the target tables, and finally, create one to one mapping or mapping as required by business.


Workflow Manager is the component where we  create the session, jobs and workflow and we run the mapping that we have created in Designer.
In Workflow manager, we have to create the workflow that comprise of mapping.
In Workflow manager, itself, we have to create the sessions also and then we have to tag the session to workflow.
In a single Workflow, there can be multiple sessions involved.

Workflow Monitor is the component where we will track the session that we have triggered in second step.We will see the passed scenario and failed scenario.Number of rows that are copied from one table, file to target location.Also we can see the failed reasons.

Repository is the one which is not to be bothered and it has mostly to do with Administrator.

While doing the database table loading,  we manually have to create table structure manually.
Informatica never create tables itself by running the workflow.
Similarly, for flat files as well, we have to name the target flat file.

To Generate the blank table in target schema, we have to go to Target Designer, copy the source database file, and then navigate to Targets in main menu and select the last option, Generate/Execute SQL, connect the database and click on Execute the query button

Friday, March 27, 2020

DATA VALIDATION TESTING

What are things that has to be tested
  • Whether source table/tables are present in Source schema?--include while creating the tc
  • Whether target tables/tables are present in Target schema?--include while creating the tc
  • If the data is extracted and loaded to staging tables, Whether staging table/tables are present in Staging schema?--include while creating the tc
  • Also the three tables that are mentioned above should have correct data structure as that while creating the tables, for instance, id should be integer or long and name should for char/var as per the requirement.--include while creating the tc
  • After the above checks are done, we have to verify that whether the ETL jobs that are created by developer are present at right location in Informatica.--include while creating the tc
  • We must do the data validation once the job is executed.Data Validation is done through SQL query
  • We have to check the count in target, count must be same as that source if no transformation rule is applied.--include while creating the tc
  • Also in Data validation query, we must ensure that target tables has perfect data as that of source tables.--include while creating the tc
We must prepare source query with transformation as target query will have data after the transformation.

Source query with transformation 
MINUS 
Target Query
UNION
Target Query
MINUS
Source Query with transformation


Conclusion
  1. We are checking the existence of the source tables and its structure
  2. Also the same logic is applicable for staging as well as target tables, so we have to check the existence of staging and target tables.
  3. Data Validation query must check count of rows of Target/Source and data is perfect as that of Target/Source.
  4. Query must be created as exact as possible so that our main task of Data Validation is done perfectly.

Thursday, March 26, 2020

TABLEAU

TABLEAU is a business intelligence tool for visually analyzing the data
User can create and distribute and interactive dashboard which depict the trend, scope, variation etc.
Tableau can be connected to files, databases and big cloud databases for the visual analysis.


Wednesday, March 18, 2020

INFORMATICA

Informatica is a tool which is used to extract the data from the source and transform the current extracted data and load it to target database.
Now the question is WHY ETL?????
The simple answer is lot of client or customer are using legacy system and they are moving to new technology. So in order to move to new technology, their current setup regarding to databases and tables become null and void because new system bring their own database.
So ETL is used to move the customer current data to new database which is compatible to customer/client new technology.


Informatica provide movement and transformation of current data from tables which can be from one source database or multiple source database to new DATA WAREHOUSE. i.e. suppose currently customer legacy system consist of databases from mysql/Oracle,TIBCO and customer is using AWS which has its own database configuration, so in this ETL tools come into picture and INFORMATICA is the widely used tools among other ETL tools.



Mapping excel document is most important table in ETL
Mapping excel document  contains Source tables, staging tables, target tables, business logic, and the table structure.

While converting or loading the data from source tables and target tables, we always have some transformation rule.

This is basic concept of ETL, Extracting the data from source tables, applying the transformation rules to data of source tables and loading the data into the target tables.

So while doing ETL, we always have to think of the source tables sql query that has to be written so that we get concise, precise and correct data

Take a look at the sample mapping document......


Important Term:
Straight pull/one to one mapping------no change or variance in data if data is copied from source to target table i.e no transformation needed.Straight forward Just Copy the data from source to target.

Now one thing is very necessary for us to know is that Transformation logic is written by developer in ETL tool.
That we will show while we go ahead in this tutorial.

Developer will create the Process or job that will be called workflow that contain the logic for the transformation.
This job or process or workflow will have basically the mapping between the source and target tables.

This workflow will extract the data from the source tables, apply the transformation on these data and lastly it will load all the data in the target tables.

Workflow
  1. Extract the table for the data
  2. Apply the transformation on the tables or individual data
  3. Load the data in the target tables
Requirement for creating workflow is the mapping document where all the rules are written.
Developer will refer the mapping document for writing the workflow and 
Tester require the mapping document to test whether all the data are loaded correctly or not.

Tester refer the mapping document and create sql queries to test the load of data.

Informatica has four component
  • Repository-It is the place we store everything. Informatica Repository is mainly accessed by the person who has the admin access.
  • Designer-It is the place where developer design the workflow.
  • WorkFlow Manager-It is the place to trigger the job.
  • Workflow Monitor- Once the job is triggered, now to see the status, whether Passed/Failed or if job failed, what is the reason, all this information can be analysed from workflow monitor.

ETL Testing

ETL Testing
Extract Transform and Load



Suppose we have some source DB that are different in configuration and databases, are moved to a single place known as Data Warehouse.
So the source Database, for instance is SQL, ORACLE, SAP HANA, few excel sheet, few csv files and AWS databases as well.
All the above DB has some or the other data, that can be dependent and can be independent with each other, are move to a temp stage or directly at data ware house, so how they are moved from source to data warehouse are done by ETL tools.
There are various ETL tools that are available in market.

Also while doing ETL transformation or movement of data, we can do the filtering of tables, for instance, only 10 tables data are moved from a SQL and 5 from SAP HANA.
This also can be done from ETL.

So testing that all the data are moved correctly, is called ETL testing , where we can compare the source and target data and verify that movement of data was correct and it was verified.

Thursday, March 12, 2020

गीता 14:5

सत्त्वं रजस्तम इति गुणाः प्रकृतिसंभवाः।
निबध्नन्ति महाबाहो देहे देहिनमव्ययम्।।14.5।।

 हे अर्जुन सत्व,रज और तम,ये तीनों गुण आत्मा को देह से बाँध देते हैं|

गीता 14:4

सर्वयोनिषु कौन्तेय मूर्तय: सम्भवन्ति या: ।
तासां ब्रह्म महद्योनिरहं बीजप्रद: पिता ॥ ४ ॥

हे कुंती पुत्र अर्जुन समस्त योनियों मैं जन्म लेने वाले व्यक्ति को जन्म भले एक माँ देती हैं परन्तु उसके गर्भ मैं उस शिशु का बीज रखने वाला पिता तो मैं ही हूँ 

Thursday, March 5, 2020

Amazon Web Services



Amazon Web Services provide numerous services on their infrastructure to build your own application.
Right from providing computing platform to storage to various Developer tools, it provide everything that helps you to build your own application.
The pricing is also very reasonable that is the best part and as an individual, most of thing that are needed to build application is free of cost for the first year and then Amazon charges very low rates for the year after.

Create an account with Amazon Web Services and note it will require credit Card information for the same.
Once the Account is create, Login to the AWS Management Console.

Starting from setting up the server to coding or uploading the code,,,,,,everything is provided by AWS.

While for big website, we will never need a single server and we all need multiple server and that too in up and running position.
The biggest disadvantage of this task is managing all the server, updating the operating system, clearing the log, cache and maintaining it 24X7, so that it is up and running.

This task is omitted if we use Amazon Web Service for hosting our website.
AWS Lambda is the service that manages multiple server in Amazon Web Services.
The maintenance can be said as clearing the log, cache, maintaining the operating system updated and many more task.


API GateWay
API Gateway is the service that is present in AWS for API creation and uploading the already created API.We can create all type of API by Amazon Web Services.

WHY Amazon Web Services
Microsoft Azure, Google Cloud Platform are also the platform that are present other than Amazon Web Services, but AWS is most popular because
  • Market Leader
  • Aggressive Pricing
  • Large number of Serverless Services
  • Rapid innovation and Rapid inclusion of new Feature/Services






  • S3 is simple storage service that helps to cater the front end of the application and it has simple static pages and other scripts pages.
  • API Gateway is the service that helps us to create API and deploy them on the server which will help us the to render data from database or the insert the data into database.
  • AWS Lambda is the service execute code on demand.
  • DynamoDB is the service that provide database related help while creating
  • Cognito is the service that provide the user to authenticate the user
  • Route 53 allow us to configure our own domain, i.e. create our own choice of url
  • CloudFront is the service that provide us to improve the performance of our website by storing cache.


API Gateway

API keys in API Gateway is the unique number by which developers are identified.
API keys are used by AWS to identify the user and limit them with the access or provide more access if they are paid for the service.
By creating API on AWS, we have multiple option of giving the domain name to API so that default AWS URL that is generated should not be used, although we can use that also.
Also we have option to give the permission to our create API that how much has to be used, how much hit by client is acceptable, regarding publishing of log file and various other stuff.
We have to write every piece of code in Resource Section.
Stages are the one we can say as environment where deployment has to be done, for example QA, primary in QA, development, pre-production and other.
Authorizers allows us to add authentication to our API, the users who can use this API
Models define to shape of the data which we are using in this API. The type of data which is coming in request and data which is going through response.
Documentation is a place where other developer will get to know that what this API is all about, how it will be used, parameter that are passed and  response type.
Binary Support will come into play if we are using files with the API. Files  can be of any type and it will come with the request or else it is going with the response.
Dashboard is the place where we get the high level overview i.e. number of usage of the API, number of hits and many other things.







First box is the Method Request which states that how the API must look like, whether this 
Particular API is meeting the criteria that are prescribed, the criteria can be anything, parameter or data model or criteria for schema specification
Also we can do the authentication for the API so that only valid users are able to do the usage of this API.We can say Method Request  is like gatekeeper who keep an eye on the data and their validity.
If the authentication is not met, we simply block the rest of the work that has to be performed.


Second box is Integration Request is used to map the data according to the way we want to use it.Also we can hardcode the data according to the use.The transformation of data can be of any type for instance, authentication data, header data, parameter data and many other.

The last box is Method Response is the last response box and it will pass all the response whether it is pass or fail and we can give the final touch of how data has to be rendered in front of client.

The third cyclic box is Integration Response and is used to extract the data from the integration request and pass it to method response and here only success response are passed.Also we can transform the data according to the need of client and send it to method response. Integration Response can also be useful in allowing the access control allow for header data type, methods which this particular API can handle and parameter datatype.

There are two places where we can change the request and response.
In Integration request, we can change the parameter that are passed by client and
In Integration response, we can modify the response that our function processed, irrespective of the result from Integration Response.


There are four different type of function that can be attached to an AWS API Gateway, which are

  • AWS Service-Call another AWS service that is already created.
  • Mock-We don't do anything except passing some mock data and returning the random hardcoded response.
  • Lambda-Which we will learn in coming module
  • HTTP-It is a function or API that is created on some other server and we will call the same by passing the url.


Cross Origin Resource Sharing(CORS)
It can be understood that data present on one server cannot be accessed from another server.
So while creating an API, we come across various use cases where we have to get the data from another API and use this data.So if we check the CORS checkbox, we can access other API as well which is on some other server.

While creating resource in API from AWS, we come across the checkbox CORS, so we usually check this checkbox.
While sending PUT,POST and DELETE request in chrome browser, chrome doesn't only send the core request but it also send the pre flight request which check that whether the API request should be send, check whether the parameter are correct and many other things.
Pre flight request is the extra request to your API.
The CORS checkbox in AWS API Gateway will also the pre flight request that are being send by chrome browser and it will handle the pre flight request very seriously.

For more AWS API Gateway

Creating a First API in API Gateway in AWS
AWS Lambda
Whenever we create our first APi, we have to create an Lambda function
AWS Lambda is a service that allow us to host our code on it and run it on a certain event.



Please get a clear picture of a above diagram to understand Lambda

Suppose there is a event trigger for instance S3 event, like upload a file, now this request of file  can be request any operation on that particular file.
Suppose it compress the file which is the input.So the code to compress the input file is written in AWS Lambda and it can be written in any of the four programming language, NodeJS, Python, Java or C sharp.
and once the processing code is executed, the result can be anything, for example, returning that file, or interact with other AWS services, or mail that file to used, anything or everything,
THIS IS HOW LAMBDA WORKS.



Above diagram shows, that AWS API Gateway trigger multiple function and for the respective API ,there is a separate Lambda function.

How to create AWS Lambda Function 
After the API is created, its time to deploy the API,
Deploy the Created API by adding the AWS Lambda function.
Once Deployed, please change the POST method permissions so that we can access it outside the AWS website.
and the change of permission is Adding access allow control header variable that comes with Options but it is not present in POST request.
So add this variable in Fourth box i.e. Integration Response and add * in third box i.e. Integration Request.

Logs of AWS Lambda are present in CloudWatch Service of AWS.



How To add dummy Response to API Gateway that has integration type of Mock?
  • This can be done from creating a API with Mock integration and then Going to INTEGRATION RESPONSE,  click on the first dropdown of first row and then it will expand , go to BODY MAPPING TEMPLATE, select application/json and in the notepad, hardcode the response.
Now once we have deployed the API, adding the lambda function, 
ideal condition would be that it return the response that is there in Lambda function, 
but still we have two place where we can change the response thing.
Remember, The two boxes, second box INTEGRATION REQUEST and third box INTEGRATION RESPONSE.

In INTEGRATION REQUEST , under BODY MAPPING TEMPLATE, we can set the template as application/json and hardcode the value in json format and then we can see the response of lambda according to us.
Similarly in INTEGRATION RESPONSE, we can also, change the data which we are getting from Lambda function through Body mapping template.Click on Body Mapping Template and just see the things that can be looked upon.


Angular JS


Tuesday, March 3, 2020

गीता 14:3

मम योनिर्महद् ब्रह्म तस्मिन्गर्भं दधाम्यहम् |
सम्भव: सर्वभूतानां ततो भवति भारत || 3||

हे अर्जुन,मेरे प्रारंभिक स्वरुप जिससे परम ब्रह्म स्वरुप भी कहा जाता है ,ये स्वरुप ही सब जीव की उत्पति का कारन है,ये ब्रह्म स्वरुप ही सब जीवो की उत्पति की योनि हैं और मैं उस योनि मैं जीव की उत्पति का बीज स्थापित करता हूँ जिससे सारे जीव की इस पृथ्वी पर उत्पति होती है| 

भगवान् श्री कृष्णा ने इस श्लोक मैं अपने आप को सब जीव की उत्पति का कारण माना है,उन्होंने सब को अपना पुत्र बोला और अपने को सम्पूर्ण संसार का पिता बोला है| 

प्रभु का ऐसा सम्बोधन होने के बाद भी हम जीव अपने आप को अकेले समझते हैं,जबकि प्रभु तो हम बच्चो पे हमेसा अपनी कृपा बरसाता रहता हैं ,
ये कहना भी काम होगा की हम भगवान् से डरें ,प्रभु हमारी सारी जरूरत का ख्याल रखता है और बदले मै एक भी चीज़ नहीं मांगता हैं,
भगवान् का हमसे वैसा ही रिश्ता है जैसा एक माँ का अपने बच्चे से होता है 
जैसे हम गंदे हो तो माँ खुद हमें साफ़ करती है,क्या हमे बोलने की जरूरत पड़ती है माँ से....
वैसे ही प्रभु  भी हमारी चिंता,रक्षा ,सुरक्षा ,जरूरतों ,आदि का ध्यान पल पल रखता हैं 
वो हम अल्प बुद्धि वाले लोगो को ऐसा लग सकता है की जो हो हो रहा है वो हम कर रहे है परन्तु बिना प्रभु की इच्छा से अपना और न किसी का एक कार्य पूर्ण नहीं होता हैं 

गीता 14:2

इदं ज्ञानमुपाश्रित्य मम साधर्म्यमागता: |
सर्गेऽपि नोपजायन्ते प्रलये न व्यथन्ति च || 2||



हे अर्जुन,जो भी इस पवित्र ज्ञान को धारण करता है,वो मेरी तरह ही परम ब्रह्म हो जाता है| 
इस पवित्र ज्ञान को धारण किया व्यक्ति किसी भी परिस्थिति मैं विचलित नहीं होता,न वो सृष्टि की शुरुआत मैं जन्म लेता है
और न ही प्रलय या विपरीत परिस्थिति मै उसका नाश होता है 

गीता 14:1

श्रीभगवानुवाच |
परं भूय: प्रवक्ष्यामि ज्ञानानां ज्ञानमुत्तमम् |
यज्ज्ञात्वा मुनय: सर्वे परां सिद्धिमितो गता: || 1||


भगवान् कहते है की अर्जुन मैं तुम्हे पुनः वो ज्ञान देता हूँ जिसको प्राप्त करके विशिष्ट मुनि गण सर्वोच्च ज्ञान के स्थान को प्राप्त करते है|

हम सब इस पृथ्वी पे एक ब्रह्म समान ही आते है परन्तु इस प्रकृति के सम्पर्क मैं आ जाने के कारण हम अपने मैं बदलाव कर बैठते है,परन्तु भगवान् कृष्णा द्वारा दिया हुआ ज्ञान प्राप्त करके हम पुनः सर्वोच्च स्थान को प्राप्त कर सकते है

Monday, March 2, 2020

गीता 15:9

श्रोत्रं चक्षुः स्पर्शनं च रसनं घ्राणमेव च।
अधिष्ठाय मनश्चायं विषयानुपसेवते।।15.9।।

कान आँख स्पर्श  जिहवा और नाक ,इन् सब के द्वारा ही हम अधिकतर चीज़ो का अपने अन्तः करन मैं प्रवेश करते है 
ये शरीर तो पवित्र वस्तु है और जब ये माँ एक गर्भ से इस पृत्वी पे आती है तो ये ईश्वर समान ही पवित्र होती है परन्तु कान आँख स्पर्श जिहवा और नाक से जो चीज़ चाहती है अपने अंदर प्रविष्ट करती रहती है और इस कारन इसका वास्तविक स्वरुप ईस्वर से अलग होता रहता है। .....