Introducing Microsoft SQL Server 2014: Technical Overview, 1st Edition (2014)

PART II. Business intelligence development

CHAPTER 4 Exploring self-service BI in Microsoft Excel 2013

CHAPTER 5 Introducing Power BI for Office 365

CHAPTER 6 Big data solutions

CHAPTER 4. Exploring self-service BI in Microsoft Excel 2013

Self-service business intelligence (BI) is not new to Microsoft Excel. Since Excel 2000, users have been able to connect to an Analysis Services cube to explore data by using PivotTables and PivotCharts. In Excel 2010, PowerPivot was introduced as an add-in based on SQL Server technology. PowerPivot permitted users to import data from a variety of sources and develop a model defining relationships and calculations that users could then explore by using PivotTables and PivotCharts. In Excel 2013, PowerPivot is still available with some new capabilities, but several other features in Excel make exploring and interacting with data even easier: Excel Data Model, Power Query, Power View, and Power Map.

Excel Data Model and Power Pivot

In Excel 2013, PowerPivot is built into Excel, so you aren’t required to download and install the add-in, but this applies only to specific versions: Office Professional Plus 2013, Office 365 Professional Plus, and the standalone edition of Excel 2013. When Power BI was announced as a new service for Office 365, PowerPivot was rebranded as Power Pivot, although for now this new name appears only in online documentation because the rebranding occurred after Excel 2013 was released. All references in the product’s user interface continue to display PowerPivot. Nonetheless, from this point forward in this book we use the new name, Power Pivot.

As part of the integration of Power Pivot into Excel, a type of object called a Data Model was also introduced. You can think of this object as a light version of Power Pivot. It provides storage for data that you import and contains metadata about that data, such as relationships between tables, but it does not contain enhancements to the data, such as calculated columns or column properties that require Power Pivot features. On the other hand, it does use the same built-in xVelocity engine (formerly known as Vertipaq) that was added to Excel 2010 to support Power Pivot. This means that your data is stored in a highly compressed, columnar, in-memory format that is efficient to query.

Working with the Data Model

A Data Model is created when you select the Add This Data To The Data Model check box in the Import Data dialog box. This check box is automatically selected, without the option to clear it (as shown in Figure 4-1), when you import multiple tables with one connection. However, if you import only a single table, the check box is not selected, and you must explicitly select it to add the table data to the Data Model.

FIGURE 4-1 The Import Data dialog box with the Add This Data To The Data Model check box selected.

You can continue to import data from other sources and add that data to the Data Model. If you import data without adding it to the Data Model, you can add it later. To do this, first highlight the cells that you want to add or place your cursor in one of the cells of a table or named range that contains your data. Next, click Add To Data Model on the Power Pivot tab on the ribbon or click PivotTable on the Insert tab and then select the Add This Data To The Data Model check box in the Create PivotTable dialog box.

Note There is only one Data Model per Excel workbook.

When you have multiple tables in the Data Model, you can import relationships when you import the tables from a relational source as a group. Otherwise, you can manually define the relationships between tables when you want to include data from them in a single report. Click Relationships on the Data tab to open the Manage Relationships dialog box, and then click New. Select the table containing the foreign column (that is, the column with values repeating across multiple rows) and the foreign column in the top row, and then select the related table and primary column (that is, the column with distinct values only), as shown in Figure 4-2.

The tables and fields that you add to the Data Model are visible in the Field List when you add a PivotTable, a PivotChart, or a Power View report to the workbook. The result is the same if you import data by using Power Pivot features and then define relationships. The key difference between the Data Model and Power Pivot is the inability to rename tables and columns or use the advanced modeling features of Power Pivot when your data is in the Data Model only. However, the Data Model is an easy way to start interactively exploring data without much effort. You can always open the Power Pivot interface to apply Power Pivot features to the model if additional refinement proves necessary.

FIGURE 4-2 Create Relationship dialog box displaying selection of tables and columns for a new relationship.

After building your first PivotTable or other type of report based on the Data Model, you can create another report based on the same Data Model. On the Insert tab, click PivotTable. Then, in the Create PivotTable dialog box, select Use An External Data Source, click Choose Connection, click the Tables tab, and select Tables In Workbook Data Model, as shown in Figure 4-3.

FIGURE 4-3 The Existing Connections dialog box displaying the selection of Tables In Workbook Data Model.

