Benefits of Pivot by Data Model

Contents

When building a pivot table in Excel, in the very first dialog box, where we are asked to set the initial range and choose a place to insert the pivot table, there is an inconspicuous but very important checkbox below – Add this data to the Data Model (Add this data to Data Model) and, a little higher, the switch Use this book’s data model (Use Data Model of this workbook):

Unfortunately, many users who have been familiar with pivot tables for a long time and successfully use them in their work sometimes do not really understand the meaning of these options and never use them. And in vain. After all, creating a pivot table for the Data Model gives us several very important advantages compared to the classic Excel pivot table.

However, before considering these “buns” up close, let’s first understand what, in fact, this Data Model is?

What is a Data Model

Data Model (abbreviated as MD or DM = Data Model) is a special area inside an Excel file where you can store tabular data – one or more tables linked, if desired, to each other. In fact, this is a small database (OLAP cube) embedded inside an Excel workbook. Compared to the classic storage of data in the form of regular (or smart) tables on sheets of Excel itself, the Data Model has several significant advantages:

Tables can be up to 2 billion lines, and an Excel sheet can fit a little more than 1 million.
Despite the gigantic size, the processing of such tables (filtering, sorting, calculations on them, building summary, etc.) is performed very fast Much faster than Excel itself.
With the data in the Model, you can perform additional (if desired, very complex) calculations using built-in DAX language.
All information loaded into the Data Model is very strongly compressed using a special built-in archiver and rather moderately increases the size of the original Excel file.

The Model is managed and calculated by a special add-in built into Microsoft Excel – PowerPivotabout which I have already written. To enable it, on the tab developer click COM add-ins (Developer — COM Add-ins) and check the appropriate box:

If tabs developer (Developer)you can’t see it on the ribbon, you can turn it on through File – Options – Ribbon Setup (File — Options — Customize Ribbon). If in the window shown above in the list of COM add-ins you do not have Power Pivot, then it is not included in your version of Microsoft Office 🙁

On the Power Pivot tab that appears, there will be a large light green button Management (Manage), clicking on which will open the Power Pivot window on top of Excel, where we will see the contents of the Data Model of the current book:

An important note along the way: an Excel workbook can only contain one Data Model.

Load tables into the Data Model

To load data into the Model, first we turn the table into a dynamic “smart” keyboard shortcut Ctrl+T and give it a friendly name on the tab Constructor (Design). This is a required step.

Then you can use any of the three methods to choose from:

Press the button Add to Model (Add to Data Model) tab PowerPivot tab Home (Home).
Choosing teams Insert – PivotTable (Insert — Pivot Table) and turn on the checkbox Add this data to the Data Model (Add this data to Data Model). In this case, according to the data loaded into the Model, a pivot table is also immediately built.
On the Advanced tab Data (Date) click on the button From Table/Range (From Table/Range)to load our table into the Power Query editor. This path is the longest, but, if desired, here you can perform additional data cleaning, editing and all kinds of transformations, in which Power Query is very strong.
Then the combed data is uploaded to the Model by the command Home — Close and Load — Close and Load in… (Home — Close&Load — Close&Load to…). In the window that opens, select the option Just create a connection (Only create connection) and, most importantly, put a tick Add this data to the Data Model (Add this data to Data Model).

We build a summary of the Data Model

To build a summary Data Model, you can use any of three approaches:

Press button summary table (Pivot Table) in the Power Pivot window.
Select commands in Excel Insert – PivotTable and switch to mode Use this book’s data model (Insert — Pivot Table — Use this workbook’s Data Model).
Choosing teams Insert – PivotTable (Insert — Pivot Table) and turn on the checkbox Add this data to the Data Model (Add this data to Data Model). The current “smart” table will be loaded into the Model and a summary table will be built for the entire Model.

Now that we have figured out how to load data into the Data Model and build a summary on it, let’s explore the benefits and advantages that this gives us.

Benefit 1: Relationships between tables without using formulas

A regular summary can only be built using data from one source table. If you have several of them, for example, sales, price list, customer directory, register of contracts, etc., then you will first have to collect data from all tables into one using functions such as VLOOKUP (VLOOKUP), INDEX (INDEX), MORE EXPOSED (MATCH), SUMMESLIMN (SUMIFS) and the like. This is long, tedious and drives your Excel into a “thought” with a large amount of data.

In the case of a summary of the Data Model, everything is much simpler. It is enough to set up relationships between tables once in the Power Pivot window – and it’s done. To do this, on the tab PowerPivot press the button Management (Manage) and then in the window that appears – the button Chart View (Diagram View). It remains to drag common (key) column names (fields) between tables to create links:

