Data Modelling - Similar Facts

[samonsey]

I have a question relating to modeling the similar facts.

We have three types of facts. They are all "transactions", with a common set of core dimensions such as Customer, Contract, Location, Date. In addition, they each have a set of dimensions that are unique depending on the type of transaction.

The types of transaction are:
Throughput movement
ancillary activity
pipeline activity

My question is whether to have 3 fact tables or combine them into 1 table. The users usually want to see the data separate, but somtimes they want to see counts or averages across all three tables.

Also, there is the problem that the dimensions they share need to have slightly different names. For example, container. Throughput movements must have a source and destionation container, while pipeline and ancillary activities may or may not have one container.

Thanks,
Asa Monsey

Asa Monsey
samonsey@houston.rr.com
Maxim Group Consultant
Visual Basic / SQL Server

 

[Subhashab]

Hi Monsey,
The idea of combining three fact table into one is very dangerous. The design of fact is the cructial for the DW design. Therefore design the fact considering the information and analysis that can be perfomed with that fact table only. Now to allow users to drill down between fact tables it is essestial that they should be common dimension. In some cases I have designed special dimension called mapping table. This mapping table maps attributes between fact tables. They are also good candidate for storing information like count and averages.

 

[blom0344]

Consider merging everything into one facttable only if the set of shared dimensions is EXACTLY the same for all transactions. This requires full outer join strategies to merge the data , which becomes painfull with more than a few transactiontypes.
I have a combined sales/order table this way, but would not consider merging other data into it. The fact is that even with one big facttable multiple queries for creating complex report are the order of the day, so dealing with them in seperate facttables is not such a nuisance as it looks like.............

T. Blom
Information analyst
tbl@shimano-eu.com