Managing data as a Power Pivot model

When you need to refine the Data Model in some way, you need to use Power Pivot. You might do this when you need to import a subset of data. Power Pivot allows you to select specific columns for import or to apply a filter to import a selected set of rows. After you import data, you can rename tables and columns, create relationships, and add formatting to improve the display of data in reports. You can also enhance the data with calculations to perform arithmetic or statistical operations or even to cleanse the data, such as replacing empty values with a default string or number.

The SQL Server 2012 release of Power Pivot for Excel is available as a downloadable add-in for Excel 2010, but it is built directly into Excel 2013. In either version of Excel, Power Pivot must be enabled. To do this, click Options on the File tab in Excel, select Add-Ins in the navigation pane of the Excel Options dialog box, select COM Add-ins in the Manage drop-down list, click the Go button, and select the Microsoft Office PowerPivot for Excel 2013 (or 2010) check box.

The latest release of Power Pivot in Excel 2013 works much like it did when it was introduced as part of SQL Server 2008 R2, as an add-in for Excel 2010. However, the workbook size limitation has been removed from the 64-bit version of Excel, which means your workbook can be as large as the amount of disk and memory on your computer permits. As you might expect, there are several new features, some changed features, and a few features that have been removed, as described in the following list:

Calculated fields Instead of right-clicking a table in the Field List to add a calculated field (previously called a measure), you click Calculated Fields on the Power Pivot tab (although you can still create a calculated field in the Calculation Area in the Power Pivot window).

Perspectives The list of available perspectives is no longer available at the top of the Field List. Now you can use perspectives to view a subset of the model only when you have the Power Pivot window open. If you publish your workbook to Power Pivot for SharePoint, you can create a connection string that uses the perspective explicitly. In addition to the Data Source and Initial Catalog keywords in the connection string, add Cube=<perspective name>.

KPIs In the previous version, you could select a calculated field in the Field List to enable the Create KPI option on the Power Pivot tab. Now you can use the KPIs option to create a new KPI or manage existing KPIs without making a selection in the Field List. You still have the ability to create a KPI in the Calculation Area in the Power Pivot window.

Descriptions You no longer view descriptions for tables, columns, and calculated fields in the Field List when working with a PivotTable or PivotChart. A description is now displayed only as a ScreenTip in the Field List of a Power View report.

Slicers The Slicers Vertical and Slicers Horizontal areas are no longer displayed at the bottom of the Field List. Instead, you right-click the field in the Field List and then select Add As Slicer from the submenu. To change the orientation of the slicer, click the slicer to select it, and then click Align Vertically or Align Horizontally on the Power Pivot tab.

Search The Search box has been removed from the Field List. Instead, use the Find option on the Home tab in the Power Pivot window to search for a table, column, or calculated field by name.

Relationships Power Pivot is no longer capable of automatically detecting relationships between tables. You must import relationships when importing a group of tables at one time or manually define the relationships in the model.

Data categorization The Advanced tab on the ribbon in the Power Pivot window includes a Data Category list that you use to assign one of the following categories to a column: Address, City, Company, Continent, Country/Region, Country, Date, Image, Image URL, Latitude, Longitude, Organization, Place, Postal Code, Product, State Or Province, or Web URL. Power View uses this categorization to apply the proper visualization to your data where possible. In addition, Windows Azure Marketplace uses this information to suggest data sources that might be useful to integrate into your Power Pivot model.

Upgrading from PowerPivot for Excel 2010

To upgrade an existing workbook that was created in Excel 2010, first open the workbook in Excel 2013. On the Power Pivot tab, click Manage. Excel displays a message explaining that you must upgrade the data model before using Power Pivot for Excel 2013. Click OK to display another message that warns that the upgraded workbook cannot be used with previous versions of Power Pivot. Click OK to start the upgrade. When the upgrade is complete, another message prompts you to take the workbook out of Excel compatibility mode. Click Yes to save, close, and reopen the workbook and thereby exit Excel compatibility mode.

Power Query

Power Pivot is a great tool for gathering together data from disparate sources and combining it into a single model for analysis, but it presumes you know that the data exists, where to find it, and how to use DAX to create calculated columns for simple restructuring or cleansing of your data. For discovering data and to use more advanced techniques for transforming data, you can use Power Query. Power Query is a separate downloadable add-in for the following Excel versions: Office Professional Plus 2013, Office 365 Professional Plus, and the standalone edition of Excel 2013.

