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:
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
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.