How to Build and Use a Data Warehouse: ETL, OLAP, and BI Overview

A complete data warehouse system involves components such as ETL, online analytical processing (OLAP) tools, and business intelligence (BI) applications.

Creating a Data Warehouse

Creating a data warehouse uses DDL statements similar to traditional databases. Historically most warehouses were built on RDBMS because dimensional modeling can be seen as a form of relational modeling. With the rise of open‑source distributed tools like Hadoop Hive and Spark SQL, modeling and implementation are often separated: modeling is done with dedicated tools, while the actual implementation runs on Hive/Spark SQL. The author notes that these open‑source tools lack built‑in visual modeling plugins.

Modern distributed tools tend to be "scattered"; a large project usually requires stitching together many tools, and visual interfaces are often poor. The author suggests that, when budget permits, using commercial big‑data platforms can improve usability despite aggressive marketing.

ETL: Extract, Transform, Load

ETL extracts data from various sources, transforms it, and finally loads it into the dimension‑modeled tables of the warehouse. The process is considered complete only after all dimension/fact tables are populated.

1. Extract – A data warehouse is analytical, while operational databases are transactional. Extraction selects data based on warehouse subjects and domains.

During development, mismatches between ETL steps and the modeled tables often require revisiting requirements; the author cites a database series that states any requirement change forces a restart and documentation update.

2. Transform – This step converts the extracted data structures to fit the target warehouse model and performs data cleaning (data cleaning).

3. Load – The cleaned data is loaded into the warehouse. Loads can be a first load (large volume) or a refresh load (micro‑batch).

The author adds that with modern distributed/cloud tools, ETL often becomes ELT: the business system does minimal cleaning, loads data into the platform, and the platform performs transformation, leveraging distributed processing and allowing the business system to focus on core logic.

OLAP/BI Tools

After the warehouse is built, users can write SQL to query it, but writing repetitive SQL for dimensional analysis is cumbersome. OLAP tools specialize in analyzing dimensional data, and BI tools visualize OLAP results.

In a normalized warehouse, OLAP cannot access the central database because the central database is normalized and not exposed to developers. In a dimensional warehouse, OLAP/BI can directly query the warehouse and the data marts built on top of it.

In a dimensional warehouse, OLAP can also operate on the data‑mart layer.

Data Cube

A data cube extends a two‑dimensional table into multiple dimensions. The article shows a three‑dimensional example, noting that most cubes are N‑dimensional. One implementation follows the star schema, which acts as a bridge between relational tables and the cube. For most OLAP users, the logical cube is the focus; they configure dimension and fact tables and specify dimensions, facts, and operations for each query.

The five common cube operations are Slice & Dice, Pivot, Roll‑up, Drill‑down, and other tool‑specific functions.

1. Slice and Dice – Slice selects a single dimension member; dice selects multiple dimensions. Example SQL statements are provided.

# 切片SELECT Locates.地区, Products.分类, SUM(数量)FROM Sales, Dates, Products, LocatesWHERE Dates.季度 = 2AND Sales.Date_key = Dates.Date_keyAND Sales.Locate_key = Locates.Locate_keyAND Sales.Product_key = Products.Product_keyGROUPBY Locates.地区, Products.分类
# 切块SELECT Locates.地区, Products.分类, SUM(数量)FROM Sales, Dates, Products, LocatesWHERE (Dates.季度 = 2OR Dates.季度 = 3) AND (Locates.地区 = '江苏'OR Locates.地区 = '上海')AND Sales.Date_key = Dates.Date_keyAND Sales.Locate_key = Locates.Locate_keyAND Sales.Product_key = Products.Product_keyGROUPBY Dates.季度, Locates.地区, Products.分类

2. Pivot – Changes the presentation orientation; in SQL it is merely reordering the SELECT fields. A diagram is shown.

3. Roll‑up and Drill‑down – Roll‑up ignores certain dimensions; drill‑down refines dimensions. Example SQL statements are provided.

# 上卷SELECT Locates.地区, Products.分类, SUM(数量)FROM Sales, Products, LocatesWHERE Sales.Locate_key = Locates.Locate_keyAND Sales.Product_key = Products.Product_keyGROUPBY Locates.地区, Products.分类
# 下钻SELECT Locates.地区, Dates.季度, Products.分类, SUM(数量)FROM Sales, Dates, Products, LocatesWHERE Sales.Date_key = Dates.Date_keyAND Sales.Locate_key = Locates.Locate_keyAND Sales.Product_key = Products.Product_keyGROUPBY Dates.季度.月份, Locates.地区, Products.分类

4. Other OLAP Operations – Different tools may provide additional functions such as drill‑through; complex queries often combine multiple operations.

OLAP Architecture Patterns

1. MOLAP (Multidimensional OLAP) – Generates a physical multidimensional cube; queries are very fast because results are pre‑computed, but cube updates are slower, so suitability depends on the specific problem.

2. ROLAP (Relational OLAP) – Does not generate a physical cube; it simulates a cube using a star schema and multiple relational tables, translating queries into SQL with joins, which is slower than MOLAP.

3. HOLAP (Hybrid OLAP) – Combines MOLAP and ROLAP by routing speed‑critical queries to a MOLAP engine and other queries to ROLAP.

The author notes a recurring pattern: tool A is created, its shortcomings lead to tool B, which introduces new issues, prompting the creation of tool C that combines A and B.

Conclusion

Developing a data warehouse involves many teams: data‑modeling, business‑analysis, system‑architecture, platform‑maintenance, and front‑end development. Aspiring professionals have much to learn. For those aiming to become data scientists, the core competitive advantages are data fundamentals, data visualization, and algorithmic modeling. The author recommends focusing on database and data‑warehouse series as the most cost‑effective study paths.