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:
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.
answered Oct 12 '09 at 05:02 PM
Kev Riley ♦♦
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):
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.
answered Oct 12 '09 at 02:38 PM