Facts with same grain: some shared and some different measures

View previous topic View next topic Go down

Facts with same grain: some shared and some different measures

Post  LoveData on Mon Jul 30, 2012 2:24 am

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

LoveData

Posts : 2
Join date : 2012-07-30

View user profile

Back to top Go down

Re: Facts with same grain: some shared and some different measures

Post  BoxesAndLines on Mon Jul 30, 2012 9:10 am

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

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

View user profile

Back to top Go down

Re: Facts with same grain: some shared and some different measures

Post  LoveData on Tue Aug 07, 2012 2:04 am

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

LoveData

Posts : 2
Join date : 2012-07-30

View user profile

Back to top Go down

Re: Facts with same grain: some shared and some different measures

Post  ngalemmo on Tue Aug 07, 2012 2:38 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Facts with same grain: some shared and some different measures

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