1 / 18

Crimewatch consulting, INC.

Presentation 3: Cube and DW Implementation. Crimewatch consulting, INC. Adwait Mulye Yuga Pawar Floyd J. Srubar Vidyasagar Velamala. Brief Overview:. A More Simplified Dimensional Model Facts from Presentation 2 Were Too Complex They have been re-designed for use in a DW.

fay
Download Presentation

Crimewatch consulting, INC.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Presentation 3: Cube and DW Implementation Crimewatch consulting, INC. • AdwaitMulye • Yuga Pawar • Floyd J. Srubar • VidyasagarVelamala

  2. Brief Overview: • A More Simplified Dimensional Model • Facts from Presentation 2 Were Too Complex • They have been re-designed for use in a DW. • Over 10,000 Records Were Found Having Inconsistencies Responsible For Errors in SQL Server Analysis Services • Incorrect FK entries • Null values in FK fields • They were deleted to save time and space. • Dimensions Have Been Altered Slightly • A redefined scope for our DW • The Police Department organizational hierarchies were found to be beyond the scope of the original intent of the DW, and were replaced. • The focus of this DW will be “Neighborhood Crime Statistics”, and the various dimensions that relate to these facts, which should still provide adequate information to consumers of our DW. (Star Schema)

  3. Brief Overview • Brief Discussion of the New Dimensional Model • View the Diagram • Discuss Relevant Dimension Table Changes • Specific Applications of the Data Warehouse Information • [Location] x [Offense Type] x [Time of Day] • AM • [Location] x [Date] x [Premises] • VV • [Location] x [Date] x [Offense Type] • YP • [Time of Day] x [Premises] x [Offense Type] • FJS

  4. Revised Dimensional model

  5. A New Dimensional Model: [Star Schema]

  6. Relevant Change Information • DimDate • Now allows us to determine many useful date measures in the DW reporting. • Day of Week • Day of Month • Week of Month • Week of Year • Quarter of Year • Weekend Days

  7. Relevant Change Information • DimTimeOfDay • Allows for determination of common times of day based on the ‘Hour’ field from the fact table. • Overnight [12AM – 5AM] • Mornings [5AM – 12PM] • Afternoons [12PM – 5PM] • Evenings [5PM – 12AM]

  8. Relevant Change Information • DimLocations (new Dimension) • Still refers to block ranges and streets, but offers more DW report generation flexibility. • Allows for measurement by the concatenated block range and street • (e.g. 1200-1299 Bay Area) • Or…Entire Street with all block ranges • (e.g. Bay Area, with “All” block ranges selected) • Or…Selected disjointed block ranges • (e.g. 2600-2699 Bay Area [AND] 2800-2899 Bay Area)

  9. Specific DW APPLICATIONS

  10. Rough View of The Cube!

  11. [Location] x [Offense Type] x [Time Of Day] • Relationships that make this possible

  12. [Location] x [Offense Type] x [Time Of Day] • And now…In SQL Server Analysis Services!

  13. [Location] x [Date] x [Premises] • Relationships that make this possible

  14. [Location] x [Date] x [Premises] • And now…In SQL Server Analysis Services!

  15. [Location] x [Date] x [Offense Type] • Relationships that make this possible

  16. [Location] x [Date] x [Offense Type] • And now…In SQL Server Analysis Services!

  17. [Time of Day] x [Premises] x [Offense Type] • Relationships that make this possible

  18. [Time of Day] x [Premises] x [Offense Type] • And now…In SQL Server Analysis Services!

More Related