Note You can download Power Query for Excel (32-bit or 64-bit) from http://www.microsoft.com/en-us/download/details.aspx?id=39379 for the January 2014 release, although a newer version might be available. Search for Power Query in the Microsoft Download Center (http://www.microsoft.com/en-us/download) to locate the latest version by release date. You can also install this add-in for Excel 2010 if you are using Microsoft Office Professional Plus 2010 with Software Assurance through Volume Licensing.

At a minimum, you use Power Query to find data that you need and then view it in a table after filtering it and shaping it to meet your requirements. You can also load the data retrieved by Power Query into the Data Model so that you can build PivotTables, PivotCharts, or Power View reports. Of course, after the data is in the Data Model, you can further enhance it by using Power Pivot. If you have an Office 365 subscription with Power BI enabled, you can share the queries that you develop, find and use existing shared queries, and monitor the use of shared queries.

Searching for data

One of the distinguishing features of Power Query is its ability to help you find not only internal data sources (if your organization uses Power BI) but also data from public data sources. Internal data sources are curated queries that designated users publish for others to use. To use this feature, you must click Sign In on the Power Query tab on the ribbon and enter your Office 365 logon credentials. Currently, public data sources include only sources based in the United States. The current collection that you can search (subject to change) includes the following data sources:

Dun & Bradstreet financial data (sample data only)

HealthData.gov

MCH Strategic Data (sample data only)

Open Government data (Data.gov)

Wikipedia

Windows Azure Marketplace

The World Bank

You start a search by clicking Online Search on the Power Query tab. You then type one or more keywords in the search box. If you used the Sign In button to log on to Office 365, a drop-down list appears to the right of the search box that allows you to restrict your search by choosing one of the following options:

All This is the default selection, with no restriction on which data sources to search.

My Shared This selection forces Power Query to search only the shared queries that you have published to Power BI. Shared queries are explained in more detail in the “Shared queries” section in Chapter 5, “Introducing Power BI for Office 365.”

Public When you select this option, Power Query returns only a set of public data sources.

Organization With this selection, Power Query includes all shared queries that you have permission to see, whether created by you or someone else.

A list of results is displayed as a series of pages in the Online Search pane, as shown in Figure 4-4. When you point to one of the results, a preview flyout screen displays a sample of the selected data source, in addition to a list of the columns the data source contains, the last modified date, the name of the source, and a link to the source. Your search keywords are highlighted in yellow.

At the bottom of the preview flyout screen, you can click Add To Worksheet to import the data unchanged into a new worksheet. If you need to modify the data first, click Edit instead. Then you can perform one or more of the steps described in the “Shaping data” section later in this chapter.

FIGURE 4-4 Power Query online search results.

Importing data

If you already know where to find the data you want, you can import it directly without performing a search. To do this, click the applicable option for your data source type in the Get External Data group on the Power Query tab (shown in Figure 4-5), and then select the data source.

FIGURE 4-5 Get External Data options on the Power Query tab.

You can import from the following types of data sources:

Web Provide the URL for a webpage containing data in a table format that Power Query can scrape or for a supported file type stored on a web server.

File Select a file type and then browse to the file location to select it. You can choose from the following types of files: Excel, CSV, XML, and text. You can even import metadata about files stored in a folder, such as file name, file name extension, date modified, and path.

Database Select a database type and provide a server name and optionally a database name. You can even provide a SQL statement if you prefer not to work with all the columns or rows of a selected table. You can choose from the following types of relational data sources: SQL Server, Windows Azure SQL Database, Access, Oracle, IBM DB2, MySQL, PostgreSQL, Sybase, and Teradata.

Other Sources Select a source type and then respond to the prompt to provide location information about the source. You can choose from the following types of other sources: SharePoint list, OData feed, Windows Azure Marketplace, Hadoop, Windows Azure HDInsight, Windows Azure Blob Storage, Windows Azure Table Storage, Active Directory, Exchange, and Facebook. You can also choose Blank Query and then type a Power Query formula that extracts and manipulates data to meet your requirements more specifically.

Table Select a table in the workbook before clicking From Table on the ribbon. As an alternative, you can select a range of cells in a worksheet and then click From Table. Power Query first converts the range of cells to a table and then imports the data into a query.

