Warehouse design question - Fact Table Primary Key - 07-20-2004 , 05:37 PM
I think I know the answer to this question but, just in case:
I have a dimensional model in a star schema with 5 dimension tables
and 1 fact table. I understand (from reading Kimball) that the
primary key on my fact table should consist of a composite of the
primary keys on my dimension tables. But, in my case, these 5
dimension table primary keys don't describe a unique row in my table.
What I originally did was add the extra 3 (!) columns that made up the
alternate primary key in my production table to the 5 dimension table
keys to get a primary key for the fact table. I'm not so sure that a
table with a primary key of 8 columns is such a good thing though.
Am I breaking any rules that really matter (or any rules at all) if
keep the 5 Foreign Keys from fact to dimensions but make the primary
key on the fact table from the 4 columns of my production alternate
(This is a warehouse used by Cognos.)
Re: Warehouse design question - Fact Table Primary Key - 07-21-2004 , 04:00 AM
Be clear about the granularity required by the users of the data.
If there is a natural granularity that the current 5 dimension tables
don't expose & this is not a problem for end-users then fine.
There is no logical demand that the leaf level of the cube should be
the row level of the fact table (even if it is in most cases).
This is the purpose of drillthrough functionality.
svigil (AT) coinstar (DOT) com (Sandra Vigil) wrote in message news:<5a364092.0407201437.64cccf62 (AT) posting (DOT) google.com>...