Creating a Power Map Reporting Content from SQL Server

Power Map is the latest self-service business intelligence (BI) tool from Microsoft. It helps users visualize geographic information using 2D and 3D maps. Let us walk you through some of the basics of Power Map.

As a three-dimensional (3-D) spatial-visualization tool, Power Map adds location context to your business metrics and even allows you to see how these metrics change by location over time. Like Power Query, Power Map is a separate downloadable add-in that you must enable in Excel after installing it. (Enabling an add-in is described in the “ MANAGING DATA AS A POWER PIVOT MODEL” section earlier in this field of topics). To use Power Map, you must install one of the following versions of Microsoft Office on your computer:

• Office Professional Plus 2013
• Office 365 ProPlus
• Office 365 Midsize Business
• Office 365 E3, E4, A3, A4, G3, or G4

Note You can download Power Map Preview for Excel (32-bit or 64-bit) from https://www.microsoft.com/en-us/download/details.aspx?id=38395 for the September 2013 release, although a newer version might be available. Search for Power Map in the Microsoft Download Center (https://www.microsoft.com/en-us/download) to locate the latest version by release date. Unlike for Power Query, you can also install this add-in for Excel 2010.

 Although a 32-bit version of Power Map is available, you should use a 64-bit computer if you are willing analyzing large volumes of data. With a 32-bit computer, you need a minimum of 1 GB of RAM, but a 64-bit computer should have at least 2 GB of RAM.

 For greatest precision, you can include latitude and longitude DATA in your data set, but Power Map can identify geocode locations in a table, a Data Model, or a Power Pivot model based on the following types of geographic data:

• Street Address
• City
• County
• State/Province
• Zip Code/Postal Code
• Country/Region

Important Your computer must have Internet connectivity to use Power Map because the geocoding in Power Map relies on the Bing Maps service.

Creating a Power Map

If you are working with an Excel table in a workbook, click any cell in the table, click Map on the Insert tab on the ribbon, and then click Launch Power Map. If you are working with a Data Model, click Map on the Insert tab. Either way, a new window displays an empty map in the center of the screen and the field list for your table or Data Model in the right pane. You can then select the geographic fields to map, such as City or State Or Province. If Power Map does not automatically recognize the map level, such as Latitude, you can use the drop-down list to the right of the field name to associate the field with the correct map level. Power Map begins plotting data points for these locations on the map, as shown in Figure 4.16. Click the Next button to continue with the map design process.

FIGURE 4.16 The creation of a new Power Map and assignment of geographic fields to corresponding map levels.

Visualizing geographic data

After identifying the geographic fields to map, your next step is to explore the data in the map by defining the visualization type to use and the value to assign to the visualization. You choose the visualization type in the Type list, and then, if you are working with the Column visualization type, you assign a value to Height by selecting it in the field list or by dragging it from the field list to the Height box. Power Map automatically aggregates the value you select; it uses Sum by default, but you can change the aggregation function to Average, Count, Max, Min, or None by clicking the arrow icon to the right of the field name and selecting the function you want.

Important Power Map reads the data from the Data Model when you initially create the map. If you make design changes to the model or refresh the data in the model, you must click Refresh Data on the Power Map ribbon to synchronize the map with your underlying data model.

You can enhance the column appearance by adding a field to the Category box. Each distinct value for the field you select is assigned a separate color, and a corresponding legend appears on your map. In addition, Power Map calculates the aggregated values for each geographic field in your data set and plots the values on the map, as shown in Figure 4.17. As the value of the aggregated field increases, the height of the column also increases. You can use the option button next to the Category label in the Task Panel to specify whether the categories should be displayed as a clustered column chart or a stacked column chart. Use the Shapes command on the ribbon to change from a square shape for the column to a different shape, such as a triangle.

FIGURE 4.17 Power Map displaying aggregated values from a Data Model as a column chart by geographic fields.