Note You can learn more about how to work with a specific type of data source at http://office.microsoft.com/en-us/excel-help/import-data-from-external-data-sources-HA104003952.aspx.

When working with certain types of data sources, such as relational tables, you have the option to import multiple items from the same source, as shown in Figure 4-6, by selecting the Select Multiple Items check box at the top of the Navigator pane. You can then select the check box for each item to import. When you point to a single item, a preview flyout screen displays a sampling of the data. A separate query is added to the workbook for each item you choose to load.

FIGURE 4-6 Select multiple items from a source in the Navigator pane and preview data in a selected item.

Note If you import multiple related tables from a relational source at the same time, Power Query detects the existing relationships and automatically adds them to the Data Model. However, if you import more related tables later, any existing relationships between the previously imported tables and the currently selected tables are ignored and not imported into the Data Model.

Loading the worksheet or Data Model

When you finish working with a query, you have the options to load the data into a worksheet only, into the Excel Data Model only, or to both locations. If you want to use the data with Power View or Power Map, you must load the data into the Data Model. This option is not enabled until the download of data from the data source is complete. You use the check boxes at the bottom of the Query Settings pane to make your selection, as shown in Figure 4-7.

FIGURE 4-7 Load settings in the Query Settings pane.

Shaping data

After using the online search option or importing data, you can manipulate the data in a variety of ways, reshaping the data by applying a series of transformations, filtering it, and splitting columns, to name just a few operations. If you import multiple data sets that share common data columns, you can combine these data sets into a single table to make analyzing the data easier.

To shape data for a particular query, you must open the Query Editor. You can do this by clicking Workbook in the Manage Queries group on the Power Query tab and then double-clicking the query in the Workbook Queries pane. Another way to open the Query Editor is to open the worksheet containing the table of data associated with the query, click the Query tab below the Table Tools tab, and then click Edit Query on the ribbon. You can then use options on the Query Editor ribbon, shown in Figure 4-8, to reduce the number of rows or columns in the query results, sort data, apply a transformation such as splitting a column, create a new column, or combine data from multiple queries by merging or appending data. You can also launch these operations by right-clicking a row or column to display a submenu of commands.

FIGURE 4-8 The Query Editor ribbon.

The process of shaping data can involve one or more steps. As a simple example, let’s look at the data from an online search, shown in Figure 4-9. In this example, the 2010 Land Area column contains data for both square miles and square kilometers, which can better be used in reports if the data is split into separate columns and restricted to the numeric portion of the data. Another column (not visible) contains latitude and longitude information that should likewise be separated.

FIGURE 4-9 Data in the Query Editor before transformation.

To edit the query, select the column in the grid and then use the ribbon or the submenu that is displayed when you right-click the column to apply a new step. For example, to separate the data in the 2010 Land Area column, you can use the Split Column By Delimiter transformation. When you specify this type of transformation, a dialog box prompts you for more instructions. In this case, you must choose Custom in the Select Or Enter Delimiter list, type sq mi in the next box, and then specify the position of the delimiter to use for splitting, as shown in Figure 4-10.

FIGURE 4-10 The Split A Column By Delimiter dialog box displaying a custom delimiter definition.

When you close the dialog box, Power Query creates two columns with the same name and a numeric value appended to uniquely identify each column, as shown in Figure 4-11. At this point, you can rename the columns to more clearly distinguish between the two and continue to apply transformations to produce query results that are more useful in reports.

FIGURE 4-11 Query results after applying the Split Column By Delimiter transformation

Power Query includes the following types of transformations for cleansing and restructuring your data:

Filter Click the arrow icon in a column to display a list of distinct values in the column, and then select the values to keep in the query results. You also have access to text, number, or date filters, just as you do when you use the Excel filter feature on a regular worksheet column. Another way to filter is to right-click a cell containing a value that you want to keep in or exclude from the results, point to Text Filters (or Number Filters or Date Filters), and then select a comparison operator such as Equals or Does Not Equal, among others. The comparison operator is applied to the selected cell value, and the rows are filtered according to the criteria you set.

Sort Select a column, and then click Sort Ascending or Sort Descending on the Query Editor ribbon. If you continue by applying a sort direction to additional columns, the initial sort remains intact, and the additional columns are sorted in the order selected. Click the arrow icon in the column and select Clear Sort to remove the sort transformation from the query steps.

