Building an Effective Data Warehouse Architecture
summary
Recommends a pragmatic hybrid: build a normalized Enterprise data warehouse as a single source, spawn star-schema data marts and cubes for reporting, and expect 75% of project time to go into integration and cleansing. Uses Microsoft BI (SSIS/SSAS/tabular/PowerPivot), Master Data Services, Change Data Capture or partitioning for incremental loads, and ELT for huge volumes (NASDAQ example: billions of rows/day). Advises surrogate keys, Fast Track hardware like a Fast Track appliance from Dell, and SSAS cubes to avoid nightly summary-table bloat.
AI-written summary · about
chapters (23)
- 0:00Data warehouse architecture intro
- 1:04Why use a data warehouse
- 3:33Separate reporting from production
- 5:19Integrate multiple data sources
- 6:23Track historical data
- 8:09Master data management
- 9:13Reduce IT in reporting
- 9:54Improve data quality with ETL
- 13:06Hardware for data warehouse
- 13:47Fast Track architecture
- 16:34Data warehouse vs data mart
- 18:02Kimball vs Inmon
- 23:19Combine Kimball and Inmon
- 28:12Populate with ETL
- 38:22ETL vs ELT methods
- 41:11Incremental extraction
- 45:10Surrogate keys purpose
- 47:35SSAS cubes intro
- 48:18Cubes outperform tables
- 51:26End-to-end architecture
- 54:33Tabular models for BI
- 57:21Microsoft BI tools
- 59:06Q&A
related talks
32mHow to Build a Data Warehouse?
44mAgile Data Warehousing/Business Intelligence: Addressing the Hard Problems
42mBuilding Tomorrow's Warehouse Today: Modern Data Warehouse Modelling Insights
56mData Unleashed: A Developer's Perspective on Navigating the Architecture Maze
27mThe Evolution of Data Architecture
31mData Unleashed: A Developer's Perspective on Navigating the Architecture Maze
05mThe Future of Data Warehousing
58mUnderstanding Big Data for Software Engineers