One cube or many

View previous topic View next topic Go down

One cube or many

Post  MParker on Wed Jul 07, 2010 10:54 am

We are currently looking at moving our current warehousing and OLAP reporting to a dimensional model, currently we have multiple cube databases each containing multiple cubes. It the best approach to develop a single cube database with a single cube and utilise perspectives, create a single cube database but have multiple cubes or have different cube databases to meet each of the different application areas?

MParker

Posts : 2
Join date : 2010-07-07

View user profile

Back to top Go down

Re: One cube or many

Post  ngalemmo on Wed Jul 07, 2010 11:30 am

I wouldn't build a warehouse using cubes (i.e. MDDB technology). MDDB (Multi-Dimensional Database) technologies are limited in their ability to store large amounts of highly detailed data, which should make up the foundation of any data warehouse.

The warehouse should be made up of atomic fact tables and related dimensions using star schemas on a relational database platform. On its own, it can support OLAP (you DON'T need cubes to do OLAP) as well as allow integration across multiple subject areas. Once built, you can OPTIONALLY publish cubes for specific applications or for performance reasons. This is relatively easy to do from a properly designed dimensional warehouse. Most cube based BI tools support drill through functionality which allow a user to access detail in the relational source that is not otherwise available in the summarized cube.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: One cube or many

Post  MParker on Wed Jul 07, 2010 12:00 pm

Sorry I wasn't clear, we are planning a dimensionally modelled warehouse with Fact and Dimension tables, the cubes will then be built off this warehouse, in a prototype we have developed, we have a single cube with different perspectives, is this the best approach, or should you build multiple cubes to do the OLAP reporting.

MParker

Posts : 2
Join date : 2010-07-07

View user profile

Back to top Go down

Re: One cube or many

Post  ngalemmo on Wed Jul 07, 2010 12:56 pm

No. Generally cubes are very subject specific. Trying to 'do it all' with one cube is counterproductive and will lead to significant problems as requirements grow. If you go with cubes, assume from the beginning that there will be many and build your infrastructure to support it.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: One cube or many

Post  Jeff Smith on Tue Aug 31, 2010 3:26 pm

I think it also depends on the software. Cubes within the database software can be bigger than cubes sitting outside the database.

We've upgraded to SQL Server 2008. The recommended approach (by Kimball) for cubes is interesting and a bit of a paradigm shift - few aggregate tables and lots and lots of cubes. They suggest putting the detailed fact tables in a cube which was shocking. I believe the thought process is that the cubes are fairly compact and very, very fast.

Building Cubes is more art than science. Bottom line is to try something and see how works. If performance is bad, then break up the cube.

The biggest problem with cubes, in my opinion, is that that people get hung up on the term. They define the cube before they define the need. It shouldn't matter to the user if they are using 1 cube, 10 cubes, or the detailed fact tables as long as they are getting the information they need.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: One cube or many

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum