11.1.3: Data Warehousing Concepts in BI

Data Warehousing Concepts in BI

The data warehouse is a central component of BI architecture. It is a central repository that stores structured data from multiple sources, designed specifically for query, analysis, and reporting rather than transaction processing. In a data warehouse, data is typically integrated, cleaned, and standardized so that it can serve as the single reliable source of information for the organization. Key concepts and characteristics of data warehousing include:

• Subject-Oriented: Data in a warehouse is organized around key subjects of the business (e.g., sales, finance, inventory) rather than around applications or processes. This makes it easier to analyze data for a particular business area across the entire organization.
• Integrated: The data warehouse brings together data from diverse sources – which may use different formats or naming conventions – into a coherent, unified schema. This involves resolving inconsistencies, such as different codes or units of measure, and ensuring that data from different sources can be compared and aggregated.
• Non-Volatile: Once data is entered into the warehouse, it is not usually updated or deleted in the way transactional systems are. Instead, the warehouse retains historical snapshots. This quality ensures a stable historical record for analysis. Updates happen by adding new data (e.g., new daily transactions) rather than altering old data.
• Time-Variant (Historical): A data warehouse typically stores historical data spanning long time horizons (months or years), allowing trends and changes over time to be analyzed. Each record in a warehouse may have a time stamp or a version, making it possible to track changes. This is in contrast to operational databases that often only store the most current value for each record.
• Schema Design: Data warehouses commonly use schema models like the star schema or snowflake schema. In a star schema, a large fact table (containing business facts or measures like sales amounts) is linked to smaller dimension tables (containing descriptive attributes like date, product, or customer details). For example, a sales fact table might link to a product dimension (with attributes like product name, category) and a time dimension (day, month, quarter, year), etc. This structure is optimized for efficient querying and drill-down analysis. A snowflake schema is a variation where the dimensions are further normalized into sub-dimensions.

Figure \(\PageIndex{1}\): Star Schema Design for a Sales Data Warehouse. This diagram shows a central Sales Fact table linked to surrounding dimension tables (Product, Time, Customer, Region), illustrating the star schema pattern commonly used in data warehouses for optimized analytical querying. (CC BY 4.0; Felix Amoruwa) Access to a detail description of Figure \(\PageIndex{1}\)

• Optimized for Read Queries: Unlike OLTP databases that are optimized for a high volume of simple transactions (inserts/updates/deletes), data warehouses are optimized for a lower volume of complex read queries. They use techniques like indexing, partitioning, and sometimes pre-computed aggregates to speed up analytical queries that scan large portions of the dataset. For example, a warehouse might pre-compute monthly sales totals by region to accelerate a query that needs those results frequently.

From a BI perspective, the data warehouse serves as the single source of truth. Analysts and report builders pull data from the warehouse, ensuring everyone is working with consistent information. This reduces the problems that occur when different departments use their own spreadsheets or databases (which often leads to divergent numbers for what should be the same metric). By having an enterprise data warehouse, a company can have one consistent view of, say, “total customer revenue in Q4,” and confidence that all reports using that metric are aligned.

Importantly, data warehousing also encompasses the process of building and maintaining the warehouse, known as data warehousing. This includes processes for regularly updating the warehouse with new data (e.g., nightly ETL jobs or real-time data feeds), ensuring data quality (through validation checks and cleaning during ETL), and managing metadata (so that data is well-documented and understood by users). As organizations gather more data types, modern architectures have extended the concept with data lakes (storing raw, unstructured data) and lakehouse approaches (combining data lake flexibility with warehouse structure). However, the classic data warehouse remains a cornerstone for BI because it provides the reliable, structured data needed for consistent reporting.

To illustrate, consider a company that collects data from sales transactions, marketing campaigns, and customer support. In the data warehouse, these would be integrated into a unified schema: a sales fact table might be created with foreign keys to dimensions like Time, Product, Customer, Region, etc. Analysts could then query this warehouse to answer questions like “What were the total sales and profit by product category and quarter for the last three years?” The warehouse enables such a query to be answered quickly, because the data is all in one place and organized for analysis. Below is an example of a simple SQL query one might run against a data warehouse’s fact table and a couple of dimension tables:

SELECT p.category AS Product_Category,
       t.year AS Year,
       SUM(f.sales_amount) AS Total_Sales,
       SUM(f.sales_amount - f.cost) AS Total_Profit
FROM sales_fact f
JOIN product_dim p   ON f.product_id = p.product_id
JOIN time_dim t      ON f.time_id = t.time_id
WHERE t.year >= 2023
GROUP BY p.category, t.year
ORDER BY p.category, t.year;

This query assumes a star schema with a fact table sales_fact and dimensions product_dim and time_dim. It aggregates sales and profit by product category and year. In an OLTP database, such a multi-table aggregate query might be slow or affect transaction performance, but in a data warehouse (OLAP-oriented), it is exactly the kind of query the system is optimized to handle.