Product Dimensions - Single Product Code Mutliple Services

View previous topic View next topic Go down

Product Dimensions - Single Product Code Mutliple Services

Post  ccioffi on Fri Mar 28, 2014 1:13 pm

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,

ccioffi

Posts : 7
Join date : 2014-03-28

View user profile

Back to top Go down

Re: Product Dimensions - Single Product Code Mutliple Services

Post  ngalemmo on Fri Mar 28, 2014 1:33 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Product Dimensions - Single Product Code Mutliple Services

Post  ccioffi on Fri Mar 28, 2014 1:47 pm

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.

ccioffi

Posts : 7
Join date : 2014-03-28

View user profile

Back to top Go down

Re: Product Dimensions - Single Product Code Mutliple Services

Post  Sponsored content


Sponsored content


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