CODEWITHSUNDEEP
databasedata-warehouse
naheliegend
naheliegend

Reputation: 197

DWH: Why build cubes, when you already have a DB star schema?

my etl process collects and transforms data into a DB base with facts and dimensions. so why should I build cubes out of this? Is there more than just a the speed benefit of queries and the pre aggregiation of values?

Thank you for helping

Upvotes: 1

Views: 475

Answers (1)

NickW
NickW

Reputation: 9788

Facts and dimensions are tables that can be built in almost any relational database. To improve performance you can build aggregate fact tables in the same relational database. These tables are generic in that, with very little effort, you could move these tables from Oracle to SQL Server, as an example.

At the risk of over simplifying, a cube is a type of aggregate fact table but is built in a multi-dimensional database and is, normally, specific to that flavour of database. So if you build a cube in SSAS you couldn't move it to Hyperion Essbase.

For a simple query, such as sum transaction amount by date, cubes would not give you much/any benefit over facts. For complex queries, the performance tends to be significantly better than with facts.

Cubes normally support their own query language (e.g. SSAS and DAX) that allow much more complex queries than can normally be written in SQL (without a lot of effort)

So whether you should build cubes depends on a lot of factors, such as:

  • Are you running a lot of complex queries that would perform better in a cube?
  • Would the improved performance be worth the effort/cost involved?
  • Is there a cost/benefit case for deploying a MOLAP (cube) database as well as your dimensional database? Cubes are normally populated from facts/dimensions so they are an addition to, rather than a replacement for, facts/dimensions

Upvotes: 2

Related Questions