Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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. –  Aaron Bertrand Apr 2 '12 at 15:46
    
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 '12 at 16:10

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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