3 edited tags
| link
2 added 3 characters in body
source | link

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 dimensionsource 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 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 dimension 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 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.

1
source | link

What if the lowest granularity level can legitimately be null?

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