rethinking sales invoice line modeling

Consider a sales invoice line table in the source ERP system that includes these three currency fields:

ExtendedListPrice
ExtendedDiscount
ExtendedSalesAmount

(In this example ExtendedSalesAmount = ExtendedListPrice – ExtendedDiscount.)

In the past, I have simply brought these three fields (or any two of them) over as measures in the DW fact table. All three fields aggregate correctly, and the user can choose whether they want to report sales based on list price or the actual discounted sales amount. There is a 1:1 ratio between source system records and DW fact table records.

However, I am toying with a design that would have only a single Amount field in the DW fact table. For each source system record there would be two records in the fact table: the first would contain the ExtendedListPrice in the Amount field; the second would contain the ExtenedDiscount as a negative value in the Amount field. There would be a 1:2 ratio between source system records and DW fact table records.

I would create an AmountType dimension to identify which records represent the ExtendedListPrice and which represent the ExtendedDiscount. I would also provide views for “list price sales” (would include only list price type records) and “actual sales” (would include all records, the aggregate of which represents discounted actual sales) that the users could use for reporting.

Why am I considering this alternative design? The existing design works fine in the example shown, but I am now facing a more complex variation. Consider this source table:

ExtendedListPrice
ExtendedDiscountTypeA
ExtendedDiscountTypeB
ExtendedDiscountTypeC
ExtendedSalesAmount

While the standard design of bringing each of these fields into the DW will still work, the fact table is now starting to get cluttered. My new design would still have only a single Amount field, but would now require up to 4 fact records for each source record. As before, aggregating all records would result in the discounted actual sales amount, equivilent to SUM(ExtendedSalesAmount).

In cases where ExtenedDiscountTypeB/C is zero (which happens frequently), there would be no need to create a fact record for that amount, so the final source system to DW record ratio would be somewhere around 1:2.5.

Any thoughts on this approach?

A lot of ERP systems have, to varying levels of detail, the components that make up a price. It usually include accounting information as well as categorizations of the particular pricing component (discount, added charge, etc...). This stuff belongs in its own fact table.

An invoice line fact and pricing component fact should coexist together. The invoice line is essentially a summary of the components and should be provided to keep things simple and to present the data in a commonly accepted manner. The component facts would be used where more detailed analysis is required.