Facts with same grain: some shared and some different measures

Hi everyone

I have a situation where I have transactional data from multiple source systems.
The grain is the same for all source systems, however due to the nature of the transactions (different products) or due to the source system being unable to provide certain information, there will be a few similar measures shared by the transactions from the source systems and then EXTRA measures per source system.

My question is this: Is is a good idea to store the transactions in the same fact table?
this would mean a few common facts would be populated but the "extra" items (which might need to be reported on) will be populated with nulls or is it more advisable to create separate fact tables per source system?

The shared attributes will be queried in a consolidated report.
AFAIK we are unable to calculate the "extra" fields

thank you for your time!
Nicole

You can do it either way. The plus for different fact tables is the model is easier to understand as you can explicitly see the measures that are dependent on the fact table. Given what you've told us, I would probably go with a single fact table and build views to present "multiple" fact tables.

Thank you for your reply
I will definitely use that approach for measures which are of the same grain.

I have since discovered that some of the facts might not be the same grain so I will create a seperate fact table for those.

thanks
Nicole

I tend to integrate whenever possible. My first data warehousing experience was at an HMO in the early 90's. They had built a data warehouse along product lines, separate tables for HMO, PPO, and Medicare products. Most of the data was pretty much the same, and the users, in particular the actuarial and clinical groups, wanted to see all the data regardless of product. Reporting was always a major headache, requiring 3 times the work.