Guest post by Colin Banfield [LinkedIn]

In Part I, we reviewed the new Data Model (which is really just PowerPivot baked into native Excel), discussed the data import process, and other related issues. In this part, we further the discussion of Data Model, and review changes to PowerPivot for Excel 2013.

Creating Table Relationships with the Data Model

After we create a Data Model in a workbook, we can create, view, or modify relationships between tables. When we import multiple tables from the same relational data source, Excel detects primary and foreign key relationships and by default, creates the relationships automatically. We can override the default behavior during the import process. In the Import Data dialog box for Access shown in figure 1, we can clear the check box titled Import relationships between tables. For some other data sources (SQL Server for example), the check box can be found in the Select Table dialog box (the dialog box in which you select multiple tables).

Import Data Relationship Drop Down

Figure 1 – Import relationships check box

Whether or not you import relationships, you can create or modify relationships after the import. You can find a Relationships button in two places – in the Data tab, and the PivotTables tools Analyze tab. See figure 2.

Data Tab Relationships

Relationship in Analyze Tab

Figure 2 – Relationships button in Data tab (top) and Analyze tab (bottom)

The Relationships buttons are disabled if a Data Model with at least two tables do not exist in the workbook. Clicking the Relationships button displays the Manage Relationships dialog box, as shown in figure 3.

Relationships Dialog Box

Figure 3 – Data Model Manage Relationships dialog box

The next figure shows the Create Relationships dialog box, which Excel displays after you click the New button in the Manage Relationships dialog box.

Create Relationships

Figure 4 – Data Model Create Relationship dialog box

Existing PowerPivot users will be familiar with the dialog boxes shown in figures 3 & 4 because they are similar to the corresponding dialog boxes in PowerPivot (with some changes in labels and button layout). However, unlike PowerPivot, there is no Diagram View in the Excel UI.

Working with the Data Model

After you create the Data Model in a workbook, and have related tables, what can you do? You can create PivotTables, Pivot Charts, and Power View reports. Power View is a new Excel 2013 add-in that you use to analyze data visually. PivotTables and PivotCharts  that you create in the Data Model have the following advantages over classical PivotTables and PivotCharts:

  1. You can work with significantly more data – hundreds of millions of rows (due the the compression algorithms used in the Data Model).
  2. You can work with multiple tables and easily create relationships between tables (or use relationships that Excel creates automatically). On the other hand, a classical PivotTable or PivotChart can use only a single table.
  3. PivotCharts that you create with the Data Model do not require a corresponding PivotTable.
  4. As previously mentioned, the Data Model allows you to create Power View reports.
  5. The level of analysis that you can perform natively in PivotTables and PivotCharts created using the Data Model is not that much different from the analysis you can perform using classical PivotTables and PivotCharts. In both cases, you can create implicit measures, apply custom calculations, use slicers, and use a Timeline (new to Excel 2013). However, you can considerably extend the analysis capabilities of Data Model based PivotTables and PivotCharts using PowerPivot. You can create calculated fields and calculated items in a classical PivotTable, but the calculated fields you create are extremely limited, and in some cases, the displayed data is incorrect.

Compatibility Issues

As you might expect, PowerPivot models created in Excel 2010 are incompatible with Excel 2013, and Data Model models created in the Excel 2013 Preview are incompatible with every current shipping product, including Excel 2010, SharePoint 2010, and SQL Server 2012. A key reason for the incompatibility is that there is no customData folder in the Excel 2013 file. In Excel 2010, the customData stores the data imported into the PowerPivot model. Therefore any application attempting to extract data from a customData folder will return an error. Figure 5 shows the Excel 2013 file structure. The file “DataModel1,” contains a Data Model with a PivotTable and a Power View report. In figure 5, the customData folder found in Excel 2010 is replaced by a model folder.

Excel File Structure

Figure 5 – Excel 2013 file structure

When you open an Excel 2010 workbook containing a PowerPivot model in Excel 2013, it doesn’t immediately complain. However, if you attempt to refresh a PivotTable in the Excel 2010 workbook, Excel displays the message shown in figure 6.

Unsupported Version Message PM

Figure 6 – Unsupported workbook message

Okay, there’s nothing alarming about the message. We get a similar message when opening a PowerPivot v1 workbook in a PowerPivot v2 environment. However, the message is misleading because the data is not upgraded after you click OK to dismiss the dialog. Click Refresh again, and the message in figure 6 reappears. What’s is going on? It’s nothing more than Excel displaying an inappropriate message in the current context – an issue that should be fixed. If you have PowerPivot installed and click Manage in the PowerPivot tab, Excel displays the message in figure 6, but this time after you click OK, you get a confirmation message as shown in figure 7. The difference now is that although Excel displays the same message as the refresh attempt, the message is shown in a context where the update can be done.

