Introduction to Data Warehousing

What Is Data Warehouse?

Data warehouse is a centralized repository of integrated data from one or more heterogeneous sources. Data warehouses are different from the online databases that store current transaction data. Data warehouses store historical data and are built specifically for analysis. With that being said, data warehouses usually include subject-oriented data from across the organization that provides a consistent point of view. Because data warehouses are centralized, you don’t run into problems with comparing and contrasting your data since everything is put together neatly. Reporting, spotting trends in your data, and forecasting is made simple with a data warehouse.

The Origins of Data Warehousing

Data warehousing began in the late 1980s after it was discovered that there was too much load on database servers used for processing transactions when running analytical processes. Many companies had systems that included both functions but learned quickly that by separating them, your data processing workload could be improved. Architects like Bill Inmon and Ralph Kimball are some of the people who pioneered data warehousing by introducing many of the principles still used today. As computer processing speeds and memory allowances continue to grow, data warehousing has grown to include new technologies such as big data, cloud databases, and even AI.

What Are Components of a Data Warehouse?

Standard data warehouse architecture includes many different components that each serve their own purpose. These main categories include your data sources, data extraction, data transformation, staging area, the warehouse itself, metadata, and front-end tools. Extraction, transformation, and loading (ETL) is the process of extracting data from operational systems and streamlining it into a format used in the data warehouse. This includes formatting, cleansing, and restructuring data to fit the needs of the business. Metadata is data about your data. It defines the structure of the data warehouse and is used by both the warehouse tools and its users. Dashboards, OLAP (On-line Analytical Processing), and data visualization are all considered front-end tools.

What Are Types of Data Warehouse Architectures?

Typically there are three types of data warehouse architectures: single-tier, two-tier, and three-tier data warehouse architectures. One-tier architecture’s main goal is to eliminate data redundancy at all costs. Two-tier architectures separate the warehouse from the front-end tools used by analysts. Three-tier architectures are where you see most data warehouses because they separate everything into three layers. From the bottom up, you have your data sources and data extraction processes, then the warehouse itself, and finally the front-end tools used by analysts.

Explain the ETL Process.

ETL process consists of extracting data from various resources, transforming it into a useable state, and loading it into the warehouse. Extraction can be done from databases, files, and even external sources. Transformation processes include cleaning data (removing duplications and fixing errors), integration (making sure data matches), and enrichment (createing calculated fields). Loading is the process of adding data into the warehouse.

What Is Data Modeling?

When building a data warehouse, it is often helpful to think of your data as objects that need to be organized. Data modeling is the process of modeling your data in a logical manner. There are two popular methodologies for modeling your data. Star schema connects all your data to a central table called a fact table. The snowflake schema takes things a step further and normalizes your data even more creating related tables.

Data Warehouse vs. Data Lake

While data warehouses are centered around storing structured data that has been cleaned, parsed, and prepared for querying, data lakes store raw data typically used for feeding into machine learning models. Data lakes can store structured, semi-structured, and unstructured data. Businesses will sometimes build data lakes that will act as a landing zone for their information and then parse the data into a data warehouse.

What Is Metadata?

Metadata is essentially information that defines how your data is stored in the warehouse. Metadata allows users to know where data came from, how it got there, and how it can be used. Without metadata, data warehouses would be considered black boxes. Users would have no idea where their data is coming from or if it can be trusted.

What Are the Advantages of a Data Warehouse?

Building a data warehouse allows you to store information in one centralized location. This allows for reporting across the entire company and not just one department. Data warehouses make it quick and easy to generate reports, identify trends, and do what-if analysis. Since they are optimized for queries, data warehouses can perform analyses faster than transactional systems. Data warehouses can also store much larger amounts of data than transactional databases. Another benefit of data warehouses is you can compare your data over periods of time. Since transactional databases are constantly changing, it would be impossible to compare your data month-over-month. Data warehouses allow you to improve your overall data quality. By defining how and where your data should be placed, you can create a company-wide standard that everyone will follow.

What Are Some Disadvantages of Data Warehouses?

Building your ETL process can be expensive and take a long time. Since data comes in many different shapes and sizes, it can be difficult to merge together. Depending on the number of sources you are getting data from, keeping your data fresh can be very difficult. As your data warehouse grows you will quickly learn that the amount of data in the world doubles every 2 years. At some point, your warehouse will become too big to manage on its own and you’ll need a scalable solution. Cloud databases, Data Lakes, and big data are all examples of technologies that can be used in conjunction with your data warehouse. Lastly, just because you have a data warehouse doesn’t mean people will use it. Unless your users are trained on how to use the system and they like the interface, your data warehouse will go unused.

What Are Some Recent Innovations in Data Warehousing?

Just like technology in general, data warehousing is constantly growing and changing. New technologies like cloud-based data warehouses are making it easier and cheaper than ever to leverage a warehouse. Snowflake, Google BigQuery, and Amazon Redshift are all examples of cloud data warehouses. Data warehousing tools are starting to automate more of the ETL process which means data pipelines can be created and monitored quicker than ever. Machine learning and data warehousing are quickly being integrated. Because of this, it is now possible to do some level of predictive analytics inside of your data warehouse. Real-time data warehousing and data streaming are other innovations that have started to take off.

Where Can Data Warehousing Be Used?

Just about every industry can take advantage of data warehouses. Here are some examples of where data warehouses are used.

Retail: Analyze customer behavior and sales data to determine where to open new stores.

Banking: Monitor customer transactions to prevent fraud. 

Healthcare: Integrate patient data to help improve care while cutting costs.

Manufacturing: Analyze your production process and supply chain. 

Conclusion

A data warehouse allows you to store information from multiple sources in one centralized repository. Data warehouses allow for reporting, analysis, and business intelligence. They differ from traditional databases because they are built specifically to be analyzed. Due to big data and Data Lakes, many believe that data warehouses are going away. Data warehouses are still very relevant and because of new technologies are more accessible than ever.