The Dimension Table For Crime - 1966 Words

I) DATA CUBE Consider a data warehouse containing data pertaining to crimes. It has three dimensions – Location, Crime and Time, and 3 measures – Number of Crimes, Cost of Investigation, and Duration of Investigation. The dimension table for Crime comprises of concepts such as Crime Type (eg, Theft, Assault, Arson) and Assigned Agency (eg, FBI, DEA, CBP, Coast guard, State Patrol, etc.). The dimension table for Location contains concepts such as city, state and country. Similarly, the dimension table for Time contains concepts such as year, month, week, date and hour. PROBLEMS: 1) (5 points) Consider that the above data warehouse has to be modeled as a data cube over the three dimensions. Assuming no concept hierarchy (i.e., each dimension†¦show more content†¦Show enough details about how you getThere are 4 levels of concept hierarchy There are 4 levels of concept hierarchy There are 6 levels of concept hierarchy *(4+1)*(5+1) = 4*4*6 = 96 for the above data warehouse. Note: in this question, there is no need to perform normalization on dimension table Time. [Hint: You are free to add any valid and relevant attributes to the dimension tables whileConsidering the concept hierarchies shown in Figure 1, calculate the total number of cuboids in the data cube (including the base and apex cuboids). Show enough details about how you getfor the above data warehouse. Note: in this question, there is no[Hint: You are free to add any valid and relevant attributes to the dimension tables while4) (10 points) Now you need to add another fact table, in which the dimensions are the measures are Number of Crimes Assigned dimension table for Officer contains concepts such as the police officers. Considering both new and existing fact and dimension tables, draw constellation schema for the data warehouse. 5) (10 points) Consider the concept hierarchy provided in Figure 1. Suppose we are provided data containing 1000 crime records which measures 25 crime types assigned to 10 different agencies across 40 cities among 20 states