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:32 am

#114134

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 4 (of 4 total)

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

Mike Chappell Old Hand Points: 390 More actions · Answer 1

Re: Dates

I wouldn't. I think it depends on what you are trying to report though. To expand a little:

Joining a FACT table to a Dimension table effectively is creating an inner join. Say you had a Start_Date of 2006-05-01 and an End_Date of 2006-05-05... if you were to join both of those fields to the same field in your Time dimension you would get zero results.

When you use the data and use the Time dimension, what are you trying to report on... If it's the dat something started then join on the Start Date, if it's the end date then join on that. You could always have two time dimensions so the users can choose... just make sure they can't use both of them at the same time in your client else their results will be erroneous!

Simplistically.. if you always think about your FACT -> DIMENSION relationships as a standard inner join then you can test in SQL what results you can expect.

On the Views front... all my cubes are based on views and it works fine. Naturally, if your view is complicated, left joined or based on poor indexing it can slow down processing but the advantages for me far outweigh the couple of extra minutes processing.

Cheers,

Mike

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

Thanks very much, glad to hear that using views works fine. And i see your point about the inner join.

A good essay by Ralph Kimball i found

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

explains the use of 'roles' and provides many other compelling arguments for using seperate, at least virtually, dimension tables.

Thanks again,

Jules

www.sql-library.com[/url]

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

re: dimensions playing roles -> Analysis Services 2005 supports Role Playing Dimensions, where you can do what you described in your initial post but without the limitation that Mike described.

Steve.

Mike Chappell Old Hand Points: 390 More actions · Answer 4

Oh to be able to code on 2005...

My company refuses to let us upgrade to new software until it's been out for a couple of years and the creases are ironed out etc etc. I understand why given what we do but it's frustrating at times. RP'ing dimensions sounds so very, very useful.