Bridges between Fact Tables?

View previous topic View next topic Go down

Bridges between Fact Tables?

Post  DavidStein on Mon May 06, 2013 12:14 pm

I'm familiar with the use of bridge tables when bridging between a Fact table and a multi value Dimension. However, this situation is a little different.

I'm working with a pre-production model which is unusual. The entire model is more or less dominated by a Fact table called FactWebRequests. The reason is that the other Facts which are collected are done so because of a Web Request which is recorded in Fact Requests. Please consider the following diagram example.


Currently, the other Fact Tables, of which FactQuarterlyExpenditures is an example, are linked directly with FactWebRequest as if it were a Dimension. A set of Quarterly Expenditure amounts is returned every time we receive a Web Request. The same exact set of records are often recorded per Customer for multiple Web Requests. I'd like to see a set of records in FactQuarterlyExpenditures that represents the Customer's measurements, without duplicates. However, I also have to accurately reflect the set of FactQuarterlyExpenditure records that were returned during each Web Request.

To me, this calls for a QuarterlyExpenditures group table along with a bridge to FactQuarterlyExpenditures. The Group Table would have a varchar field with the list of business keys (Quarters) for it as well as an BeginDate and EndDate.

Incidentally, FactWebRequest would accumulate approximately 18 million records a year at the current rate and I'm working with SQL Server 2012. The FactQuarterlyExpenditures would increase at 3-6 times that rate per year.

What do you folks think of my assessment? Am I missing something here?

DavidStein

Posts : 24
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Bridges between Fact Tables?

Post  ngalemmo on Mon May 06, 2013 2:02 pm

Why not stick to a dimensional model?

You do not 'bridge' fact tables. What are your dimensions for FactQuarterlyExpenditures? WebRequestID? What about customer, or date, or other aspects of the measures?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Bridges between Fact Tables?

Post  mru22 on Mon Jun 03, 2013 1:16 pm

I too am curious about this topic.

I have the following Scenario where i have a many to many between two fact tables.

I have the FactPermitReqirement which shows the limits for permits based on each requirement. Then i have another fact of Analytical Results which hold a value
for a test result. I need to know the relationship between each Analytical Result and the Permit requirement without dupes and I wasn't sure how else to handle it.

requirements

Is the Many to many in this case correct ?

Thanks,

Matt

mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Bridges between Fact Tables?

Post  ngalemmo on Mon Jun 03, 2013 2:31 pm

Given any two fact tables, the relationship between the two is assumed to be many to many.

What your diagram shows is not a dimensional model. A dimensional model does not have direct relationships between facts. Relational models have relationships between tables. Dimensional modeling form is more restrictive. Facts have FKs to dimensions, not other facts.

Its a matter of form. In a dimensional model each fact table stands alone, surrounded by its context, the dimensions. You are free to create whatever dimensions you want, as well as include degenerate dimensions. You can combine measures from any two fact tables at query time provided they have some context in common (the conforming dimensions).

The advantage of this form is you can introduce new fact to the DW at any time, without concern of or modification to existing fact tables.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Bridges between Fact Tables?

Post  mru22 on Mon Jun 03, 2013 2:52 pm

Thanks for the reply and insight.

In my case how else could I associate the Permit limits to an analytical results given that there are many locations for each Permit but each result only has one location ?

It sounds as though I should Dump the "PermitAnalyticalResult" table and as you mentioned and just provide the info a query time given that they share the same location dimension ?




mru22

Posts : 34
Join date : 2011-06-14

View user profile

Back to top Go down

Re: Bridges between 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