Quick Intro From Rob
First of all, it’s good to have Colin back on the blog!
Second, Colin underestimates, I think, how far ahead of the curve he typically is, so as a quick prelude to his post below, I thought I’d share a few quick points:
- Excel 2013 should be releasing sometime in the next month or two
- Much of PowerPivot is now built-in to Excel, rather than being a separate addin!
- Those built in features are being called the “data model,” and this post on the official Excel blog provides an introduction.
- The rest of PowerPivot’s capabilities, the ones that are NOT built in to Excel 2013, are still included as an addin that gets installed when you install 2013 – no more separate download!
OK, now on to Colin’s post
Guest post by Colin Banfield
By now, most readers are aware of the new Data Model object in Excel 2013. But what is the Data Model, and how is it created? What can we do with the Model after it is created? Where does PowerPivot fit in? We will address these, and other questions in this two-part post.
The Data Model is a new Excel object that you use to create PivotTables, PivotCharts, and Power View reports. The Data Model uses a built-in version of xVelocity, which is an in-memory, column-based version of engine that powers Analysis Services. xVelocity stores data that you import in-memory (compressed), and calculates implicit measures (created by dragging a a table field to the values area of a PivotTable) and explicit measures (created using DAX functions). We can create the Data Model using one or more tables. Generally, we create relationships between tables in the Data Model, but there is no absolute requirement to do so. Another key characteristic of the Data Model is it’s ability to consume large amounts of data.
Excel 2013 is, in my view, the first version of Excel that that has fully transitioned beyond its spreadsheet roots (while still maintaining these roots) to become a BI tool that can compete with the likes of Tableau, QlikView, SpotFire, etc. Two key additions to the product has facilitated this transition: the Data Model, and the Power View add-in. Power View is a visual analytic tool – an very important component in any serious BI application.
Excel 2013 can act as a central hub for tabular BI development. For example, we can develop simple or complex analytical models that we can store centrally on an Analysis Services server (and extended with partitions and roles), or that we can store centrally on a SharePoint server. Of course, we could already do these things in Excel 2010 and PowerPivot. However, for basic models we create in Excel 2013, we don’t need PowerPivot at all. Also, Excel 2013 allows us build Power View reports that can be stored centrally in SharePoint. Figure 1 shows how the BI components are related in Excel 2010, and figure 2 shows how they are related in Excel 2013 (with changes highlighted in red).
Figure 1 – Excel 2010 BI Architecture
Figure 2 – Excel 2013 BI Architecture
The main changes in figure 2 compared to figure 1 are the built-in xVelocity engine (the Data Model) and the Power View add-in.
Creating the Data Model
We need to revisit some of the questions posed at the beginning of this post. The first one being; how do we create a Data Model?
There isn’t a global command in Excel 2013 for creating a Data Model. There are various dialogs in Excel that allow you to add data to the Data Model by setting a check box in the dialog. For a given workbook, the first time you use a dialog with this check box set, Excel creates the Data Model. We will first look at the Import Data dialog box, as this is the most common way of adding data to the Data Model.
Let’s first see what happens when we import a single table from an Access database. Figure 3 shows a single table selected from a list.
Figure 3 – Access database single table select
The Select Table dialog box in figure 3 is typical of the dialog box displayed for other data sources. Figure 4 shows the Import Data dialog box that Excel displays after you click OK.
Figure 4 – Access single table Import Data dialog box
A new element in the Excel 2013 Import Data dialog box is the check box labeled Add this data to the Data Model. If you don’t check this box, you can’t create a Power View report, but you can create Tables, PivotTables, or PivotCharts. If you create a PivotTable report, Excel will create a classical PivotTable. In this two-part post, we define a classical PivotTable (or PivotChart) as one that is limited to using a single table, having limited data handling, and having limited calculated field functionality. In other words, a classical PivotTable/PivotChart is one that we have been creating in Excel for generations. Building a classical PivotTable/PivotChart allows you share your reports with anyone using an older version of Excel. If you create a PivotChart report, Excel will create a classical PivotTable, and a PivotChart linked to the PivotTable. In summary, if you don’t select the check box, the functionality is exactly the same as importing data in earlier versions of Excel.
Now let’s see what happens after we select more than one Access table to import.
Figure 5 – Access database multiple table select
In figure 5, we’ve highlighted the new option available in the Excel 2013 Select Table dialog boxes – i.e. the check box labeled Enable selection of multiple tables. Figure 6 shows the Import Data dialog box that Excel displays after you click OK.
Figure 6 – Access multiple table Import Data dialog box
As you can see in the figure, when you select more than one table to import, the check box is disabled, and the tables are added to the Data Model. This behavior is logical because reports based on classical PivotTables can use only a single table. Moreover, because the imported data is added to the Data Model, you can create a Power View report.
It’s interesting to understand how some of the other report options work in the multi-table import scenario:
- If you choose Table, Excel creates a separate worksheet to hold each table, and also independently adds the tables to the Data Model. If you subsequently delete the table worksheets, the table data (defined by the connection) will still be available in the Data Model.
- If you choose PivotChart, Excel creates the PivotChart without a PivotTable. Hallelujah! A Data Model PivotChart is not umbilically connected to any PivotTable.
- If you choose Only Create Connection, Excel imports the tables into the Data Model, but does not create any specific report (In earlier versions of Excel, this option is available only when you imported data from Analysis Services). The option is useful if, say, you want to use cube functions without creating a PivotTable, or if you plan to import data from multiple sources before you create a specific type of report (much like importing data in the PowerPivot window). After you import data into the Data Model using the create connection option, the only way to view the tables is via the PowerPivot 2013 window.
What’s the name of the Data Model internally? There are a few ways to determine the name. We show the first in figure 7.
Figure 7 – Internal Data Model name
Figure 7 shows the Workbook Connections dialog box that Excel displays after you select Data—>Connections. The internal name for the Data Model is ThisWorkbookDataModel, as you see in the figure. Note that when you select ThisWorkbookDataModel, the option buttons Remove and Properties are not available. Note also that the external connection containing the data we added to the Data Model is listed as ContosoSales.
Let’s examine the internal Data Model name for a minute – ThisWorkbookDataModel. The name implies that a Workbook contains only one data model. For PowerPivot users, this should not be surprising, since in the Excel 2010 PowerPivot add-in, we can only use one instance of xVelocity in a workbook.
Another way to see the name is through formula auto-complete, using a cube function. In figure 8, for example, we can see the name displayed after typing the opening quotation for the connection argument.
Figure 8 – Data Model name listed for the connection argument
A third method is through VBA. Typing either of the following lines in the VBA IDE Immediate window and pressing Enter will display the name:
Thus far, one major change that we’ve seen to importing data in Excel 2013 is the ability to import multiple tables, just as we can do in PowerPivot. Another change is the support for new data sources. Figure 9 shows the From Other Sources drop-down list, with the new sources highlighted.
Figure 9 – New data sources available in Excel 2013
You will recognize that we can already import from these sources in PowerPivot. Let’s see what happens when we select a table from an OData source.
Figure 10 – Single table selection from Odata source
In figure 10, we’ve selected a single table – Company Sales. After we click Finish, Excel displays the Import Data dialog box shown in figure 11.
Figure 11 – OData single table Import Data dialog box
Although we selected only one OData table to import, it’s added to the Data Model automatically after we click OK. In other words, we cannot create a classical PivotTable report directly from an OData source, even if we import a single table. The same behavior applies for data you import from the Azure Marketplace.
Let’s now import data using the pre-stone-age Microsoft Query. After crafting your query, you return a single table to Excel. The Import Data dialog box is shown in figure 12.
Figure 12 – MS Query Import Data dialog box
The figure shows that you can’t directly add an MS Query table to the Data Model.
One very major difference between importing data in Excel 2013 and PowerPivot (all versions), is that in the Excel data import, you cannot pre-filter tables during import. This is unfortunate, since unsophisticated users can end up importing more rows and columns that they need, resulting in performance issues. In most cases, it would be preferable to import data into the Model through the PowerPivot Window.
Adding Data to the Data Model
After creating the Data Model, we can add new data in various ways. The most obvious way is to import new data and select the check box in the Import Data dialog box, if the check box isn’t already selected. We can also add existing data in the workbook to the Data Model (or even create the Data Model from worksheet data, if the Model hasn’t been previously created). We can add worksheet data by simply highlighting a range and selecting Insert—>PivotTable(or PivotChart or Power View Report). In figure 13, we selected a range and selected Insert—>PivotTable.
Figure 13 – Selecting a range to add to the Data Model
In the Create PivotTable dialog box, after selecting Add this data to the Data Model, the range appears as a table in the PivotTable field list. If you haven’t defined a name for the range, Excel uses the generic name “Range.” This process of adding a range to the Data Model is like pasting a range into a new table in PowerPivot. If, instead of a PivotTable, you insert a PivotChart, the Create PivotChart dialog box is similar to the one shown in figure 13. However, if you insert a Power View report, the data is immediately added to the Data Model, because only the Data Model supports Power View reports. With the check box option in the Create PivotTable or Create PivotChart dialog boxes, if you don’t add the data to the Data Model, Excel creates a classical PivotTable, or a classical PivotTable with a PivotChart. One point to note is that if you click a cell within a Table to create a PivotTable, PivotChart, or Power View report, the results are the same as the results described for the highlighted range.
Let’s compare the PivotTable field list that Excel 2013 displays when you create a PivotTable or PivotChart based on the Data Model, and the field list that Excel 2013 displays for a classical PivotTable.
Figure 14 – Data Model PivotTable field list (left figure) and classical PivotTable field list
On the left of figure 14 is the field list of a PivotTable based on the Data Model, and the right is a field list based on a classical PivotTable. We show only the field lists in figure 14 because the drop zones below the field list are identical for both types of PivotTables. For PivotTables based on the Data Model, there are no horizontal and vertical slicer drop zones like those that appear in PivotTables based on the Excel 2010 PowerPivot add-in (these drop zones don’t exist in the classical PivotTable field list task pane either). The visual differences between the two field lists can be clearly seen in the figure. The PivotTable field list based on the Data Model show table names in the field, whereas the classical PivotTable field list shows only field names (since it works with only a single table). The other obvious difference is the presence of the ACTIVE and ALL tabs in the field list task pane based on the Data Model.
All tables that you import into the Data Model are shown in the ALL tab. The tables that appear in the ACTIVE tab depend on how you create a PivotTable or PivotChart. Suppose that you begin with an empty workbook, without the Data Model. You then import some tables from a source, and choose PivotTable Report in the Import Data dialog box (refer to figure 6). The tables are added to the newly created Data Model, and Excel displays the field list task pane for the PivotTable. In this scenario, both ACTIVE and ALL tabs will show all of the tables. Now suppose that you import another table, and add the table to the Data Model (using the create connection only option). The new table will appear in the ALL tab of the PivotTable field list, but does not appear in the ACTIVE tab. You can right-click the new table name in the ALL tab and select Show in Active Tab from the context list (which has only one option). This selection is shown in figure 14. The table will also appear in the ACTIVE tab if you drop a field from the table to one of the drop zones below the field list. Alternatively, you can remove a table from the ACTIVE tab by right-clicking the table name and selecting Remove from Active Tab. Note, however, that this option is disabled if any field in the table is participating in the PivotTable.
If you begin with an empty workbook, and choose Only Create Connection in the Import Data dialog box (with the check box selected), Excel adds the data to the Data Model as before. If you subsequently create a PivotTable or PivotChart based on the Data Model, all of the tables that you imported into the Data Model show in the ALL tab of the PivotTable field list, but none of the tables appear in the ACTIVE tab. You can choose which tables you want to work with my moving each to the ACTIVE tab as we described previously.
A neat feature of the new field list is that when you hover the mouse cursor over the table name, a tooltip displays the name of the connection to which the table belongs.
In Part II, we will continue the discussion by looking at the type of analysis that you can perform with the Data Model, and discuss aspects of PowerPivot 2013.