Group rows Click Group By on the Query Editor ribbon, and then select one or more fields to use for grouping rows. You must provide a name for a new column that’s created to hold the aggregated value for the grouped rows, select the aggregate function to use (such as Sum or Count Rows), and specify the column to be aggregated.

Expand column Certain operations return a column of complex values, which is analogous to associating a table of columns and rows with each row in the data grid. You can click the expand icon in the column header and then select the columns to add to the data grid. The expand icon looks like this:

Aggregate When you have a column of complex values, click the expand icon in the column header, select the Aggregate option button in the column drop-down list, point to one of the aggregate functions (such as Sum of SalesAmount), and then select one or more of the available aggregation functions: Sum, Average, Minimum, Maximum, Count (All), and Count (Not Blank).

Insert index or custom column You can add an index column by clicking Insert Index Column on the Query Editor ribbon. Each row is numbered consecutively, beginning with zero for the first row. Another option is to create a column to contain values calculated from a query formula that you define. Click Insert Custom Column to open a query formula box for the new column and then provide a query formula to calculate a result for each row in the column.

Remove column(s) You can right-click a column you want to delete and then select Remove on the submenu to eliminate the column from the query results. As an alternative, you can right-click a column you want to retain and then select Remove Other Columns to reduce the query results to the single column you selected.

Remove error rows When you want to eliminate rows containing errors from the query results, right-click a column containing error rows and select Remove Errors on the submenu.

Promote row to column headers If the first row of the data grid contains column headers, click the table icon in the upper-left corner of the grid, and then select Use First Row As Headers in the submenu.

Split column Separate a single column into two or more columns by using delimiters or a fixed number of characters.

Merge column The merge operation requires all columns to have the text data type, so you might need to change the data type of some columns first. Select the column, and then select a data type in the Data Type list on the Query Editor ribbon. While pressing the Ctrl key, select two or more columns to merge, and then select a separator to insert between column values, such as a comma or a space. A new column replaces the selected columns in the query results. If you prefer to create a new column with the merged column values, click Insert Custom Column on the Query Editor ribbon and use a formula to concatenate columns, like this: =[City] & “ “ & [State]

As you perform each operation on the data, notice that new steps appear in the Query Settings pane. Figure 4-12 shows the set of steps required to reshape the 2010 Land Area and Location columns. You can click any of the steps to see the shape of the data after the selected step was applied. If you try a transformation that fails to produce the result you want, you can delete the step by clicking the X icon that is displayed to the left of the step when you point to the step’s name. If you need to change a step setting, such as a delimiter value for the Split Column By Delimiter transformation, click the gear icon to the right of the step’s name.

FIGURE 4-12 The Query Settings pane displaying a series of transformation steps applied to a query.

As you select a step in the Query Settings pane, the formula bar above the data grid displays the query formula, as shown in Figure 4-13. After you learn the Power Query Formula Language, you can build more complex expressions and customize a transformation to better meet your needs.

FIGURE 4-13 The formula bar displaying an example of a query formula for a Split Column By Delimiter transformation.

Note More information about the Power Query Formula Language is available at http://office.microsoft.com/en-us/excel-help/learn-about-power-query-formulas-HA104003958.aspx.

When you finish editing a query, confirm that you have selected the correct Load Settings and then click Apply & Close on the Query Editor ribbon. The data loads as a table in a worksheet, as a table in the Data Model, or both, depending on your Load Settings selections.

Combining data

If your workbook contains two or more queries that have a column in common—such as when one query returns Sales Header data and another query returns Sales Detail with a common sales order number column in each query—you can use an inline merge to merge data as a step in a single query. You do this by clicking the table icon in the upper-left corner of the grid and then selecting Merge. Your other option is to use an intermediate merge to create a separate query for each merge, which you launch by opening the query to use as the primary table and then clicking Merge on the Query Editor ribbon. In the latter case, your workbook contains multiple queries.

Whichever option you use, you select the second table for the merge operation in the Merge dialog box and then select the matching column(s) for the primary and secondary tables, as shown in Figure 4-14. Power Query compares the data in both tables to determine how many rows match, which helps you judge the quality of the merge operation. By default, the Only Include Matching Rows check box is not selected, which means the query results might contain rows for which the columns from the secondary table are null when no match exists between the tables, similar to a left outer join in a relational query. However, if you select this check box, the query results contain only rows that match, which could be fewer than the number of rows in the primary table, similar to an inner join in a relational query.