Unsupported Version Confirmation PM

Figure 7 – Unsupported data update confirmation

And now, after clicking OK, the data is truly updated. The message that Excel displays when you attempt to refresh the incompatible workbook should be more instructive.

PowerPivot 2013

We’ve seen in the previous section that we can create only fairly basic reports in the Data Model using the tools available in the Excel UI. To extend the Data Model with complex calculations using DAX, to create KPIs, hierarchies, perspectives, etc. – even to view and delete a table, we must use PowerPivot.

PowerPivot 2013 provides essentially the same functionality as the 2010 version, although a few things have changed in the user interface. PowerPivot ships with Excel 2013, so you don’t have to find a download site. If you plan to use both Office 2013 with PowerPivot and Office 2010 with PowerPivot, one installation should be in a virtual machine, or in a separate physical machine. It appears that PowerPivot 2013 overwrites data in folders that PowerPivot 2010 also uses, so after enabling PowerPivot 2013, PowerPivot 2010 no longer works.

PowerPivot 2013 is not enabled by default. Select File—>Options—>Add-Ins. In the Manage drop down, select COM Add-ins and click Go. In the COM Add-Ins dialog box, select the check box labeled Microsoft Office PowerPivot for Excel 2013. See figure 8.

Enabling PowerPivot Add-in

Figure 8 – Enabling PowerPivot for Excel 2013

I agree – the name is unnecessarily long, especially when you consider the much briefer naming convention that Microsoft uses for other Excel add-ins.

Up to this point, we have been making loose references to “measures.” However, in Excel 2013, we no longer create measures in PowerPivot. We now create calculated fields. Yes, they’re both the same thing, but I suppose that the gods are on a campaign to rid us of multidimensional terminology in Excel. Besides, we have been using the term “calculated field” in classical PivotTables long before PowerPivot was a pup.

The contents of the PowerPivot 2013 Ribbon has been rearranged somewhat, as shown in figure 9.

PowerPivot Ribbon Tools

Figure 9 – PowerPivot 2013 Ribbon tab

The change from “Measure” to “Calculated Field” is only interface deep, however. For some reason, the development team decided to confuse the issue by creating objects with the names ModelMeasureNames and ModelMeasureName. Go figure.

Data Modification and Refresh

Perhaps one of the most significant advantages of the internal Data Model compared to the add-in xVelocity engine in PowerPivot for Excel 2010 , is that external data that you refresh, calculated columns that you add, and other metadata changes that you make in PowerPivot, are immediately reflected in any underlying dependent report that you create in Excel. Say, for example, that you have a PivotTable that uses a Date table and a Sales table from an external data source. You then go into the PowerPivot window and refresh the data in the Sales table. Next, you add a couple of calculated columns and select some columns to hide from client tools. When you switch back to your PivotTable in Excel, all of the changes you made in PowerPivot are reflected immediately in the PivotTable (and the PivotTable field list), without having to refresh the PivotTable in Excel!!! In Excel 2010, refresh is a two-stage process, which causes considerable consternation with users.

Field List Task Pane Changes

In Part I, we reviewed the Data Model field list for PivotTables and PivotCharts. Since the field list task pane has to work independently of PowerPivot, much of functionality that was unique in the Excel 2010 PowerPivot task pane has been removed in Excel 2013. For instance, vertical and horizontal slicer drop zones have been removed from the field list task pane. In Excel 2013, you can right-click a field in the list and choose Add as Slicer. Then, in the PowerPivot tab (figure 9), you can click Align Vertically, or Align Horizontally.

You can no longer create or edit a measure…um, a calculated field by right-clicking a table name in the field list. You create and manage calculated fields through the Calculated Fields split-button in the PowerPivot tab (see figure 10). KPIs are managed in a similar fashion, using the KPIs button shown in figure 10. You can now create a KPI without having to select a calculated field first.

Calculated Field Options

Figure 10 – Create and exit calculated fields

Perspectives, unfortunately, no longer appear in the field list of Data Model based PivotTables and PivotCharts. You can still view perspectives in the PowerPivot Window’s Diagram view, however. If the Excel model is exported to an Analysis Services Tabular server, an Excel user can connect to the model on the server and the perspectives will appear in the field list of a PivotTable.

In figure 14 of Part I, the field list task pane shown for a report based on the Data Model does not have a metadata search box, like the one in the Excel 2010 PowerPivot field list task pane. However, there is a Find button in Home tab of the PowerPivot 2013 window. Like PowerPivot 2010, metadata searches are limited to table and field names.

