2 Dimension FKs pointing at same Dim table?

Question

Post reply

2 Dimension FKs pointing at same Dim table?

Jules Bonnot

SSCertifiable

Points: 5488
More actions
May 9, 2006 at 3:31 am

#169501

If i have 2 dates for example,start_date and end_date, in a fact table is it ok to point them at the same date dimension. Likewise i have sales_manager and acccount_manager foriegn keys in the fact table which both point to a users dimension.
Do i need to create seperate dimension tables inorder for the star schema to correctly roll up into an aggregated cube. Could i use views instead of create multiple differently named instances of the same table.
The DW is built with SQL server 2005 and analysis services.
Any help would be greatly appreciated.
www.sql-library.com[/url]

Viewing 5 posts - 1 through 5 (of 5 total)

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

Augustin Carnu SSC Enthusiast Points: 181 More actions · Answer 1

No need to add duplicate dim. tables (aka 'alias' in SQ2K), using views works better in my experience

kenno_rules Mr or Mrs. 500 Points: 503 More actions · Answer 2

Hi. I haven't used AS2k5 yet, as I am stuck on AS2000. But from what I have read, what you are proposing sounds like a need for 'Role Playing Dimensions', whereby the same dim table (eg dimDate) can be used many times for dims such as OrderDate, ShippedDate, InvoicedDate etc. Sounds like an excellent feature, and I hope you can post back some of your experiences with using it.

Regards,

Matt.

ND-215452 SSCrazy Points: 2037 More actions · Answer 3

General speaking, the relationship between dim and fact is the dimkey. If your dim has start_date and end_date then yes your fact table should reference the dimkey.

Building UDM database(STAR) best to keep everything in physical storage form(ie. table), view is not.

Building cube:

1. you can create datasourceview mininize number objects/items to be accessed/seen in the cube.

2. your aggregates "maybe" incorrect due to the mapping of multiple dimensions to fact table. You can redefine the mapping in "Dimemsion Usage" tab in BIDS.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

Matt (hope the UK weather is not getting you down) 'Kenno' is right, the role playing dims in AS2K5 should be what you use to sort this out. In case you've not done it yet, just go to the Dimension Usage tab and add the same DB_dimension in 'x' times (where 'x' is the number of times you need it). You will need to specify cube_dimension names for each one (obviously) and then set what the relationship is (ie PK -> FK) between the tables.

Cheers,

Steve.