Where is crime growing fastest in Denver?

In my last post I showed how to transform public (and free!) crime data from the City of Denver's Open Data Catalog into an in-memory analytical playground using Microsoft Power View on SharePoint and a SSAS tabular database.

In this post, I am going to show how to build the same Power View data visualizations, but only using Excel 2013 this time.  In addition to looking at which areas have the highest number of crimes, we will also investigate the rate of growth (or decline) in neighborhoods throughout Denver. 

First, we can see that the following types of crimes declined from 2009 to 2013:

1. theft from motor vehicles
2. white-collar crime
3. auto-theft

Next, we see an increase in the following categories:

1. "all-other-crimes"
2. drug-alcohol
3. larceny
4. public disorder

Overall, the number of crimes increased from 2009 to 2013.  2014 data is only up to mid-November so it shows a decrease from 2013 but it will probably be higher then 2013 when the data is updated in the new year.

Shown another way, the overall trend definitely shows an increase in arrests:

If we look at number of arrests per year by area, we can see the following neighborhoods have the fastest rate of growth:

1. Five-Points
2. CBD (central business district)
3. Capitol Hill
4. Montebello
5. Stapleton
6. East Colfax

It is hard to discern because the above chart is so cluttered. If you filter for just those above mentioned neighborhoods, it becomes easier to see:

Below is a video of using Power View within Excel 2013 to make these slides. 

Building this Power View in Excel is much simpler and requires less steps than when you use Power View in SharePoint. In a later post, I will talk about the differences between Power View in Excel and SharePoint. 

Step 1: Get the Data

File can be downloaded for free at the City of Denver Open Data Catalog 

Step 2: Create Power View in Excel 2013

1. Launch Excel 2013.

2. Create New Workbook.

3. Insert Power View:

4. While the Power View sheet is activated, click on PowerPivot menu and then click on the Manage icon:

5. When the new PowerPivot window appears, click on the icon that say "From Other Sources":

6. In the Table Import Wizard, select the Text File source, usually at the bottom of the wizard:

7. Click on Next, then browse to where you save the Crime CSV file on your computer.

8. Make sure that "Use first row as column headers" is checked:

9. Go into Data View in order to add a calculated column to the flat file that you just imported:

10. Create a new column in the data table and put a DAX formula such as =Year([First_Occurrence_Date]). Rename the column to "YEAR" after entering the DAX formula.

11. Close the PowerPivot window which will return you to the Power View window.

SQL Fix-It Guy
I am a Denver-based database developer, trainer and coach who has spent the past two decades helping leading organizations turn their data into competitive intelligence.  I have built and fine-tuned reporting capabilities for hospitals, energy companies, banks and government agencies and I am available for projects small and large.  

Noah Meyer (noah@sqlfixitguy.com)
Cell: 347-678-8279

Visualizing Crime in Denver

Thanks to the City of Denver's Open Data Catalog, anyone who is interested can download crime statistics for the past few years. The level of detail is amazing! 

I decided to use this data to showcase one of my favorite Data Visualization tools: Microsoft Power View.  Although Power View is available in Excel 2013, I am using Power View for SharePoint which is slightly different than the Excel version.

Video of Using Power View to Visualize Denver Crime

The data I used is since the beginning of 2009 through the end of October 2014. There has been much improvement in crime in some neighborhoods over that time period.  In a future post, I will be showing the growth or decline rate of crimes in specific neighborhoods.

Below is how I was able create the Power View above.

Step 1:  Get access to a SharePoint 2010 or 2013 environment with SQL Server 2012

If you don't have access to the above environment, you can still try out this exercise by using a Microsoft TechNet Virtual Lab here.  Please be aware that anything developed on a TechNet Virtual Lab is temporary and will be permanently deleted once you end your lab session. 

Step 2: Get the Data

Get free data file at http://data.denvergov.org

Click on download button and save CSV file to your computer

Step 3:  Prepare the Data for SQL Server Import

The SQL Server Import Export Wizard easily imports data files of different formats into SQL Server.  I tried importing directly from the CSV file, but the wizard consistently gave errors after importing half the rows.  I found that importing from an Excel almost never fails, so I quickly convert the CSV into an XLSX first, and then import into SQL Server.

 1. Open the CSV file in Excel.

2. Save file as an XLSX.

Step 4: Import Data into SQL Server

1. Launch SSMS (SQL Server Management Studio).

2. Create new database, call it "DenverCrime".

3. Right-click on DenverCrime database, select Tasks then Import Data.

4. In Import and Export Wizard, select Excel as the Source.  

5. Select SQL Server as Destination. The new table will automatically go into the DenverCrime database.

6. Copy data to a new table in the DenverCrime database.

Step 5: Create a SSAS Tabular Database

1. In Visual Studio 2012, create a new Analysis Services Tabular Project.

2. Import Data from SQL Server by selecting Model, Import from Data Source

3. In Table Import Wizard, select Microsoft SQL Server.

4. Specify server and database where we created the DenverCrime database.

5.  When prompted for "Impersonation" choose Service Account option.

6. Click close when prompted that Import Wizard is done.

Step 6: Deploy Tabular Project to SSAS Tabular Server

Specify the SSAS server and database where the tabular database is going to be deployed to.  Later, we will create a Power View in SharePoint that sources its data from this location.  

1. In Solution Explorer, right-click on the top file, select properties.  For Server, specify "sqlbi\TABULAR".  For database: "DenverCrime".

2. Right-click on the top file again and this time select Deploy.

Step 7: Create Data Connection in SharePoint

We will create a connection file that is called "BI Semantic Model Connection" or just "BISM" for short.  This connection file will tell SharePoint how to connect to the DenverCrime SSAS Tabular database that we created in the previous step.

1. Go to a BI Site Collection page on your SharePoint.  In the virtual lab example, this would be http://sqlbi/SitePages/Home.aspx.   Then, click on Shared Documents.

2. Create a BI Semantic Model Connection document by clicking on Documents, New Document. 

3. Put information about the SSAS Tabular database that we just deployed from Visual Studio.  Where it says Workbook URL or Server Name, specify "sqlbi\TABULAR".  Where is says Database, write "DenverCrime".

4. Click on OK.

Step 8: Create a PowerView in SharePoint

1. Click on the down arrow to the right of the BISM connection file.

2. You will now be in the new Power View report.  Watch the video at the start of this blog to see how the data within this report can be visualized with charts and graphs.

SQL Fix-It Guy
I am a Denver-based database developer, trainer and coach who has spent the past two decades helping leading organizations turn their data into competitive intelligence.  I have built and fine-tuned reporting capabilities for hospitals, energy companies, banks and government agencies and I am available for projects small and large.  

Noah Meyer (noah@sqlfixitguy.com)
Cell: 347-678-8279