bridge table and junk dimension on customer dimension (bank/credit union)

Hello,

I am working on building a dimensional model (first one) for a credit union. I am following the Kimball methodolody in the Microsoft Data Warehouse toolkit and the Dimensional modeling 2nd edition.

I am using Microsoft SQL server 2008 with SSAS.

I will be implemention the bridge table structure (customers to product) to the grain of one balance per product per day in the fact table.

Three related questions:

1) If I have a bunch of flags and miscellaneous attributes about the customer (has chequing account (Y/N), age segmentation (0-18, 18-25, etc), has investments (Y/N), etc). Can I make a junk dimension that attaches to the customer dimension? I was under the impression that junk dimensions should be connected through the Fact table, but this would not work in this case because of the bridge table.

2) I want to include an attribute of the customer that would enable me to filter based on their total share of wallet with the credit union (total loans+total investements+ total chequing accounts, etc), do I create an attribute in the main customer dimension and use a partition function in SQL, and add it to the customer main table, or the separate junk dimension as per the above?

2b) Value band reporting: I have reread the value band reporting section many times, but I do not know how to implement it. I understand that I will create a mini dimension with the value bands, but how would I connect that to the customer dimension to query for customer with >$x for example (from question 2b above)? I am used to the idea of foreign key primary key relationships, I have no idea of how to connect this value band reporting to the customer dimension.

Any ideas would be greatly appreciated.

Ivan

You are not implementing a bridge table. Your description sounds like an account balance snapshot fact table.

Hi ngalemmo,

Sorry my question may have been unclear. Yes, it is an account balance snapshot fact table, but there is a many to many relationship between the customers and products which then links to the main fact table which has the one account balance per row.

To rephrase my questions (to try to make them more clear):

1) in this structure, how do I implement a junk dimensions table that is based on the customer, but cannot be linked to the main fact table? ((has chequing account (Y/N), age segmentation (0-18, 18-25, etc), has investments (Y/N), etc))

2) Each customer would have a total services (loans+ deposits, + investments), does this figure live in the customer dimension, or should I add it to the junk dimension in question #1.

3) How does one actually connect a value band reporting dimension to another dimension (customer dimension for the total services $) in SSAS without PK and FKs?

Thank you for your time,

Ivan

I don't follow. What is a product? If I have an account, is it not one product?

A bridge does not associate one entity (customer) with another (product). A bridge associates groups of the same entity. For example, if you have accounts that are jointly held, you would have a customer bridge between the fact and the customer dimension. The fact key would represent a customer group which, through the bridge, fans out to the customers on the account.

The association of different entities is handled by the fact table. It would have a customer key, product key, and account key. Individually they may represent groups with an appropriate bridge.

#1 - junk dimensions are linked to facts, that is the point of having them. Why do you say they cannot be linked to the main fact?

#2 - the total customer holdings is an aggregate fact table. You could put it in the dimension table, but the update process can be messy. It you want history, it is best left as an accumulating snapshot fact.

#3 - banding is best done with small helper tables with min/max values. You join using the measure being banded.

Link to Image

Hi ngalemmo,

Thanks for taking the time to write back and help me through this.

I am trying to implement a spin off of what I see in Kimball's dimensional Modeling 2nd edition - Figure 10-3.





#1: If this is the case, then a junk dimension (that in my case is meant to be a bunch of flags and miscellaneous information about the customer) is not possible.
#2: Would I create another fact table that only links the total customer service totals to the customer dimension in this case?  
#3: Sorry for my ignorance, but I don't know what "You join using the measure being banded" means.  I would create the helper table, understood.  how do I "join" the two, is it a query that I create, or is there some functionality in SSAS?

Much appreciated,  

Ivan

You don't join or shouldn't join the product dimension to the Customer dimension. They should join to the Account Balance Fact table.

As far as joining to a banding table, assuming the band table has min_value and max_value columns, the join would be:

FROM fact_table f JOIN band_table b ON f.some_measure BETWEEN b.min_value AND b.max_value

That will get you the band you are looking for.

Thank you for the responses