Fact Dimension question

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

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.

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

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.