SQL Server Pro - May 2013

by L1_Superfunction. Notice that several columns have been placed on the Axis section of the chart. Although you can still click a bar to make it act like a filter, you can now double-click a bar to drill down from one axis column to the next. Figure 6 shows the progression of what this chart looks like as I double-click from Superfunction to Function, and then from Function to Sub Function. You can drill up at any time by hovering over the chart and clicking the Up Arrow icon in the top right corner of the chart.

Drillable charts don’t require explicit hierarchy development ahead of time. You can select and place columns in any order on the chart axis. Drillable charts work across column, bar, scatter, and pie charts.

A Power View report can be exported to PowerPoint so that it can be viewed on the desktop.

New SP1 Capability: Map Control

With SQL Server 2012 SP1, Power View now includes a Map control that leverages Microsoft Bing Maps. This control requires Internet connectivity on the client (not the server) to function. If you have geocoded data (i.e., latitude and longitude coordinates), Bing Maps can use this information for precise plotting. Columns from a data model (e.g., country, province, zip code), however, don’t have to be geocoded in order for the map integration to work.

When a column is placed on the Map control, Power View sends the column values through a secured web service for dynamic geocoding. For example, in Figure 7, all of my MapReference columns, with the exception of the Middle East, were accurately geocoded. Most of the time, Bing Maps does a good job of matching common names. Note that PowerPivot for Excel 2013 and SSAS 2012 SP1 tabular databases also let you specify a column’s Data Category value (e.g., address, city, county) to improve matching speed and accuracy.

The maps created with the Map control are drillable. For example, in Figure 7, I placed both MapReference and Entity in the LOCATIONS section. (Note that a location is similar to a chart axis.) If I double-click one of the MapReference pie charts, the Map control will automatically redraw the entities that roll up into the selected MapReference.