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

  • 1
    If someone can go in and make ad hoc changes to the data... sounds like you need to take better control over data security so that these discounts are properly recorded and are a fully documented part of the order. Apr 2, 2012 at 15:46
  • 1
    I couldnt agree more. Unfortunatly the current situation allows them to do so. (was this way before I was hired) the plan for the future is to lock this down.
    – Lumpy
    Apr 2, 2012 at 16:10

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.