I have a normalized database that I'm going to be using as a source for a dimensional data store; one of the facts I'm collecting is Orders, the normalized schema looks roughly like this:

OrderDetailUnits -> OrderDetails -> OrderHeaders

However when a new order is created in the source system, it doesn't have sizing information and so, there is no record for it in the OrderDetailUnits table.

I'm planning to make an Orders source table that flattens these tables into a single, denormalized table, but then I won't have a lowest-level record for quite a lot of the latest order data... which is expected as far as business is concerned.

How should I structure my Orders facts? Making separate "AllOrders" and "SizedOrders" fact tables / cubes would be annoying for the business users.


I agree. Your business users would not like "AllOrders" and "SizedOrders" fact tables.

I suggest creating a synthetic row in OrderDetailUnits for each OrderDetail where you have entries which are legitimately null. I recommend populating *NotApplicable* or something like that for each dimension which is in the OrderDetailUnits.

While not exactly parallel with what you're doing, I suggest reading up on Late Arrriving Dimensions to get some ideas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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