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