question

Mark avatar image
Mark asked

What are the advantages of using BI Cubes over a regular Warehouse?

I've read a good deal about BI, but have yet to find a definitive answer to this.

business-intelligencecubes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

Ben has mentioned speed which can be one of the major reasons for using cubes over traditional relational data, but as he rightly points out it does really depend on the amount of data you have in the first place.

Other reasons to consider are:

  • Multi-Dimensionality - if there was only one word allowed to describe OLAP cubes it would be multidimensional. These data structures allow the data (and therefore the business they model) to be analyzed in the most logical way. Key to this concept is the ability to assign hierarchies to the data.

  • Analysis - OK this very much depends on the tool or front end that is layered over the data, but the idea that you can very quickly navigate around the data, finding trends, spotting patterns, 'drilling down', 'slicing and dicing' - again are all key to the concept of cubes. Allowing the user to intuitively 'wander' around the data, not even realising that they performing analysis.

Don't get me wrong, all of this is possible with relational data structures (it's called ROLAP), but cubes can make it easier.

Often what is underlying any reporting technology, is a structured data warehouse / data mart (call it whatever you want), and it is here that the real benefits are made, consolidating the data from various sources into a single corporate view of the business, removing ambiguity, forcing standardization, creating a common language. Creating cubes or simply exposing the data for relational reporting is just adding the icing on the cake, and allows the users to get to the information.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ben Rees avatar image
Ben Rees answered

Do you mean what are the advantages of using cubes over a standard OLTP database? Or using cubes over a Data Warehouse (where the data has been de-normalised, ready for cube building..)? I think, in either case there are two reasons (the first being the main one):

1) Speed,

2) Creating clean, structured views on the OLTP database that anyone can understand.

On the first, OLTP databases are generally optimised for INSERTs and UPDATEs, not reads. Therefore running even basic queries on large databases can take far too long. NB: I emphasise "large" because if your transaction database is only small, then I don't think there's much point implementing a cube. Unless, you consider the second advantage..

Creating clean, structured views on the data. An OLTP database will contain all sorts of complexity (some documented, some not) which will make it very difficult to a user who isn't familiar with the structure to understand what he/she is looking at. By translating the data in to facts and dimensions it is clear to the user what data is relevant/of interest and that data should be cleaned or re-formatted so that it makes sense. For example, if your database has NULL values for "Invoice Date", how should these be dealt with? The process of cube-building will generally include some work on how to handle NULLs properly without the end user having to worry too much about them.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.