1 / 24

Crimewatch Consulting Firm

Presentation 2: Data Warehouse Design Discussion. Crimewatch Consulting Firm. Adwait Mulye , Yuga Pawar , Floyd J. Srubar, Vidyasagar Velamala. Brief Review of Business Needs. “Value You’ll See Consulting” provides decision support services to clientele from various industries.

chase
Download Presentation

Crimewatch Consulting Firm

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 2: Data Warehouse Design Discussion Crimewatch Consulting Firm AdwaitMulye, Yuga Pawar, Floyd J. Srubar, VidyasagarVelamala

  2. Brief Review of Business Needs • “Value You’ll See Consulting” provides decision support services to clientele from various industries. • City of Houston– Staffing and Resource Planning • Realtors – Neighborhood Crime Statistics • School Districts – Land Purchases • Business Owners – Location Decisions

  3. Brief Review of Business Needs • Additionally, the use of a data warehouse allows our firm to compile and re-assemble raw publicly available crime data into specific decision supporting material tailored to our clients’ information needs.

  4. Tonight’s Discussion Overview • Tonight’s discussions will address the structure of the Data Warehousing system for our consulting firm. • Database & Table Structure Discussion • Facts • HPD Crime Data: June 01 – December 31, 2009 • Dimensions • [Date, Type of Crime, Police Beat, Premises] • Dimensional Modeling Discussion • Star Schema • Snowflake: Dimensional Hierarchies

  5. Database and Table Structures

  6. Database & Table Structure Takeaways • The fact table is publicly available data from the City of Houston website. • Dimensional tables are a joint effort by both the City of Houston and our consulting firm. • City of Houston data revealed natural dimensions based on hierarchies found in the HPD organizational chart. • Future Dimensions in the works: • DimTimeOfDay: Morning, Mid-Day, Evening, Overnight • DimSceneOfCrime: Based on Premises node, we see a pattern emerging in that table for rolling up, or drilling down. • Part of the “Data Cleansing Process” involved simple tasks such as changing field names, and removing five orphaned records.

  7. Fact Table: HPD Crime Data Jun – Dec 2009 *The data originates from the Houston Police Department’s OLTP systems.

  8. Fact Table: HPD Crime Data Jun – Dec 2009 *The data originates from the Houston Police Department’s OLTP systems.

  9. Dimension Table: Police Beats

  10. Dimension Table: Police Beats

  11. Dimension Table: DimOffenseTypes

  12. Dimension Table: DimOffenseTypes

  13. Dimension Table: DimDates

  14. Dimension Table: DimDates

  15. Dimension Table: DimPremisesCodes

  16. Dimension Table: DimPremisesCodes

  17. Dimensional Modeling

  18. Snowflake Schema

  19. Snowflake Schema

  20. Dimensional Hierarchies

  21. Police Beat Hierarchy • Structure is similar to the HPD organizational chart. • Divisions: Treat these as Police Station Locations, as this trend emerges from the fact tables, and later discovered on the HPD website. (see map) • Districts: A Division can have authority over multiple Districts. (e.g. Airport Division covers Hobby and Bush Airport districts. • Police Beats: A District has jurisdictional authority over many police patrol beats.

More Related