Modelling Sales of Products and Product Packs

Hi all, I am currently building a warehouse for a large corporate client. They sell products via many different channels and receive sales metrics back from these resellers.
One of the challenges I have is that they sell single products and they also sell a number of products grouped up into a "pack" of products. When the feeds come in from the supplier individual products and packs come in exactly the same way (we can identify by the name which is which however) The challenge is how to model this, the sales metrics all come in the same way, regardless of if it is a pack or a single product sale, with a line value.

Now the client will be providing a list of "packs" and the individual product contents of each pack and also a weighting factor of how the pack sale value will be distributed between its individual products.

So if they sell 10 of pack A and pack A contains products 1,2 and 3 then they want ten fact rows for each product 1,2 and 3 (30 fact rows in total).

At the moment I have modelled it so that the fact links to a "pack" table and that has a factless fact between it and the product table to manage the many to many and weighting value. The instances where it is a single product sale I plan to create a "pack" & single factless fact record for every product individually and manage it all that way.

There is an additional complexity to this in that for each reseller for each product & pack the client gets a different percentage of the final sale price (these will be provided by the client also)

I'm not 100% convinced the way I have done it is the best way to do it however and would like some guidance. The other approach I am considering is to have the product table connect to a factless fact to manage the percent of sale then hook that factless fact into both the pack table and also hang it directly off the fact also.


Any guidance would be greatly appreciated and if I'm not explained anything very well then please ask


Thanks in advance

For the first problem, my first inclination is to provide two product dimension keys on your fact:

1. Product Key
2. Pack Product Key

For items that are sold individually, the Pack Product Key value will point to a default product row (i.e. No Pack). When items are sold as a pack, it would have a value according to the lookup. All records will point to a specific Product.

As you stated, you would still "break out" each pack into separate rows as the client requested. But IMHO, you should apply the weighting values at the time of ETL, not dynamically joined and calculated on the fly. This will improve your analysis performance. The weightings can be stored in a work table.

----

For the second problem, I think you are going down the right path, but... I would not make the reseller percentages live in their own fact that needs to be joined to another fact in order to calculate the credit amount. Again, the percentages should be applied to the transaction at the time of ETL.

Thus, your fact would contain a column called: Reseller Credit Amount. This would be calculated during the fact load based on the percentages stored in the work table.

Thus, I see your fact having:

Client Skey, Product Skey, Pack Product Skey, Quantity, Final Sale Amount, Reseller Credit Amount

----

The two tables described can be used as ETL work tables only. If you need to provide the actual percentages or weightings used and a report of how they have hanged over time, then you could store in a factless fact as well.

Hi thanks for the reply, yes I was looking at doing that exact same thing, having both the product and product pack key on the fact, however the reseller percent of sale complicates this approach because a pack has it's own percent of sale, independent of the products, which can raise the situation were a pack contains products that have different percent of sale values. So I would have to maintain two bridge tables, one by individual product and reseller contain the individual product % of sale and a second table by pack and reseller with its own percent of sale.
If I added the product to the fact as well as the pack, and the row in question was a pack sale I would be linking to a bridge table (to get to the individual product) that would have an invalid percent of sale as it would be the products individual percent of sale and not the pack from which it came.
My other concern here was the multiple possible routes to a product, because I have to record which products are in which pack and the percentage of that pack value they get. I'm sure the end users will want to drill down from a pack to see it's composite products.

I would love to do all the weightings during the ETL but as I have it currently, the grain of the fact is the transaction and a transaction can be either a single product sale or a pack sale. So I will certainly be calculating and storing the percent value my client get and what the reseller takes in the fact but the distribution of that value over the products in the pack will have to be dynamic

Just to give some clarity let me give some scenarios:

Scenario 1 (simple):
A sale is made by reseller A of Product 123 for £100, product 123 is an individual product and the reseller takes 20% of the sale of this product. My client then needs to record that sale by reseller A of product 123 and the value is £80

Scenario 2(More complex):
A sale is made by reseller A of product 456 for £100, product 456 is actually a pack containing products X, Y and Z.
The client has set this pack up in the system and has told us that the reseller takes 20% of the sale of the pack.
The client has also told us that Product X should take 20% of the pack final value, product Y should take 15% of the pack final value and product Z should take 65% of the pack final value.(by final value I mean after the reseller has taken their cut)
The client want to see both the individual product sales but also pack sales and a combined view of the two (all sales of product A regardless of if it has been sold as an individual product or as part of a pack)

My current design will store the transaction value, so product 456 was sold and we got £80. When they want to report on the individual products the system will on the fly apply the percent of pack value per product to give the relevant values.


Hope this all makes sense and all input is welcomed


Thanks

elmorejr wrote:For the first problem, my first inclination is to provide two product dimension keys on your fact:

1. Product Key
2. Pack Product Key

For items that are sold individually, the Pack Product Key value will point to a default product row (i.e. No Pack). When items are sold as a pack, it would have a value according to the lookup. All records will point to a specific Product.

As you stated, you would still "break out" each pack into separate rows as the client requested. But IMHO, you should apply the weighting values at the time of ETL, not dynamically joined and calculated on the fly. This will improve your analysis performance. The weightings can be stored in a work table.

----

For the second problem, I think you are going down the right path, but... I would not make the reseller percentages live in their own fact that needs to be joined to another fact in order to calculate the credit amount. Again, the percentages should be applied to the transaction at the time of ETL.

Thus, your fact would contain a column called: Reseller Credit Amount. This would be calculated during the fact load based on the percentages stored in the work table.

Thus, I see your fact having:

Client Skey, Product Skey, Pack Product Skey, Quantity, Final Sale Amount, Reseller Credit Amount

----

The two tables described can be used as ETL work tables only. If you need to provide the actual percentages or weightings used and a report of how they have hanged over time, then you could store in a factless fact as well.

I believe that my original thoughts still hold. I tried to illustrate in this table:



Green = Scenario #1, Blue = Scenario #2

I did add a new column to the mix: Pack Quantity

The Pack Quantity and the Original Amount are allocated using the rules driven by the Pack-2-Product rules {i.e 456 --> X (.2), Y (.15), Z (.65)}. The Final Amount and Reseller Amount columns will follow the other calculation for determining the Client's cut (20%).

Again, by doing all of the calcs up front, you can still account for the various allocations for the packs and still total to the original values.

At the moment I have modelled it so that the fact links to a "pack" table and that has a factless fact between it and the product table to manage the many to many and weighting value. The instances where it is a single product sale I plan to create a "pack" & single factless fact record for every product individually and manage it all that way.

This is fine. (By the way, it is a bridge table, not a factless fact table).

Creating a row for each item in the pack is not a good idea as it complicates use of the main sales table, and the bridge table serves the same purpose.

A potential risk with the bridge is the business may wish to change how they allocate revenue within a pack, so there may be maintenance issues as well as historical issues.