Question: 1) Create a dimensional model containing an aggregated fact table, where a fact table shows a summary of daily revenue amount for each officer. Use

1) Create a dimensional model containing an aggregated fact table, where a fact table shows a summary of daily revenue amount for each officer. Use ERDPlus to create your model.
2) Populate the tables created in part d) with the data. Use the data that you used to populate the tables in the Figure 8.46 as a basis for aggregation.

Problem #1 (60%) E. Consider the following scenario imobing the City Police Department The City Police Department wants to cr payer of the ticket, including payer DLN payer name payer gender payer birth year • vehicle, including • vehicle LPN • vehicl Eigure 8.45 illustrates how a ticket revenue analysis data warehouse based on these sources and requirements would be designe Figure 8.43 ER Diagram DRENGTE OFFICER Figure 8.43 Relational Schema a) 10 points. Based on Figure 8.43 ER Diagram, draw the DROVER DLN Drivertama DiverGender Divertirth Year Vehicle LEN VahMake Vehicle 222 Female DLN Vehicle PN DTTypeID DTICKETTYPE CALENDAR Dimension PAYER Dimension TICKETREVENUE Fact table OFFICER Dimension VEHICLE Dimension Calendar Key Officerkey Payer PAYER Dmenson CALENDAR Demon Carey Pole Day Day OFFICER VEGLED w Figure 8.46 c) 20 points. Using the source data from Figures d) 8 points. Create a dimensional model containing an aggregated fact table, where a fact table shows a summary of daily reve

DRIVINGTICKET DIID DTDate OfficerID (FO OFFICER OfficerName OfficerRank DTTypelD (FO VehiclePNF DRIVER DLN DriverName DriverG

OFFICER DLN DriverName DiverGender DiverBirthYear 1988 IL1111 IL2222 CRV Civic 222 Suzy 1977 Bob DRIVINGTICKET DTID DTDate Of

Problem #1 (60%) ES: Consider the following scenario intvolring the City Police Department The City Police Department wants to create an analytical danabane to analyze its ticket reseme. The two arailable data sourcen, Source ! and Source 2, are described below. Source 1 The City Police Depertment maintains the Ticketed Violations Database, shoNm in Eiguze 8.43 Source 2 The Department ofMotor Vehicles (DAV) maintains the Vehicle Registation Teble, shown in Eague 44. The daa warehouse han to emable an analynis ef ticket evemes by: date, including full date day of week day of month month * quarter officer, including * officer ID *officer name officer rank payer of the ticket, including

Step by Step Solution

★★★★★

3.38 Rating (148 Votes )

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

To create a dimensional model and populate it with data follow these steps Step 1 Create a Dimensional Model Fact Table DailyRevenue Columns OfficerID Foreign Key CalenderDate Foreign Key Amount Dimension ... View full answer

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Each entry in this table shows marginal revenue and marginal cost when a firm increases output to the given quantity: What is the profit-maximizing level ofoutput? Quantity MR MC 10 30 25 29 23 12 27...

Shown below are the histogram and summary statistics for the number of camp sites at public parks in Vermont. a) Which statistics would you use to identify the center and spread of this distribution?...

Solve Question E8.3a please 270 Part 2 Analytical Databases peoduct type ER.2b Cmate a dimensional model containing an aggre- gated fact table, where a fact table shows a sum- * product supplier name...

There are two problems due this week (each worth 35 points) as follows. Problem 1.6 (page 20) In comprehensive paragraphs, answerrequirements a to e. You will have 5 paragraphs total of four to five...

Robert (Bob) S. and Sally D. Grove are husband and wife and live at 4112 Larkspur Lane, Denton, TX 76201. Bob is a retired petroleum engineer, and Sally is a portrait artist. 1. When he retired at...

i want complete solution for my assignment and it should be without plagiarism COIT20274: Information Systems for Business Professionals, Term One 2016 Assignments 1 & 2 Requirements Assignment 1 -...

Confirming Pages C H A P T E R 19 Analyzing Information and Writing Reports Chapter Outline Using Your Time Efficiently Analyzing Data and Information for Reports Identifying the Source of the Data...

Briefly describe how cyclic AMP controls phospholipid synthesis.

A company invests $94,000 for equipment to produce a new product. Each unit of the product costs $12.40 and is sold for $16.98. Let x be the number of units produced and sold. (a) Write the total...

a On the same grid, sketch the graphs of y = |sin2x and y = tanx for 0

The owner of Showtime Movie Theaters, Inc., would like to estimate weekly gross revenue as a function of advertising expenditures. Historical data for a sample of eight weeks follow. a. Develop an...

Halles Berry Farm establishes a $400 petty cash fund on September 4 to pay for minor cash expenditures. The fund is replenished at the end of each month. At the end of September, the fund contains...

Consider the following information: 1. Service Revenue for the year = $80,000. Of this amount, $70,000 is collected during the year and $10,000 is expected to be collected next year. 2. Salaries...

Below are several scenarios related to control activities of a company? 1. A manufacturing company compares total sales in the current year to those in the previous year but does not compare the cost...

Listed below in random order are the eight steps comprising a complete accounting cycle: Prepare a trial balance. Journalize and post the closing entries. Prepare financial statements....

Explain the legal considerations regarding copyright and intellectual property in relation to collaborative practice. In your discussion, consider the organisational agreements that guide legal...

a. Using the data in Figure 8.41, illustrate how the tables in the dimensional model shown in Figure 8.42 would be populated. b. Create a dimensional model containing an aggregated fact table, where...

Solve question E8.3b Answer for 8.3a 270 Part 2 Analytical Databases peoduct type ER.2b Cmate a dimensional model containing an aggre- gated fact table, where a fact table shows a sum- * product...

a. Based on the sources and requirements listed above, create a dimensional model that will be used for the dimensionally modeled data warehouse for Big Z Inc. b. Using data from Figures 8.47 and...

a. Based on the sources and requirements listed above, create a dimensional model that will be used for the dimensionally modeled data warehouse for Big Z Inc. b. Using data from Figures 9.31 and...

customer, including: customer ID customer name customer type customer zip depot, including: depot ID depot size depot zip order clerk, including: order clerk ID order clerk name order clerk title...

a. Using the source data from Figures 8.43 and 8.44, illustrate how the empty tables in Figure 8.46 would be populated. b. Create a dimensional model containing an aggregated fact table, where a fact...