FIGURE 4-14 The Merge dialog box displaying a selection of common columns for primary and secondary tables.

When you click OK in the Merge dialog box as a step in an intermediate merge operation, a new Query Editor window opens to display the query results for the new query. You must then expand the table link column—the last column in the data grid—to add the new columns into the grid alongside the columns from the primary table.

Instead of expanding an existing query by adding columns from a second query, you can expand the existing query by adding rows from a second query, which you achieve by performing an append operation. As for a merge operation, you can choose an inline append or an intermediate append. Launch an inline append operation by clicking the table icon in the upper-left corner of the data grid and then selecting Append. To launch an intermediate append operation, click Append on the Power Query ribbon. Either way, launching the append operation opens the Append dialog box, in which you must specify the secondary table to append to the primary table. When you click OK, the query results are displayed in the data grid.

Power View

Power View in SharePoint was introduced in SQL Server 2012 as a feature in the SharePoint integrated mode of Reporting Services. Much of the functionality in that version of Power View (described in our previous book, Introducing Microsoft SQL Server 2012, published by Microsoft Press in 2012) is available now in Excel 2013 as a built-in add-in that you must enable as described earlier in this chapter for the Power Pivot add-in. Power View in Excel also includes the features added to Power View in SQL Server 2012 Service Pack 1, such as maps, hierarchies, and themes.

Note The two features available in Power View in SharePoint that are not available in Power View in Excel are the ability to develop reports using an Analysis Services multidimensional model as a source and the ability to export the report to Microsoft PowerPoint format. Also, unlike the Power Pivot and Power Query add-ins, which you can install in Excel 2010, the Power View add-in works only with Excel 2013. Like Power View in SharePoint, Power View in Excel requires you to install Silverlight.

Creating a Power View report

As we mentioned at the beginning of this chapter, Excel has one Data Model per workbook. You can insert a Power View report into a workbook based on this model or on an external data source. By using the external data source option, you can add different Power View reports that rely on separate data sources to the same workbook.

To create a Power View report, click Power View on the Insert tab on the ribbon. A special sheet is displayed in the workbook, with a report design surface and a filters pane. Select fields in the Power View Fields list to add a table to the report design surface. You can click the Design tab on the ribbon to switch to one of the following data visualizations, all of which are also in the Power View in SharePoint version:

Matrix

Card

Bar (stacked, 100% stacked, clustered)

Column (stacked, 100% stacked, clustered)

Line

Scatter

Pie

Map

Note Like Power Pivot, Power View is not a new self-service BI feature, although it is new to Excel. Because we have elected to dedicate the majority of this chapter to new features, we do not repeat the information we provided on this topic in Introducing Microsoft SQL Server 2012. You can learn more about the features added as part of SQL Server 2012 SP1 at http://office.microsoft.com/en-us/excel-help/whats-new-in-power-view-in-excel-2013-and-in-sharepoint-server-HA102901475.aspx#_Toc358038111.

Working with visualizations

To start a new visualization on the same report, click an empty area of the report and begin selecting fields to add to the new table, which you can switch to a new visualization later. You can also create a visualization by copying an existing visualization and pasting it into the same sheet. After you paste the copy, you can change the fields selected in the bottom section of the Power View Fields list to arrange the visualization to suit your needs, as shown in Figure 4-15. You can also copy and paste visualizations from one sheet to another, but only if you are working with the same data connection on both sheets.

FIGURE 4-15 A Power View report in Excel with multiple visualizations on a single sheet.

Sharing a Power View report

After designing a Power View report in Excel, you can publish it to Excel Services as part of an on-premises SharePoint infrastructure or to Office 365 as part of a cloud infrastructure. If publishing your workbook to an on-premises SharePoint installation, you can add it to a standard SharePoint document library or to the Power Pivot Gallery. If you choose the Power Pivot Gallery, the thumbnail image for the Power View report is not displayed in the gallery views, although the Power View report displays normally when you open the workbook from that location. We describe the user experience for viewing a Power View report in the cloud in the “Power BI sites” and “Power BI for Mobile” sections in Chapter 5.

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 chapter.) 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 http://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 (http://www.microsoft.com/en-us/download) to locate the latest version by release date. Unlike for Power Query, you cannot 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 will be 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 and 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 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 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.

Enhancing a map

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 is. 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.