Fact Dimension question

View previous topic View next topic Go down

Fact Dimension question

Post  AmandaWoods on Thu Jun 30, 2011 5:14 am

hi,

I have a fact table with two dimensions, Date and Rank, and I have an attribute on the table, ClientName, as well as lots of measures Amount1, Amount2 etc.
The ClientName is different for each combination of Date and Rank. I want users to be able to view the ClientName attribute when querying the measures in Excel.
I think a fact dimension is the way to go but all the documentation shows how to create a fact dimension using an attribute on the fact table that relates to one of the dimensions (like some order information relating to the order number on a fact order table). However my attribute relates to the combination of both key fields on the fact table. Different date / same rank will have a different clientName, and same date / different rank will have a different client name. So how do I do this?

Thanks

AmandaWoods

Posts : 7
Join date : 2011-04-13

View user profile

Back to top Go down

Re: Fact Dimension question

Post  pkettley on Mon Jul 04, 2011 8:23 am

Hi Amanda,

How many different combinations of ClientName would you possible have within your Fact? You can use a special dimension for storing the different client name combinations and using the surrogate key in that table as one of your relationships in your fact, or you can use what is called an outrigger table between the date and rank dimensions.

Kind Regards,

Paul.


pkettley

Posts : 5
Join date : 2011-06-30

View user profile

Back to top Go down

Re: Fact Dimension question

Post  ian.coetzer on Mon Jul 04, 2011 1:05 pm

Hi,

Alternatively you could leave it on the fact table and call it a degenerative dimension attribute if i am not mistaken.
However if you want to try and move it out of the Fact table I would consider creating a 'junk' dimension.

Option 1)
Leave it in the fact table if: the clientname will be unique for say 80% or more of your fact records.

Option 2)
However if the client name is different for the date (year/month/day) and rank combination - and assuming that for one specific day you have several thousand fact records coming in then rather store it in a type of junk dimension.

Option 3)
However analyze the requirements in detail, MAYBE just MAYBE this should go into a DimClient and you can then rather create another DimRank both with their own attributes - THEN at the time that you have to insert a new FACT record lookup the particular client and rank combination and have their surrogate keys as 2 foreign keys in the fact table - then your Dimensional model will look nice and clean star schema with no funnies
avatar
ian.coetzer

Posts : 57
Join date : 2010-05-31
Age : 36
Location : South Africa

View user profile

Back to top Go down

Re: Fact Dimension question

Post  ngalemmo on Tue Jul 05, 2011 3:56 pm

Is the only thing you know about a client is their name? Are there no other attributes about a client that are of interest to the business?

It is not appropriate to have large text fields on fact tables. Performance will suffer. As far as the relationship between date and rank, it doesn't matter. You would have three dimensions with three FKs in the fact table. Set the FKs to reference the appropriate dimension row.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Fact Dimension question

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum