Data Visualization, Excel, Microsoft Technologies, Power Map, Power View, SQL Saturday, SSRS

Choosing A Mapping Tool in the SQL Server BI Stack

With the addition of the Power BI suite to the SQL Server BI stack, there are now 3 main options for creating data-driven maps. I have been working on a presentation to help you choose which mapping tool is appropriate for your needs on a given project. I gave the presentation for the first time in SQL Saturday Chicago, and I’ll be presenting it again at the first SQL Saturday – BA Edition in Dallas on May 3. I’ll share the highlights of the information I provide in my presentation in this blog post, and you’ll just have to attend a SQL Saturday where I’m speaking to get the rest of the details.

There are 3 main tools between Excel, SQL Server, and SharePoint that can visualize data on maps:

  • Power View
  • Power Maps
  • Reporting Services

There are also a few apps for Excel 2013 that create maps (Bing Maps, Geographic Heat Maps). I’ll explain the Excel apps in a later blog post, and focus on the more mainstream tools for today. Although you can make maps with each of these tools, there are some significant differences in features, time to delivery, and system requirements for each of these tools. The image below summarizes the pros and cons for each of these tools.

Maps OverviewPower View is the easiest to use, but it has the most limited features. The drag and drop interface allows you to build a map in a matter of minutes. But this ease of use comes at the price of limited customization. The only type of graph you get on a map is a bubble chart, which turns into pie charts if you add a category. You can only build one layer of data on your map. Also, fonts and colors are limited to the themes available within Power View. Power View offers great interactivity and cross-filtering capabilities. You can set up your map so it drills down from country to state to city. You can use slicers and other graphs to filter and highlight data on a map. An example of using a map to filter a related table in Power View can be found here. Power View reports are usually visually appealing and lend themselves well for presentations. And if you are using Power View inside of SharePoint, you can export your Power View to a PowerPoint presentation and retain interactivity.

Power Map’s strongest features are a time axis that enables you to watch trends over time, the ability to show multiple layers at once, and the storytelling capabilities that come from scenes and tours. Power Maps have more choices for graphs, including columns, bubbles, heat maps, and region shading. But there is no drill-down capability to see an increased level of detail. Power Map also lacks the ability to filter data.  For example, if you have sales data for three products and you only want to see sales for Product A, Power Map doesn’t offer a way to filter to just Product A. This means that you may have to add some DAX calculations to your Power Pivot model to facilitate your map creation. DAX has its own learning curve, so this increases the time required to build the map if you have to learn DAX and alter your Power Pivot model first. Update: As of Sept  2014 you now have the ability to filter your data in a Power Map. The great thing about Office365 is that you can get monthly updates.  The inconvenient thing about that is I have to go back and update blog posts when this happens. Power Map can facilitate great storytelling with data through the use of scenes and tours. You can look at a particular geography and time period in each scene and combine scenes into a tour, then you can share that tour to take users on your journey to gain insight.

Although Reporting Services isn’t getting much love from Microsoft’s marketing or engineering groups, it is still a very viable option for creating maps. While SSRS can require more technical expertise and time to deliver a finished product, it allows the most customization of the three tools and offers subscriptions (time-based and data driven) and multiple rendering and export formats. It also allows custom shapes, so you aren’t limited to whatever the Bing Maps API can do. This helps with custom geographical regions within a company or its customers. It also means you can map more than just points on a globe; you can map floor plans and other shapes you can capture with SQL Server spatial data types. Where Power Map and Power View don’t necessarily require a lot of infrastructure to share a report, SSRS reports require a Report Server or SharePoint Server to make a report available to users.

I’ve developed the following high-level categories to be used to evaluate the appropriateness of each tool for your map.

Maps Eval CriteriaI used these evaluation categories to summarize my findings for each tool in the table below. You’ll notice that I split Power View into Power View for Excel and Power View for SharePoint. This is because these two different development environments have some different system requirements, data sources, and methods of consumption for the resulting Power View report. Although there are two different modes of Reporting Services (Native and SharePoint Integrated), this difference only affects system requirements so SSRS was kept as a single category.

Maps SummaryFor system requirements, note that Power View works with Excel 2013 Professional Plus and O365 Pro Plus and a few other versions, but you must have an Office 365 Pro Plus subscription to get the GA version of Power Map.

Although the Power Map release notes state that you can use it with a 32-bit processor, large amounts of data or graphics intensive map layers can slow the computer to halt or cause Excel to crash. If you can help it, it’s best to have a 64-bit computer for Power Map development or consumption in Excel.

You’ll notice that the list of data sources for SSRS is fairly long compared to the other development environments, but don’t let that mislead you. I’m just listing direct data sources. Although Power Pivot only takes up one line, it can connect to a multitude of data sources, and Power Query extends those options even further. SSAS Tabular also has many options for data sources.

Also, be aware that bubble sizing in Power View for small values can be misleading. This issue is compounded by the fact that turning on data labels for a Power View map only labels the location and not the amount related to the size of the bubble.

