Can Fact table link to more than 16 dimensions?

Hi,

I am new to building cubes. I am trying to build a cube which includes:

7 database dimensions

22 cube dimensions (19 cube dimensions are liked to 4 table dimensions)

The problem is when I try to make the PK of the fact table to include the 22 fields I receive an error that the PK cannot be more than 16 fields. What should I do?

Thanks in advance,

Aref

It is a bit strange design. You should not have that many cube dimensions in you only have 7 database dimensions.

Make sure you take a look at the AdventureWorks sample database for example of how to build your cube.

Going through tutorial is also a good idea.

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

|||

I might be wrong. But I have 6 date cube dimensions which I relate to one date database dimension.

Aref

|||

Do you have all 6 columns in your fact table so you can relate Date dimension to your Measure Group?

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

|||

Yes I have the 6 cube dimension fields in my fact table. But what I know about star schema fact tables is that all dimension fields should be PK. Am I wrong?

|||

You are correct. That is the recommendation in most cases.

I dont think you should try and create a single PK including 6 columns. You can create 6 separate PK's.

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

|||

But these 6 columns are only part of the 22 dimension fields that I have.

I am thinking of creating another table that holds all the possible combination of these multiple dimensions related to one database dimensions the use then use the PK of that combination as a single dimension field in my fact table then use a view to represent my fact table and the dates combinations table in the cube.

Do you think that's a good way to solve the issue or you have better suggestions?

Thanks,

Aref

|||

Take a look at the AdventureWorks sample database and see how it implements Date dimension.

It is single dimension in the database but included 3 times in the cube. Every time it is playing different role Date, ShipDate, DeliveryDate.

If you look at the way relationship between FactSalesSummary table and DimTime table are defined you would see 3 FK- PK relationships.

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

|||

That's what I have so far, but the problem if I want to include every cube dimension in a PK FK relation I need to have a number of PK fields equal to my cube dimensions = 22 fields which violate the SQL Server 2005 limitation of number of fields in a PK.

Please Help!

Aref

|||

Are you trying to create a single PK with 22 fields or 22 distinct PK's?

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

|||

I don't know what is the difference. I want to define 22 fields in my fact table to be the PK for this table. In Star schema I gather all the PKs of the cube dimensions to be the PK of my fact table. Right?