Hot swappable dimension and conformed dimension usage (bank/ credit union)

View previous topic View next topic Go down

Hot swappable dimension and conformed dimension usage (bank/ credit union)

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

Hello,

I am using SQL server 2008 with SSAS.

I am modeling a credit union (banking) with loans, deposits, investmemt with a granularity of one balance per day.

I have read Kimball's books Dimensional Modeling 2nd edition and the Microsoft Data Warehouse toolkit (as well at the Kimball tip on hot swappable dimensions). I am unclear how to actually implement it.

Hot swappable dimensions

I understand that I will build one common dimension, as well as build separate full dimensions for each of the different type of products that I need full dimension information for. What I do not understand is what to actually do in the cube. If right now I have the one standard dimension attached to the fact table, do I detach that one somehow and attach the full product dimension, or do I create another cube? In other words, I am not clear on what exactly the 'hot swapping' is, and how it is implemented in SSAS.

Conformed dimensions

I will be building another transaction table with granularity of one row per transaction. Does conformed dimensions mean that I would actually include this fact table in the same cube as the above fact table (granularity of one row per product balance per day)? Or, do I put the transactions fact table in a separate cube? Ultimately, I want to be able to see what type of transactions have occured in the month for the customer, as well as movements in their balance (drill across I think it is called) but I do not need the transaction amount to be addititve (do not need the transaction amount to reconcile to month end balance). I do not know where the second fact table should 'live', or how to implement the concept of a conformed dimension in SSAS.

I apprecitate any info on this, as I have been trying to figure this out for a long time.

Ivan


ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

Re: Hot swappable dimension and conformed dimension usage (bank/ credit union)

Post  Mike Honey on Mon Nov 25, 2013 9:36 pm

Hi Ivan,

Hot swappable dimensions

WRT Kimballs Design Tip #16 Hot Swappable Dimensions, I've built something similar to scenario #1 "Investment dimension" in SSAS. I collated/unioned together all the rows for all the variants, forcing them into common attributes (effectively Level 1 , Level 2 etc). I presented this as 1 SSAS dimension. The top level of all hierarchies separates the structures. This needs some user education, as in most client tools (e.g. Excel Pivot Tables) can bypass this.

Under the covers, I built a bridge view (which Investment Dimension rows relate to which Fact rows), and that was the basis of a Many-to-Many relationship in the cube.

Conformed dimensions

In the cube Dimension Relationships tab, just define the fact-dimension relationships the same way for a single dimension against multiple facts. Voila: a Conformed Dimension.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Hot swappable dimension and conformed dimension usage (bank/ credit union)

Post  ivan77 on Tue Nov 26, 2013 11:28 am

Hi Mike,

Thanks for the reply. #2 is exactly what I am trying to do.

"But when we restrict our attention to a single account type (e.g., mortgages), we swap in a drastically wider (more fields) dimension that only contains mortgage related attributes."

I don't understand what "swap in..." means.

Is this implementation the same as you suggested? I am sorry, but I do not understand how your solution works.

I create a uniform dimenison that will have the same attributes for all of the different products (loans, deposits, investment) which would include product start date, product closed date, etc. I'm ok with this one.

Do I create one database table per product type (loan, deposit, investment)- that I would actually populate in the database, which I would then somehow include as a full dimension table in the DSV and cube structure?

I very much appreciate you helping me out.

Ivan

ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

Re: Hot swappable dimension and conformed dimension usage (bank/ credit union)

Post  ngalemmo on Tue Nov 26, 2013 1:29 pm

All 'hot swappable' means is the other table uses the same surrogate primary key as the main dimension so that you can use an existing fact FK to join to that table instead (or in addition to).
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Hot swappable dimension and conformed dimension usage (bank/ credit union)

Post  ivan77 on Tue Nov 26, 2013 2:08 pm

thanks.

ivan77

Posts : 12
Join date : 2012-10-09

View user profile

Back to top Go down

Re: Hot swappable dimension and conformed dimension usage (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