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

Mark gravatar image

2.6k 23 25 27

(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

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(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

Ben Rees gravatar image

Ben Rees
61 2 2 8

Yes, I probably should have been more specific about what I meant by a "Warehouse." I think that ideally a warehouse should be more like summary tables for reporting. If so, then I think that the advantages you mention could be built into the warehouse except maybe in special circumstances. The data could be cleaned up and of course it would run faster since there would be no need for one row per transaction. Millions of rows could be summarized into hundreds or thousands of rows. You have provided a good answer - that's what I've read - but I'm still not completely convinced.
Oct 12, 2009 at 04:02 PM Mark
You're right, it's the summarizing that provides the speed. However, the cube essentially is that summarization. Without creating the cube you would have to create all of those summary views yourself. Then, when a user turns around and asks for something slightly different you'd have to re-do your SQL code to take in to account the changes. Cubes also look after issues such as splitting date fields up properly in to days, weeks, months, quarters, years and so on - without this you would have to handcraft the SQL to aggregate by all of these different values.
Oct 12, 2009 at 04:56 PM Ben Rees
I think the speed issue is more of a timing issue. You are moving the calculations forward in time to the cube processing time, instead of query time. You also trade some storage space for very quick answers to analytical queries.
Oct 13, 2009 at 03:54 PM Steve Jones - Editor ♦♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 10819 times

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