How many fact tables

View previous topic View next topic Go down

How many fact tables

Post  abokn on Sat Aug 27, 2011 9:11 pm

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.

abokn

Posts : 2
Join date : 2011-08-27

View user profile

Back to top Go down

Re: How many fact tables

Post  gerardnico on Sun Aug 28, 2011 12:24 pm

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



avatar
gerardnico

Posts : 15
Join date : 2009-08-02
Age : 44
Location : Netherlands

View user profile http://gerardnico.com/

Back to top Go down

Re: How many fact tables

Post  abokn on Mon Aug 29, 2011 7:41 am

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

View user profile

Back to top Go down

Re: How many fact tables

Post  gerardnico on Mon Aug 29, 2011 8:18 am

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.
You can do it without snapshot because your status date are transactional and then fixed.

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

avatar
gerardnico

Posts : 15
Join date : 2009-08-02
Age : 44
Location : Netherlands

View user profile http://gerardnico.com/

Back to top Go down

Re: How many fact tables

Post  BoxesAndLines on Mon Aug 29, 2011 8:54 am

I would go with an accumulating snapshot as well. It provides the utmost simplicity for what you are trying to do.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: How many fact tables

Post  nash on Mon Aug 29, 2011 7:02 pm

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

View user profile

Back to top Go down

Re: How many fact tables

Post  John Simon on Mon Aug 29, 2011 8:32 pm

You should create a transaction fact table first, then from that derive your accumulating snapshot table.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: How many fact tables

Post  hang on Mon Aug 29, 2011 8:49 pm

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

View user profile

Back to top Go down

Re: How many fact tables

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