Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact Dimension question

4 posters

Go down

Fact Dimension question Empty Fact Dimension question

Post  AmandaWoods 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

Back to top Go down

Fact Dimension question Empty Re: Fact Dimension question

Post  pkettley 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

Back to top Go down

Fact Dimension question Empty Re: Fact Dimension question

Post  ian.coetzer 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
ian.coetzer
ian.coetzer

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

Back to top Go down

Fact Dimension question Empty Re: Fact Dimension question

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Fact Dimension question Empty Re: Fact Dimension question

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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