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).
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.
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.
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.
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:
- You can work with significantly more data – hundreds of millions of rows (due the the compression algorithms used in the Data Model).
- 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.
- PivotCharts that you create with the Data Model do not require a corresponding PivotTable.
- As previously mentioned, the Data Model allows you to create Power View reports.
- 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.