Dimension as a Fact

View previous topic View next topic Go down

Dimension as a Fact

Post  Irtaza Hassan on 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

View user profile

Back to top Go down

Re: Dimension as a Fact

Post  Irtaza Hassan on 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

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