A typical inquiry for this database might be "Has the number of burglaries increased or decreased in the past 5 years?" The DB2 data contains all the information you need to respond to such an inquiry. The data—all textual—includes the date of the incident, the defendant, the type of crime, the judge, the prosecuting attorney, and the result of the case. The source data contains no dollar amounts, counts, or any other numeric information. Figure 1 shows the dimensions and dimension levels in the OLAP cube that you'd use to respond to the inquiry.
The fact table consists of incident filings from each case. Because each case can have more than one filing, the fact table includes two count measures. One measure, Count of Incidents, gives the total number of incident filings, and the other measure, Count of Cases, is the number of cases. The Count of Cases measure is set up as a distinct count measure on the Case number column of the fact table.
So, to answer the question about the trend of burglaries, you'd create a query that displays the Count of Incidents for each type of burglary for each of the past 5 years. You'd also select a status of Guilty to be sure you weren't counting false charges. You'd use the Count of Incidents rather than the distinct count because a charge of burglary could be just one of many charges in a case.
After the cube in this example was constructed, a calculated measure was added to return the conviction rate as a percentage. The formula for this measure is the count of guilty incidents divided by the total count of incidents. In MDX, the formula is
([Count of Incidents], [Status].[All].[Guilty])
/ ([Count of Incidents], [Status].[All])
Unfortunately, the prosecuting attorneys office doesn't track the name of the defending attorney. If the defending attorneys' names were included, you could analyze the relationship between the conviction rate and the combination of prosecuting attorney and defending attorney. But as it is, the cube can help you determine whether the combination of a particular prosecuting attorney and a particular judge affects conviction rate. Such an analysis could suggest a judge's bias toward or against a particular attorney.
Besides revealing this type of controversial finding, this cube can help authorities analyze many types of crime trends. For example, you might ask what types of crimes are seasonal. If you integrate local weather data, you could determine how temperature, precipitation, or moon phase affects crimes such as car theft. Even without weather data, you can uncover such information as which crimes are most common during the holidays and what the conviction rate is for holiday crimes.
You can summarize nearly any kind of database by using counts. If the data already includes numeric information, then count information is complementary, offering a new way to analyze the same information. Counts also combine well with numeric information to create averages or ratios. If you want to count only fact table records in your summarized information, the implementation is simple. But if you're counting derived information such as invoices, you can use one or more of the techniques I've presented here. Thanks to Michael Patterson, a senior consultant at ProClarity, for providing some details of the criminal cases cube for this article.
End of Article
Prev. page
1
[2]
next page -->
