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.
2. Create a BI Semantic Model Connection document by clicking on Documents, New Document.
4. Click on OK.
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
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.
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.
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
Very cool. The full treatment! Nice step by step. Thanks for the contribution.
ReplyDeleteThank you for reading the blog, Chris.
DeleteJust a small tip. Why do you need to convert csv to xlsx. Excel tries to interpret data and converts data to automatically chosen format unless you force it to do otherwise. It may change your data in a way you wouldn't want to. So unless there's a good reason, I would avoid this step and load directly from csv (use flat file source in import wizard).
ReplyDeleteExcellent point! Thank you for bringing this up. I tried to import directly from csv but the import wizard errored-out. I seem to always have difficult importing flat files over a certain size. I never have problems importing an xlsx, but you are correct to be weary of Excel changing your data.
Delete