GeoFlow for SQL Server Database and Tabular Model
One of the highlights of PASS Business Analytics Conference in April 2013 was the announcement on GeoFlow Preview for Excel 2013. As you may already know, GeoFlow is a 3D Visualization of geospatial data. There are a number of reviews available since the Beta version was announced and even more since the Public Preview announcement.
This article will answer two questions based on the current Public Preview release of GeoFlow:
- Can GeoFlow connect to SQL Server Database?
- Can GeoFlow connect to SQL Server Tabular Model?
The answer to both of these questions is "Yes" - but indirectly. Let us go through the basics of GeoFlow first.
The Basics: Data Model for GeoFlow (Public Preview)
GeoFlow makes use of the Data Model definition that is defined on the Excel 2013 workbook, which can be modified and managed in PowerPivot. GeoFlow actually only makes use of the columns and calculated columns in PowerPivot. This means that a plain table that has been added to the Data Model in the workbook, would be in PowerPivot, including the data, and is accessible by GeoFlow. In addition, GeoFlow recognizes relationships in the data model as outlined later in this article.
Can GeoFlow connect to SQL Server Database?
Yes, it certainly can. PowerPivot can be used to import one or more tables from SQL Server database, which can in turn then be consumed by GeoFlow. In fact it is a good idea to use PowerPivot to model the data first.
To do this:
1. Create a new Excel 2013 workbook. Click on the PowerPivot tab and choose Manage to launch PowerPivot.
2. On the PowerPivot window, click on the Get External Data button which allows you to choose how to connect to your data source. As we are aiming at connecting to a SQL Server database, click on Get Exeternal Data > From Database > From SQL Server, which will launch the Table Import Wizard dialog box.
3. In the Table Import Wizard dialog box, enter the connection details similar to below. From here the steps of adding data into PowerPivot is similar to as the one described in https://technet.microsoft.com/en-us/library/gg399137.aspx
Writing a query against the database to add data into PowerPivot is also an option.
4. From here, you can continue enriching the data model in PowerPivot. However, GeoFlow will only make use of the columns defined in PowerPivot, including calculated columns.
Below is an example of a calculated column to concatenate City, Region and Country values that can be used later in GeoFlow.
5. Once the data model is defined, jump back to Excel 2013, go to Insert > GeoFlow and create a new GeoFlow tour. Now you are all set to go with exploring geographical data in GeoFlow.
At the time of writing, GeoFlow does not support SQL Server spatial data type. So in order to make use of data in SQL Server, GeoFlow accepts the following format:
- Text form (City, Country, Region, Address, Street, … Other)
- Latitude and Longitude coordinates.
Tip: In some cases, it may be a good idea to add a new calculated column which contains concatenated Locality columns as mentioned in Step 4. GeoFlow uses Bing to map the geographical information provided, based on the classification setting. The following "Locality" calculated column is created to provide better mapping accuracy.
Below is a screenshot of GeoFlow working area based on data sources from SQL Server tables, with over 22 Millions of rows.
This particular example actually takes a couple of minutes to refresh the 22 Million of rows. so if you have a large data set in a SQL Server database, it is possible to map it in GeoFlow.
Can GeoFlow connect to SQL Server Tabular Model?
Yes, it can. Using PowerPivot, we can import the data from Tabular Model in a tabular format. Please note that the model or the definition of the Tabular Model cannot be imported into Tabular Model, such as KPI information, calculated measure. You can use DAX or MDX to import the data from Tabular Model into PowerPivot.
Below is a step by step list to get the data from Analysis Services into PowerPivot, which in turn is accessible by GeoFlow.
1. Create a new Excel 2013 workbook. Click on the PowerPivot tab and choose Manage to launch PowerPivot.
2. On the PowerPivot window, click on the Get External Data button which allows you to choose how to connect to your data source. As we are aiming at connecting to an Analysis Services Tabular Model instance, click on Get Exeternal Data > From Database > From Analysis Services or Power Pivot, which will open up Table Import Wizard dialog box.
3. In the Table Import Wizard dialog box, enter the connection details similar to below. From here the steps of adding data into PowerPivot is similar to as the one described in https://technet.microsoft.com/en-us/library/gg399137.aspx
4. Enter an MDX or DAX statement in the Specify a MDX Query step. Click on Validate to double check that the query.
Once the data is imported, you can enrich the data model within PowerPivot as necessary for GeoFlow.
5. Jump back to Excel 2013 and click on Insert > GeoFlow to create a new tour based on the PowerPivot model.
Below is a screenshot of GeoFlow working area based on data in PowerPivot sourced from a Tabular Model,with nearly 1 Million rows of data.
Wrap Up
In essence, because GeoFlow makes use of PowerPivot, it opens up the ability to use data from a wide variety of sources, including SQL Server and Tabular Model. Data must be imported and modelled in PowerPivot which is then exposed to GeoFlow.
GeoFlow and PowerPivot restrictions:
- Can only use columns
- Supports relationship
Further Reading / Research
"Getting Started with GeoFlow Preview" document is available for download here.
GeoFlow Preview for Excel 2013 is available for download from here.
GeoFlow Reviews at MsSQLGirl.com
From Impaired to Insightful: Analysis with Geospatial Data session at PASS Summit 2013 (Oct 15 - 18, 2013)
Special thanks to NetIndex.com for providing ISP Daily Speed Data.