Combining master and history tables to fact

Hi,
I have a situation where I have to link the order table and order history table to a fact.
Order table will contain only current or recent record and earlier data will be moved order history.
We tried having a fact table with keys from order table and order history table. But when we query the fact table for count of records it is not providing the right results due to the order history key. So what should be the approach in this case? We do not want snow flaking.

Can you supply some additional information for us?
What is DDL for the tables in question?
Can you supply sample data to demonstrate the issue?

I would think having OrderKey and OrderHistoryKey in the fact table would be enough, depending on what that history table looks like. You may have to use a snowflake design but without the layout and sample data I can't say for sure what options you have.

buckleyc wrote:Can you supply some additional information for us?
What is DDL for the tables in question?
Can you supply sample data to demonstrate the issue?

I would think having OrderKey and OrderHistoryKey in the fact table would be enough, depending on what that history table looks like. You may have to use a snowflake design but without the layout and sample data I can't say for sure what options you have.

The table structure would like like
Order table:
Order id
Order date
order amount
ProductId
Price
status

Order history:
id
Orderid
order date
Order Amount
Productid
Price
Status
Created Date
Modified Date

Looking at the example tables, I'm not quite sure whether they are intended to be facts or dimensions.

It looks like there is some relational design still showing and that these may not be fully dimensionaly modeled. In fact, having a Order dimension is usually a warning sign in dimensional modeling, as that can lead to a "fast changing dimension". Order information should usually be put in fact tables. Customer and Product belong in dimensions.

Did you use the Kimball 4-step process when designing these tables?

VHF wrote:Looking at the example tables, I'm not quite sure whether they are intended to be facts or dimensions.

It looks like there is some relational design still showing and that these may not be fully dimensionaly modeled. In fact, having a Order dimension is usually a warning sign in dimensional modeling, as that can lead to a "fast changing dimension". Order information should usually be put in fact tables. Customer and Product belong in dimensions.

Did you use the Kimball 4-step process when designing these tables?

Ok, actually I have provided the oltp data structure.
What is kimball 4-step process

Basically the 4 step design is
1) Identify the process you want to model for,
2) Define the granularity of the data to be stored,
3) Determine what the dimensions are,
4) Determine what the facts are.


What exactly is the scope of the work you need to accomplish?
Are you just trying to put together a report or are you building a portion of a warehouse/data mart?

buckleyc wrote:Basically the 4 step design is
1) Identify the process you want to model for,
2) Define the granularity of the data to be stored,
3) Determine what the dimensions are,
4) Determine what the facts are.


What exactly is the scope of the work you need to accomplish?
Are you just trying to put together a report or are you building a portion of a warehouse/data mart?

You mean these are 4 step process. Ok, we are any following this rule. We are building a mart. We have a situation similar to it.

Actually, I'm not sure what you're building. Any fact that joins to OLTP tables to get history or anything else for that matter doesn't qualify as a dimensional design. The order fact should contain all data required to generate reports.

BoxesAndLines wrote:Actually, I'm not sure what you're building. Any fact that joins to OLTP tables to get history or anything else for that matter doesn't qualify as a dimensional design. The order fact should contain all data required to generate reports.

These are the OLTP tables to be moved to be moved to data mart and segregated in dim and fact tables. Normally dwh gets the data only from OLTP.