Another "two fact tables" question

View previous topic View next topic Go down

Another "two fact tables" question

Post  Romulusuk on Thu Jan 31, 2013 7:12 am

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.

Romulusuk

Posts : 1
Join date : 2013-01-31

View user profile

Back to top Go down

Re: Another "two fact tables" question

Post  Jeff Smith on Thu Jan 31, 2013 12:17 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

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