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

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

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

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

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).

thanks.