The need for having both a DW and cubes
I have heard some people say if you have a data warehouse, there is no need for cubes (when I say “cubes” I am referring to tabular and multidimensional OLAP models). And I have heard others say if you have OLAP cubes, you don’t need a data warehouse. I strongly disagree with both these statements, as almost all the customers I see that are building a modern data warehouse use both in their solutions. Here are some reasons for both:
Why have a data warehouse if you can just use a cube? (i.e. is the trade-off of duplication of data and more ETL worth it?)
- Breaking down complex steps so easier to build cube
- Cube is departmental view (cube builder not thinking enterprise solution)
- Easier to clean/join/master data in data warehouse
- Processing cube is slow against sources
- One place to control data for consistency and have one version of the truth
- Use by tools that need relational format
- Cube does not have all data
- Cube may be behind in data updates (needs processing)
- Data warehouse is place to integrate data
- Risk of having multiple cubes doing same thing
- Data warehouse keeps historical records
- Easier to create data marts from data warehouse
- Need a relational database to track Slowly Changing Dimensions (SCD)
- Security in a data lake is file/folder
- Use the data warehouse as a place to store the metadata since people are more familiar with a relational database
Why do I also need a cube if I have a data warehouse? (or, reasons to report off cubes instead of the data warehouse) (a summary from my prior blog post of Why use a SSAS cube?):
- Semantic layer
- Handle many concurrent users
- Aggregating data for performance
- Multidimensional analysis
- No joins or relationships
- Hierarchies, KPI’s
- Row-level Security
- Advanced time-calculations
- Slowly Changing Dimensions (SCD)
- Required for some reporting tools
The typical architecture I see looks like this:
