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?

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.

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.

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.

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.