Dimension as a Fact
Page 1 of 1
Dimension as a Fact
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).
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
Re: Dimension as a Fact
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.
,
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
Similar topics
» Dimension Design with intermediate tables between fact and dimension
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Using the Date Dimension for Summary Fact Tables or new specialized Month Dimension?
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|