At the time of this writing, three additional options are available for visualizing your geographic data:

 Bubble You can use the Bubble type to produce a visualization that increases the size of a bubble at a geographical location as the aggregated value assigned to Size increases. If you add a category to the visualization, it switches to a pie chart to display the categories as ratios, as shown in Figure 4.18.

 Heat map A heat map is a representation of data onto a graph where the individual values are represented in the form of colors. A typical example is a matrix, where each cell has a different color depending on the value of that cell. However, heat maps can have other representations as well, such as a row or column inside a table or a geographical map. When you switch to the HeatMap type, a spectrum of color is associated with the range of aggregated values, with smaller numbers displayed as blue hues and larger numbers displayed as red hues, as shown in Figure 4.19. There is no option to categorize a heat map.

 Regions Rather than display data for a specific geographic location, Power Map can aggregate the values at one of the following levels: country/region, state/province, county, or zip code/postal code. Figure 4.20 shows an example of total sales calculated by state or province worldwide. When this type of chart includes a category, the legend indicates how the opacity of the category color increases as the category represents a higher percentage of the overall value in the geographic region. You can change the options for category shading by clicking the arrow icon to the right of the Category label.

FIGURE 4.18 : Power Map displaying aggregated values from a Data Model as a bubble/pie chart by geographic fields.

FIGURE 4.19 : Power Map displaying aggregated values from a Data Model as a heat map chart by geographic fields.

FIGURE 4.20 : Power Map displaying aggregated values from a Data Model as a region chart by state or province.

Regardless of the type of map you create, you can point to a data point to display a dynamic ScreenTip. This ScreenTip includes the geographic details, the aggregated value, and the category label, as shown in Figure 4.21. You cannot customize the ScreenTip to include other data elements.

FIGURE 4.21 :A ScreenTip in Power Map.

Exploring the 3-D map

Power Map allows you to navigate your map by using a mouse, a keyboard, or both. Table 4.1 lists your navigation options.

TABLE 4.1 : Power Map navigation options

You can also use the Find Location command on the Power Map ribbon to find a specific location, even if that location is not represented in your data set. After clicking Find, provide one of the following: region, point of interest, or latitude and longitude. Power Map then adjusts the map to your specified location.

Displaying values over time

If you have a field with a date data type in your data, drag it to the Time box in the Task Panel. Click the arrow icon to the right of the field that you added to map the field to one of the following time types: None, Day, Month, Quarter, or Year. When you click Play in the Power Map Time player that appears in the map area, Power Map animates the map to show how values change over time or how values accumulate over time. You control animation behavior by clicking the Settings button next to the Time label in the Task Panel. Your options include:

 Data Shows For An Instant In this case, the visualization of data changes with each date and location combination in the data set.

 Data Accumulates Over Time With this option, the value for each date in a particular location is aggregated, with the final value representing the total aggregation of records for the time series.

 Data Stays Until It Is Replaced A data value persists in a location until a new date record for that location occurs in the time sequence.

For indepth knowledge on Microsoft Power Map Reporting Content from SQL Server, 
Join SQL SERVER TRAINING from Mindmajix.

Frequently Asked SQL Server Interview Questions & Answers

Enhancing a map

• Maps are an immediate and visual way to display information for countries and regions.
• As countries, regions and states (or counties) are, for the most part, instantly recognizable your users do not need added text in the visualization. This frees up space on screen.
• Maps allow drillthrough that lets you hierarchize your information

You can enhance your map in the following ways:

 Add a two-dimensional (2-D) chart To do this, click 2D Chart in the Insert group on the Power Map ribbon. If your map has multiple layers, you must first pick the layer to display in the chart. A chart of the top 100 locations is displayed above the map. You can customize this chart by using the drop-down list in the upper-right corner to change the chart type. If the map contains a category, you can click the category name, such as Bikes in Figure 4.22, to select a different category. You can also toggle to view the bottom 100 locations by clicking the phrase Top 100 in the chart. When you point to the horizontal axis, a scroll bar appears when the entire set of locations can’t be viewed in the size allotted to the chart. If you select a bar in the chart, you can see cross-filtering applied to the data on the map.

