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.

more ▼

asked Oct 12, 2009 at 01:32 PM in Default

avatar image

2.6k 24 27 31

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 12, 2009 at 05:02 PM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 12, 2009 at 02:38 PM

avatar image

Ben Rees
61 2 2 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 12, 2009 at 01:32 PM

Seen: 15343 times

Last Updated: Oct 13, 2009 at 12:27 PM

Copyright 2016 Redgate Software. Privacy Policy