1 Dimension used multiple times in 1 fact table?

Hello,

I have a dimension used to store several types of 'bands' like monthly income bands, available income bands, risk bands, settlement bands etc. If I leave out all the lineage columns this is what the dimension looks like:

DimBandID

BandType

BandName

MinBandValue

MaxBandValue

Now I have a fact table that contains some facts about an application, the granularity is "Application",
this one application (which consists of one record in the application fact table) may be linked to several types of bands.
For example on one application I know what a person (prospective customer's) monthly income band is, available income band and even the risk band based on some scoring methods.

So the Fact table (again without lineage columns would look like this)

FactApplicationID

MonthlyIncome_DimBandID ***

AvailableIncome_DimBandID ***

DimApplicationDateID

My question: Is it correct to have several foreign keys (see the ones highlighted with ***) in one fact table that all reference the same dimension? is the propper star schema - OR - should ALL the band types be separated out into different physical dimension TABLES?

And is this naming convention ok? having the type name and then an underscore with the actual name of the primary key of the dimension??

Hi,
I think I have just found an answer to my question:

Star Snowflake Schema

In this example (see last image / diagram) it appears as though they link one fact table to the same dimension twice.
in this case it is a location dimension.
and they simply prefix the primary key with from / to etc.

So in my example MonthlyIncome_DimBandID and AvailableIncome_DimBandID will do the trick?

Here is the diagram I was referring to:

Absolutely. You just need to role name the FK's. Think of time dimension. I'll always need multiple relationships in my fact to the time dimension.

You could also snowflake the Location onto the branch table. Or you could keep location on the sales table and put it on the Branch table as a Type 1 (so that you had the current location of the branch).

If you snowflake the location dimension onto the Branch you can make it a type 1 where it get's updated if the branch moves or you can make it a Type 2 so that you know what activity occurred at the branch before and after it moved.

I have a situation where I need to know both - the activity before and after the move and restated based on the current location.

Kimball's modelers would probably not even have a Location Dimension. They would put the location information into the various dimensions such as the branch dimension. I like the location seperate.

Question... is the data coming in actual numbers or references to a band?

My experience with banding (age in clinical analysis, length of server in HR applications, etc...) has been that bands are not absolute. The business would typically like to create their own bands and apply different bands depending on the analysis they are performing. If that is the case, I would not create a band dimension and assign foreign keys.

If you are receiving actual numbers, I would store the numbers on the fact table and provide an adjunct table containing banding definitions (not really a dimension and not really a bridge) that would allow the user to apply a specified banding to the data at query time.

Hi Interresting thought.

Yip I was intending on storing the actual value, say a monthly income of a prospective customer is 15,215.65
Then I would store that value in the Fact table simply as [MonthlyIncome], and I was planning on ALSO storing a foreign key in the Fact table say [MonthlyIncome_DimBandID] which would refer to a band record so that the [MonthlyIncome] of this fact falls within the Band range.

That is why I'm planning on storing the maximum and minimum value of each band record in the dimension.

I'll role play the names of the foreign keys - as also mentioned in another reply, and with that ALSO stored the actual value in the fact table.
That way the users can group etc. by the set list of bands -

however - while this is all good and well something just started bothering me (a thought in the back of my mind)

what if ....

the business want to alter these monthly income band ranges in the future ....
if i update the DimBand (central table with all bands and ranges) what would happen to historic facts? will if be right for them to now simply be slotted into the NEW bands ranges? at least the ETL for the old ones will not be re-calculated - but that means that old facts will be linked to their original bands (unless i delete them!) and those original bands may now have diffirent max and min values ..... oh great it seems which ever way one goes their is almost ALWAYS a "what if" scenario ...

You could make the band dimension SCD2 and track all the change history so that you have options of applying either historical or current band definition on the fact. For historical view, the transaction fact will aggregate correctly at any point of time. For current view, you need to self join on the band dimension on the natural key and current band definition so that you can alway have a logical current band key in the fact.

Hi

From looking at the thread my understamnding is that you only store the natural value in the fact table not the FK of the row in the band definition table so the fact and band tables are not physically joined

Regards

Tim

Yes. The natural value either in the fact as a degenerate dimension or measure, or a dimensional attribute, with the band definitions off on the side. You would apply a specific set of bands at query time.