Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension as a Fact

Go down

Dimension as a Fact Empty Dimension as a Fact

Post  Irtaza Hassan Mon Oct 26, 2009 6:52 am

Is it wise to use a Dimension as a Fact table as well?

The Scenario:

Whenever a subscription based service makes a sale a Sales Fact table records all the transactions. However each sale generates a new Subscription Code as well. I am not really sure how to handle this subscription code. I am tempted to create this as a degenerate dimension in the Sales Fact. However there are various other business processes that use the subscription code generated by the Sales process e.g. Subscription registration/Activation, Training provided to the subscriber, Subscription Payments etc. So this subscription code is a dimension.

Now should I create the subcode as a degenrate dimension in all the Fact tables? Or should I create a Subscription Dimension which stores other attribuites like Product, Registration and Activation Dates and current status and measures like lag between sale of product and registration of product. Thus making this table a dimension that can also be used as a fact table with keys to other dimesnions (product, registration/activation dates).

Irtaza Hassan

Posts : 7
Join date : 2009-10-19

Back to top Go down

Dimension as a Fact Empty Re: Dimension as a Fact

Post  Irtaza Hassan Mon Oct 26, 2009 10:12 am

Having a rethink I have created a Subscription dimension that has the Subscription Code, Start Date, End Date and Payment Method as attributes. I have also created a Subscription Record fact table that stores all the measures like SaleToRegistrationLag, SaleToActivationLag and EarlyCancellationLag with Activation/registration dates, product, customer etc. as the dimension keys.

Now the Subscription dimension will have 1:M relationship with Subscription Payments, but still it will have a 1-1 relationship with the Sales fact and Subscription records fact, which makes me question the vailidity of the design.


,

Irtaza Hassan

Posts : 7
Join date : 2009-10-19

Back to top Go down

Back to top

- Similar topics

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