How do you model a dimension that behaves both as a dimension and a fact??

View previous topic View next topic Go down

How do you model a dimension that behaves both as a dimension and a fact??

Post  jflanner on Sat Dec 22, 2012 1:57 pm

I am in the credit card business. We have the concept of a subscription - which is a relationship between an individual, a bank, and time (Start Date, End Date). The concept is tangible in that a piece of plastic is issued. An individual can have multiple subscriptions on the same card - but not with the same Start and End dates. Subscriptions typically last a year.

The plastic is used to create transactions. So ... simple: Trans is a fact and - if star modeled has DateKey, IndividualKey, BankKey and SubscriptionKey. Subscription is a dimension with StartDateKey, EndDateKey, IndividualKey and BankKey. (Note - no attributes. It certainly looks like a fact.)

My problem is - we count subscriptions. We actually invoice on subscriptions so ... they are counted with much passion from the perspective of the bean counters. So - Subscription should be a fact. Further - from a grain perspective - we do not care about subscriptions when counting the transactions. But we do count transactions by some of the attributes in Subscription such as SubscriptionStartDate or Bank.

How do I model this?

jflanner

Posts : 5
Join date : 2012-06-04

View user profile

Back to top Go down

Re: How do you model a dimension that behaves both as a dimension and a fact??

Post  hang on Sat Dec 22, 2012 9:53 pm

I guess if you treat Subscription like fact then it is a fact. I think the key is to identify the grain of your Subscription fact table. I imagine you would have a card number that will stay the same even if multiple subscriptions have applied on the same card after a few years. So the card number and Start date identify the subscription, or as an alternative key if you already have a Subscription number.

If you don't have Subscription number, you may easily create a subscription NK by concatenating Card number and Start date. Now it's clear that you have a degenerate dimension (DD) for your Subscription fact. Think of Subscription as an aggregate fact of your transaction fact. So you would repeat all those dimension keys including DD (Subscription NK) around Subscription fact in the transaction fact table as well, therefore you will get all the relevant attributes you want for your transactions.

I am not sure if there is a potential fact allocation in your case. If allocation is applicable, you would have a single transaction fact that repeat subscription dimensions for each transaction under the same subscription. But you need make sure the measures for subscription can be allocated down to transaction level without double counting. If it's just count, the COUNT DISTINCT should work for allocated facts.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

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