how a data warehouse works
the need to warehouse data evolved as businesses began relying on computer systems to create file and retrieve important business documents the concept of data warehousing was introduced in 1988 by ibm researchers barry devlin and paul murphy 1data warehousing is designed to enable the analysis of historical data comparing data consolidated from multiple heterogeneous sources can provide insight into the performance of a company a data warehouse is designed to allow its users to run queries and analyses on historical data derived from transactional sources data added to the warehouse does not change and cannot be altered the warehouse is the source that is used to run analytics on past events with a focus on changes over time warehoused data must be stored in a manner that is secure reliable easy to retrieve and easy to manage there are certain steps that are taken to maintain a data warehouse one step is data extraction which involves gathering large amounts of data from multiple source points after a set of data has been compiled it goes through data cleaning the process of combing through it for errors and correcting or excluding any that are found the cleaned up data is then converted from a database format to a warehouse format once stored in the warehouse the data goes through sorting consolidating and summarizing so that it will be easier to use over time more data is added to the warehouse as the various data sources are updated a key book on data warehousing is w h inmon s building the data warehouse a practical guide that was first published in 1990 and has been reprinted several times 2today businesses can invest in cloud based data warehouse software services from companies including microsoft google amazon and oracle among others 3data miningbusinesses warehouse data primarily for data mining that involves looking for patterns of information that will help them improve their business processes a good data warehousing system makes it easier for different departments within a company to access each other s data for example a marketing team can assess the sales team s data in order to make decisions about how to adjust their sales campaigns the data mining process breaks down into five steps investopedia theresa chiechithe concept of the data warehouse was introduced by two ibm researchers in 1988 4data warehouse architecturedesigning a data warehouse is known as data warehouse architecture and depending on the needs of the data warehouse can come in a variety of tiers typically there are tier one tier two and tier three architecture designs single tier architecture single tier architecture is hardly used in the creation of data warehouses for real time systems they are often used for batch and real time processing to process operational data a single tier design is composed of a single layer of hardware with the goal of keeping data space at a minimum two tier architecture in a two tier architecture design the analytical process is separated from the business process the point of this is to increase levels of control and efficiency three tier architecture a three tier architecture design has a top middle and bottom tier these are known as the source layer the reconciled layer and the data warehouse layer this design is suited for systems with long life cycles when changes are made in the data an extra layer of review and analysis of the data is completed to ensure there have been no errors regardless of the tier all data warehouse architectures must meet the same five properties separation scalability extensibility security and administrability data warehouse vs databasea data warehouse is not the same as a database for example a database might only have the most recent address of a customer while a data warehouse might have all the addresses of the customer for the past 10 years data mining relies on the data warehouse the data in the warehouse is sifted for insights into the business over time data warehouse vs data lakeboth data warehouses and data lakes hold data for a variety of needs the primary difference is that a data lake holds raw data of which the goal has not yet been determined a data warehouse on the other hand holds refined data that has been filtered to be used for a specific purpose data lakes are primarily used by data scientists while data warehouses are most often used by business professionals data lakes are also more easily accessible and easier to update while data warehouses are more structured and any changes are more costly data warehouse vs data marta data mart is just a smaller version of a data warehouse a data mart collects data from a small number of sources and focuses on one subject area data marts are faster and easier to use than data warehouses data marts typically function as a subset of a data warehouse to focus on one area for analytical purposes such as a specific department within an organization data marts are used to help make business decisions by helping with analysis and reporting advantages and disadvantages of data warehousesa data warehouse is intended to give a company a competitive advantage it creates a resource of pertinent information that can be tracked over time and analyzed in order to help a business make more informed decisions it also can drain company resources and burden its current staff with routine tasks intended to feed the warehouse machine some other disadvantages include the following provides fact based analysis on past company performance to inform decision making serves as a historical archive of relevant data can be shared across key departments for maximum usefulness creating and maintaining the warehouse is resource heavy input errors can damage the integrity of the information archived use of multiple sources can cause inconsistencies in the data 