The PowerPivot for Excel 2010 Ribbon tab includes a Relationship Detection button. The button is a toggle that turns automatic relationship detection on and off. As you see in figure 9, the Relationship Detection button is absent in PowerPivot 2013. Since relationships can be created in the Excel UI independently of PowerPivot, relationship detection logic  is now built into the Data Model. If you drag a field from a PivotTable field list to the Values area of the PivotTable, and the Data Model detects that there is an unresolved relationship between that field and other fields present in the PivotTable, a message appears on the field list task pane, as shown in figure 11.

Relationship detection

Figure 11 – Data Model relationship detection

You can click CREATE to create a relationship, or click the close button (x) to dismiss the message. The message disappears automatically if you remove the “offending” field from the PivotTable. There is no way to turn off automatic relationship detection altogether.

Other changes

We didn’t expect to see any changes in DAX, but as it turns out, there is one new function. The new function is DIVIDE. See figures 12 for a self-explanatory description of the new function.

Divide Function

Figure 12 – DIVIDE function

Some time ago Rob wrote an article about importing a date calendar from the Azure Data Market. In the article, he lamented in one of the import steps: “OK, now is where things get choppy, because frankly, the DataMarket site itself has a terrible user interface.” In PowerPivot 2013, retrieving data from the Azure Marketplace is much improved.

The first major change is that the dialog box in step 1 of the Table Import Wizard has been replaced with a window into the marketplace, as shown in figure 13.

Azure Marketplace 1

Figure 13 – Azure Marketplace Table Import Wizard step 1

In the window, you sign in (or sign up for a new account if you don’t have one). If you have already subscribed to the data feed that you want to import, click the My Data link and select the feed. If you have not yet subscribed to the feed that you need, you can type an appropriate search string to display a list of feeds that match the search string. After you subscribe to and select a feed, a default table will be displayed in the window, as we show in figure 14.

Azure Marketplace 2

Figure 14 – Data feed table display

Note that in the window of figure 14, filter arrows are placed in the header of each column! In the figure, we show the filter box for the year key, with a YearKey value greater than or equal to 2008.

Scrolling down to the bottom of the window shows a list of all the tables that you can select for display. See figure 15.

Azure Marketplace 3

Figure 15 – List of tables for selecting and pre-filtering

After you complete selecting and filtering tables, click SELECT QUERY. The rest of the import wizard is identical to that of the corresponding PowerPivot 2010 wizard. You import the tables into PowerPivot without having to paste a service root URL and account key into a dialog box! Alas, the Azure Marketplace import from the Excel UI uses a variation of the PowerPivot 2010 Azure DataMarket dialog box in step 1 of the Table Import Wizard.

In figure 13, you will notice that the Azure DataMarket is rebranded as the Azure Marketplace. The name change is reflected in the import wizards of both Excel 2013 and PowerPivot 2013.

Should PowerPivot be an Integral Part of Excel?

Given that Microsoft has integrated xVelocity into Excel, should PowerPivot have been integrated also, i.e. not be an add-in? I am asked this question occasionally. The answer is no, PowerPivot should remain as an add-in. Excel is a mature product that ships as part of a mature application suite (Office). A new version of Office appears roughly every three years. This interval is fine for a mature suite with virtually no competition. Furthermore, Microsoft does not add new functionality in Office service packs, i.e. functionality that would require modifications to the UI or object model of an Office application. The BI market, on the other hand, is still growing rapidly, and is highly competitive. Three years is an eternity for a product upgrade in such an environment. Features like writeback, dimension measures (calculated items in Excel-speak), new DAX functions, and so on, would have to wait until 2016, if the PowerPivot functionality were built into Excel.

As an add-in, however, PowerPivot need not be tied to the development cycle of Office. We  can expect to see new features in the SQL Server 2012 R2 timeframe – sometime in 2014, or about two years before Office 2016 ships. To support the additional functionality exposed in a new version of PowerPivot, the xVelocity core that resides inside Excel will have to be updated. This update can be done in a service pack, as there would be no impact on the Excel UI or object model.

This Post Has 6 Comments

  1. I need calculated items in PowerPivot please, what are dimension measures? Say I have 2 tables imported as 1 via union all but I have them labeled as a column Source, values A and B. I want a column A-B so I can see by region what the differences are, but I can’t figure it out.

  2. Was able to get Excel 2010 and Excel 2013 both running PowerPivot at the same time without resorting to VMs. Using the 64-bit editions of Office.

  3. There is “Search” bar appearing in Field List displayed in Excel 2010 PowerPivot but it is not appearing Excel 2013. Have MS guys removed this in Excel 2013 version?

  4. I am unable to see Figure 14 with option to show YearKey ‘greater than or equals to’, I am only able to filter by ‘equals to’ with no other selection given. In that case, any work around? I would like to have dates for Year 2013-2015 but unable to retrieve it.

Leave a Comment or Question