Bridges between Fact Tables?
3 posters
Page 1 of 1
Bridges between Fact Tables?
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?
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
Re: Bridges between Fact Tables?
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?
You do not 'bridge' fact tables. What are your dimensions for FactQuarterlyExpenditures? WebRequestID? What about customer, or date, or other aspects of the measures?
Re: Bridges between Fact Tables?
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.
Is the Many to many in this case correct ?
Thanks,
Matt
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.
Is the Many to many in this case correct ?
Thanks,
Matt
mru22- Posts : 34
Join date : 2011-06-14
Re: Bridges between Fact Tables?
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.
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.
Re: Bridges between Fact Tables?
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 ?
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
Similar topics
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Storing Date Keys in dimension tables versus fact tables
» Multiple Fact Tables vs. Consolidated Fact Table
» Number of Columns in Fact Tables vs. Dimension Tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum