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

View previous topic View next topic Go down

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

Post  ivan77 on Mon Nov 25, 2013 10:01 am

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


ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

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

Post  ngalemmo on Mon Nov 25, 2013 12:06 pm

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  ivan77 on Mon Nov 25, 2013 12:22 pm

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

ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

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

Post  ngalemmo on Mon Nov 25, 2013 4:35 pm

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  ivan77 on Tue Nov 26, 2013 10:25 am



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


Last edited by ivan77 on Tue Nov 26, 2013 10:30 am; edited 3 times in total (Reason for editing : trying to fix image, added link to image)

ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

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

Post  Jeff Smith on Wed Nov 27, 2013 2:32 pm

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


Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Nov 27, 2013 2:40 pm

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  ivan77 on Wed Nov 27, 2013 3:14 pm

Thank you for the responses

ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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