One fact table/cube or multiple fact tables/cube

Hi,

I am now starting on my 2nd analysis server project and I have 8 dimensions and 6 fact tables. One fact table has 90 million rows, the other one has 30 million rows. The other four are less then 1 million rows big.

Should I create one cube with 6 fact tables in it or 6 cubes with one fact table ?

The advantage of the first one is that if you need to make a report you can have all the data in one query which is great for the users.

The advantage of the last one is that if you develop, you can easily calculate and test a small cube.

I am also thinking to get the best of both worlds namely going for the last option and a a 7th cube which has links to all the other six cubes.

Any suggestions ?

Constantijn Enders

This will have thoughts or considerations to address most of your questions.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1358193&SiteID=1

I would love to hear other people's thoughts, too.

|||

And here's a separate discussion on the same topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1402506&SiteID=1

|||

Hi,

I finally ended up at this blog http://prologika.com/CS/blogs/blog/archive/2006/06/27/1331.aspx

This confirmed my final thought, split into smaller and then one cube to rule them all

CE

One data source view and multiple data source

Hi,

In my datawarehouse we have different database one for dimensions and one for fact tables.

can we create a cube to pull dimensions from one data soure and fact from other databsource?

I recommend you to have the fact tables and the dimensions in the same database.

Your long term quick-fix is to use views between the databases.

Your short scenario description looks like you are building a cube directly from a source system.

If you need to connect another source system you will have to create a data wareouse to consolidate each source.

If not, you wille be creating information silos above each source system that you cannot connect to a second system.

HTH

Thomas Ivarsson

|||both the source are on the same SQL Server but different databases, I was planning to use View but was just considering the performance impact that will cause.|||

Actually Analysis Services allows for having dimensions and parittions to come from different datasources.

The caveat here is not to use different datasources to define your dimension. In such case Analysis Services might decide to use OPENROWSET clause as part of the query it sends during processing of dimension. This would slow you down considerably. But having partitions to come from different datasource should be perfectly fine.

Run Profier to capture SQL queries Aanlysis Server sends during processing and verify you dont get OPENROWSET is these queries.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.