Bridge table - two customer-related dimensions

Hello everyone,
Probably the problem that I struggle with is not the most complex but I have stuck and I am not sure which way to choose. I present the model below:



- Currently the grain of the fact table is account and day (among others – not relevant here)
- Relation between Account and Customer is many-to-many so I have had to create a bridge table between the fact table and customer in order to be able to have aggregates at customer level.
- Moreover in requested reports I need to get different types of aggregated values for each customer - defined on the basis of the attribute Type_ID in Bridge Table (Account – Customer)
In the next step I have defined a minidimension with Customer Attributes that shall be tracked using SCD Type 2 (called here DIM_CUSTOMER_BEH).
My question is: How shall I connect this new dimension with the rest of the model?
1) Shall I create another Bridge Table to the fact in the similar way as for DIM_CUSTOMER? However in this case I need to have a reference between Customer DIM Surrogate Key and Customer Behavioral DIM Surrogate Key.
2) Shall I use existing Bridge Table by adding new foreign key to DIM_CUSTOMER_BEH (I have never seen a bridge table which connects two dimensions to the fact table.
3) Or perhaps the most optimal solution is to snowflake the model by adding foreign key directly to the Customer Dimension.

I would really appreciate any suggestion. Thanks in advance.

How big is your customer dimension? And how often do you get updates for your SCD2 attributes? Perhaps you can add the SCD2 attributes to your customer dimension?

Unfortunately Customer Dimension is quite big (more than 100 attributes in total), it is updated daily and there are more or less 500.000 customers to be handled so I had to discard your solution. Thanks anyway for reply.

I think you should consider adding a new fact table with a grain of day, customer, account to answer customer centric questions.

Is there a "lead customer" for each account? Use that as your single customer in the account grained table.

This makes me ask:

What is normally done when using a mulit-valued bridge table in conjuncton with an SCD2 dimension? I can't find any "multiple account holders" examples where the customer dimension is SCD2!

There is a solution to your problem in Kimball’s book ‘The Complete Guide to Dimensional Modeling’, Chapter 9 Financial Services.

Briefly, you have your bridge table connected by customer and account, and your fact table grain is one account per row. There is no direct connection between fact table and bridge table, and any measurement on customer in this fact table need to go through the path account-bridge-customer.

However with size of 500,000, the customer dimension can still easily grow into a monster dimension with multi million rows if you are not careful with type 2 SCD attributes. I would only leave high cardinality type 1 attributes (eg. Name, DOB etc.) in the dimension and snowflake high cardinality type 1 attributes. You then may need another transaction fact, as suggested by Bob, at the discrete customer grain to keep track of type 2 changes on customer dimension by correlating FKs of those dimensions that hold type 2 attributes including your DIM_CUSTOMER_BEH dimension. The customer grain fact can also be used for aggregations on customers. Please also refer to this relevant post: http://forum.kimballgroup.com/t1040-a-trio-of-interesting-snowflakes-article?highlight=trio

BTW, You may need to snowflake the customer dimension by DIM_SUSTOMER_BEH but only pointing to the current profile as it would be far more costly, if not impossible, to get it from the transaction fact table.

Thank you for all replies.

Bob Probst wrote:I think you should consider adding a new fact table with a grain of day, customer, account to answer customer centric questions.
Is there a "lead customer" for each account? Use that as your single customer in the account grained table.

@Bob: I think that by creating a fact table only with lead customers I am loosing possibility to answer customer centric questions for customers that are never lead customers (and this is one of requirements).

I have been thinking of the following solution (based on the Kimball Design Tip #50 http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT50FactlessFact.pdf ). Has anyone ever tried to implement this kind of model?
My approach would be to create two accumulating snapshot tables. One (FACT_ACC_CUSTOMER-ACCOUNT) which gives me possibility to calculate business indicators related to account balances considering different business rules, second (FACT_ACC_CUSTOMERMASTER) - which joins all customer-related dimensions in order to have full customer view. Any opinions?

hang wrote:BTW, You may need to snowflake the customer dimension

The second possibility would be more or less this one - to snowflake Customer dimension(s) - probably in this case might be the only feasible way...

Chris_S wrote:Thank you for all replies.

Bob Probst wrote:I think you should consider adding a new fact table with a grain of day, customer, account to answer customer centric questions.
Is there a "lead customer" for each account? Use that as your single customer in the account grained table.

@Bob: I think that by creating a fact table only with lead customers I am loosing possibility to answer customer centric questions for customers that are never lead customers (and this is one of requirements).

I wasn't clear. Your original fact grain can be day, account with an additional dimension for the lead customer. This could be used to answer account specific questions.
The second fact that I suggest would have a grain of day, account, customer. This would handle customer centric questions.

What are you using for your reporting layer? The ease with which it can manage the structure can have a bearing on the solution. The solution I stated above can be easily managed with Business Objects since the tool can be configured to use the correct set of facts and dimensions based on the objects used in a report. It's largely transparent to the users.

Bob Probst wrote:
I wasn't clear. Your original fact grain can be day, account with an additional dimension for the lead customer. This could be used to answer account specific questions.
The second fact that I suggest would have a grain of day, account, customer. This would handle customer centric questions.

OK. Thanks. I understand your approach.

Bob Probst wrote:
What are you using for your reporting layer? The ease with which it can manage the structure can have a bearing on the solution. The solution I stated above can be easily managed with Business Objects since the tool can be configured to use the correct set of facts and dimensions based on the objects used in a report. It's largely transparent to the users.

We are going to use SAS and I do not see any problems to implement proposed solution .

On the other hand. Has anyone ever implemented model similar to that I have described in my last post (customer-master accumulating fact table)?