Modeling a fact with multiple sources

View previous topic View next topic Go down

Modeling a fact with multiple sources

Post  schnedar on Fri May 29, 2009 12:19 pm

We have a situation where multiple business processes will generate a recommendation. A recommendation is a set of instructions, estimated costs, risk analysis, etc. for identified hazards. Examples of various business processes that generate these recommendations are a mishap investigation, workplace evaluation, hazard identification, inspection report, and a few others.

Now, the end-user wants to slice and dice either by individual source processes or all recommendations in general. For instance, I may want to see recommendations from inspection reports in California, or recommendations from workplace evaluations at construction sites. Each source process has its own unique criteria that I may want to query (i.e. mishap reports have causal factors, inspections have inspection types). The recommendations themselves, though, have common data (i.e. cost, risk analysis, etc.) regardless of source. Therefore, the customer wants to see recommendations across the board as well. Analyze all recommendations received by an organization during 2008 (NOTE: organization is common across business processes).

Would I have a fact table for each inidividual source prossess-recommendation combination and then a fact for all recommendations? Or, is there a way to have a single fact for all recommendations and somehow type back to the appropriate source process.

Thank you.

schnedar

Posts : 4
Join date : 2009-04-23

View user profile

Back to top Go down

Re: Modeling a fact with multiple sources

Post  ngalemmo on Fri May 29, 2009 5:17 pm

Usually you have one fact table with appropriate dimensions to segment and analyze the data.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

One fact or many with a view

Post  Jeff Smith on Mon Jun 01, 2009 9:37 am

There are a couple of different ways to go on this and the correct method "depends". If all of the seperate source systems have the same columns and the same measures, then go with one fact table. If the source systems have different columns but you can map them to common dimension tables (for example, you have 3 source systems, each source system has 3 unique columns, you could create a dimension table with all 9 columns - this would work great if the columns from the 3 source systems rolled up to a common hierarchy).

What you want to avoid is a situation where you have dimension key columns in your fact table that only apply to data from one source.

Another alternative to to create seperate facts tables and a view across the fact tables that contain the common dimensions. Another option is seperate fact tables with an aggregate table populated from the seperate fact tables.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Multiple Facts with a View

Post  cbusch on Tue Oct 20, 2009 3:31 pm

Could you expand on the following statement? What would be wrong with having some of the dimensional keys being N/A?

"What you want to avoid is a situation where you have dimension key columns in your fact table that only apply to data from one source."

cbusch

Posts : 4
Join date : 2009-02-03
Age : 56
Location : Albany NY

View user profile

Back to top Go down

Re: Modeling a fact with multiple sources

Post  BrianJarrett on Fri Oct 23, 2009 9:30 am

Sounds like this might be a candidate for Level 1 and Level 2 fact tables. Level 1 facts would look exactly like they do from their respective source system. Then all the ubiquitous facts shared between all sources roll up into a single fact record (level 2). It would look kinda like this:

LEVEL 1 FACT TABLES
================
Source 1:
Fact A, Fact B, Fact C, Fact D

Source 2:
Fact A, Fact B, Fact E, Fact F

LEVEL 2 FACT TABLE
===============
Fact A, Fact B

Since facts A and B are shared across all sources they're the only ones that make it into your level 2 fact table. Facts C & D only belong to source 1, Facts E & F only belong to source 2. End users can still get to them, they just have to go to the lower level fact tables. This avoids having to "plug" missing facts within a single fact table row that don't exist across all systems. This also applies to dimension keys as well, not just facts. I think this is essentially what Jeff Smith is saying when he mentioned an aggregate fact table. I also agree within him that you should avoid having "n/a" dimensions foreign surrogate keys in your fact tables whenever possible. Those are really just acting as a "plug".
avatar
BrianJarrett

Posts : 61
Join date : 2009-02-03
Age : 43
Location : St. Louis, MO

View user profile

Back to top Go down

Re: Modeling a fact with multiple sources

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