Another "two fact tables" question
2 posters
Page 1 of 1
Another "two fact tables" question
I'm designing a datamart for a police force. They have 2 main facts: an offence and an arrest. One offence can have many arrests and one arrest can have many offences. I understand that I need 2 'stars': one for the offences and one for the arrests. If someone wants to count the number of arrests for all offences that are of a particular offence type, 2 queries will be generated by Business Objects, one filtering the offence fact table on the offence type dimension, the other querying the arrests table. However, the latter will be very inefficient because it has no filters. The 2 result sets will be joined and filtered before displaying the result to the user, but the query will take too long.
What is your solution to this problem?
Many thanks for your time.
What is your solution to this problem?
Many thanks for your time.
Romulusuk- Posts : 1
Join date : 2013-01-31
Re: Another "two fact tables" question
This is kind of like what is done in Medical where they need to track diagnosis and and treatment.
One suggestion is to have a 3rd fact table - Offence/Arrest. Everyone that gets arrested has a list of all the offenses. At the very least, you would need a table that had the dimension keys for the Offence and the Arrest which could be used to bridge the 2 tables. The exact design would be determined by the frequency and performane of the queries.
One suggestion is to have a 3rd fact table - Offence/Arrest. Everyone that gets arrested has a list of all the offenses. At the very least, you would need a table that had the dimension keys for the Offence and the Arrest which could be used to bridge the 2 tables. The exact design would be determined by the frequency and performane of the queries.
Jeff Smith- Posts : 471
Join date : 2009-02-03
![-](https://2img.net/i/empty.gif)
» Modelling question - Multiple fact tables with one to many relationships and querying from many fact tables
» Design Question - Multiple Fact Tables at the same Grain
» Accumulating Snapshot and Transaction Fact tables : question to design and use them together
» Question on Deleting records from dimension tables
» Storing Date Keys in dimension tables versus fact tables
» Design Question - Multiple Fact Tables at the same Grain
» Accumulating Snapshot and Transaction Fact tables : question to design and use them together
» Question on Deleting records from dimension tables
» Storing Date Keys in dimension tables versus fact tables
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum