Igor Peev, Bryan Smith, and Ari Schorr collaborated on this article.
Igor is a Senior Program Manager who enjoys working on challenging projects that deliver new, innovative and immersive experiences. In project codenamed “GeoFlow,” Igor works on the data Time animation and the interactive Tours functionality.
Bryan is a Business Intelligence Technology Specialist who works with customers in a variety of industries, helping them understand how Microsoft Office, SharePoint and SQL Server can be used to derive meaningful business insights.
Ari is a Product Marketing Manager on the Office Technical Product Marketing team that works closely with engineering to prioritize and message features in the new Office. He has worked closely with the project codename “GeoFlow” engineering team as they launched public preview and will continue to do so as they work towards general availability release.
Editor’s note: Since this article was originally published in April 2013, Project codename “GeoFlow” has been renamed Power Map as part of the new Power BI for Office 365 offering.
The Microsoft account team in Dallas, TX wanted to create a localized demonstration that would be recognizable to customers in the area. Taking advantage of publicly available residential data and awareness of the extreme summer temperatures in and around Dallas, the team decided to present simulated household energy consumption data using Excel 2013, Power View and project code name “GeoFlow” Preview for Excel, believing the presentation would peak customers’ interests.
Collecting the data and preparing it to use in GeoFlow Preview
The Dallas County Appraisal District provides access to property data at this URL: http://dcad.org/.
The team used property data from that site to obtain information about the size and specifics of the houses. The next step was to generate simulated monthly kWh consumption data on a per household basis. National averages on seasonal consumption and the account team’s personal household energy consumption were used in the simulation. They also added random numbers to each household’s monthly energy consumption to enhance the variability of the data.
A sample of households in the cities of Addison and Carrollton were extracted and are provided in the sample data file included at the end of this blog post.
Constructing demo material
For both the Power View and the GeoFlow Preview demonstrations, calculations were added to the PowerPivot data models for kWh per Square Foot and kWh per USD Market Value. These values allowed the team to normalize energy consumption across homes of varying size and value and gave additional measures to work with for the demonstration.
For the Power View presentation, various predefined reports were built up within the Excel workbook. Animated bubble charts, pie charts, maps, line charts, and column charts were used to illustrate a range of interactive visualizations. (Monthly average temperature data was also imported into the PowerPivot model in this demo so that energy consumption relative to temperature could be shown.) Here is an example of a report showing a breakdown of electricity consumption:
For the GeoFlow Preview presentation, households were mapped using longitude and latitude. Columns were presented on these points illustrating square footage or market value of the properties. The decade of the construction of the homes was used to highlight the various neighborhoods. A second layer was added to the presentation to show household kWh consumption as a heat map. This data leveraged the Play option in GeoFlow Preview in order to show how the map of kWh consumption changes over time: The first day of each month was embedded in the PowerPivot data set since the Play option requires a date value so that the customer could see the ebb and flow of energy consumption over the three-year period. Various neighborhoods were zoomed into to illustrate differences in consumption patterns between them.
Building the demo
Give it a try! Follow these steps to recreate the key GeoFlow Preview visualization showing houses in height by SqFeet, colored by Decade Built and their electricity consumption over time using heatmaps on your own:
- Download and install the GeoFlow Preview.
- Click Map, then click New Tour.
- Click Map It. Now you will see red points appear on the globe in Dallas.
- Click Zoom in.
- Select the Square Feet field. This will place that field in the HEIGHT well. You will see this on your screen:
This visualization shows house locations and the height of the columns shows their size. There are some really big houses which are condo complexes.
Now do the following:
- Click the DecadeBuilt field. This will color the columns specific to the decade in which they were built and will refresh the Legend.
- You can adjust the Legend to see that there houses built from 1910s to 2010s in the dataset.
- Zoom in again. You should see something that looks like this:
You can adjust the thickness of the columns by accessing the Layer properties. To do so, click the Settings icon on top of the Field list. Lower the thickness to 20%. At this point we can spot patterns. There is a neighborhood which was built in the 1980s, and the columns there are blue. That neighborhood is flanked by two neighborhoods built in the 1960s, which are green. This mix is interesting because it lends itself well to seasonal comparison. You should see something that looks like this:
Now add the second layer which will show electricity consumption per month, and we will transpose that visual using heatmaps. To do this, follow these steps:
- On the top of the Field List task pane, click the left most icon to access the Layer Manager.
- Make a New Layer by clicking the icon.
- Select Latitude and Longitude from the field list on the right – each layer needs GEOGRAPHY to be added to the field well in order to be geo-coded (mapped).
- Click Map It.
- Select the kWh field.
- Change the CHART TYPE from Column to HeatMap by using the drop down menu.
- Click the PeriodStart field. This will add it to the TIME field well.
- Scroll down in the bottom right field pane and change TIME SETTINGS from Time Accumulation to Persist the Last (last event which happened in a particular geo-location; ignores Null or 0 values) using the drop down menu.
- Close both the Tour task pane and the Layer Manager task panes by clicking each .
Your visualization should look like something like this:
Click the Play button on the left of the time scrubber on the bottom. You will see that as seasons change, the houses built in the 1980s (the blue columns) consume less electricity than the houses built in the 1960s (the green columns).
To play your visualization in full screen, click Play Tour.
The demonstration has created quite a bit of interest in the Microsoft BI stack and clearly aligned with various aspects of the lead architect’s BI vision for the company long term. While the demonstration leverages simulated data, the power of GeoFlow to illustrate spatial patterns consistently resonates very well with customers in industries from real estate to healthcare to the arts (the Seattle Art Museum has used GeoFlow to visualize member/non-member, online/in-person Picasso exhibit ticket sales). Businesses are always tracking geospatial data in one way or another to assess performance across locations, pinpoint opportunities for growth, and observe seasonality for making forecasts.
Want to learn more about the GeoFlow Preview for Excel? To learn more, follow these links:
Looking for data sets that light up in Geoflow? Try the Data Explorer preview for Excel and find them through the online search feature. Search for key words such as largest cities, tallest mountains, earthquakes, GDP, to find geo coded data to light up and explore in 3D with Geoflow. With Data Explorer, you can discover relevant data sources from Excel, then shape and combine your data with other sources so that you can gain deeper insight by analyzing and visualizing with features such as Power View and Geoflow.
–Igor Peev, Senior Program Manager, Office Graphics and Visualization Team
–Bryan Smith, Business Intelligence Technology Specialist
–Ari Schorr, Product Marketing Manager, Microsoft Office Division