How to model Product Bundles

The situation:
Wholesale sales business process

1. SKU1 and SKU2 can each be sold separately.
   
2. SKU3 can also be sold separately but is a kit of SKU1 and SKU2.
   
3. We need to calculate royalties for each unit we sell of SKU1 and SKU2.
   
4. SKU1 has two royalty payments. (two different licensors)
   
5. SKU2 has one royalty payment. (to a third licensor)
   
6. Not all SKUs will be sold in a bundle, and the same SKU sould be sold in more than one bundle.

I currently have a star schema for Direct Sales that includes a product dimension which I want to use for the wholesale process. We don't sell bundles direct to consumer.

How do I model the data? Is it a separate dimension all together? Is it some type of bridge?

Any help would be greatly appreciated!

Dan

Use a bridge.

Something like:

sku
component sku
royalty agreement
vendor
% of sale allocated
effective/expiration dates

There would be multiple rows for a component that has multiple royalty arrangements, the total % under the parent sku should total 100%.

You may also consider replicating this information in the sales facts as well, adding additional measures for revenue allocations and possibly the royalty dimension as well. It would make for easier reporting, assuming royalty arrangements and allocations change over time.

I already have the Royalty measures in the Direct fact table and plan on the same for Wholesale, so we're on the same page there.

So how do I handle sold SKUs that aren't part of a bundle? Would those exist in the bridge table as a single row with 100% allocation? So as a result, I would essentially have one row for each of the rows in DimProduct, plus the rows that make up the bundle?

This is the first time I've dealt with a bridge table - thanks for the help!

Yes, for non-bundled skus you would have a row with the same bundle sku and component sku key and a 100% allocation.

So would this approach make it necessary to rework how I've done the Product dimension for the Direct to Consumer sales? DTC doesn't sell bundles in the same way Wholesale does.

I want to be able to drill across to Wholesale numbers and see them in the same context as DTC.

Not really, other than maybe adding a flag in the product dimension indicating a particular sku is a bundle.

Your sale is going to reference a product which is either a single item or a bundle. Without the bridge, the query would show the sku sold, regardless of type. With the bridge in the query it would show single items and the components of bundles. So you use the bridge or not depending on what you want the query to show.

In our environment, a bundle is a product as well as the the individual products that may or may not participate in a bundle. For example, I may have local phone and DSL service. If I have a bundle I will also have a bundle product in addition to the local phone service product and the DSL service product. If I don't have a bundle, the bundle product (which triggers the discount) will not be on the account. My fact grain is at the product level so I'm not facing the same issues as you are.

I think Nick has provided the conceptual solution. Here is an example and some more references to help clarify it for you.

This approach is an application of the bridge table in the context of variable depth hierarchies. In the product context, especially in manufacturing, this is often referred to as the bill of materials (BOM). You could call it the Product Bundle bridge table.

Here is some sample data to show how it works. Since the hierarchy only has two levels in your example, the level flags don't make as much sense. However, if you add another level, like cases of bundles shipped to retailers, then you will be all ready.

First, the Orders and Products tables (based on sample data I made up on the plane!):


Here is a simple product bridge table I called Product BOM, for bill of materials:


You will need to figure out where to put your royalty percentages. I put a revenue allocation in the bridge table which would allow you to credit different revenue amounts to different participants.

Here is some SQL to see how you would query these tables:

To report sales by Order SKU
SELECT Product.Prod Desc, SUM(Amount * BOM.Allocation Pct)
FROM Orders O
JOIN Product BOM BOM ON O.SKU = BOM.SKU
JOIN Product P ON BOM.Child SKU = Product.SKU
WHERE BOM.Top Flag = 'y'
-- Or, you can join straight to the Product table

To report sales by base SKU
SELECT Product.Prod Desc, SUM(O.Amount * BOM. Allocation Pct)
FROM Orders O
JOIN Product BOM BOM ON O.SKU = BOM.SKU
JOIN Product P ON BOM.Child SKU = Product.SKU
WHERE BOM.Bottom Flag = 'y'

Finally, here are some references to dealing with hierarchies in the books and articles:
pp. 268-270 of Lifecycle Toolkit, 2nd Ed.
pp. 162-168 of the Data Warehouse Toolkit, 2nd Ed.
pp. 199-204 of the Data Warehouse ETL Toolkit; includes SQL to unpack recursive parent-child
Design Tip #17 - Populating Hierarchy Helper Tables
DBMS Magazine - 9/98 - Help for Hierarchies http://www.dbmsmag.com/9809d05.html

Good luck!
--Warren

I'm using IBM Cognos 8 for Data Modeling and BI reports.
Anyone can tell me some suggestions about designing in Framework Manager for variable-depth hierarchy?
I think we need some advance techniques ...

Thanks in advance!