One cube or many
3 posters
Page 1 of 1
One cube or many
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
Re: One cube or many
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.
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.
Re: One cube or many
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
Re: One cube or many
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.
Re: One cube or many
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.
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
![-](https://2img.net/i/empty.gif)
» SSAS Cube - zero downtime even during cube processing
» Calulcations YTD in the cube
» Understanding Cube
» Zero Data Cube
» Cube Designing
» Calulcations YTD in the cube
» Understanding Cube
» Zero Data Cube
» Cube Designing
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|