designing DIM table

Question

Post reply

designing DIM table

MuditGupta

SSCrazy

Points: 2596
More actions
August 12, 2008 at 10:17 am

#190910

Hi there,
I am working on creating cube. I have few doubts regarding designing FACT and DIMENSION tables.
Can a DIM table have composite primary key?
The thing is the relational table with which i am working with has composite primary key & i am wondering how to get DIM table from it?
Please advise.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MuditGupta SSCrazy Points: 2596 More actions · Answer 1

I don't think that is possible. One should go with creating surrogate key for DIM table.

however, i still have doubt regarding dealing with composite PKs as in.. creating SKs

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 2

There are a few good articles on why it is a good idea to use surrogate keys. As to the how, I would recommend using a staging database. I am fairly new to SSAS, and by no means a BI expert, but in my own experience having a staging database provides several advantages, including the ability to use your staging database for reporting (the surrogate keys provide much faster joins) seperate from your OLTP, as well as the ability to combine mutliple data sources into a business-centric database, seperating the data from legacy issues that plague many systems.

MuditGupta SSCrazy Points: 2596 More actions · Answer 3

yeah, I also came to same conclusion.

I came across this article: which explains advantages of having surrogate keys.

http://www.dbmsmag.com/9805d05.html

While going through some articles, it seems for generating SKs, one need Pk on single column.

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1156608,00.html

So, now, i am wondering ... is this a must condition?

Reason being, the tables I am working with.. don't have any PKs, FKs.. no RI.

though I can have a composite Pk & then go about generating SKs

Greg Milner SSCrazy Points: 2425 More actions · Answer 4

Hi, The best advice I can give you is get a copy of The Microsoft Data Warehouse Toolkit and read Chapter 1 through Chapter 6. It will save you so much trouble it will pay for itself within the first 2 hours of your work on any data warehouse. I cannot stress this enough.

You can see it here: http://www.amazon.com/Microsoft-Data-Warehouse-Toolkit-Microsoft-Business-Intelligence/dp/0471267155

The short answer is: No, no composite PKs in a Dim table. Yes, you must use a surrogate key and it must not be the same as the Business Key.

The Kimball book, mentioned above, will explain all this.

Cheers.

G. Milner

john.wilder SSChasing Mays Points: 625 More actions · Answer 5

Yes you can have a composite primary key in a dimension table. In the Dimension Wizard the step for specifying the key allows you to check multiple columns. A single column must be used as the name attribute, but in my experience one doesn't care much about the name of the key attribute since the key attribute will most likely be hidden. If the dimension is already built, then in the dimension editor bring up the properties for the key attribute and click on the Key Columns property. This will open a dialog that will allow you to add additional columns.

mickielsmith Right there with Babe Points: 723 More actions · Answer 6

I will agree with John. You can make a composite key via the wizard. Does your schema only include Dimensions to one FACT or do you have any snowflakes? If you have snowflakes, different logic will apply in SSAS.

Hi there,

I am working on creating cube. I have few doubts regarding designing FACT and DIMENSION tables.

Can a DIM table have composite primary key?

The thing is the relational table with which i am working with has composite primary key & i am wondering how to get DIM table from it?

Please advise.

Thanks