In my presentation at SQL Saturday Chicago, one of the attendees expressed the opinion that Power Maps tend to be very busy and it’s difficult to tell where to look when you first encounter one.  I don’t disagree with that, so I’ll throw in the warning that it can be very easy to make a Power Map that is visually appealing but doesn’t provide any insight into your data. I think there are some good use cases where Power Map is the best tool to deliver insight, but not every map needs a time axis and multiple layers/multiple graph types. Another limitation of Power Map is that is can only be opened from the Excel file that contains it (there’s no way to view it in a web browser). While I expect that to change in the future, this is the current state today. Outside of exporting a tour to video, the development environment and the viewing environment are one and the same. To create a new Power Map or insert an existing Power Map, you must go to the Insert tab in Excel and choose Map. This will open the Power Map window where you can view and update your map.

I also want to point out that SSRS is the only one of the three tools that can make use of SQL Server spatial data types. If you would like to learn more about spatial data types in SQL Server, Hope Foley has a great presentation on the subject and has her slides and scripts posted on her blog. Jason Thomas has some good blog posts on SSRS maps.

Data Visualization, Excel, Power Map, Power View, SQL Saturday, SSRS

I’m Speaking About Geospatial Data Viz and Data Viz Best Practices in April

I will be speaking at two SQL Saturdays in April.  First, I will be at SQLSaturday #297 in Colorado Springs on April 12.  I’ll be presenting my session on The Accidental Report Designer: Data Visualization Best Practices in SSRS.  See my previous post on this subject to understand why I think it is an important message for all data professionals.  In this session, I share guidance on evaluating the effectiveness of data visualizations and a few good tips and demos to take your data visualizations from chaotic and ineffective to professional and powerful.

Next, I will be at SQL Saturday #291 in Chicago on April 26.  I will be presenting a new session to help you Choose Your Geospatial Mapping Adventure. Here’s the abstract:

You have geospatial data and you know that plotting it on a map will deliver insights. Or maybe you keep hearing “geospatial” as a current BI buzzword and you want to be informed about your options when you get to it. The Microsoft BI stack provides several options for visualizing location data. But how do you know which tool will best serve your purpose, or what factors are important in making this decision? We’ll identify factors to consider when choosing a tool to visualize your data in maps such as system requirements, availability and type of location data, required level of technical understanding, and more. You’ll leave the session confident in your ability to choose the best tool within Excel and SQL Server.

The Microsoft BI stack offers several great options for presenting data on a map, but your decision of which tool (Power View Map, Power Map, SSRS map, free mapping apps in Excel) can limit you in features, accessibility, and time to delivery. Some of the tools require more technical skill than others, but you lose some of the ability to customize when you go with the highest ease of use.  The session will be filled with tips, explanations of features and requirements, and demos.

If you will be in the area on the dates of these events, please stop by to participate in my sessions or just say hello.

Excel, Microsoft Technologies, Power BI, Power Map

Power Map for Excel is Now Generally Available for Office 365 With a Few New Features and Bug Fixes

Today, Microsoft announced that Power Map for Excel is now in GA. As Chris Webb noted, it will only be available for those that have Office 365 ProPlus. Those with standalone Excel or Office Professional Plus will not get the GA version of Power Map. I just finished applying the update to Office to get the GA version and I have a few observations.

Once you have the Office SP1 update, you will need to enable to the GA add-in and disable the preview add-in, as it installs separately from (rather than over the top of) the Power Map Preview add-in.

Enable Power Map Add-in

If you had the Power Map Preview installed, when you go to insert or open a Power Map, you will receive a dialog box that asks you to allow it to uninstall an older version.  This only takes a minute. Then you can open Power Map once again and work with the new GA version.

There don’t seem to be any significant differences between the preview and GA version, but they did fix a few bugs, add a couple of new features,  and make a few UI updates.

Some of the icons in the ribbon have been updated, but the options are still the same.
PM Updated Ribbon icons

If you click on Themes, you will notice there are now 12 themes available where before there were 8.

PM Themes

When you go to add a new layer, you will notice that the Chart Type drop-down was changed to an array of icons.  The available selections remain the same: stacked column, clustered column, bubble, heat map, region.

PM - chart type icons

They have added a new icon to the Layer Manager panel that allows you to add a new layer to the selected scene.

PM added New Layer IconThey also added a new option in the Layer Options to change the opacity of the layer.

PM Layer Option - Opacity

And they fixed the color picker in the Layer Options!  This was awful in the preview, but it looks much better now.

PM - Color Picker worksThey also updated the UI for annotations.

PM - Updated Annotation UI

And, I’m not 100% sure on this one, but I think they added the ability to add line breaks in the text of the annotations (using Shift + Enter).  I seem to remember struggling with the lack of this ability in the preview version.

PM - annotation line breaks

Those are all of the changes I noticed.  Leave me a comment if you see something I missed.  I was able to open and update several maps I made with the Preview version and I haven’t noticed any incompatibilities between the preview and GA versions.

There are still some features that I feel should be included in this product:

  • filter data in the map without having to make lots of calculated measures in Power Pivot to accomplish this
  • show/embed a Power Map on a web page without having to open it in Excel or download/play a video of the tour
  • include images and text in a single annotation
  • choose which fields appear in tool tips/hover text
  • reposition annotations so they don’t overlap with another annotation or a chart item
  • two-color scales for region shading (ex: blue -> red for showing temperature, red – > green for under/over goal)
  • stacked column charts with categories, let the user choose how the items are stacked ex: chronologically asc/desc, alphabetically by category, etc.)
  • create a map from a copy of an existing map

Hopefully some of them will come with the next release.

Happy mapping!