Over the past few months, I have been asked if it is possible to connect GeoFlow to SQL Server database; and the more intriguing question is, why don’t GeoFlow connect to Tabular Model straight away instead of using PowerPivot. Although I do not know if there is such a plan related to the latter question, I do know that you can visualise data in SQL Server database or Tabular Model using GeoFlow.
The key ingredient for exposing this data is by storing and modelling it in PowerPivot. Why so? Perhaps one of the reasons is that most organisations, big or small, and individuals have or know how to use Excel (yes, they will need Excel 2013). Buying Excel 2013 Professional edition or higher would be cheaper to afford than the cost of buying a license for SQL Server. So, PowerPivot is the middle ground and it is the best win-win situation, for now. Those organisations which have invested time and effort converting their PowerPivot workbooks to Tabular Models however, would probably appreciate being able to expose their data in Tabular Model using GeoFlow.
Exposing SQL Server for GeoFlow
I have recently posted a step-by-step instruction to expose data stored in SQL Server database and Tabular Model to GeoFlow at Microsoft Customer Reviews of STB Products.
In the step-by-step instruction, I did mention that I was using 22 Million of records. The sample workbook is about 580 MB and shared via SkyDrive. PowerPivot in the workbook contains daily data of Internet Speed tests for 5 years and about 2,200 unique locations. The workbook cannot be opened using Excel Web App due to the size and GeoFlow is not supported in Excel Web App either. So, you will need to have Excel 2013 Professional edition or above, and have GeoFlow installed.
I have also created a tabular model based on the 22 Millions records, but only exposing the aggregated data via DAX. The PowerPivot in this workbook contains about 1 Million of records, containing monthly data for 5 years worth of Internet Speed tests conducted in about 2,200 unique locations. The workbook is about 6.2 MB and shared through SkyDrive.
I am deliberately making the workbooks available via SkyDrive, in case in the future and wouldn’t it be nice if, GeoFlow can be supported in Excel Web App too 🙂
Wrap Up
GeoFlow Preview for Excel 2013 can connect to SQL Server via PowerPivot. This article has shown how GeoFlow can render geospatial information from millions of rows stored in SQL Server database and Tabular Model.
Further Reading
GeoFlow for SQL Server Database and Tabular Model at Microsoft Customer Reviews of STB
Sample Workbook: GeoFlow using SQL Server database
Sample workbook: GeoFlow using Tabular Model
No responses yet