Product Dimensions - Single Product Code Mutliple Services
2 posters
Page 1 of 1
Product Dimensions - Single Product Code Mutliple Services
Hello,
I am designing a model that will have a transaction fact table recording the addition/removal of services from a customer's account. The facts will be quantity and price (rate). The source system is a billing system that uses billing codes for services, and there is a rate for each code. The transactions are the addition/removal of codes from an account, so my question is about the granularity of the fact table Example:
Code Product Rate
1A Family Plan 69.95
1A Shared Data 69.95
1A Text 69.95
If code 1A is added to a customer's account, then they are adding all 3 products (even though 1 code was added to their account). If I have those 3 products in a dimension I will be inserting 3 rows to the fact table with a quantity of 1 each which is good, but how do I record the rate fact? Is it acceptable to divide this value by 3? In the source system, I do not have what the actual price breakdown is for the 3 services.
Product_Id Quantity Rate?
1 1 69.95 or 23.32
2 1 69.95 or 23.32
3 1 69.95 or 23.32
Thanks,
I am designing a model that will have a transaction fact table recording the addition/removal of services from a customer's account. The facts will be quantity and price (rate). The source system is a billing system that uses billing codes for services, and there is a rate for each code. The transactions are the addition/removal of codes from an account, so my question is about the granularity of the fact table Example:
Code Product Rate
1A Family Plan 69.95
1A Shared Data 69.95
1A Text 69.95
If code 1A is added to a customer's account, then they are adding all 3 products (even though 1 code was added to their account). If I have those 3 products in a dimension I will be inserting 3 rows to the fact table with a quantity of 1 each which is good, but how do I record the rate fact? Is it acceptable to divide this value by 3? In the source system, I do not have what the actual price breakdown is for the 3 services.
Product_Id Quantity Rate?
1 1 69.95 or 23.32
2 1 69.95 or 23.32
3 1 69.95 or 23.32
Thanks,
ccioffi- Posts : 7
Join date : 2014-03-28
Re: Product Dimensions - Single Product Code Mutliple Services
Product packages are best handled using a bridge table. The package would have a product code and each component would have a product code. You record the package product in the fact and use a bridge to break down the components (one row per component). The bridge could contain allocation factors to distribute revenue and cost.
The bridge would also need to contain identity rows for atomic products (one row with the same product key) so that it can be used in more general queries. You then either use the bridge or not depending on if you need an atomic breakdown of what was sold.
The bridge would also need to contain identity rows for atomic products (one row with the same product key) so that it can be used in more general queries. You then either use the bridge or not depending on if you need an atomic breakdown of what was sold.
Re: Product Dimensions - Single Product Code Mutliple Services
Thank you. I was thinking that the bridge was the way to go, thanks for confirming. In this scenario, is it acceptable to arbitrarily assign a weighting factor based on the number of components in a package? So in my example above it would be .3333 since there are 3 components in the package?
I know it should be up to the business, and the business wants to report quantity by the individual components (hence need for bridge), but is only interested in revenue at the package level. Since they are not interested at revenue at component level, I was going to assign a weighting factor by taking the number of components in a package and dividing by 100.
I know it should be up to the business, and the business wants to report quantity by the individual components (hence need for bridge), but is only interested in revenue at the package level. Since they are not interested at revenue at component level, I was going to assign a weighting factor by taking the number of components in a package and dividing by 100.
ccioffi- Posts : 7
Join date : 2014-03-28
Similar topics
» CRM DW, measuring product and services subscription
» Correlated - Separate Dimensions OR Single Dimensions ?
» Product and Related Dimensions
» Hierarchy within single dimension or two dimensions
» single denormalized dimension or 2 separate dimensions?
» Correlated - Separate Dimensions OR Single Dimensions ?
» Product and Related Dimensions
» Hierarchy within single dimension or two dimensions
» single denormalized dimension or 2 separate dimensions?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum