SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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?

  • 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:

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...