{"id":981,"date":"2012-07-29T01:13:18","date_gmt":"2012-07-29T01:13:18","guid":{"rendered":"http:\/\/bicortex.com\/?p=981"},"modified":"2012-08-09T11:24:44","modified_gmt":"2012-08-09T11:24:44","slug":"implementing-self-service-bi-with-tableau-public-microsoft-powerview-and-worldwide-telescope-with-excel-add-in","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/implementing-self-service-bi-with-tableau-public-microsoft-powerview-and-worldwide-telescope-with-excel-add-in\/","title":{"rendered":"Implementing Self-Service Geospatial BI with Tableau Public, Google Fusion Tables, Microsoft Power View and WorldWide Telescope With Excel Add-In"},"content":{"rendered":"

Every now and again, when I want to display the data visually and do so quickly, I wish I had some more sophisticated tools at my disposal. Staple Excel and SSRS functionality is pretty good out of the box, however, particularly when I want my data in a geospatial context with some interactivity built-in, I wish I could quickly and painlessly whip out a simple map to give me a good indication of the status quo. That is where the following three applications may offer some of that functionality: Tableau Public Edition, Google Fusion Tables, Excel with WorldWide Telescope and the new kid on the block Power View add-in for Excel 2013.<\/p>\n

In this post I will explore some of the features that they all offer and how to create a quick and simple solution to map the data points in an eye-pleasing manner for exploration and analysis.<\/p>\n

First, let\u2019s start with the dataset I will be using throughout this post. HERE<\/a> or HERE<\/a> you can find an Excel file which contains Australian post codes with their corresponding Latitude and Longitude coordinates. I have imported the file into my local MS SQL Server instance table and generated some dummy content\/narrowed its content to the data applicable to only one state i.e. Victoria (710 records will be sufficient for this presentation), using the following script.<\/p>\n

\r\n\r\nWITH Final_Result\r\n(PostCode, Lat, Long, State, SalesValue, SalesQTY, DivisionNumber)\r\nAS\r\n (SELECT a.PCode as PostCode, a.Lat, a.Long , a.State,\r\n CASE\r\n WHEN a.PCode BETWEEN 3000 and 3100 THEN 10000\r\n WHEN a.PCode BETWEEN 3101 and 3200 THEN 9000\r\n WHEN a.PCode BETWEEN 3201 and 3300 THEN 8000\r\n WHEN a.PCode BETWEEN 3301 and 3400 THEN 7000\r\n WHEN a.PCode BETWEEN 3401 and 3500 THEN 6000\r\n WHEN a.PCode BETWEEN 3501 and 3600 THEN 5000\r\n WHEN a.PCode BETWEEN 3601 and 3700 THEN 4000\r\n WHEN a.PCode BETWEEN 3701 and 3800 THEN 3000\r\n WHEN a.PCode BETWEEN 3801 and 3900 THEN 2000\r\n ELSE 100 END AS 'SalesValue',\r\n CASE\r\n WHEN a.PCode BETWEEN 3000 and 3100 THEN 400\r\n WHEN a.PCode BETWEEN 3101 and 3200 THEN 350\r\n WHEN a.PCode BETWEEN 3201 and 3300 THEN 300\r\n WHEN a.PCode BETWEEN 3301 and 3400 THEN 250\r\n WHEN a.PCode BETWEEN 3401 and 3500 THEN 200\r\n WHEN a.PCode BETWEEN 3501 and 3600 THEN 150\r\n WHEN a.PCode BETWEEN 3601 and 3700 THEN 100\r\n WHEN a.PCode BETWEEN 3701 and 3800 THEN 50\r\n WHEN a.PCode BETWEEN 3801 and 3900 THEN 10\r\n ELSE 5 END AS 'SalesQTY',\r\n CASE\r\n WHEN a.PCode BETWEEN 3000 and 3100 THEN 'Division 1'\r\n WHEN a.PCode BETWEEN 3101 and 3200 THEN 'Division 2'\r\n WHEN a.PCode BETWEEN 3201 and 3300 THEN 'Division 3'\r\n WHEN a.PCode BETWEEN 3301 and 3400 THEN 'Division 4'\r\n WHEN a.PCode BETWEEN 3401 and 3500 THEN 'Division 5'\r\n WHEN a.PCode BETWEEN 3501 and 3600 THEN 'Division 6'\r\n WHEN a.PCode BETWEEN 3601 and 3700 THEN 'Division 7'\r\n WHEN a.PCode BETWEEN 3701 and 3800 THEN 'Division 8'\r\n WHEN a.PCode BETWEEN 3801 and 3900 THEN 'Division 9'\r\n WHEN a.PCode BETWEEN 3901 and 3999 THEN 'Division 9'\r\n ELSE 'Unknown' END AS 'DivisionNumber'\r\n FROM\r\n (SELECT PCode, Lat, Long, State\r\n FROM AU_PCodes_and_LatsLongs\r\n WHERE Lat <> 0 AND Long <> 0 and PCode LIKE '3%'\r\n GROUP BY PCode, Lat, Long, State) as a)\r\n\r\nSELECT *,\r\n CASE WHEN DivisionNumber LIKE '%1'\r\n OR DivisionNumber LIKE '%2'\r\n OR DivisionNumber LIKE '%3'\r\n OR DivisionNumber LIKE '%4'\r\n OR DivisionNumber LIKE '%5' THEN 'District A' ELSE 'District B' END AS 'District'\r\nFROM Final_Result\r\n\r\n<\/pre>\n
After execution, you should be left with the following output (partial view only).<\/p>\n
\"\"<\/a><\/p>\n
Make sure you copy the output to Excel (alternatively, Tableau Public recognizes \u00a0MS Access, text file, OData or Windows Azure Marketplace DataMarket datasources), save it and have your copy of Tableau Public installed on your machine, ready to go. I will be using this Excel file throughout this post with the remaining two applications to keep it consistent.<\/p>\n
First let\u2019s have some fun with Tableau! Fire up your Tableau Public application and select \u2018Microsoft Excel\u2019 from the \u2018Connect to Data\u2019 pane on the left hand side (I am using Tableau version 7.0.5 here). Select \u2018Single Table\u2019 and the Sheet name you want to import and click on \u2018Yes, the first row has field names in it\u2019 from the options provided. Hopefully, this is the screen you will see when the import process finished:<\/p>\n
\"\"<\/a>
\nNext, let\u2019s place \u2018SalesQTY\u2019 data and \u2018SalesValue\u2019 data on the development pane which will automatically get summed up as our measures. Now, onto our dimensions. Let\u2019s click on \u2018PostCode\u2019 dimension (should have a little globe symbol next to it). This will bring out the \u2018Show Me\u2019 dropdown pane which auto-suggests the best graphical interpretation of the data based on what we imported earlier (if that pane does not appear, just maximize it clicking on the \u2018Show Me\u2019 tab in the top right-hand side corner). Next, click on the little map icon \u2013 fourth one down in the first row \u2013 which will automagically map out our measures\u2019 values based on the post code values we provided in the spreadsheet and generate a map for us. All is left now is a couple of touch ups for increasing the interactivity. Let\u2019s right-click on \u2018DivisionNumber\u2019 and \u2018District\u2019 dimensions individually and choose ‘Show quick filter’ from the options displayed – this will create our filter which we will be able to use to interact with the map. And that’s pretty much the whole process…we are finished! The whole exercise took no longer than 5 minutes and what we have left as a result is a good interpretation of what the sale quantities and values are in respect to the stores locations. We can also upload the report to Tableau\u2019s public servers if sharing it (read: showing it off) is what you\u2019re after. The exact copy of this report with all its functionality is embedded below (pending Tableau is not doing any maintenance on their servers in which case they will serve a static image with no interactive features enabled).<\/p>\n