FIGURE 4-22 A 2-D chart superimposed over a map to display the top 100 locations for the current value by category.

 Add text There are two ways to add text to a map. First, you can right-click a data point in the map and select Add Annotation. In the dialog box, you provide a title for the annotation and an optional description. The description can be custom, a selection of fields displayed in the map, or an image. The second way to add text to a map is to insert a text box. The result looks like an annotation, but the text box is not bound to a data point, as an annotation does. You can format the font properties for both a text box and an annotation.

Add legend When you add a category, Power Map automatically adds a legend. If you remove the legend to see more of the map area, you can add the legend back by clicking Legend on the Power Map ribbon.

 Apply theme Use the Themes command on the Power Map ribbon to apply formatting to the colors and images used in the map. Some images can provide road details, while other images provide a satellite view. If your tour contains multiple scenes, you have the option to use a different theme in each scene.

 Add map labels Click Map Labels on the Power Map ribbon to superimpose the names of countries on the map when it is zoomed out. As you zoom in, the labels for states or provinces, cities, and points of interest are displayed.

 Switch to flat map For some visualizations, you might find it more helpful to view the data on a flat map. Click Flat Map on the Power Map ribbon to switch between the 3-D view and the flat map view.

Working with tours, scenes, and layers

When you first launch Power Map in a workbook, you create a new tour containing a single scene by default. The initial scene contains only one layer. You can add more layers to a scene to present different visualizations of data simultaneously. Click Add Scene to add scenes to your map and have a collection to play in sequence. Click the gear icon at the top of the Task Panel, and then click the Scene Options link to configure the following scene settings:

 Scene Duration (Sec) You can specify how long the scene should be displayed in seconds. The default is six seconds.

 Scene Name The name is displayed in the Tour Editor pane to help you distinguish between views. It is not displayed when you play the tour.

 Transition Duration (Sec) This value represents the time to move between the locations at the focus of consecutive scenes. The default is three seconds.

 Effect You can choose the style of transition. The available effects are described in Table 4.2. The default effect is Station. The transition duration and speed must be configured properly to see the full effect. That is, you might need to extend the transition time or set a faster speed to view the transition before the scene ends.

 Effect Speed You can increase or decrease the effect speed by using a slider.

TABLE 4.2 Available transition effects

When you close a scene in Edit mode, you save its current state. Later, during Playback mode, which you launch by clicking Play Tour on the Power Map ribbon, you can pause a tour and explore the map without stopping the tour. However, any changes you make to a scene in Playback mode are not saved. You can use the Next and Previous buttons to accelerate switching to the next or previous scene.

A workbook can contain multiple tours. When you return to the worksheet view in Excel and later insert a new Power Map, a dialog box displays existing tours for you to open, but it also gives you the option to delete a tour or to add a new one. Using the same dialog box, you can also duplicate a tour to use it as a starting point for a new visualization. Just right-click an existing tour, and select Duplicate Tour.

Important You cannot undo the deletion of a tour.

Sharing Power Map

You can share your map with others who have no access to Power Map by using the Capture Screen or Create Video commands on the Power Map ribbon. The Capture Screen command simply captures an image of your map and places it on the Clipboard so that you can paste it into a document or slide presentation. When you create a video, you must choose one of the following quality levels for the MP4 file format:

Presentation & HD Displays Use this option for high-definition resolution of 1080p.

 Computers & Tablets This option is best for computer monitors and tablets with a resolution of 720p.

 Quick Export & Mobile This is a small video format for sharing on mobile devices at a resolution of 360p.

Note You can configure the quality of the graphics for the video capture by displaying the File tab and selecting Options. The Power Map Options dialog box displays the following three choices: Speed for lower-quality graphics, Balanced for balancing quality with performance (default), and High Quality for producing higher-quality graphics at a slower speed.

List of Related Microsoft Certification Courses: