Data Warehouse Tutorial For Beginners | Data Warehouse Concepts | Data Warehousing | Edureka

hey guys my name is Gordon and on behalf of ADA Rekha I welcome you all to this webinar on data warehousing and business intelligence I'll be your instructor for today and I will be teaching you why any company needs to business intelligence and thus thereby do data warehousing so without wasting much time let me show you the agenda for today so these will be the topic that I will be covering in today's session and this will be the order in which I will be covering them ok the first topic that I will be talking about is what are the needs of business intelligence and after that I will talk about the need for data warehousing so business intelligence is one of the most important aspects for any company to grow well and do good right and data warehousing is among the most important activities of business intelligence so that's why these two things are interlinked and that's the connection these two have so you can think of data warehousing to be a kind of a subset of business intelligence so I will talk about these two things enough that I will talk about the key terminologies that are related to data warehousing architecture right and some of the key terminologies are those of OLTP and Ola the differences between the two okay the OTP somewhat very similar to the databases and OLAP is what represents data warehousing so when you understand the difference between the two you will also understand the difference between a database and a data warehouse you will also understand why a data warehouse suits this is intelligence more than data base so that's about these two topics and then we'll talk about ETL so ETL stands for extract transform and load eatle is a strategy to convert the data from your database onto your data warehouse right so moving the data from one place to another that's all done by ETL so we'll talk about ETL and detail all right and after that I'll talk about water data martyrs and then what's at metadata now these two things are two topics which I can only explain once I have given you an introduction to the other topics all right so any of you tie it out that you have during the session you can ask me at that time and I will clear them right away and once I am done teaching about all these four different topics related to data warehousing architecture I will show you the complete architecture and the complete the life cycle of data and what kind of insights your company can get and what can our advantages you can get out of data warehousing right so data browsing architecture will be the last topic in my presentation and after my presentation I will show you a demonstration of creating data warehouse where I will import data from database and sort in a data warehouse right so this will be the topics of today's session I hope at the agenda is clear everyone if you all agree with me please start knowledge that and also to acknowledge the fact if you can hear my voice and see my screen so if everything is fine I can get started with this session you can acknowledge and put any of your doubts or queries during the session inside the chat box that you see on your right side so I'm going a couple of acknowledgments from my everyone all right so Rodney says yes Roger says yes all right guys and a couple of more people are also able to get my voice and see my screen great so since the agenda is clear everyone let me get started with the first topic that is what is the need for business intelligence okay so will understand why business intelligence and data warehousing are among the fundamental and the foundation for any company's success so why do we have to go for business intelligence right business intelligence is the activity which contributes to the growth of any company and there are also so many MCS which have been established with the past few decades now how did that happen they just didn't happen by luck right so there were all small ideas there were small companies that start with a small idea and then they grew bigger so that's what any company that wants to do good that's what they do the first thing is they plan what they want to be and depending on that plan then they start gathering data okay now once they gather data they know they're in the right direction now so they know what to do and how to do it and then they do further data analysis on that they make up their plans and they come up with strategies they come to know what is the important thing that needs to be done and all these things so when they finally have a conflict and then they execute it into a business action and once those actions are taken then they're all good right that's when the business starts to grow that's when the company gets back all their investment and that's how actually any company grows so any company that has done well over the past few decades of beaut Microsoft or Google or Facebook or Amazon Facebook's all of these companies that have all grown from small ideas and they've become something big right and any startup that's also trying to do great nowadays even they have got off the same strategy and the same plan this is a very common thing and this is something that everyone knows okay but this is not whatever you know come to teach all in this section what I've come to teach all is something about data warehousing and that is one of the most important strategies or activities which is part of business intelligence right so before I talk about data warehousing let me go into details of business intelligence so what exactly is business intelligence BI is the act of transforming raw or operational data into useful information for business analysis right so BR your stance or business intelligence that's the short form and yeah it is the act of transforming any law or operational data so when we say R or operational data it's basically the data that you've collected the data that you have about your business so it can be revealed with your company starting from scratch then whatever data you've gathered so you've kind of got to take that data and convert that into useful information right so that you can plan and make strategies and if you are a company that's well established then you have to look at your passes us how your company has done over the past six months or the past the last quarter or the last year or two and then may come up with proper plan for your future so when you do this then this entire access call has business intelligence and how does it work and this working of business intelligence is with respect to the ID technology okay so VI which is baitul data warehouse technology okay this is the key term that you got to remember the VI is based on data warehouse technology it extracts information from a company's operational systems and the is extracted is first transformed and remediates transformed it's cleaned and integrated and then it's loaded into data warehouses now the thing here is there be data in many forms it can be the form of flash files you can be the form of databases such a running company and if you are trying to do good ok if you've been working for a number of years then you'll have data about your past success about your sales data your marketing data your expenditure all these things you might have served on any form maybe the form of databases may be in the form of for Excel flat file so all these things so these make up your data source right so you have this data over here and this data is first transformed ok it's of course it's first extracted then insert the data warehouse it's transformed we just cleaned and integrated ok so once it's transformed then it's ready for you to do your data visualization or data analysis it's in a form in which you can get inside and this is the data which the end users will be using so you will have your data analyst in a company right your data scientist your data analyst and all the other people your managers your Rob and the other guys you call the big shots in your company so all these people they'll be getting they'll be using this data to make your analysis and that the role of data warehouse it is between these two end points and this serves as the basis or the springboard for success and finally since the data is credible it is used for business insights yeah this is again something that I just spoke about right so now you'll have a better understanding of how business intelligence works right so you all know that this is intelligence is something important and how important and how good it is how does it work is what I've explained in this slide so you all guys all agree with me here anybody has any doubts Rodney Rajesh Jacob okay pretty good all right nice okay so I have a question from Jacob Jacob is asking is it our house the only thing that's needed okay Jacob this is just an overview of business intelligence okay the rough diagram and it's actually not just data warehouse so data we're also something that I'm concentrating on today's session okay so that's why we have data webs there's another important step called of data visualization so that visualization is done by end-users right since it's done over here on mention this diagram but what you got to remember is data warehousing is probably the springboard without data warehousing the visualization cannot be done and data from the source is right from here it cannot be directly used for any other purpose so that's the role of data warehouse so that's what data warehouses and yeah I'll clear take off now okay great that's fine so let me go to the next slide then another you know what exactly is business intelligence let's go to details of data warehousing let's analyze the challenges in achieving business intelligence so first of all why should be user data warehouse because data collected from various sources and certain various databases cannot be directly visualized ok now look at this immature in this diagram we have different databases like Oracle you have the sap base you have the - of sequel server and then you have other databases like sequel database and all these things ok you can also have flag files in this list so all these make up your data sources you as a company can show data everywhere so if you're a small company you might just deal with Microsoft Excel and you might just use small analysis tools okay but occur a big company that has a lot of data coming in so if you're a retail company then you'll ask for details about your sales your marketing and what's been your growth so for all these purposes you need big databases right so all the data will be stored in all these databases ok now the problem is some teams in a company might be using one database and the other teams may be using another database now the biggest problem that people would find while they are doing visualization or doing analysis data is they're in different databases and they'll have a tough time integrating them right now that's where data warehouse comes in and that's where data warehouse course data Pharos it will get data from all these databases and then processes that data and brings it in a form it is very easy to do visualization ok that's what the second point says the data first needs to be integrated and then processed for visualization takes place now this is the problem that you have with the regular databases ok the data from here they cannot be directly use of visualization and since data warehouse can do that since it can integrate data from multiple data warehouses and since that data can be processed easily since it brings the data in a form which can be easily visualized that's where it arose has the advantage that's where its course so that's the problem with data base and that's the advantage the data by rows are in fact little arrows it's more like an act it's a discipline which is followed by people ok these are actions which are adopted and studies which are taken ok that's what rate of warehouses and that is the role it plays in us doing very good visualization all right so now there should be a little more clear few people as to why data barrels plays the key role in the whole BI aspect right ok great let's go to the next slide then now let us understand in details what a data warehouse is now a data warehouse is a central location where consolidated data from multiple locations ok or databases that's what locations me from multiple locations are stored now this needs this is exactly what I explained earlier right so you have got a DES that's coming in from multiple data sources you have all the data you consolidate all the data into one single place and the data barrows is mated separately from the organization's operational database now TWh your Stanford rate of warehouse all right so and here it says that data warehousing is made in separately from normalizations operational database ok and yeah the DW has your Stanford data warehousing and the reason data warehouses are stored separately from the operational database is because the reader should not get affected so you will have your operational data on one end ok where all your legacy data will be stored where all your Raymond probably in your real time data will be stored so all your transactions all your sales all your marketing order operations data all these will be sold in one place and in during the alpha-beta warehousing what you're doing that when you are making analysis when using the data you don't want that to get corrupted right so it's more like a backup so for backup to our purpose your operational data is separated so you have an operational data you keep it in one area and then you create a new database okay in fact it's called a date of bed house okay so you get all the data from multiple sources or maybe from a single source get it into a data warehouse and from here you do your analytics so the process of getting the operational data into your data warehouse that's called extraction transform and loading ok now when you've done these three things you form your data warehouse and from your data warehouse you use the OLAP strategy okay so all apps and for online analytical processing so you use this OLAP or strategy or with this analytics processing for the business users to do analysis so it's there in the name right stands for analytical processing so the business users water analysis they want to do they do it because there is the the observe pull up and then along with the analysis they can also do polarization for visualization you have various the tools like tableau and click you write they are some amazing tools so you can get this data you can get it into the data warehouse and the data warehouse also it can be sort in some kind of database it can store this data back into some kind of Oracle or sequel server or maybe even in Excel and when you have stored there then you can do your lab activities there and also you can import that data into your various visualization tools like tableau or Rock click view and tasks you can you know get insights you can get insight into your data you can download presentations during your board meetings you can show your findings to your superiors or your managers into all these things so that's what a data warehouses ok and then the next point we have your end users access it whenever any information is needed yeah so this again the same thing right so once the data comes in from the operational system it's stored in the data warehouse it stays there so this data is not going to change so whatever change you want to do your operational data that can be done ok you can modify the data you can update it you can delete data here you can do all these things but once your data comes in your data warehouse it cannot be deleted ok you can maybe modify things the worst case scenario you can modify data here ok but it's highly advisable not to but of course you can so that's the thing but yeah the keep on you got to notice the angelus can use any they can probably access data that is 10 years old or 20 years old and all these things and how can they do it by using Ola so they can do the analysis and they can run it over different times right so these are a series of snapshots so based on you can find data analysis like what happened at this particular day on this particular York all these things you can see what kind of product was sold Namie customer spot which product all these details can be easily gathered and access from here so that's what we save and you know the data can be accessed at any time by the end users or the business users so the business users here are typically those managers it can be managers or people who are leading board meetings who are making a plan for the next quarter or the next half year or the next one year and all these things so guys yeah that that was a question which Jacob asked eyes so Jacob ours would be the end users would be the business users so that's what they are okay people will be using this data it can be even data analysts or data scientists and all those people right guys are you clear Jacob okay great and there is no to get the data whereas your it is not do it every time when new data is added to the database so what this means is you have data coming in to your operational data ok this operational data will get updated every minute probably every second if you have a 24/7 working sales team then they'll be making sales around the clock right so as and when any sale happens the data will be added to your database your operational data but that not necessarily needs to be added to your rate of arrows also so what you find in your data warehouse is legacy data right its historical data which you can use to perform analysis or all those find inside the operational data if you have new radar coming in here this has to be imported and this has to be moved to your data warehouse first and then once it's moved your rate of warehouse from here it can be used for analysis and all these things by our end users so that's what this diagram here means and that's what the last point also means okay data warehouse is not loaded every time you read are added to this database okay so I hope it's all here Rodney Rodney and Rajesh okay great so fine then if you guys are gonna said what data warehouses I can go to my next slide and I'll talk about the next topic so was about data warehouse now let's look at the advantages of a data warehouse when we compare it to any database or just regular flat files and all these things the first advantage is that subject questions can be answered by storing trends so this is the biggest benefit that you can get right your raw data analysts and data scientists they can answer strategic questions they can read the past data they can predict your future also by coming up with by having their strategic questions be answered because friends can be analyzed they're using the data warehouse is basically the Riddler stood in an operational data also but it is that so easier to study trends on your data warehouse rather than data base ok because Rajat sure has a question guys Rajesh nothing why not a database what is it that data where else can do that database cannot do so guys that's what I was answering correct Rajesh remember the first thing is let me go back my previous life was this ok so you have an operational data here so you have all your data here which is probably legacy which is even real time all these things will be present here but in your data warehouse you only have your legacy data you only have the historical data you won't have real-time data but that doesn't mean it's not you know it's any lesser than operational data since you have your data completely your you have the freedom to do your analysis and you also have your freedom to do your data visualization ok so that's one advantage and the other advantage with the data warehouse is that your data will be coming in from multiple sources right it will have water coming in from multiple sources your tables will not be related to each other even if it's from the same greater risk you'll have multiple tables for multiple teams right and you can't easily integrate all these tables because they'll be separated ok that is one big problem that you will face when you are doing the analysis or visualization but in a data warehouse it will be stored such that all the data will be interlinked right could be related by using schemas or all these things so you have a different schemas X star schema snowflake schema galaxies schema and all these things so you have all these dimensions and facts all these concepts using which you can relate your row tables you can relate your data so all your rows and all your columns your which are unrelated which are sort of separate databases or in separate flat files separate tables they all be integrated they cannot be stored together so there'll be a relation so every single row or every single table will be linked with each other and when you do an analysis like that then you can probably pull data across the database right so whatever it has stored across the various databases you can put all that data and link them all together by running one query and you can get all those details in this store by just running one single query using your data warehouse so that is the advantage Ratish right so did you get it Rajesh this is the second advantage okay that's why business users they prefer to use the data coming in from data warehouse so this is a more structure and this is the more related data when you compare it with the operational data and the basic data source all right fine fine fine very good very good as it I hope you've cleared your doubts I hope that the same thing with even others right okay even with your great grid Rodney is telling now I've got reading okay fine fine ready night that was a very good question good you asked me that and yeah I'm glad you stopped me because I could explain it in a better way so anyways moving on that was about tour data warehouse and talking about the advantages of data warehouse I spoke about the fact that you know you can other strategic questions by studying all the trends by asserting your past data you can you will have all the graphs you can have the pictorial representations right you can see what was whether the trend is growing or not which practice getting sold how better is getting sold all these things you can easily read by using a data warehouse because data warehouse makes your data more readable information so that is the thing so you guys must own the different wien data information right so information is something that is processed process the data is called information so information is easier to understand easier to relate to and easier to use now that's what data warehouse does it takes you one step closer to information right so that's the advantage and yeah the other thing is data warehousing is faster and it's more accurate yes this is something that's completely true because in your data base you will have loads of data you will of course you'll have a historical data and real time data but the thing is it's run going to be as fast as our data warehouse data warehouse you'll have links there you will have we'll have tables you have relations between the various tables and because of all these things you can easily gather and you can now easily access data here and the data that is gathered from your also more accurate because they only much change because there's not going to be any question of real-time data coming in and changing things around right so you may live so whatever you know processing or analysis it's done based on the past data that is stored in the data warehouse and that makes this data more accurate it makes it more stable so stability is the key word here and stability is not something that you can have all the time in database but you will have it with the data where or so that's the second big advantage and in fact there are many more advantages right so data warehousing is something that you guys will understand when you start implementing so in the demonstration that I'll show you later today that time you'll understand you know you lunch and the other advantages with data warehousing ok and one important point that you need to notice that data warehouse is not a product that a company can go and purchase ok it needs to be designed and it depends entirely on the company's requirement so like I said your data base is something if an answering right your database or your various own your data your data source is something that you have to have and then your data warehouse is something that is designed and which complete depends on your company's requirement based on your data source based on the requirements that you want to get out of your data source out of the error that you have in your data source you can come up with a way to design your data warehouse right so data warehouse is more for concept and strategy and it's not an end product it's not a tool or something that you can use you have multiple tools to implement data warehousing and the thing you go to notice data warehousing is not a product ok so it's a strategy that you adopt to make your data more readable and make your data in a better fashion ok so that's the biggest advantage with data warehouse so look at this guy here ok he'll just run one query on the data warehouse ok now what the data warehouse will do so the data is taken from the operational systems alright and in fact if there are multiple operational systems then all those multiple data from multiple operational systems will be integrated together and then that will be standardized and any inconsistencies there and that data will be removed ok now these are the three important things the data has taken from the operational systems and that data if there are multiple operational systems those will be integrated okay and then the real will be standardized and any inconsistencies will be removed and once all these three things are done then it will be stored in an easy format which can be you know which is very suitable for analysis and access and that is what the data warehouses so whenever you run the query on this kind of data warehouse which is the process on which is ready in such a fashion you can get the result quickly and this result close to be more accurate all right so this is a big advantage with the Year data warehouse so I hope but this clear it's a pretty simple concept and it's just overview of what I explained in the previous slides right right Jacob cadre Rodney all right okay so moving on there are four important properties that a data warehouse has okay and the default properties are based on what Bill in one set bill inmon is the father of data warehousing and initially you define data warehouse as a subject oriented integrated and myriad and non-volatile collection of data in support of the management decision-making process okay so when we say subject oriented it means that the data will be categorized and stored by the business subject rather than by the application now let me get back to this point after I finish these three ok now this the most complicated point okay now talking about integration right he said that it has integrated so the meaning here is data on a given subject is connected from disparate sources and sold in a single place so this is something you people known it has collected from multiple sources and we are all stored in one single place so you don't have to you know go about searching for data and different tables or different sources and all those things and then your data it is time variant it is stored as a series of snapshots each representing a period of time so when you do your analysis you can do it based on a series of snapshots of time okay you can see what was your raw company status on this month or that your or on this month this year what is the progress that has been made or if it's not a progress if it's the same if your raw code hasn't stagnated then you can find out what are the metrics what are the reasons why that has happened you can find all these things and you can look at all those idiots from a time approach right from a time variant approach so is what data where O's the advantage sure is okay that's one of the properties and the advantages are you have and then data is non-volatile the data in a data warehouse is not updated or deleted so this is what is the other property that I mentioned earlier once the data comes into a data warehouse it cannot be deleted or run either can it be changed in fact it can be updated but the process of form to update it is a little complicated okay but of course it can be updated and deleted so that's the thing but it's highly recommended not to operate okay so that's the advantage with the data warehouse and since it will not be changed there is no question of quit getting corrupted and that's why doing analysis and all these things are you know a better option now getting back to the first point we you said that it is subject oriented right there does categorize and store by business subject rather than by the application now what this means is the data here will be stored or the data that you will your that you retrieve from a reader warehouse right you will get in the form that you wanted to now if you want to go me an example of that let's say that we are dealing with a radial company and in my retail company I have a marketing team I have a sales team and I have a Operations team and my system kind of keeps keeps track of all the sales that happens over a period of time okay let's say the last one month whatever sales they've done they have showed all those details and then you have your operations team which will make sure there is a smooth running of all the process once the sale is done gradually the activities involved right like shipping the product and you know all these things shipping and coordinating via transferring activities and all these things and then your marketing team is or someone is probably that team which would take care of for your sales which would ensure that the right leads come in to ensure that the right people get the right the kind of service and it's all about acquiring more such sales right so your marketing team is on top of the funnel and they do all these things now if you want to integrate all these details if you want one single view of them and you want to find details such that in this particular month what was your dose is and what was the kind of operations that was done right what kind of service was given to those customers and from how they became our customers so when we the question of how something related to marketing so if you have a question like this where three factors are involved then at that point of time it's your data warehouse which comes to the rescue because many order questions are related to this particular time and these three different term metrics say is marketing and operations then all these things can be integrated and you can get one single view similar data warehouse this is what a database lacks correct so you know integrating all your different data sources and you know storing them together and making them ready for any time axis is the biggest advantage and the most important property of data warehouse so if you guys had any problem the previous slide then I'm pretty sure this is something you have really understood after this slide because this is probably the epitome of for data warehouse right these three properties if you understand these four properties then you are pretty much ready to understand the next part the next or you are ready to go to the next step and data warehousing right so finally you should have understood how important all this is why I know how important business intelligence is and what can improve the data warehouse place you can just think of how among us our deal a data warehouse is correct yeah so a couple of people are satisfied with that so in order to spoken about the properties let's go to the next slide and okay now we have to talk about key terminology okay so right now we've understood data warehousing from a higher level okay now let's dig deep let's go to mode basics okay let's understand the key terminology that are all related and that are involved in a data warehousing so first of all we have LTP and OLAP okay now there are four things I will talk about the differences between OLTP and Ola okay then I'll talk about UDL I'll talk about data Mart and then finally about metadata okay so let me go to the first topic that is Ltd versus OLAP okay so in this part which is l DB over DT stands for online transaction processing okay now this is something that is the representation of data fir database if you're running any kind of queries on your database then that's called online transaction processing okay and then o is last stands for online analytical processing and this is the property of a data warehouse so any kind of query or any kind of analysis that you run on your data warehouse that's called as an OLAP activity correct so let's go to the differences between the two so first of all any data that is stored in a relational database right in an which involves OLTP that contains the current data as well as past data okay current data as well as path data but with respect to data warehouse and while performing an OLAP you will be dealing with only historical data here okay it contains only historical data and the data that will be stored in your database okay when you use the OLTP then those queries will be useful and running your business okay when you have to run your business like if you want to store the data of the number of sales that has happened today like every time a field happens then your records in your database as we updated right so that's what we say so when you update your record with the latest details of your customer then that is what is the meaning of useful and running the business okay but in order for something that is usually analyzing the business so here the kind of activities that you would do is that of finding out details like at what time how many customers bought the products or at what time which customer but which all products all these kind of questions will be answered with as both hola okay and then the whole OLTP model of accessing data of accessing or querying data on a database is based on the entity relationship model okay but whereas with the data warehouse it's based on the star schema or the snowflake schema and the fact constellation schema okay so it's called also called as the Gatsby schema so all these three things will come into picture okay and then your relational database it provides a primitive and a highly detailed data so since you'll have one database if you run one kind of a query like a select star from this particular table then it would give you all the details that are stored right so you cannot filter too many details with respect to the data that is stored in a database okay of course you can but the level of you know the filtering and analysis that you can do is not that much so the reader that you will get back from your query write the result that you will get back from a query will be highly detailed okay and it will not be exactly what you want so it will not be that accurate but whereas with pull up when you do an OLAP on a data warehouse it provides summaries and the consolidated data Neverland point to you exactly what you want to look for correct so it will it's a very processed data and it points to one particular aspect which matters the most so that's what this is and then the oil TB you use this for writing data into the database okay so ever like I said the same sales example whenever a new sales happens your database has to be updated right with the new records of the product sold of the customer who bought the product and all these things so you basically use it for writing data into the database but your data barrows is primarily used for reading data from the data warehouse so right into the data warehouse or something that is done so that you can do the reading from the data warehouse ok the primary concept here is to read the data from the data warehouse and to do the analysis and all the visualization actually but with the database it's more of writing the data into the database alright and the size so speaking of the size a databases size would raise anywhere between hundred me to 1gb ok and this is also a very big number 100 MB is a very less number I would say and one tip is also very less so it would typically be much more than this but come back to this range if you look at a data warehouse a data warehouse is ranges from 100 GB to 1 TB correct so your data wells will have all the historic Durant and it will have all the relationships between the different data right such that so you can do your analysis straight away so since it makes all the data more efficient and stuff the data here the size ranges from 100 GB to almost 1 P so that is what our data warehouses and that's the part of data warehouse all right and I can actually show you the difference between the two in today's demo session ok later during this session I'll show you that the size of the source file that I have added with the different sizes of the two files that are they using as the source and then after the UH processed the data are all sorted in my data warehouse I'll show you what is side of that data so there will be a big difference between the two okay and the red arrows will be modes in the database I will show you that aspect later all right so and that's about this point and then yeah of course this will be fast ok database is fast and it provides high performance alright so your data where owes of course it's not as fast as your database but it's how you sexy it's highly flexible because it gives you different views so you have something called as the OLAP cube right so using the OLAP cube you can get the you can look at insights from different angles different perspectives and different views of data we get so that is the big advantage sure okay and the number of cycles that is accessed it is intense but whereas with the data warehouse the number of records access is in millions all right an example of this can be all the bank transactions made by a particular customer so if there is one customer and whatever transactions he's made you will get all those or you know all those videos right so supposing take the example of any email statement that you request for any email statement or a bank account statement all these things so they are an example of OLTP so whatever radials are present in the database those will be given to you but whereas the bank transactions made by your customer at a particular time okay this is a most filtered query and the answer also will be a very accurate and point Q to exactly that particular question so that's what an OLAP is so it's normal to be one overview here is one exactly point you to what you want it's not very much in detail but it's more accurate correct so that's what an app is and that's the difference between LTP and OLAP okay so there are two strategies that are used for you know accessing data well DB is used for accessing data in a database and we'll appreduce for accessing data in a data warehouse alright so I hope I mean this is cured everyone all right guys okay if you have any doubts then that will be cleared by this slide okay because the examples are there for both these strategies so if you want more examples of an OLTP then one would be that of a supermarket server which records every single product purchase at that market okay so every single perk in their history or probably in the last one month all these things can be accessed using your OLTP okay from your database so you don't have options to do much of filtering here and then another example that of a bank server which records every time a transaction is made for a particular account every time or transaction is made in a particular account the data will be updated in that table and you can create that kind of data and you'll get that result okay another example is our railway reservation server which records the transaction of a so whatever equities that passenger does so all these things will be recorded by the reservation server right so this is one example but when you look at the OLAP there will be much more detail the query juror will be much more detail and the answers will be much more accurate and very crisp okay so an example is a data from bank manager wants to know how many customers are utilizing the ATM of his branch okay because maybe based on that he may take a call whether to continue that idiom or relocate that idiom to a different place right so this guy this bank manager would want to one of us for understand if there's any use and having the ATM in that place are people using it correct if people are using it then how many people are using it at what time are they using it or would it be better to have the ATM in a different location where it's much more easier for people to access it so all these kind of questions will be answered only if you have the oil up in place okay only when you are a data warehouse in this not with the database so another example is that of an insurance company that wants to know the number of policies each agent has sold okay this will happen a better performance management of agents so you'll have multiple agents in your company and you'd want to know their performance right you know you would want to know who is the best performing agent you would want to know how they're performing why is this person performing better you can you know optimize each of their performances so all these things can be done with the help of data barrows and they can be done with help of Allah right the OLAP strategy the OLAP activity that is done on a data warehouse so that is a difference between the two right so I hope you're getting the difference as your guys right OAP and OLAP okay since it's all clear let me go to the next slide now the second important terminology that we have with data warehouse is that of ETL extract transform and load so by going by the definition usual is the process of extracting the data from various sources transforming this data to meet your requirements and then loading the data into a target data warehouse okay you are exciting the data from here right you're extracting it and then you're transforming it into the way you want in a more readable fashion in a more relatable fashion then once that is done you load the data into a data warehouse and the whole process of for getting rid of from your data source to your data warehouse this done by the EDL the activities of extraction extraction transformation and loading so we have got popular tools for for this very process so you have tools like talent informatica you have open all these things and informatica and talent are probably the most popular tools for this process for extraction transformation and loading data into a data warehouse right so this is something that usually understood by now and it out guys because I don't want to waste much time I want to go to the next slide and teach the next concept ok great so the next one is data Mart okay now if you've understood so far to LA ETL then half your job is done because data Mart is something that's very close to a data warehouse and you don't have that much of a difference when it compared it to our data warehouse but the basic difference between the two is the data Mart is just the same data warehouse itself but a smaller version ok so let's look at the line the definition here the data Mart is a smaller version of the data warehouse which deals with a single subject ok the data Mart's are focused on one area hence they draw the data from limited number of sources and the time taken to build the data match is very less compared to the time taken to build a data warehouse now to give you an example or an explanation of this in your data warehouse you will have all your details right all your raw periods that you have so this itself is more refined ok but your data mods are smaller versions of that data warehouse which is used to satisfy only a certain users supposing you have one particular user base ok that is your sales team or your sales manager who wants to use only or receives data then that can be done with the help of a data Mart and similarly you have a marketing team who wants to access all the marketing data then that can be done by using a data Mart ok and then again you have other operations team that wants to do wants to access the operations data then you can give them only access to that data by using data Mart separately ok now the need for data Mart is first of all your data warehouse will show all the data ok your sales data marketing data operations data so all the data coming from different data sources so you'll have multiple data sources right all that will be stored in one single place and from here this will again like separate data sources right so for the sales team this will act as a data source for a marketing team this lap as a data source and then for the operation seen this Mart will act as a data source now the now as in the question as to why we need them it's to you know probably more security to enable more security and integrity because since all your data will be there in your data warehouse and if you let all your your entire company access the data warehouse they will get access to all the details and all the all the work that is done by other teams okay so there might be multiple teams and every multiple strategies which you might not want to reveal other rates so at that enough time you can divide your data warehouse such that only this particular users okay your CL user or a particular user base gets access to only certain data from your data warehouse so at that kind of a time a data Mart is useful or you can have a second data Mart for only another set of user base for example your marketing users so they will get access only the marketing data from your data warehouse right and then your operations data similarly which can be accessed only by your operations force so that's the different advantages that you have since the data is all divided it's all stored separately different people have access to different different parts of your data warehouse so this probably brings more advantages right so that's the data Mart and that's why I told you that you know if you understood so far till then half your job is done but data Mart or something like that extends the functionality of your data warehouse right so that is the thing and stinking off the differences that is there in this table the data warehouse will store the enterprise-wide data right the enterprise of the enterprise wide data whereas data much will store it departments wise data there'll be multiple departments in the whole company in the enterprise and they will sort department-wide data and then the data warehouse it will have multiple subject areas okay but a data Mart will have a single subject area there'll be multiple data sources in case of a data warehouse okay but in case of data Mart's they'll be limited data source in fact they can be just one data where all right this will act as a source pure data match but however we have gossiped limited your because not always do you need a data warehouse there are also instances where your data source itself as an input data model okay now that is something that you will answer next slide okay so just don't get transfused when we see that limited resources it does not mean this is the only source they can also come from a data a property a source like a flat file or from a data base and all these things all right and then a data warehouse it occupies largely this of course because there is a lot of data on to price where data will be stored your multiple subject areas will be dead because of that there's larger data here at state and that's why there is greater memory that's occupied okay but in case of your data mod it occupies limited memory because it's very crisp and limited to only a particular department okay and then the other thing is data where else is long it takes longer time to implement but a data Mart is a takes very short time to implement it is once you have all your data warehouse or stuff in place you can easily divide them by creating different data mass okay so be a tough part is your data well so once you've got a data warehouse ordered you can easily from your data mass in fact there is even the other way there are also practices where you first bring your data mass and once you've done that after that you create one single repository and that's when you create a data warehouse so there's also two approaches here one is the top/bottom approach and the other one is bottom-up approach so those are the two things and I'll go to details about these two approaches in my next session ok of course I can't do it today because we have very limited time all right so moving on to the next slide ok now speaking of the different types of data mass ok this is what I was talking about in the previous slide so you have three different types one is a dependent data Mart the other one is independent data Mart the third one is hybrid data mod you are dependent data matters are the data's first extracted from the OLTP systems and then populated in the central data barrows and then from this data warehouse the Ritter travel to the data Mart so look at this example ok this is the standard practice or the year the default approach where you have an OLTP source then you get the data into a data warehouse and then from the data warehouse you form a data model ok we'll have multiple data mass where each different Mart will have particular data from the entire data warehouse ok this is the first regular approach and then you have the independent data Mart which is a slight variation compared to dependent so here the rears directly received from the system okay you don't have a data barrels in place over here that is what this line means and this is suitable for small organizations or smaller groups within an organization so basically an organization which is very small it might not need to go to the trouble of creating a data warehousing stuff so you can just have an OLTP source and from there they can just get the data in onto a data Mart and they can use for various purposes okay that's what we have an independent data Mart for so that's the difference it just does not involve a major data warehouse so directly the data goes to a data Mart in fact you'll have multiple data Mart over your you'll have great or not one dat amount to one stuff okay which they coming directly from the OLTP source and then you have a third type which is the hybrid data Mart so by definition you might know what this is right by your the name of by the name itself is pretty obvious it's combination of these two the data here is fed from both the OLTP systems as well as the data warehouse okay so look at this example for that tour instance you three are here it's coming from the ODB source as well as from a data warehouse so this is what the hybrid data models and depending on your company depending on the size of your company the requirements of your company or your organization you can choose one of these you can model your entire database and data barrows in any one of these models either the dependent or the independent data mass or hybrid Radames alright so that's about the different types of data model so moving on to the next slide we have something called as metadata now people here from programming background or from the technology background you might all be aware of what America does metadata basically is defined as data about data okay it contains data about where your actual data stored supposing you have your raw data right where is that data stored what is the size of that data so these are the answers to these kind of questions is what will be Plus on your metadata your metadata will contain the location of your actual data it is not n the size of your actual data it will contain details like which was the source it came from and when was it created all these details will be stored in your metadata right so that's what made our IRA's so that's how different our metadata is from regular data and a metadata is specifically in a data warehouse it defines the Soraa there is a flat file a tional database and other objects so the reason that we give so much importance to metadata in a data warehouse is because take the example of any company that's having a 24/7 business okay they have a rolling system that works throughout the clock the 24/7 they are sales coming in data will be going into your database okay now in this case you cannot always you know keep adding data into your data warehouse because you know that data warehouse is not real-time correct so you have to manually update your data into your data warehouse probably at every day at a particular time maybe at 6 o'clock every day in the morning or maybe once in the night at 10 o'clock or at basically at regular intervals you've got to so data into your data warehouse and this whole strategy becomes difficult because you have to do this process every day so every day when a guy comes in in the morning he has to look at the new data that has come into your data database correct and then from that data base which is data source he has to add that adagio data warehouse now this process becomes difficult that is where a metadata comes in handy with metadata you define the address where your data sources okay you define the address of your raw of that file from my data's coming in or your relational data from measure data is coming in and then you can also impact us or the metadata of your raw data warehouse where you want the data to go so it basically Ellen you know see a lot of your time okay and this is the most common and the most you know unspoken fact about data warehouse so any professional that's dealing with data barrows you would always be using metadata because it saves a lot of time because every time you cannot be importing data from a database you always have to get it from your metadata by defining rules and defining your source and your targets correct so mirror it has gives lot of your time and this is something I will show you a demonstration on ok and when I show demonstration you will understand how easy it is so we just read the rule says that you have your source over here and then you have your destination over your target over here and once you've written this thing you don't have to go back here so every day it might pick up the data from here and it might move your data warehouse and all these things it will update all the new details which are present in your database and it will add it into your data warehouse so that's what a metadata does it's a very big advantage and it's one of the best sergey's are being okay and then the final point here is metadata is used to define which table is source and target and which concept is used to build a business logic called transformation to the acts alone but yeah this is what I told you right so it's used to define which is your source and target and how to build your business logic called transmission so transmissions basically your rob act of converting your source data into the form that you want to and what is the logic that you use correct so all the different the filter criteria all the transformational criterias all these things can be also done using the metadata correct so even the process of forgetting data from your source your destination it can involve extra additional steps which can save your time and default process basically so every time you have data in a particular format you might want to store it in a different format and for all that purpose you can use a metadata right so since things will already be defined you're the work of metadata is just to get the data from the source define and do the set of activities that is required and which is already defined it will perform all those activities and it will sort in the case where you want to do so that is the role of a metadata correct so many data is very very important very very very highly used and it's actually the most important or let's say the epitome of data barrows okay this is probably the best thing that can happen to data warehouse right so that is the thing about the major a degrees now going to the next slide we have the architecture so now that you know everything okay these four raw terminologies are in our removin enough for you to understand the architecture okay so let's understand what the architecture is so this is the entire architecture diagram okay so this is what you know data comes in from various sources it can come from either a database or in the from flat file and then that data an action of ETL will be performed on that data and it will go to the staging area okay this called the sitting area and this is the staging database and the rails are stored over here it is temporary data before data completely moves to the data warehouse it will be present in this area okay and that is done by using the act of ETL and also between moving to the data warehouse the process of idle continues so ETL process starts over here and it ends over here okay and between the conversion it is a temporarily stored in a staging area and this is most often present inside the ETL Coolidge okay like your talent order semantics and all these things and then this data will be stored in your data warehouse so whatever is the extracted transformed unloaded it will be loaded into your data warehouse and in your data barrows you'll have metadata okay and of course you'll have your raw data and then you will have your aggregate data okay and this is the reason why a data warehouse is generally you know it's a larger in size because it has not just raw data or data base which from where the data is coming in it will only have your raw data okay but your data warehouse will have additional stuff here it will of course your metadata and your aggregate data and together all these three things together is what helping you being you you doing your analysis sooner okay this is what powers you're ready to do OLAP online analytical processing okay so that's what a data warehouse is and that's what it stores and there's this data warehouse you can either your entire company can use the data warehouse or if you want more for security based access then you can divide it into a different data mass where your sales team and your different teams like here we have a purchase table and then you have a stock table so these are like three different maths data mass for three tables like a sales purchasing stock so you'll have different tables here and different teams can access different set of tables okay your purchase can be something that's used by your operations people right and then this may be something that's used by your raw sales people and your stock may be again used by your operations or your sales group so that's what each of these your you know defines you have your user group one user group two and user group three and each group will be getting access to different parts of your raw data warehouse because your data valves will be divided into different data Mart's and your data your different groups we could access only that data which they want to or which they can get access to right so this way no group can get access to every data that is personal data warehouse and there's advanced there's a little more data security in this is okay same thing over here so this group get action into this data and this data and this table or this mod right and then this user group gets accessed only the Riddler is Belgian they smart and then this Mart so that's what the entire architecture looks like and resolve the data flow is right so if you guys have understood this much then you're ready to get a demonstration correct guys and this is also my last topic in this presentation so right now I can go to my demonstration and what I'll be doing in my demonstration is I'll be using talent okay so I'll be x importing data from my database I'll be using be getting it from my Oracle database and I'll be showing that into a data warehouse which can be ready for any kind of analysis or visualization on any other visualization tool okay so this act over here which I'm going to show you this is what powers your business intelligence right so are you guys ready for the demonstration any doubts your guys okay great Ronnie says he's already he's all pumped up already Ronnie that's very good so Jacobs also ready and so is Rajesh are you Rajesh can I get a yes from you yes very good very good olive oil Rajesh so let's go so let's go to the next part of my session and there's going to be demonstration where I will populate or detail barrows okay I'll be using talent for the data warehouse activities and let's see how we can import data from waste data sources and create or data barrows so for our hands-on session I'll be importing data into my talent bi okay like I said earlier we love using my talent bi for night data warehousing and ETL processes and I will create a data warehouse out of using talent bi okay now the data set that I'll be using is that of per 10,000 row table and 3,000 row table okay so the there be one table having 10,000 customer details okay and then there'll be another table having 50,000 rows of transactions which each of those customers make okay now based on this data we have to find those customers who have the lowest number of purchases okay so right now my dataset is present in my Oracle database okay and so I have two tables and both my tables customers table and my transactions table is present in my Oracle database I also have them in my here excel so first let me show you how they are present in my excel file okay okay so this is my freshmen table as you can see we have all these days right we have all these fields here so we have customer ID we have customer name we have the address of that customer city the country is from the contact number and his email address right and if I do this then you can see that there are almost 10,000 cutoff fields here so 10,000 rows are there here so this means that you know I have it's a pretty big data set and I can use this in competition with my other table okay so here we have customer ID as the primary key okay and if I go back to my other data set that's my transactions table you can see that I have furthermore freeze your Abdul for invoice number of a stock code I have description app quantity I have invoice date unit price customer ID and Product ID okay so there's a customer ID over here also so the customer ID is the primary key for there and this is the foreign key over here so basically the customer ID is the same and I will have to do a lookup to that table with the help of my customer ID okay since these are two different tables I can create my data warehouse with the help of this particular primary and foreign key concept I can create a link and I can create I can probably just link these two tables with the primary key for a key concept okay so any doubt guys I'm sure that you all know what a primary key and a foreign key is and I fed all anybody has a problem please let me know okay so Rodney says I don't know what's a foreign key okay so see Rodney the thing is we have something called as the primary key and a foreign key okay and we use these two columns when we want or when you want to use a combination of two different tables right so if you would consider the example of this one then you can see that there are a number of columns here and not everything is matching with the radius in that put in the other table okay so only customer ID is the only common field okay so there's a customer ID here and even in this table we have a customer ID now the thing is the customer ID is a primary key because it's it's constant and it's unique for each and every single record over here so each and every customer here will have a separate customer ID okay so no two customers will have the same customer ID okay and over here the transactions here which has the inverse numbers the description of the product data spot is the stock code the quantity all these things are sorted by the customer writing okay so if you find a particular customer ID more than once then well here you can find the person already more than once that's because a customer would have made more than one transactions he might have you know come on to their no we might have bought from the same person more than twice or thrice or four times or something like that right so supposing I go back to this person and I buy his products since its radial so I buy products say 50 50 times then elapsed for three different times my customary will appear here okay but however URL it will not appear again because it's a primary key right here so that's the difference so here the uniqueness is there that's why it's called the primary key in this case and here there is no uniqueness for the customer writing okay can appear more than once and that's why the foreign key over here and what we're going to do is for every customer ID over here right so for every customer ID over here we are going to link it to this particular table with the help of this customer ID since the customary is the common field in both the cases I can make a table which would show which customer which would probably display all these details along with the other columns okay now that's because I have customer ID which is common in both the tables and using that I can link the freezer are there in this table and which are there in the customers table right so that's what I'm going to do now the similar thing is also there in my or actual database so let me show you that so this is my your sequel developer GUI okay so I have two tables that have customers table and I have transactions table right and then I have a final table but this is not something which is relevant so this is not something we'll be using but we have the same two tables customer table and transactions table as I showed you you have these two here and you have almost the same fields over here right because if you see the customers table we have the customer ID we have the customer name we have the customer number and the email address okay and here we have originally we have the address city and country now what I did is when I imported this excel file into my database I did not I ignored these three columns right I didn't want these details because I was not going to use them in my data warehouse so if you see a problem statement here it money says you have to find out the cosmos grab the lower number of purchases it doesn't mention that you need their contrary details or city details or anything for that matter of fact so so because so for that reason in power using the country city and address fields would be your ways so that's why I'm not imported these into my data warehouse I have only the customer ID the customer name my contact number and my email address fields okay this would be sufficient to find out the customer details okay and to find the purchases right and to see who has all the lowest purchases we can use the other table so in the other table there is a transactions table we have this quantity field right we have this column this quantity table basically represents how many products this particular person purchased so this person with this customer ID process 71 products right and then you have another person whose purchases 67 projects products and so on so you also have people who purchase only seven or nine okay now our problem statement is such that we have to find those people who have made unless a very less number of purchases okay now if we define less to be ten then we can extract the data such that the data warehouse gets the details of only those people who have made purchases of less than ten items per order right so per transaction whoever has made less than ten number choices so this person has made seven and this one this person has made nine right so we can get a list of those customers right using the customer ID in go to that customer table and get his name we can get his email addresses phone number and other details okay and we can now publish all those things and we can probably export them to another CSV file like this or we can put that into our database basically into anything which would support data visualization right so that's what we want and this function is what our talent is going to do okay and what I'm going to do for that purposes I'm going to do a lookup from this table onto my customers table right so this is my transactions table and from here I'm going to use the customer ID field to look up to my customers table to obtain their name email address and phone number and all those things so first of all let me introduce you our talent interface again so this was the Oracle sequel database which I showed you earlier and this one is the talent right to talent open studio this is basically the data integration version of the project okay now what I'm going to do here is I'm going to show you how talent works I'm going to show you the layout over here first of all so on the left here you can see that you have something called a job design we have a standard you have demo and then you have various other projects here right so all these are the different projects that I have or created okay and when we say job design so job design is what you use to create your jobs on the UI here okay so in our case in talent whatever the actions are we performed it we gotta do it with with respect to a UI so we don't have a quoit this is not a coding interface okay so of course this talent runs on Java but it we don't but talent is known for its GUI okay so we can drag and drop the items on the job task so this is the job design task bar and we can first of all create a new task ok supposing I wanna create on your radar arrows by importing videos from another database then we can start by creating a new job design okay okay and then create a new project in that job design so right now we are in my your local project so I'm going to create a new job design okay I want to say create standard job let's give a name to this particular job so let's say data warehousing bi session ok and demonstration is the purpose so I'm just gonna copy plates the same videos into my description field also and I'm gonna say finish ok now what you can see this is a job designer so this is where I can get all the data from my different databases or fields files and get them to my data warehouse so I can run my I can write my projects over here and then on the right hand side you have your pilot okay so from your palette you can choose the files or the database from which are you from where you want to input your data ok supposing you want to import from a flat file then you can just go to this heading file here so under file you have input right so under input you have all these options so if you choose any of these options here and if you just drag and drop them over here then you can use them as a means input data using set the path where the exit file is stored okay and you can choose that and when you the Center for the fields you can get the data in from these excel files to your data warehouse okay to your talent okay but exa file is a very simple thing and I don't want to show you that let me show you how to import this large data from our database right so we have also other options like big data big data intelligence intelligence we have business we have cloud right so we have integrations with numerous technologies here and what we would be considering now on is that of databases right so I have my Oracle database so I'm going to go to a rattle under databases and under Oracle I'm going to say tea or rattle input okay let's see where is it articulate current put is right here so I'm going to just click on to your input and paste it here okay I'm going to drag and drop it here and now that I've done this I can use this as a means to get my data in from from my database okay so this is my Oracle database right so let me contribute my input first so first of all I need to configure all these details with respect to my host the port number on which its hosted the database name the username the password and all these things okay so let me first start off from the host it's hosted on localhost right it's not on any server it's not on any remote server so one is going to say low close and the port number is 1 Phi 2 1 so these are details that I got when I was when I installed this Oracle sequel server right so you will have to probably enter the same details when are you create one and one fight 1 is actually the default port on Metro at use what happens usually was it so you should have any problem there so database you're this exit in my case so ok and then my username is important and the password is something we have to set over here ok all right then I've done this my connection type is our atlas ID my database version is 11.6 alright so all the deals here are correct now let's check my schema the schemas basically is what is going to you know map your fields from your database to your they are talent right so this is your you so we have to define a schema here to add details okay so now that we have all these filled here now let me go back to my database right so we are in my customers table and in my customers table I have four pins customer ID customer name customer number and email address so let me add these four fields in my talent okay first column is going to be customer ID this is going to be customer name this is going to be customer phone number this will be customer email address all right so that's all fine basically the data type that is coming from is all bad care okay so if you see here the data type that is it currently is in is back at okay they have different sizes oh but email address beat email address or contact number or name or ID all are back to type so I'm gonna just say the input also to be as bad care right so it's all bad care - everything is technically work attitudes it's picking up the same data type by default all right so let's define the length over here - let's say the customer ID is going to be 5 characters in length I'm going to say the lens for the name is going to be around 30 phone number to be around 20 or technically it's 10 so let's do a buffer of another 10 and say 20 email address is going to be another 20 characters okay the customer ID let me just increase the length through 10 so these these are the lengths of the fields which are going to be stored okay so I'm going to set the length over here and I'm going to say ok now whatever do is I need to set the default query okay so I'm going to say select star from customers select star from customers okay because customers in the name of the table in my Oracle database right so I'm going to choose the table name here I'm going to say the table name here also okay there's something I forgot earlier you got to mention the table name that you want to import from and you order specify the query you want to run okay now let me run the same query in my Oracle and let's see what details I get so I'm going to feed the same query here okay so it's going to be select star from customers so when I say this I have this table option here okay now let me just run this query okay so I've got the same radius in here also right so my table name is customers so let me put my talent and set that its status customers already and this is the quays in korea be running here buddy hold all right I'm going to save this so I have set my first data and write it so I've gotten a cosmos data and here so let me say another T Oracle input and I'm going to configure another input for my transactions table right so I have another table for transactions so let me set that also and as you can notice every time you add a new input or configure a new output then you'll have to add these details right you have to add the hosting and database name username password on all these things so instead of this you have a shortcut here so you have a shortcut with respect to mater it are some metadata something that I explained earlier right so it basically contains data about your data so under metadata tab if you see you have DB connections you have file all these things right so so if you want to create a standard input for data coming in from a particular database then you can just set it once over here okay so onto your DB connections you can set one connection and it can be using any number of jobs in this particular project so instead of four so the same probably laughter enemies enter the details of this particular table okay but if I set the same thing through my metadata here then I can use the same metadata every time I want to import data to my talent warehouse so what I'm going to do is the same let me show you how to do it through metadata I'm going to say create connection once the name of the connection is daily input all right let's say database input purpose to get data from Oracle to Ireland let's have the description also and say finish so k we go to the database type over here okay now the database type that I want to import is that of Oracle right and I'm going to do it with the help of Oracle with the service name so when you choose that you have your login you have your password and all these details so in place of your login give your username here so in my case it's worse than okay password is this one the server is localhost and the port is one five to one so is name is X II okay now to verify if the connection is correct you can now click on check over here so as you can see the DB input connection successful messages come okay this means that your tunnel be able to successfully communicate with your Oracle which is hosted on a port number one five to one okay so now that I have done this I can not straight away say finish and add this metadata over here okay now I'm just going to click on the drag and drop this DB input over here when I do that I can choose this to be for either input or output or anything here because under Oracle you have all these options right so I have chosen this metadata for any of these options your so I want to use as input I can do that also if I want to use that output I can do it even at that time so I'm going to say to your our input and I'm going to say okay now as you can see my DB input here this is a meta data which has these properties already input okay so in case of this one I had to give it manually but here in my metadata I specified it once and it's already replicated so the advantage here is that even if there are ten different tables from which you want to get data in then you don't need to add a ten different database connections in your row talent right when updating your metadata if you create the database connection once and then using that one time you can establish ten different connections you can now get it and from ten different tables or replicate the same thing again and again so that is the advantage you're right so that's a thing so next let's give the table name here okay so the table is the transactions table right so that's what I'm going to enter here and the query that we have to run is select start from transactions i ruinous transactions okay now let me also verify if this query is running in mind my sequel developer okay and let me run this query and yes it able to fetch shooter from my transactions table also now going back to my talent now that I've set this let me go and edit my schema now okay so where do I have my schema option okay it's right here so I'm going to say edit schema I'm going to consider all the different of columns and fields over here because I'm not wrong there are five different columns your invoice number description quantity customer ID and product right so I'm going to rename everything here I'm going to say invoice number description quantity what else do we have we have customer ID and Product ID right all right so even the details here have been set to work l okay now this basically means that I'm getting the data from a database and the reader type of that is back here right so as you can see from here mana click on this all my data all the fields have that care to wait attack so that's why I'm setting them as like a 2 over here and I'm gonna set the default length for everything to be as well not for everything so for invoice number let me give a length of 10 for description and want to give a length of hundred for quantity let's say a 5 for customer ID it's going to be 10 and product ID again let me give a length of 10 so I want to say okay so my schema is basically set for both of these files now what I've got to do is now I have the controller two inputs now have to get these inputs into my talent now to check if my data is coming in from my data sources appropriately let me just check the T log row right so the component name is a deal or oh but since I can't find it or I can also search for it here so T log row then I just put enter I have these options right so in this captain Steele oh go and drop it here now you can create connections from here you can say main and you can establish a connection over here okay and similarly I can use another T log row you're okay drag it I can paste it here and check actually no I won't do that check of my data coming to my tea log row from here right so my component is added over here so let's say so let's just check the schema once so as you can see these are the details that are entered in my source and it's already been mapped since I gave a connection between to a pretty mighty log row and my input one these fields have been mapped here also so I'll be getting my customer ID customer name phone and my email details you're okay now let me just stop you conocchia go down and here the field separator would be this pipeline I'm going to say print header print content with log4j all right so now let me just stop save this and let me run this now you can see the part near right when I say run it would build my job basically and should be able to import my data from there as you can see all my data is being imported to this let me just minimize my screen so as you can see all my materials are here right so customer ID customer name customer phone and customer emails where the four different fields that I'd configured to enter into my t logo from my input and they have come there D headers of these and the radius R so for customer ID its thousand and the customer name is this one here and then his phone number is right here right so this is his name here this entire row and things like that so each and every feature is separated by a pipeline so that's because I had in the component editor over here because in the component editor where your ad set we feel separator to be pipeline okay so that is the pen so since my component editor has the pipeline's for every field in my data that is coming through my Teague log row it has been separated by a field separator that is pipeline now if I just go back here and you can see by just clicking on run over here or over here you can get your output okay this I've just done this to show you how trade up comes in okay so now that our data is coming in successfully I'm just going to delete the steel over okay because we don't really need it for our demonstration so you can just click on this clear here to clear your lock screen so now that we've established our connection what we got to do is we've got to merge these two tables right so we've got to create a lookup field and I've got to make sure that my tables are joined so for that we have another component you are called tea tea join and when you hit Enter you have this right so under processing you have this option of tea join so you can just drag and drop here and what this would do is it basically join these two different inputs so we have the source input of this one and this one right so it would basically merge the two of them so let's first create one link over here to my tea Jane and similarly let me create one over here from my input to 290 low now if you can see here instance this was the first connection that was established this took up the name of main okay and my second connection took up the name of lookup now that's because this table here this will look up to the customer ID field in this table and match the subsequent rows or entities with this particular columns right so what this lookup would do is it would use the customer ID field in my in this table and it would do a lookup to this particular table and match the remaining rows and columns right so let's configure the schema for that first of all since we are joining these two we are using this tea joiner okay so tea join let's go to the component and edit all the other details here so we have to check this include lookup columns and output this means that at the output that comes out of this joint we want to also include the fields which are present in my lookup fries right so that's why I have a check this car so besides that here it says column mapping right so we have to always map these two different tables during one lookup field right so it's either one lookup fluid or the primary key foreign key relationship so both are the same so that's what we were to define you gotta define which is the parameter which is the foreign key or which is a lookup or failure okay so first of all let's go - let's go edit the schema here all right so we have the two different fields here we have the input field one and the input stuff we do over here so let's add all of these I'm gonna say control a and I'm going to move it here all right so I'm going to join all these things and from the second table I'm going to move my invoice number my description well I don't want to move description so I'm going to move my quantity customer ID is already there so I would then want to move my product ID okay now the reason I'm not going descriptions because I don't need description as for my problem statement they don't need for our description okay similarly even product ID is not really needed for me so I can actually remove this also right so I have these so these will basically be my output fields my customer ID customer name customer phone customer email address invoice number and quantity right so this will be my output fields and another upset my schema even the event has been auto picked right so this is the nice thing about talent things are pretty simple and it's all straightforward so in this click on okay so guys another will set a schema here let's add the columns that we want in our output from our lookup table okay so this is a lookup table and from here what all columns will be what we want in our output so that we've got to include over here right so let me add the different entries so I really want my customer ID to be there so I'm going to say a row to got customer ID row to a row basically means this table right so and then I want my quantity to come and that I'm going to set as a row to what quantity I'm going to add another entry here and that's going to be for invoice number okay so I'm going to say this is row 2 dot invoice number all right so now that we've done our column mapping here this is pretty good enough now let's go down and set our key definition so here we set our primary and secondary key a foreign key definition okay so let me add an entry here so the primary key is going to be customer ID and the rock table is going to be row 2 dot customer ID okay so with the help of these two fields we're going to find our we're going to make our way over toward lookup and another advantage here is that I can also go back to schema and edit the data of my quantity okay since I'll be using quantity as my differentiator for my problem statement I can set the data type to be something else I can set it to be integer okay so I can do this and set the length to be either 5 or 10 right I can give okay so now this basically means that or even though all these are in string format my quantity can be an integer and the best part about talent is I don't need to change anything over here okay so I can get the data and from any data type the data that comes in can be of any data type okay but the data that I store all the other I finally export that I can change the data type of that particular data so that's the advantage so quantity comes in the form of vodka to data type and I'm converting it to into integer from inside talent right so that's the thing about advantage with talent so I'm just going to say okay now all right so now that I've done this I think I'm done everything with respect to T join okay so now let's go back to my problem statement for a minute now what they say is they wish to find out only those customers who have a low number of purchases okay so it is quantity which defines how many purchases that they have right so I'm going to go back to my talent here another of added might be log in over here I will be getting all my details and all my data into this component right so my input one would be giving me those rows from customers and this would be giving me those rows and fields and records from my transactions table so I will be getting approximately 50,000 rows from here and 10,000 rows from here now that I have all these rows over here what have to do is have to filter them based on the quantity and our problem statement here says that we have to find out those customers who have a low number of purchases and if you remember we define that when if you want to find row number of purchases it's not exactly defined so let's say we want to find those people who have less than 10 transactions or whose quantity is less than 10 right who put is less than 10 items so for that we can just go back to our data warehouse and create another filter okay which would filter out those people who have less than who opposites less than ten items per transaction right so for that let's again go back to my palette here and search for this component filter so as you can see there is pee filter row and tea filter columns okay but I want to filter rows Swami is going to drag this one drop it here so T your filter okay and I want to connect these two right so now I'm going to give a connection here and what I'm going to do here is I'm going to define the the filter criteria okay so I'm going to add an entry here but before that let me go to my or schema and as you can see I have all these entries here and same fields are here also right and even the data type has been picked up by default that's the talents functionality so this will be the input that will come to my filter and this will be the output that will go right so let's just click on OK and now coming to my criteria for filtering I want to filter my quantity right so the input column that I want to check is that of quantity okay on the function that I would want to do is empty now for now let let's just leave it empty and the operator is if it's lower than or equal to right so if it's lower than or equal to 10 that is the value that is present in this column if it's less than or equal to 10 then I want to filter only those rows and I want to send only them okay so basically my command is also set over here all right so in my schema and my function is set over here and now what have to do is have to set the output for my data warehouse okay now where do I want to extract these results and store these this data warehouse all right so let's say I want to store it in an Excel then I have to just go back to files over here that means it's typed file and hit enter and I have these options read out 50 file input XLV file output X L so I am going to choose the file output Excel and drop it here this would essentially mean that the result that are filtered over here in this component this would come and get stored in this excel file which I am going to create now okay so let me connect these two all so I'm going to say filter and put it here and supposing if I want to also get a list of all this who have more than 10 transactions then I can do that also by using this option of rejecting so whichever rows get rejected right which will get rejected from moving to this particular file those can go to another file right so we have that functionality also that as I'll show you even that but before that so let me just show you how this looks like so I've set a row filter t file output Excel and let me edit the component over here and let's say the file name is this one d wh VI demo all right I'm going to say include the head also head up basically is the part which would say customer ID phone number and all those things from one say include head up alright and I'm going to say define all columns to auto size that's just to auto size those columns right so it's all fine now everything set and my file would be saved in this particular path okay and this would be the name of my file which will be stored when I run this so I've copied this path now what I've gotta do is I just save this job and execute this okay when I executed that it's showing me an adult over here okay so the error it shows us that the error and the complex properties type mismatch cannot convert from string to integer okay not a big deal I can fix this error okay we can fix that by first of all checking our schema right over here if you see the schema here it says quantity is type integer right but over here quantity is still type spring so that is what we need to change we got to change the type over here to integer so I am going to say number and I'm going to click on okay fine okay so now let's go back to this file right and let me edit the schema and set the properties correctly okay so it's not able to convert this right so the type of string and the DB type is number so this is the problem so so I can treat this also two integer right so it goes right here so I'm gonna choose this as the integer database type I'm going to click on OK and now let me go back to the T log join and check the schema over here okay so this is my table and quantitate this is integer this also integer is coming here and now it'll get converted over here into this so now it would lightly throw me in here now let me just save this and run this job ok building job ok it's showing an error again cannot convert integer to string okay so let's see what the error is now okay so we have a problem here invoice number right so this is the problem so invoice number it should be Row 2 dot invoice number so this was the type mismatch error that we were getting right so no problem okay so again it's changing right so right I'm going to save this now and we won't have the type mismatch era now okay great so it says connected and disconnected as you can see the output over here we have got around these many rows from this table and about 50,000 rows from the table job and we've got it here and then we would have probably join these two with the help of the customer ID field and then to my T log row it would have filtered all the fields so we've got the input has basically been eight three four two number of rows eight thousand three hundred forty two rows out of which 841 rows got filtered and stored in this excel file awesome right so let's just go to this path and check if on field content are correct so this is the path where the file store right so I'm going to copy this path I'm going to go back here and paste this path so this was the file that we created right and yes as you can see we have got customer ID customer name customer phone the email address invoice number and the quantity okay and the best part is the quantity here is the sorted and it's only that which is less than ten right so that was the criteria that we attack we had filtered for those fields to be only less than ten and to show you further proof what I can do is I can now do this a lot of filter okay and if you can see here the only options you are 110 okay that means nobody whose quantity was 90 or 100 plus has been added over here right so so this is how simple our talent is and this is what data warehousing is in basics right so this is a very simple example that I've showed you where you can get data in to your data warehouse by filtering some data from the database and probably you can use this data to make your analysis or visualization on a tableau or click-through or any other data visualization tool okay but there was another thing that I promised you right I told you that you will have a filtered row and you will have another rejected row so if this was the filtered column then I can probably have another excel here I can say T file output Excel so okay so T file output Excel I'm going to paste it here and what I'm going to do is I'm going to start one when I say reject and here so the rejected the fields or rows and columns are going to go into this particular fields okay so I'm gonna name this also as rejected data right so this also won't be present in the same path and let me edit the gonna say define Auto columns sighs I'm gonna say include header and yeah I'm gonna save this and run it again not again I have the same list of fields over here right another same rows over here right so as you can see the same 8:41 rules have been added to this file okay the filtered results are here and the remaining ones which were rejected they have gone to my new file and this file is called the rejected one so if I go back to that same path this was the original one and reject data is your so when I open here you can see that the only options here are those of above n okay so if I just show you with a filter column right so you can see that there is nothing less than ten so that's the thing so that's how you filter data that's how you get data in and all these things so that's how we work with talent guys so you get a run so the same thing here you can also store it to some database right so instead of having a file to excel for this I can just delete this and I can just delete this and let me go to databases first of all all right so here when I say Oracle and T or a clout put okay I'm going to paste it here and now if I am going to say reject or yeah project right so all these rejected entries would go into this particular file of mine so let me add the components and edit the component or the hose reels here it's localhost okay port is one five to one database is X E right yes it is XE and my username is important my password is a de Rekha all right so and then you have the action on table right so the action is going to be create table okay so when we say create table they've got to give me your table name also and the table name let's say rejected output okay when I'm getting there is nothing by that name yep there is nothing here by your that name so we have customers final table and transactions right so we will have a new table that will be formed over there so which the action is going to be interesting you want to insert or update an existing file then you can choose the various options but in our case it would be create a new table and the action would be inserted okay so let me just save this okay and run this now so seems like my new table has been not created here let me just go back my sequence here or a clue table and we need to refresh this yes so as you can see there's a new table has been created and when you see here these are the different fields error message was another field that gets auto-generated and if you look at the contents of the field you have all these right so guys that's how you get your data back into your database right so earlier I showed you how to do it and sort on an excel file now I've showed you how to create a new table and how to get your data warehouse data back into your database so that's the end of my demonstration all my queries have been passed successfully now let me just quickly go back to my slides for a minute and oh yeah this hands-on is what I completed it showed you how to get our data and your data warehouse so yeah so the I think that brings us to the end of the session and let me just summarize whatever I did in a minute right so first of all I spoke about the need for business intelligence and then I told you what is business intelligence why you need it for your business and what is the role of data warehouse and then I spoke about data warehousing right I give we went into the depth of data warehousing and spoke about the key terminologies and data warehouse we have the EDL we have data Mart's we have the metadata and all these things and then finally we spoke about the architecture of the data warehouse and we finish off the session with the hands-on demonstration of how to populate your data warehouse using talent so let's that's it and thank you for being in the session so that brings us to the end of the session trip probably will have another session on new data warehousing and I'll talk about more advanced concepts like Chema's right I spoke about there are three different types of schemas right like star schema snowflake schema galaxy schema all these things so I will talk about all these things in my next session and probably also I will talk about dimensions and fact tables and all these things right so it doesn't seem like you guys have any doubts so if you do have any doubts please put that in the chat box right okay so thank you guys thank you for being in the session probably I'll see you on until next time okay so I hope you enjoyed listening to this video please be enough to like it and you can comment any of your doubts and queries and we will reply to them at the earliest to look out for more videos in our playlist and subscribe to our ready Rica channel to learn more happy learning

Channel: edureka!

Views: 927,237

Keywords: yt:cc=on, data warehouse tutorial, data warehouse tutorial for beginners, data warehousing tutorial, data warehousing tutorial for beginners, data warehousing tutorial videos, data warehouse concepts, data warehousing concepts, data warehouse architecture, what is data warehouse, data warehouse basics, olap in data warehouse, olap vs oltp, data mart vs data warehouse, edureka, data warehouse and data mining, data warehouse, Introduction to Data warehouse

Id: J326LIUrZM8

Length: 98min 50sec (5930 seconds)

Published: Thu Jun 22 2017