Data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered a core component of business intelligence.
A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision-making process.
- Subject-Oriented: A data warehouse can be used to analyze a subject area. For example, "sales" can be a subject.
- Integrated: A data warehouse integrates data from multiple data sources. For example, source A and source B may have diverse ways of identifying a product, but in a data warehouse, there will be only a single way of identifying a product.
- Time-Variant: Historical data is kept in a data warehouse. For example, one can retrieve data from 3 months, 6 months, 12 months, or even older data from a data warehouse. This contrasts with a transactions system, where often only the most recent data is kept. For example, a transaction system may hold the current address of a customer, where a data warehouse can hold all addresses associated with a customer.
- Non-volatile: Once data is in the data warehouse, it will not change. So, historical data in a data warehouse should never be altered.
ETL: Extract Transform Load
The typical Extract, transform, load (ETL)-based data warehouse uses staging, data integration, and access layers to house its key functions. This method of integrating data from multiple systems and sources is still a core component of an organization’s data integration toolbox.
Businesses have relied on the ETL process for many years to get a consolidated view of the data that drives better business decisions.
A data mart is the access layer of the data warehouse environment that is used to get data out to the users. The data mart is a subset of the data warehouse and is usually oriented to a specific business line or team. Whereas data warehouses have an enterprise-wide depth, the information in data marts pertains to a single department.
It could also be defined as a single domain aggregation used for reporting, analysis and decision support. A single domain or subject could be Customer or Supplier.
Business Intelligence (BI) Tools:
Business Intelligence software systems provide historical, current, and predictive views of business operations, most often using data that has been gathered into a data warehouse or a data mart and occasionally working from operational data. Software elements support reporting, interactive “slice-and-dice” pivot-table analyses, visualization, and statistical data mining.
Business Intelligence tools are all designed to make sense of the huge quantities of data that organizations accumulate over time, by analyzing it and presenting it in a form where it can guide decision-making.
Analyzing Data using BI Analytics:
Once the data is accumulated in the data warehouse, BI analysis tools like SSAS will perform multiple complex aggregations and present the data in multi-dimensional view using cubes. This data from cube can be used for visualization.
Visualizing Data using BI tools: Funnel Chart (PowerBI)
A funnel chart helps you visualize a linear process that has sequential connected stages. For example, a sales funnel that tracks customers through stages: Lead > Qualified Lead > Prospect > Contract > Close. At a glance, the shape of the funnel conveys the health of the process you're tracking.
Each funnel stage represents a percentage of the total. So, in most cases, a funnel chart is shaped like a funnel -- with the first stage being the largest, and each subsequent stage smaller than its predecessor.
This page gives an overview of how an enterprise data warehouse and business intelligence can be implemented by any organization and support both business users and executives in decision making.