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?
- 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 DW
- 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)
- DW is place to integrate data
- Risk of having multiple cubes doing same thing
- DW keeps historical records
- Easier to create data marts from DW
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:
