what is Data ware house | The purpose of data ware house

What is the data ware house |What are the benefit of data ware house 

 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 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 

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 d 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 its 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.