Informatica Tutorial | Learn Informatica In 60 Minutes | Informatica PowerCenter Training | Edureka

hello everyone this is Nia from ed Eureka and welcome to this ad Rekha live session on learning informatica in 60 minutes this is the second installment of a business intelligence tool series and in our previous session we had looked at ba ba now informatica is the markets leader when it comes to data management data integration data processing basically anything that deals with data now even if you have a three-hour session on informatica we would still not be able to complete it explore it as such however post today's session I'm quite sure most of you would be able to work with informatica and begin your journey on playing with data through informatica power center now moving forward let's look at today's agenda to get a better understanding of what we'll be discussing as part of this session now the first thing we'll try to understand is what exactly is business intelligence now informatica is a tool that provides you a business intelligence based solution so let's try to understand what is business intelligence first after that we'll try to understand what does extract transform and load because this is a tool that performs this operation so it's essential that you understand how extract transform and load works moving on from there we talk about informatica power center 10 and then we'll be looking at the various tools associated with informatica power center starting from informatica power center administrative console and the four remaining tools of informatica would be explored through a demo as part of this session now I hope you guys are definitely looking forward to this session and I request you to be a bit patient because this is a long session as such now we will begin with the first topic of today's session that is what exactly is business intelligence now business intelligence is one of the biggest buzz words in today's industry everybody wants to know what it does how it works out so let's try to understand business intelligence first now business intelligence basically this is a group of techniques as well as tools wearing you gather your data from different sources once you have your data from different sources you bring it onto a common platform that is usually a data warehouse once I have my data present here that is all the data from different sources in a calm point then from this data I can perform various analysis and make it quite easy for the end-user to understand this now to put it quite simply let's take it in this way let's say your boss comes up to you and tells you ok gather the data from all my sales departments and then show me a result or a visualization which tells me how my sales department is performing now for that first what you need to understand is that not all the departments would be using the same kind of application to store their data so first what I need to do is that I need to bring the data from all these applications to a common platform once I have a hit here then I can go on to perform my required analysis operation now this is what business intelligence lets you do now again if you look at the example here I have my data present in different databases as well as in different files as such it could be in a sequel server database it could be an SI p database oracle or any other database as well it could also be present in an XML file a workbook a PDF file a CSV file and so forth so I need to extract the data from all these sources transform it to my requirement and then load it into a data warehouse so this is what is the process that we are going to follow that is extract transform and load now this is one of the techniques that business intelligence follows and it's again one of the most widely used as well going forward let's try to understand what exactly is extract transform and load now to be quite straightforward extract transform and load basically is a four stage process that happens in the first stage you capture the idea of what exactly is present in your source system once you've got an idea of the data that is present here you're gonna scrub and clean this data that is you got to take only the required data or the consistent data from the source once I've done with this then I'll move forward and then perform the required transformation on this data I'll only take the data that meets my requirement ahead finally I'm going to load it ahead into my enterprise data data warehouse that is ok so here I have the data from all the sources as well as the data that meets my requirement as such let's now try to understand what exactly is informatica power center okay informatica is the markets leading data integration tool apart from being a data integration tool it also lets you migrate your data from different data sources it helps you integrate your application data the data that is present in different application as well as it helps you transform your data as per your requirement as such now talking about the various informatica power center applications these can be classified into two categories the first would be an administrative tool now in the Administrative Tools section you have mainly two tools present here you have your repository manager as well as you have your administrative console now let me first address the administrative console as the name suggests this is basically a console through which you would be performing most of your administrative tasks here you would be working with various services as well as performing operations on your domain let's say I have two teams who are working with informatica power center on my own in my organization that is now each one of them would be referred to as a node and the collection of these nodes together would form my domain so all the operation from creation configuration as well as management of these nodes would be done from my administrative console apart from that there are various services that are associated to each of these applications as well it could be a repository service or an integration service as well so the creation the starting the stopping the deletion of these also will be managed on this administrative console itself let's say you want to backup your data or you want to restore an existing data as well then that also can be done from the existing administrative console as well so again this is a tool that most informatica administrators would be playing around with after that you have informatica power center repository manager now this basically helps you create your various repositories manage your various folders as well as the users and groups now again what is the repository here you need to understand is that it's quite similar to the project folder that you would be creating in your PyCharm or Eclipse as such so all the work that you will be doing usually is stored in a project here it's referred to as a repository as such now in your development site you have mainly three tools you have informatica power center designer workflow manager and workflow monitor your designer helps you mainly create the ETL mappings are such basically what you're going to do is that you're going to define the flow of data from the source to the target definition in between any operation that you want would be considered to be a transformation and that would be included as part of your mapping now mappings themselves are not directly executable assets so for that you need to create a workflow associated with it so once you create your workflow then you can go on to execute your mapping which will be done in the workflow manager now if you want to see the status of your workflow you want to manipulate it you want to start it stop it restarted or even check the status of an executing workflow you can do that in informatica power center workflow monitor as such now again just remember each of these tools because these are the most essential tools with informatica power center now moving forward let's look at informatica power center administrative console now again as the name suggests this is the console where you would be working with and again you'd be mainly performing operations like managing various applications various services creating new domain objects configuring your node and so forth now move forward let me connect to my cloud service where I have already configured my informatica power center okay so this is my cloud service on which I have already configured my informatica power center so always make sure before you're working with informatica power center the informatica service is running in the background okay I'm sorry about that okay so this is my informatica power center administrative console so here you can see that the service is presently not running so I'm gonna start this okay and let me login to my administrative console again the reason that I have started the service is because if it's not running in the background the communication would not take place now again by default the first page that you would be looking is the managed domain now here basically you can manage your various services as well as associated applications running here so let's say if you are running an email service I'm running a workflow and there and what happens is that after every workflow executes I need a mail sent to me so I can configure that here apart from that you have two major associated services in informatica that you always need to remember you have your informatica integration service and you have informatica repository source now remember how I told you what repository service was basically it's basically it's like your project folder so everything that you're going to be creating in informatica is going to be stored in a database as well so repository service is basically the service which collects all the objects that you are creating and stores it in your database so let's see if you're working with the source that would be considered as an object let's say if you're working with and target that is also considered to be an object a mapping that you create also an object so everything is going to be stored in a database and your repository service is the service that does this now integration service is the most essential service while you're working with informatica this is the service that actually does all the work now once you've designed the workflow and the mapping the integration service is the actual service that takes the data from the sources performs all the operations that you have specified and finally stores stores it in your target database as well so remember these two service because these are the Extension Service in informatica now apart from this let's say if you were configuring your different nodes and services so you have the option present here now you can see here by default I have my informatica integration service my repository service and license management present here as well there's also a single load now what I've done here is that I have basically installed my informatica server and client on the same system so that it's easy for me to show you both the tools as such but usually what happens is the server is stored on the actual server and usually each system gets the client installed on this now there are various things that you can explore here let's say if you want to monitor what each one of the people are doing each one of the nodes you can do that as well or if you want to see you want to log to refer to as what operations each one has done or let's say you want to see who has called for which service all these can be present viewed here so again this is something most administrators would be working on with now the final thing that I want to show you here is with respect to the security aspect now since I am a single user I have not defined the security users or roles here now what you need to understand here is that not everyone would get the same access while you're working in a company now let's say there's a new team who's working on an informatica product usually what happens is it's the team leader who gets the access to the repository manager and that members would usually get access to the client tools now this is mainly because once you make any changes with respect to the repository it cannot be reverted back let's say by a mistake someone actually deletes your repository or let's say the project folder as such then you cannot recover this so this is where your users roles and security features actually come into picture okay now going back to our presentation I hope you've got a simple understanding of the administrative console as such now the next tool next tools to explore will explore it through a demo as per the session now what we're basically trying to do is that we have a retail organizations data and we want to understand our customers better so I have my product as well as transaction details present on flat files and my customer detail is present in my database I'm going to load all this data into informatica power center and then I'm going to create process various transformations on this and finally store it into a data warehouse future anytime that I want the data would be present in the data warehouse from which I can create various data visualization now we'll break down this solution into four phases now in our first phase will be using informatica power center repository manager now here basically we're going to create our work folder and ensure that all the sources transformations mapping and workflow would be stored here so let me go back to my remote desktop okay and let me launch the informatica power center repository manager you know again if any of you are actually trying to look into install informatica you can check out the link to install informatica on your system and then begin your journey as well so again these are the four tools that you will be working with informatica power center a+ manager designer workflow manager and workflow monitor so let me start with r a+ manager so this is what my repository manager would look like so here again you can configure your various domains and then you can also configure your various repositories now it's not necessary that one domain should just have one repository and it's something usually major Pro and usually in major practices what happens is you have multiple repositories working when there are different teams working on different projects so each project would be under a specific repository as such okay now I've already configured my repository so let me just connect to it okay now by default you would not be seeing any fold up present here so first let me begin by creating our work folder so click on create and it's going to ask you a name for the work folder so for today's session what I'm going to do is that I'm going to call it live folder okay one thing you need to remember is that in naming convention of informatica you cannot use space okay so always remember that white space cannot be used in naming convention okay so I'll just click on OK and a new folder gets created now here there's nothing present as part of this folder because if you go inside this configuration folder present here in future when we are going to create the various sources the targets mapping and workflow each one is correspondingly going to get a separate folder as such now again as i stoled repository manager can be used to check the details with respect to the project's present so let's say you want to search for something that is present in a folder so you can do that as well okay you have the analyze option here where you can analyze the various dependencies as well as search for a specific mapping or a source now let's say there's a specific mapping that you have created and you want to use it again but you're not sure where exactly it's present so you can search it by a target keyword or let's say you want to search it completely you can do that as well now again this is something that's very important for and informatica administrators and project leaders as such moving forward let's look at the second phase of our project okay now here what we need to do is that we need to create a mapping through which we'll be loading our source definition adding the transformation creating the target definition and finally we will connect them all as part of a mapping so let me go back to my informatica repository manager and launch the informatica power center designer now there are two ways that I can approach this I can either go back to my start option and launch informatica power center designer from there or I can actually launch it from here now informatica provides you this option of quickly quick launching of each of its tools so if I click on the designer option present here it will automatically launch informatica power center designer so this is my informatica power center designer okay now I'll just give you a brief so with respect to how this works so to your left you have the navigation panel when you can find the different folders for both sources targets cubes dimensions transformations so everything that you get everything that you create here as I said would be considered as an object and then it would be stored inside these corresponding folders as such okay now the bottom the bottom pane is for your output so this is where the output for any operation that you perform would be present now coming to the workspace now there are mainly five workspace in informatica power center design will be mainly working with three as part of this session the first is source analyzer workspace here basically any operation that you wish to perform with your source can be done okay so let's say you want to load your source you want to modify it or make or remove specific rows from a source as well every of those option would be done here because moving further you cannot manipulate the source details in any of the other workspaces similarly you have the target designer workspace so any operation you wish to perform with respect to your target definition also has to be done here let's say you want to create a target definition that would be done here or let's say you want to modify an existing target definition again has to be done here as well the third and the most important workspaces are mapping designer workspace okay so here what happens is this is the place where you are going to create all the mappings and define the data flow so let's go back to our source and source design our source analyzer workspace and begin by importing our sources okay now first what I'm going to do is that I'm going to import it from a data so you just click on the source option present here and click on import from database option now once you do that it is going to specify which connection do you want to specify use okay now what you need to do is that let's say you have a database present in your system you need to create a connection object from informatica to connect to the database so again just click on the three dots present here and then you get this option of user DSN here press on add and then you can specify which kind of driver it is now I have already configured this so I'm not going to reconfigure it as of now okay now I need to specify the user to the state and once I click on connect it's going to give me which tables are visible to this user okay so this is my customer table so I'm just going to import this so if I click on ok it's going to load my customer table now before I move forward here let me show you the table in my Oracle database so this is my Oracle database which I've connected using the SQL Developer now I want to connect to my HR underscore SRC database now here let me select star now let me just run this query so that you can get an idea of what kind of data we are working with so here you have your customer ID you have the name of the customer the address city country contact number and their corresponding email okay now there are 10,000 entries in this table so we'll be loading this completely to our informatics our Center now again with respect to the capability of informatica power center it can process thousands of rows in just minutes now we will be seeing how effective and useful it is why we have executing this mapping as well okay so I have loaded my customer database now let me load it from let me load my product as well as transaction details from a flat file so let me click on sources and import from file so I have my customer details so I don't want this because it's all be present in my database now again while you're loading a file to informatica you can load only a single file okay so what you need to do is that select the single file and then process with the file wizard now this is a flat file basically a comma separated flat file now it said it's using a delimiter rather than a fixed width difference now in the first step what you basically need to do is that ensure which type of flat file it is and then ensure that you have clicked import field names from the first line because this helps informatica understand the headers for the table ok once you've done with this click on next now in the second step basically you need to specify whether it's using a text qualifier or if it's using a delimiter that is not comma so you can choose anything's present here finally click on next and you'll be reaching the third step this step basically helps you specify the data type as well as precision associated with respect to each column as such so again once you're done with this just click on finish now again what you need to understand here is that it's not the complete data that gets uploaded here ok this is basically a metadata of the data that is present in your database or the flat file as such let me load the third file as well so we've load our crust load customers our product now we need the transaction details so again import from file so I hear I have already loaded my three source files so now let's begin creation of our mapping so let's go back to the mapping designer workspace and here let me bring in all the sources as such now here you can see the separation right inside the source folder there is based on fat file and then there is based on the database so this is how informatica differentiates these okay now let me bring in all the sources let me bring in the product table that me so first thing I need to do is set the name for my mapping now again by convention you follow you start every mapping with a small M followed by the name of the map again no white space is here so live it M underscore live as such so click on OK and we are going to create a mapping now you see when I had only just one source definition now something new has been added this is basically a transformation that informatica automatically creates okay so this transformation is called source qualifier transformation this gets created whenever you are working with either a relational table or a flat file source basically what it does here is that it converts this data types of these relational tables into informatica supported data types okay I'll just show you the difference here okay just notice here where my product already initially was of type number it has changed to decimal now again this basically helps informatica integrate your data from different sources and as one of the major factor why informatica is so effective while working with different data types different data sources I am sorry okay now simultaneously let me load my transaction details as well and let me load in my customer tables okay it's a very big workspace so I hope you guys can see it so again see the same I had loaded a relational table from my database and I had loaded two flat files so for all of these the source qualifier transformation is created now this is really helpful when you want to process the data before you perform any operations on this let's say there are two tables that are coming from the same database and I want to join them I can do that using the source qualifier transformation without using any other transformation here okay just go inside the source qualifier transformation and you have the option to edit the transformation now once you double click on any transformation you can modify this now if you go into the port's option this tells you the different ports or the column numbers which are associated from the source okay so any operation that you want to change here can also be done now coming to the statement of joining two tables that can be done here okay so let's say I have two tables and I want to specify a join condition with respect to that is the user defined chart so I can just click on this arrow mark and then it will help me specify which column to add now since transaction is a flat file I cannot do that so let me go down to my customer table and just show you an example okay so you can see here we use a defined joint field is open here so click on the sarama and then I can actually choose the port based on which I want to join these tables okay so again let's say two tables coming from same database based on the condition I specify here I can join them once I have specified the condition if I click on generate SQL it will also automatically generate an SQL for the expression that I have specified so that is the join condition okay so it's really helpful and your work gets cut short to do a lot of huge extend actually okay now again let's say I don't want all the data to come from my data source I want to specify a filter condition you can do that as part of my source filter condition so here I just specify which data needs to come through and it filters the state apart from that if you want to ensure only distinct values come from the source you can just click on select a state so there's a lot of operations that you can perform and again as I mentioned informatica is a limbic pole so spending even days on this you would still have not exploded completely I myself have not completely exported informatica so definitely this is something that is quite interesting now what will be first doing is that let's try to join our transaction details to our product details basically what this will help me understand is the details with respect to the products which are bought in each of the transaction okay now for that we're going to use our first transformation now again informatica has close to 38 different transformations as part of it and a lot more that keeps coming so which to choose is the first question that you need to understand now since I am going to join only two transformations sorry two data sources I'm going to use something known as to join her transformation so click on transformation option here and select create option once you do that you get the complete list of transformations that are available here now again as I said there are different kinds of transformations present to perform different operations and informatica will leave you completely puzzled with respect to how it works out so let's try to explore each one of them in our upcoming sessions but today let's start with joyner transformation okay now again let me start neighbor J in our product underscore transaction once I click on create this transformation gets created so this is my transformation I'm done with this so let me bring it and add it here now what I need to do is that I need to connect my source qualifiers to my joiner transformation and it needs to have the corresponding columns also present in it so for that just click on select all drag it and drop it into my joiner transformation this basically does two things firstly it replicates all the columns or ports present in this transformation into my joiner transformation as well as it creates a connectivity between the source qualifier transformation and the joiner transformation that I have just created now only one transformation or one source definition has been linked to my joiner transformation it's time I linked the second one so again select all drag it and drop it okay now I have created a connection between my source to my joiner transformation but my job is not done here I need to still specify the condition based on which these data has to get join so for that I will click on the joiner transformation and then go to the condition tab here basically it's going to ask you whether you need to take the master or the detail now let's come back to the master and detail little later let me just click on add a new condition option okay now here it is going to give me different columns present here so I have my invoice number stock code description quantity so again what I can understand from this is that it is coming from my transaction table so my master refers to my transaction table detail basically is then going to refer to my product table so here what informatica is doing that is this labeling the two tables as master and detail to help you differentiate between them now usually masters are tables which have the signal refills okay let's say I want more detail with respect to a product then I am going to set the table which has the product ID as the master and the table which has relevantly all the details with respect to product as detail okay so this by default has set it now what I'm going to do is that I'm going to specify the join condition now I'm going based on the product ID so product ID one from my transaction table is equal to my product ID so if we go back to the properties table I'm sorry if we go back to the ports table here you can see the different ports that are present so there are 17 ports present here now I don't want all of them to be taken out so what am I going to do is that I'm gonna uncheck the option from input-output so you see the I and hope resin here this is not 1 and 0 guys this is I and Howe so it basically helps you understand which is an input port which is an output port basically again with respect to a data flow it helps you differentiate between which is an incoming value and which one you should go out now in our case let's say I do want the rating nor the seller details the the price day I'm gonna remote department company number sold discount I'm going to keep the invoice number stock code I don't need description quantity state I'm going to keep an invoice date unit price okay customer ID yes definitely and I don't want the product ID again so I'm going to uncheck this basically what I have done here is that two mites will join a transformation all the ports are inputs but only the select port should go as output now this is what I have ensured by checking these options here ok once I'm done with this just click on apply and ok so till here what I've done is that I have joined the data coming from these two tables based on a condition that the product ID from the product table is equal to the product ID from the transaction table ok now based on this all the data would get joined and sent across from here now again talking about join operations normally in SQL you have four different types of charge similarly in the joiner transformation you also you have that so just double click here let me go back to my properties so here you have the joint types option ok you can either have a normal joint master outer join detail outer join and full outer join basically your left join a right Chuck ok so this is how it has classified it so we are not going to change with respect to know the type of child okay so fury here I hope you guys find it quite interesting now moving forward I want to join this detail that is the details with respect to transaction and product to my customer data okay so what I'm going to do I'm going to create another Joyner transformation so again I can either go back to my transformation tab and select Joyner or I can select Joyner from here so this is basically a shortcut to different transformations now again you might be wondering why am i using two different join us to join three tables basically the restriction with the Joyner transformation is that you can only join two sources or two inputs from through Joyner transformation okay so with the second Joyner I'm going to join these two these three tables are such okay so here first I will bring my customer details select all drag and drop it similarly let me select everything from here and drag and drop it to my gist second joint transmission now what you need to understand here is that all the tools that are present here all the columns that are present here how many columns are present here I think there's seventeen two columns present here let me see in the second join a transformation if everything comes here now again if you see here there are close to 24 ports now I don't need all of them as part of this so I don't need a duplicate Product ID select this let me remove it let me remove quantity I don't want description so this basically happens because I have dragged and dropped let's say individually select it then this would not happen but again even though these columns are present here they are not selected as output if you'll notice here they're just inputs they are not going as output so it does not make much difference but I don't want data inconsistency to happen here so I'm going to remove all these ports which does not have an output specified again the alternative to this manual work is that you manually drag and drop the columns okay okay this is a bit better now I have to basically work with 14 different columns now again let's specify the condition so the condition here our new condition is always with reference to the customer ID so customer ID is going to be linked to the customer ID of the customer table customer ID from a previous joint to the customer ID of my customer table okay and let me go back let me just check this once again alright everything seems okay I'll click on apply and click on ok so with this we've almost completed our transformation now I basically have the details of the product of my customer has purchased with respect to every transaction so this becomes a highly detailed data so let's say I want to filter or group it with respect to the customer details for that what I'm going to do is that I'm going to create a new transformation known as aggregation Transocean now aggregation transformation basically as the names of this helps you perform various aggregation operation so here I'm just going to click on application transformation and then again click in the workspace so here I have an aggregation transformation created to this I'm going to select all the columns present here and then drop it so automatically these gets replicated here as well as linked now inside my aggregator transformation I have a useful option known as group by ok if you see the last column sure is group so I'm going to group by the customer details or the customer ID now again there are two customer IDs here so what I'm going to do is I'm going to uncheck this option present here okay now this ensures that there is only going to be one customer ID and it's going and my data is going to be grouped based on my customer ID as such so once this is set I'm going to click on OK but again let me show you one interesting thing now aggregation transformation can also be used to specify a value to a port or a column as such so let's say there's a column that is present here to which you want to create annual now can give you a better example let's say I have the details of my employees now I have their basic pay I have their HRA I have their TA and then I know how much tax has to be cut so to specify the total amount what I'll do is I'll just use an aggregation transformation wherein I'll specify the condition based on which the total salary has to be calculated so for that there's this option of adding an expression so let's say let me just show you how it's how you going to do that so the moment you uncheck the output option it basically assumes that this value is going to be sorry the moment you uncheck the input option it basically understands this value is going to be computed so it it unlocks the expression column present you if you click on this arrow option then you get the expression editor so here you can specify the expression based on which the value has to be computed now again if you look at the various functions that are present in aggregate or transformation this is the complete list okay there are a lot of options that you can perform so again this is something that you can definitely explore apart from this any expression that you wish to perform there's also a separate transformation known as expression transformation so again there are different ways to approach a problem as such so for now let me just cancel this and click on apply so with this let me just summarize what we have done till here we have taken the details of our product we have taken the details of our transaction and joint them based on the product ID after that we bought in the details of the customer and then we've joined the earlier details with the customer details as well finally I have created an aggregator transformation based on which this data is going to be grouped finally I need to create a target definition target definition basically is where my target has to be stored now again there are two ways to approach this first I can begin by creating a target definition from scratch now that's a very tedious work that I have to follow rather than that I'm going to take the second approach this basically is modeling the target definition based on a transformation I have already created now there are two advantage of this firstly it reduces of my work of designing the structure of my target table secondly it copies all the transfer all the columns as well as data types decisions associated in that transformation now rather than telling this let me show you how it start so if you want to model a target definition based on any transformation right click the transformation and go to the option of create and add target once you click on this a target definition gets created based on the transformation okay so if you notice here it's almost the same as my aggregated transformation but if you see I had unchecked the option for customer ID 1 so that is not present here now I want to modify this target definition now if you remember I had mentioned you cannot manipulate the source definition or the target definition in any other workspace apart from their respective workspaces so let's say I want to make anything this then I need to go to the target analyze a target designer workspace ok so helping you understand let me double-click this and if you go to the poor's option you cannot modify any ants this present here so let me go back to my target this target designer workspace and here let me bring in the target definition so I'll drag this and drop it now first what I'm going to do is that I'm going to begin by renaming this now I don't like the name aggr trance so I'm going to rename it TGT that is for understanding it's a target definition and I'll call it final ok with this click on OK and the target definition gets renamed now by default it has selected as the data database type to be Oracle now I don't want it to be an Oracle database file I want it to be a flat file then I can change now there are various different types of database files that you can create now I'm going to choose flat file for this now even when I choose flat file it's going to ask me for more information whether it has to eat delimited file or it should be a fixed width 5 if you click on advanced you have the option of even specify what the delimiter should be ok so let's say if you want to make any changes with respect to the columns and let's say you want to change the name of the column or if you want to set the column to be key all the operations can be done here so basically this is a workspace where you can modify your target definition so I'm not going to make any changes with respect to this I'll just click on apply click on OK and we have our updated target definition now if you go back to your mapping design of workspace first thing that you need to notice here is that the target definition present here is not updated so let me first delete this and then bring in the updated target definition ok so finally what I need to do is that I need to close this data flow so for that I need to link this aggregator transformation to my target definition again I can either select all and then join it or right-click and then I have the option of auto link auto link basically it helps you link to transformations and complete your flow now here it's asking me from which transformation so it should come from my aggregator transformation ok so as you see the name is similar now it is going to ask me to which transformation so it needs to get connected to my TGT final transformation so with this you know the next step is basically by defining how the auto link should happen either should it be based on name or based on the position of the ports for now I'm going to keep it as name because they follow the same naming conventions so if I click on apply now automatically the link gets created finally when I click on ok I have completed my mapping design ok now this is slightly complicated for me to get a complete view so what I'll do is I'll just right click ok let me just click on the workspace and I'll choose arrange all iconically so this will convert the entire mapping to an iconic representation so I have my customer table I have my product table I have my transaction table product and transaction is going to get joined to adjoin a transformation then this joint data is going to get joined with the customer data so basically I'm performing a double join here once this is done I'm going to pass it to an aggregator transformation through which I'm going to group the data based on my customer details and finally I'm going to store into my target definition which is a flat file ok now the final step of this is basically to save this operation now once I press control s it is going to save this while this is done basically it also checks if it is a valid transformation valid mapping assets now if you see here this operation which has happened so it's trying to validate it now it sees that the data flow is correct but again it finds her inconsistency now in my aggregator transformation customer name has an invalid reference okay so thereby it ensures that my map that I buy it has notified me that the mapping has become invalid so I need to go back to my aggregator transformation and check it again so this is why informatica is really helpful so even while you're designing a mapping it checks now so this is something that was mistake on my part I had to recheck the input option here because we I had shown you the expression option it has considered this to be an invalid thing so again click on apply the pink click on OK and save this so now you can see it has considered it to be a valid transformation because I have specified that the value of customer name is going to come as input I clear to here I hope you guys have an understanding so moving on now is the time that we go to the third phase of our solution okay in phase 3 we basically have to create a workflow involving various tasks and components which will help us execute our mapping ok as I had mentioned we have basically just defined a flow of the data from the source what kind of operation has to happen on the data and finally where it has to get stored now we actually have to initiate the data flow for that we need to create a mapping so let me go back and here launch the third third to last part of today's session that is informatica power center workflow manager okay so this is my informatica power center workflow manager okay let me just hold on to this one second I'll just give you a brief overview with respect to the two workspaces that we have missed out here now as you can see there are different transformations I have created now let's say there's a specific transformations I want to use across multiple mappings that I'm going to create let's say the same aggregation operation has to be performed on different mappings it's gonna get the same input it's going to give the same output and the same aggregation operation has to be done now rather than recreating it every time what I can do is that I can actually make this transformation as a reusable transformation this will basically ensure the same transformation can be used across different mappings as such remember how each of the source definitions gets added in the source folder and the target definition inside the target folder similarly if you make a transformation reusable it gets added inside the transformation folder this will make it easy for me to use it again and again now let's see this series of mapping let's say there's a complete mapping that I want to use again and again let's say I want to create a basic mapping and then from there branch out for different purposes then I can create that I can call that as some applet again similar to how you had a reusable mapping reusable transformation you can have a reuse will not be so the two remaining workspaces that is an applet designer workspace basically is used to work with rust with your reusable mapping and the transformation developer workspace is used to work with respect to your reusable transformations okay now coming back to our flow manager this is a workspace where we are going to create our workflow or basically you're gonna create a task which is going to help execute your mapping as such okay now again there are three workspaces here you have a tossed out upper workspace vocal a designer workspace and workflow designer now in our session we're just going to use the workflow designer workspace here let me begin by creating a new workflow so click on workflow option present here and you have the option of creating a new workflow now the naming convention for creating a new workflow is that it should start with WF followed by the name of the mapping M underscore life okay so I'll click on okay and I have a start I can present here this basically is helping me understand that my workflow is going to start from here now again this is something that is useful this is something that is very useful when you're working with multiple mappings because when you are working in the industry you don't just create one single mapping or one single operation as such you have a lot of data you have lot of analysis that you have to do so for that you'll be creating multiple mappings and these mappings can themselves be interrelated as well so this is just a simple understanding for you but again in real times in order when you're working with multiple mappings you're going to create a very big workflow as such so for today's session we'll just create a simple workflow so for now for create linking our workflow to a mapping you need to create a task known as a session task ok session tasks basically helps you on informatica understand that this is the mapping that needs to get executed so click on the session tasks present here and then again click on the workspace now it is going to ask me if which mapping should be associated with this session so I'm going to select the M underscore live mapping workspace mapping I'm sorry so once I do that automatically a session gets later now again this is not complete I know my workflow is going to start from here I know this mapping has to get executed so what there needs to be is that there needs to be a connectivity between these two as these would not get executed so select so dragged from start and drop it to session so this becomes a flow ok now my work flow is going to start and then the following session is going to get executed and in that session the corresponding mapping that we have defined is going to get executed as such now again it's not completely we need to actually specify from which data source the data has to come where my flat file is present and where the output file has to be stored as well so for that you need to go into the session properties for that double-click the session icon present here and then go to the mapping option now here you can find the details with respect to your sources as well as your targets and transformations now if you click on the products or let me take the customer okay now here it is going to use a relational reader basically because it's going to read from a relational table and then it is going to use a connection object now I have already created a connection object to my database so in case if you are wondering on how to create a connection object you can check out our video on informatica tutorial where i have talked in detail about creating connection objects and working with informatica power center as well so let me just cancel this now there are no changes here but let's say if you are working with a flat file by default or informatica checks in its default path for it's so so for these source files are such now this is not where the source file is present if you actually see my source file is present in my downloads folder ok this is where my source file is present so I have to update this power so copy this and then paste it here ok so this it will check for my transaction dot CSV file in this location up same I need to do with respect to my product table as well now one last thing that I need to do is with respect to my target folder now with target file I'm sorry so in my target file there are again two things that I need to it firstly I need to ensure that headers are present in my target fat because this is what helps me identify which column is for what purpose so in my header option choose as output field names this basically will set the field names as headers as well as the second thing that I need to do is that I need to change the extension of that okay by default what informatica does is that it creates an output file of the extension dot o UT but I needed to be dot CSV because it's a comma separated flat file so I'm changing it to dot CSV let's say you want to put present this file in a different location you can specify the location as well but right now I'm not changing this because I can help you understand where it is going to store this target file as well so once I click on apply I am done setting my session properties now again let's save this workflow and as you can see informatica is validating this as well so here you can see informatica has completely checked all the details with respect to my workflow and then given me a valid order so this is a very good option from informatica which checks every step of your process so every time you save informatica ensures that you've not made a mistake now again this is on a very high level till here what I have done is that I've just defined from where my data has to come what operation should be done on this data and where it has to be stored now there may be still some errors that occur while the actual transfer of happens so for that you have informatica power center workflow monitor now automatically when you execute this session workflow monitor gets executed now to start a workflow either go to the workflow tab and select start workflow or just right-click on the workspace and select start workflow once you do this you can automatically see the workflow monitor getting launched so here you have the details with respect to the session as well as the workflow as such so you can see here it has taken just four seconds for the session to execute now to get more details with respect to a session double fill corner and then you have the task details apart from that let's see if you want to see the source target statistics this is something that is really helpful for you to understand the transfer of the data between the source and the target now if you see here I had 10,000 rows of my customer 3,000 of my product and 50,000 transaction data okay now I have loaded all this data I have performed the required operation and finally I have nine thousand nine forty five rows present here so basically informatica has processed close to sixty five thousand rows of data and then converted it to just nine thousand five hundred and forty five rows in a matter of four seconds ok informatica is really powerful in process in terms of data processing this is just one example earlier I had processed hit coreos equal rows and I had performed almost close to twenty one transformations all it took me to get fifty thousand roses output was just me eleven so that's how capable informatica power center is okay this is to give you a simple idea again to see the output file okay by default informatica target path is going to be your installation folder inside so inside informatica 10.1 server Infernus koshered and this are these are the default path for informatica so by default informatica will check for the source files in informatica installation folder server in Fernandez core shared and source file and the target files will be created in the same directory with respect to the target flights so this is my target file okay now I do not have a csv viewer but this is a rough idea with respect to the target file okay now there are basically 9000 rows almost 10,000 rows present here so i'm not able to show you it completely okay but again i hope you've got a simple understanding of how it works now again I'm quite sure post the session you've got an understanding of each of the informatica power center tools and how they work with each other what is the role of each of the tools and how you can work with respect to them so I'm quite sure post this session I my team would be sharing the informatica installation linked as well so you can you can go on and install informatica on your system and begin exploring your data okay now with this we have come to the conclusion of this session you can just to summarize what we've started off we start off by understanding what is business intelligence we talked about extract transform and load then we talked about informatica power center and then we saw a demo now for more information on our informatica certification training you can check out our page yury korotkov slash informatica and get the upcoming details with respect to the informatica courses we have both we day and weekend batches and if you enroll and give us your detail today as a goodwill gesture from our side we can give you an additional 15% discount since you've all stayed for this long I would have written a recommend that you do that so just given your details here and our team would correspondingly get in touch with you now if you're looking for blocks that can help you can check out our ad ricardo dot co / blocks and find all the details with respect to our informatica blocks you can check out the interview questions you can find it more details on the transformation as well as get an insight with respect to what is informatica as well so here's the installation block that is installing from erica i9 is steps so with this you can get started with informatica on your system itself now finally if you're looking for more details on informatica you can check out our informatica power center playlist wherein we start about informatica from scratch apart from that please ensure that you have subscribe and select and notify to our channel to get the latest updates with respect to the a drakkar views so any future events or live sessions we would be creating any videos that we will be updating you would definitely be the first to be notified so with this we come to a conclusion I hope you had a create experience thank you and good you

Channel: edureka!

Views: 196,352

Keywords: yt:cc=on, informatica tutorial, informatica tutorial for beginners, informatica training, informatica powercenter tutorial, informatica powercenter training, informatica etl tutorial, informatica powercenter 10, what is informatica, informatica introduction, what is business intelligence, what is etl, extract transform load tutorial, informatica transformations with examples, informatica training on transformations, informatica training videos, informatica edureka, edureka

Id: 3scD3llibJA

Length: 58min 57sec (3537 seconds)

Published: Mon Sep 18 2017