Creating a PowerPivot Data Model in Excel 2013

Exploring self-service BI in Microsoft Excel 2013

Self-service business intelligence (BI) is not new to Microsoft Excel. Since Excel S2000, 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 so 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 PowerPivot

A Data Model is a new approach for integrating data from multiple tables, effectively building a relational data source inside an Excel workbook. Within Excel, Data Models are used transparently, providing tabular data used in PivotTables, PivotCharts, and Power View reports.

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 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 tutorial, 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.

Related Article: MS Excel Interview Questions

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 checkbox is automatically selected, without the option to clear it, when you import multiple tables with one connection. However, if you import only a single table, then 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 checkbox 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 a selection of tables and columns for a new relationship.

After building your first PivotTable or other types of a 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.

Frequently Asked SQL Server Interview Questions & Answers

Managing data as a Power Pivot model

Power Pivot Management Dashboard is a collection of predefined reports and web parts in SharePoint Central Administration that help you administer a SQL Server Power Pivot for SharePoint deployment.

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 dialogue 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 a 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 as 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:

Related Page: SQL Server 2008 R2 – LookUp Enhancement In SSRS

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, in tabular models, define viewable subsets of a model that provide focused, business-specific, or application-specific viewpoints of the model. 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=.

KPIs In business terminology, a Key Performance Indicator (KPI) is a quantifiable measurement for gauging business success. 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 even making a selection in the Field List, but still having the ability to create a KPI in the Calculation Area in the Power Pivot window.

Descriptions SQL Server stores column descriptions as so-called Extended properties, using the extended property named ‘MS_Description’. 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 are one-click filtering controls that narrow down the data shown in PivotTables and PivotCharts. Slicers can be used interactively to display data changes when you apply filters. The Slicer is added as an extra control in the PivotTable or chart, and lets you quickly select criteria and instantly show the changes. You could also embed the breakdown by promotion in the report itself, by including the field in the row or column heading, but Slicers do not add extra rows to the table, only provide an interactive view into the data.

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. Full-Text Search in SQL Server and Azure SQL Database lets users and applications run full-text queries against character-based data in SQL Server tables.

Relationships A relationship works by matching data in key columns — usual columns with the same name in both tables. 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 Data classification is the process of organizing data into categories for its most effective and efficient use. 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, the Windows Azure Marketplace uses this information to suggest data sources that might be useful to integrate into your Power Pivot model.

Related Page: How To Create A Power View Report In Excel 2013

Upgrading from PowerPivot for Excel 2010

This topic explains the user experience of workbooks created in previous Power Pivot environments and how to upgrade Power Pivot workbooks so that you can take advantage of new features introduced in this release.

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.

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSAS	SharePoint
SSRS	SQL Server DBA
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator