Dutch Data Dude Data Nerdiness


The most awesome Power BI soundtrack

29 Apr 2014

The most awesome Power BI soundtrack is of course I’ve Got The Power by Snap! :) If you have a better idea, just let me know. Enjoy!

Analyzing Amsterdam Crime Rates with Power BI

22 Apr 2014

Here is another example of what can be done with Power BI. This time I attempted to analyze crime rates in Amsterdam.

I retrieved burglary and burglary attempts figures from the Dutch Police website: http://www.politie.nl/misdaad-in-kaart/lijst?geoquery=amsterdam&categorie=1&categorie=2&pageSize=500&page=1

There are three pages here, so I use Power Query to get all three pages into Excel and then appended them together in one big table. I did this using three ‘from web’ commands in Power Query, so I got the following queries:

Next step was to append the three tables together. I started with the ‘append’ command in Power Query which allowed me to append Page1 and Page 2 together. A quick edit of the code gave me the result I was looking for:

let
 Source = Table.Combine({Page1,Page2}),
 Source2 = Table.Combine({Source,Page3})
in Source2

And the resulting table:

Next, I loaded the table to PowerPivot, added a quick sum and then opened Power Map by going to InsertàMap in Excel.

In Power Map I created three layers, one using shapes, another one using a heat map and a third using a column graph. Then I created a tour using these layers. The resulting video is below.

As you can see, it is really easy to visualize geographical data using Power BI in Excel! Pretty cool huh?

SQL Server 2014 Generally Available since April 1st

15 Apr 2014

This is not a late April fool’s joke. SQL Server 2014 has been made generally available on April 1st 2014. See the original blog post here: http://blogs.technet.com/b/dataplatforminsider/archive/2014/04/01/sql-server-2014-now-generally-available.aspx

I encourage you to download and try SQL Server 2014 or run it in a VM in our Azure cloud. Also you might want to block your calendars for a live stream on April 15: “Accelerate Your Insights” (http://www.microsoft.com/en-us/server-cloud/new.aspx?WT.mc_id=Blog_SQL_Launch_SQL2014).

Power BI Pro Tip: Troubleshooting Power BI Data Refresh

08 Apr 2014

With Power BI you can create an Excel sheet based on your data and then get it refreshed so your report always has the latest information.

If you run into trouble with this, here is how to troubleshoot the data refresh.

  1. Firstly, make sure you use Power Pivot to connect to your data source and not Power Query as refresh with Power Query is currently not supported.
  2. When making the connection to your data source, just use a regular data connection to the SQL Server source and not through an OData feed.
  3. In Power Pivot, make sure to use the same data provider as your data source has been configured to use in the Power BI Admin Portal (i.e. Native SQL or OLEDB) and enter the server and database name exactly the same as the data source has been configured in your Power BI Admin panel. With "exactly" I mean also casing: so myServer is something else than MyServer. So the server and database name needs to be identical, including casing.
  4. Finish your workbook and upload it.
  5. Make sure the Database Management Gateway is running. Do this by checking the status in the Power BI Admin Portal and check under Data Management Gateways.
  6. Next in the Power BI Admin Portal, check that the data sources have correct settings for server name and database name. Also check that the credentials entered here have permissions to the data sources on the server and have been re-entered after the last time the DMG has been registered. There is no harm in re-entering them just to be sure. Also check that the connection provider here matches the one used in Power Pivot and again the server and database name need to be exactly the same as in your Power Pivot connection settings (see item #3 above).
  7. In the Power BI Admin Portal, check that the user you will be using to schedule the refresh has permissions to do so on every data source you would like to refresh. Do this by opening the settings for the data source in the Power BI Admin Portal and making sure the user is listed under 'users and groups'.
  8. Plan the refresh on the file. When the schedule hits, your schedule should start and finish successfully.

 

Power BI Pro Tip: Troubleshooting Data Management Gateway stability

01 Apr 2014

If your Data Management Gateway crashes a lot, just make sure you have .NET framework version 4.5.1 or later installed. 4.5.1. introduced fixes that the Data Management Gateway needs.

If you install it and reboot the server your DMG should be running much more smoothly.