After that, in the summary for the Data Model, you can throw in the summary area (rows, columns, filters, values) any fields from any related tables – everything will be linked and calculated automatically:

Benefit 2: Count unique values

A regular pivot table gives us the opportunity to choose one of several built-in calculation functions: sum, average, count, minimum, maximum, etc. In the Data Model summary, a very useful function is added to this standard list to count the number of unique (non-repeating values). With its help, for example, you can easily count the number of unique items of goods (range) that we sell in each city.

Right-click on the field – command Value field options and on the tab Operation Choose Number of different elements (Distinct count):

Benefit 3: Custom DAX Formulas

Sometimes you have to perform various additional calculations in pivot tables. In regular summaries, this is done using calculated fields and objects, while the data model summary uses measures in a special DAX language (DAX = Data Analysis Expressions).

To create a measure, select on the tab PowerPivot Command Measures – Create Measure (Measures — New measure) or just right-click on the table in the Pivot Fields list and select Add measure (Add measure) in the context menu:

In the window that opens, set:

Table namewhere the created measure will be stored.
Measure name – any name you understand for the new field.
Description – optional.
Formula – the most important thing, because here we either manually enter, or click on the button fx and select a DAX function from the list, which should calculate the result when we then throw our measure into the Values area.
In the lower part of the window, you can immediately set the number format for the measure in the list Category.

The DAX language is not always easy to understand because operates not with individual values, but with entire columns and tables, i.e. requires some restructuring of thinking after the classic Excel formulas. However, it is worth it, because the power of its capabilities in processing large amounts of data is difficult to overestimate.

Benefit 4: Custom field hierarchies

Often, when creating standard reports, you have to throw the same combinations of fields into pivot tables in a given sequence, for example Year-Quarter-Month-Day, or Category-Product, or Country-City-Client etc. In the Data Model summary, this problem is easily solved by creating your own hierarchies — custom field sets.

In the Power Pivot window, switch to chart mode with the button Chart View tab Home (Home — Diagram View), select with Ctrl desired fields and right-click on them. The context menu will contain the command Create Hierarchy (Create hierarchy):

The created hierarchy can be renamed and dragged into it with the mouse the required fields, so that later in one movement they can be thrown into the summary:

Benefit 5: Custom stencils

Continuing the idea of the previous paragraph, in the summary of the Data Model, you can also create your own sets of elements for each field. For example, from the entire list of cities, you can easily make a set of only those that are in your area of responsibility. Or collect only your customers, your goods, etc. into a special set.

To do this, on the tab Pivot table analysis in the drop down list Fields, Items, and Sets there are corresponding commands (Analyze — Fields, Items & Sets — Create set based on row/column items):

In the window that opens, you can selectively remove, add or change the position of any elements and save the resulting set under a new name:

All created sets will be displayed in the PivotTable Fields panel in a separate folder, from where they can be freely dragged to the rows and columns areas of any new PivotTable:

Benefit 6: Selectively Hide Tables and Columns

Although this is a small, but very pleasant advantage in some cases. By right-clicking on the field name or on the table tab in the Power Pivot window, you can select the command Hide from Client Toolkit (Hide from Client Tools):

The hidden column or table will disappear from the PivotTable Field List pane. It is very convenient if you need to hide from the user some auxiliary columns (for example, calculated or columns with key values for creating relationships) or even entire tables.

Benefit 7. Advanced drill-down

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

In the Data Model summary, this handy tool works more subtly. By standing on any cell with the result that interests us, you can click on the icon with a magnifying glass that pops up next to it (it is called Express Trends) and then select any field you are interested in in any related table:

After that, the current value (Model = Explorer) will go into the filter area, and the summary will be built by offices:

Of course, such a procedure can be repeated many times, consistently delving into your data in the direction you are interested in.

Benefit 8: Convert Pivot to Cube Functions

If you select any cell in the summary for the Data Model and then select on the tab Pivot table analysis Command OLAP Tools – Convert to Formulas (Analyze — OLAP Tools — Convert to formulas), then the entire summary will be automatically converted to formulas. Now the field values in the row-column area and the results in the value area will be retrieved from the Data Model using the special cube functions: CUBEVALUE and CUBEMEMBER:

Technically, this means that now we are not dealing with a summary, but with several cells with formulas, i.e. we can easily do any transformations with our report that are not available in the summary, for example, insert new rows or columns into the middle of the report, do any additional calculations inside the summary, arrange them in any desired way, etc.

At the same time, the connection with the source data, of course, remains and in the future these formulas will be updated when the sources change. The beauty!

Plan-fact analysis in a pivot table with Power Pivot and Power Query
Pivot table with multiline header
Create a database in Excel using Power Pivot