I'm trying to design a set of fact tables for a datamart that will work in an ssas cube for one of our clients.
One of the challenges I'm facing is that there appears to be some disperencies between order item pricing and order pricing such that the summation of all of the order items does not equal the pricing of the order. This occurs because admins are aloud to go in after the fact and change an order price inorder to discount the customer. However no record of the discount exists.
In this case would I want to create a fact table for the order and then a fact table for the order item? If so I'm assuming I would need to create a key for the fact tables to maintain the relationships.
Is this an acceptable practice or will I run into problems with this type os solution when I go to build the cube. Any thoughts on a better way to model this would be appreciated.
Thanks in advancce
Best Answer
The simplest approach I can think of is to have one fact table for order items, and have two price columns in that fact table: original price, and discounted price. Original price would be the price as listed on the line item. Discounted price would be calculated by totaling the price of all line items on the order, calculating the percent difference between that and the total stated on the order header, then scaling down the prices of the line items using that percentage.