How many fact tables
+2
gerardnico
abokn
6 posters
Page 1 of 1
How many fact tables
I am working on a data warehouse redesign assignment for a telco company. The warehouse is meant to have both normalised and dimensional structures. Data from source systems needs to be first staged (in a staging database), then integrated (in an ER database) and then moved to various data marts (in dimensional databases). Now here is my question:
In my ER/normalised database I have two tables "Order" and "Order Status" (open, closed, cancelled). The requirement is to report on #total orders, #open orders, #cancelled orders. And if users see there are 20 cancelled orders, they should be able to drill down to those individual orders. I have this requirement modelled in the ER database. How to do this in dimensional schema?
Is the following approach correct:
1. I must have transaction fact tables to provide detailed drill down. These facts will be actually very similar to my "Order" table "Order Status" table in the ER database.
2. Theoratically all reporting now can be done from these transaction fact tables, but it will make life difficult for our BI architect (he will have to calculate #open orders, #cancelled orders etc on the fly. #total orders should not be an issue though as it comes straight from Order transaction fact). Therefore, to help them...I should do next step
3. From this "Order Status" transaction fact, I should create a snapshot fact table (daily/weekly/monthly depending on requirement) which will take the latest status in that snapshot period (day/week/month) of individual orders and store them in the relevant fact (e.g. #cancelled orders, #open orders etc) count.
Please comment on this if this is right/wrong/incomplete.
Thanks.
In my ER/normalised database I have two tables "Order" and "Order Status" (open, closed, cancelled). The requirement is to report on #total orders, #open orders, #cancelled orders. And if users see there are 20 cancelled orders, they should be able to drill down to those individual orders. I have this requirement modelled in the ER database. How to do this in dimensional schema?
Is the following approach correct:
1. I must have transaction fact tables to provide detailed drill down. These facts will be actually very similar to my "Order" table "Order Status" table in the ER database.
2. Theoratically all reporting now can be done from these transaction fact tables, but it will make life difficult for our BI architect (he will have to calculate #open orders, #cancelled orders etc on the fly. #total orders should not be an issue though as it comes straight from Order transaction fact). Therefore, to help them...I should do next step
3. From this "Order Status" transaction fact, I should create a snapshot fact table (daily/weekly/monthly depending on requirement) which will take the latest status in that snapshot period (day/week/month) of individual orders and store them in the relevant fact (e.g. #cancelled orders, #open orders etc) count.
Please comment on this if this is right/wrong/incomplete.
Thanks.
abokn- Posts : 2
Join date : 2011-08-27
Re: How many fact tables
It will depends of the capacity of your BI tools.
I work intensively with OBIEE and I have just build this kind of requirement.
On your order, you just need to have a date for each status in your fact table. All other dimensions are shared.
Logically, you have one fact table by date because the date of the status are transaction date.
OBIEE can via alias, create this virtual fact tables (one by status and in your case one for open, one for cancel, one for closed, ...). OBI create one query by status to make the calculation against the same fact table on each status date and federate the result by the date of the report.
The name of this technique is known as the canonical time.
Your snapshot table is made to know the stock of your orders by status. You can also create them by calculation.
Which one is better, I don't know. Calculation on the fly can give you flexibility when your are on hour basis.
Cheers
Nico
I work intensively with OBIEE and I have just build this kind of requirement.
On your order, you just need to have a date for each status in your fact table. All other dimensions are shared.
Logically, you have one fact table by date because the date of the status are transaction date.
OBIEE can via alias, create this virtual fact tables (one by status and in your case one for open, one for cancel, one for closed, ...). OBI create one query by status to make the calculation against the same fact table on each status date and federate the result by the date of the report.
The name of this technique is known as the canonical time.
Your snapshot table is made to know the stock of your orders by status. You can also create them by calculation.
Which one is better, I don't know. Calculation on the fly can give you flexibility when your are on hour basis.
Cheers
Nico
Re: How many fact tables
gerardnico wrote:
On your order, you just need to have a date for each status in your fact table. All other dimensions are shared.
This will be like accumulative snapshot. This will work except that we also have a requirement to reproduce the same report at two different reporting days. So we do need to keep point in time history (i.e. transaction facts I suppose). I am not sure if I just go with accumalting snapshot or both. But more I think, more I am inclined towards keeping both fact tables.
Also, we are using Business Objects, not sure if the canonical time techniques is available to us in BO (will ask BI team...this sounds very good).
Thanks.
abokn- Posts : 2
Join date : 2011-08-27
Re: How many fact tables
You can do it without snapshot because your status date are transactional and then fixed.abokn wrote:
This will be like accumulative snapshot. This will work except that we also have a requirement to reproduce the same report at two different reporting days.
For the date of yesterday, you can know how many orders were still open for instance. It's all orders where the order date is above yesterday and the closed below. No snapshot is obligatory required.
Can you give us up-to-date on the BO side. I think that it's possible to import more than one time the same fact table in an universe but I don't remember. The last time, I worked with BO was with the version 5 then a long time ago.
Cheers
Nico
Re: How many fact tables
I would go with an accumulating snapshot as well. It provides the utmost simplicity for what you are trying to do.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How many fact tables
BoxesAndLines wrote:I would go with an accumulating snapshot as well. It provides the utmost simplicity for what you are trying to do.
Yes - that's how I had designed it too originally.
But what about the requirement of drilling to individual transactions (i.e. individual statuses). I will try to explain this......(sorry if wasn't clear).
An order can go through multiple statuses (even same) mutliple time in a day....e.g. open/closed/re-open/cancelled/re-open again. If I go with accumalating snapshot (pls correct me if I got this accumlating snapshot definition wrong...i suspect so) with daily frequency, one of the facts, among others, I will create will be #cancelled. If same order is opened then cancelled then re-opened and cancelled again on the same day, I will only be updating fact with the latest status (i.e. cancelled) - (is this right?).
So if users want to see all statuses for that order, I must have transaction tables to show them all statuseds went through (right?).
Your comments will be much appreciated as I am about to base-line the design (in next 3 days) for business review. Thanks.
nash- Posts : 18
Join date : 2010-03-12
Re: How many fact tables
You should create a transaction fact table first, then from that derive your accumulating snapshot table.
Re: How many fact tables
John is right! Accumulating snapshot table is normally built based on transaction fact just like other aggregate fact tables from base fact. So obviously you would drill down from aggregates to base for more granular facts.
hang- Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia
Similar topics
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum