Combining master and history tables to fact

View previous topic View next topic Go down

Combining master and history tables to fact

Post  rbs100 on Wed Sep 21, 2011 6:01 am

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.

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  buckleyc on Wed Sep 21, 2011 9:01 am

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

Posts : 7
Join date : 2011-09-19

View user profile

Back to top Go down

Order and order history table

Post  rbs100 on Thu Sep 22, 2011 6:25 am

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

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  VHF on Thu Sep 22, 2011 10:36 am

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

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Combining history and master

Post  rbs100 on Thu Sep 22, 2011 1:36 pm

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

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  buckleyc on Thu Sep 22, 2011 2:11 pm

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

Posts : 7
Join date : 2011-09-19

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  rbs100 on Fri Sep 23, 2011 5:04 am

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.

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  BoxesAndLines on Fri Sep 23, 2011 9:13 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  rbs100 on Sat Sep 24, 2011 8:47 am

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.

rbs100

Posts : 12
Join date : 2011-09-14

View user profile

Back to top Go down

Re: Combining master and history tables to fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum