Lesson 9: Data Warehousing and Business Intelligence

A data warehouse is a specialized database designed for analysis and reporting (OLAP), unlike an operational (OLTP) database which handles day-to-day transactions. A warehouse consolidates data from multiple sources (sales, inventory, finance) into a unified schema, often using a star schema: a central fact table linked to dimension tables (e.g. FactSales with dimensions Date, Product, Store). This design allows fast queries on large datasets. For example, a retailer's data warehouse can quickly compute total sales per region per quarter. In contrast, an OLTP system (like a point-of-sale) is optimized for fast inserts/updates.

Building a data warehouse involves ETL:

1. Extract data from source systems (databases, spreadsheets).
2. Transform data by cleaning (fixing errors), aggregating, and converting formats.
3. Load the cleaned data into the warehouse.

For instance, if different hospital branches use varied codes for departments, the ETL process would map them to a common standard before loading. Philippine companies often use ETL tools (like Pentaho or Informatica) to automate this nightly or weekly. In exams, you might outline ETL steps or explain its purpose (ensuring consistent, analysis-ready data).