Advanced Analysis and Power PivotHenk Vlootman Excel MVP

Ashvini Sharma Program ManagerExcel

Agenda

Power Pivot (Bulk of this session)

Analyzing Uncertain Data

Power Pivot

Analyzing External Data (before Power Pivot)

DATA

Calculations

What is Power Pivot?DATADAT

A

DATA

DATA

Power Pivot is the bridge between Databases and Excel

Calculations

RelationshipsCalculations

Excel before 2010 Excel w/ Power Pivot

Basic…… handles max 1,048,576 rows in one sheet. … easily handles tables with millions of rows.… works primary with one “table”. … handles multiple tables.… loose formula based lookups across ranges. … uses relationships between tables.… has PivotTables. … extends PivotTables (as Henk says, “PivotTable plus”).

And…… uses cell (row, column) storage. … uses a column-based structure.… allows manually creating data. … imports/links tables, no cell value editing.

Great spreadsheet application … with very powerful data storage and analysis engine

Power Pivot is a BGCPower Pivot...… gives the manager much faster insight in his -often critical- KPI’s.… melds the business knowledge of managers with his business data.

What does Power Pivot means to your business?… your information is available at any moment, so your decisions are made faster.… All kind of (external) data are linked to your business data, so you will be more creative.… your personal desktop model is easily ported to enterprise corporate applications.… sharing information will be much more easy and much safer.

(Business Game Changer!)

DemoLaunching Power Pivot

Power Pivot and Data Model

Power Pivot

Data

Excel

Power Pivot and Data Model

Data Model

Power Pivot

Data storage & query engine

User experience

Data

Excel“nativ

e” Excel

Importing data into Data Model

Data ImportYou’ll find (almost all) the import options in the Power Pivot addin, on the Home tab, Get External data section.

You can use “Add to Data Model” to add Excel tables to your data model. However, preferred to use the import section within Power Pivot.

You can add all kind of database flavors in your model. You find it in one the three sections, roughly separated by on-prem databases, web-based databases and other sources. The two important import sources for Excel users are Excel and text files. Both flavors are found in the section “From Other Sources”:.

Best Practices for data importHeaders in databases (also those from Excel tables!) should stay the same. If the header’s name is different from the original import Power Pivot cannot refresh the associated table..

On the Windows platform, files in-use get a file lock. This locks prevent Power Pivot to refresh the source. If refresh fails, first check if Excel files are closed. Ensure the correct data type is used by Power Pivot. Text files (also called flat files) do not come with a data type, so Power Pivot needs to guess. Sometimes Power Pivot makes the wrong choice. Use a schema.ini file to tell Power Pivot what the data type of the columns in your text files are.

Headers

Relationships

Basic principles of relationshipsA Power Pivot model is built around two types of tables:• Dimension tables contain information about the

descriptors/“nouns” in your business – customers, products, teams, ...

• Fact tables contain measurements such as Quantity sold, Tax paid, etc.

• Relationships connect these tables together.

Dimension table

Fact table

Creating Relationships between tables

Foreign Key column

Primary Key Column

Relationships are made by linking two “Key” columns across tables.

Power Pivot currently supports one-to-many relations.

All values in Key column in the Dimension table (“Primary Key”) are unique.

Values in Key column for a relationship in Fact table (“Foreign Key”) can repeat.

DemoRelationships in Power Pivot

Best Practices for TablesTables are tables, but not quite in Power Pivot. All tables look the same, but they have different functions in your model.You can distinguish at least 4 kinds of tables.

Two kinds of tables have been mentioned. Those tables are the core of your model.• Fact tables (start this table name with the letter ’f’).• Dim tables (start this table name with the letter ‘d’).

Measure tables (start this table name with the letter ‘m’).Measure tables are use to separate measure calculations from original fact tables. They don’t have data connections nor relationships with other tables. This is the table to add a column for Comments.

Variable tables (start this table with the letter ‘v’).Variable tables are used by Fact tables, for instants to expand basic data over a series of time rows. It is basically a Dim table, but not used for filtering. By using the LOOKUPVALUE DAX, there is no relation needed. The demo at the end will show you the use of different tables ‘How to calculate values in the future’.

You can also use this sort of tables if you need a in-between table for handling your slicers.

Best Practices…Most of time you got access to Fact tables, but Dim tables are harder to find. Luckily you have Excel to make those!If you need to make multiple Dim tables, make them in one Excel model. Make text files from each table, so you can’t be surprised by a file lock. In that way each Dim has his own connection, so you have more overview and the connections are less vulnerable.If your Dim primary key column doesn’t have unique values, the relation will not be made.Use the DAX (we will handle DAX a little later) DISTINCTCOUNT to check whether your primary column is unique.

Sometimes you can solve the problem by using the Table properties and filter-out the double values, but most of the time it is back to the source.Beware of the data type of the key columns. If they are not the same, again no relation.

Defining Calculations

Introducing DAX (“Data Analysis eXpressions”)DAX is the calc language for Data Model

If you know Excel function language, DAX should look familiar!

Designed to work on very large volumes of data

Understands tables, relationships, columns.

Calculation are designed to be defined once, and reused…

Calculated ColumnsA Calculated Column is evaluated for each row in a table

It’s recalculated when underlying data changes.

As a best practice, try to move the calculation as “upstream” (in the data import) if possible

DemoCalculated Columns

MeasuresValues can be calculated dynamically, based on use (“Query Context”)

DemoMeasures

Best Practices for Calculated ColumnsThere are two different species of (Calculated) Column names: • If the column is used in PivotTables as filter element, use a

understandable name, like Month. • If not, use the table name in the name. A name like

fSalesDate gives you insight in which table it comes and what the column contains.

If refresh is not possible anymore, deleting and re-importing the table is the only options. Measures are placed in your measure table, but you will need to rebuild your Calculated Columns in that table.

Best Practices for MeasuresYou use instead “:=“ to separate the measure name and its DAX expression.

There are also two different species of measures names: • If you use the measure in a PivotTable, choose a

understandable name, for example, Sales. • If the measure is only an in-between calculation for another

measure, use the name that gives you the most information.

In a measure, don’t use the name of the table. Easier to different from the name of a Calculated Column.

Best practices for measuresDo I nest my DAX?In Excel it is common use to nest functions within functions. So in Excel you do something like =IF and in the argument VALUE_IF_TRUE, you nest the function SUM. In Power Pivot measures itself can be used in other measures. So if the measure TotalSales (SUM([fSales[Sales]) contains the total of the sales, you can use the name in other DAX expressions, like YTDSales:= TOTALYTD([TotalSales]….

Where do you place your calculations?You want your measures in one measure sheet with no data connection. If the data connection fails, your precious made measures are safe. You can delete the table that is corrupt, make a new connection, restore relations and calculated Columns and you are good to go.

Using the Data Model

“PivotTable plus”The real power of the Power Pivot lies in its output. That output in Excel is most of the time a PivotTable. The difference with the “normal” Excel PivotTable is its ability to calculate any DAX expression within the filter context of the PivotTable!

Compare this with the usual 11 calculations functions of an Excel PivotTable.

Rule of thumb (and stick to that!):You use Dim tables columns from for the filtering section (Row, Column, filter sections, slicers and timelines).You use Measures for calculations in the ∑ value section.

Timelines and SlicersSlicers and timelines are the same as columns placed in the filter section of the PivotTable. And that’s where the similarity stops!

Slicers and timelines makes it easy to intuitively work with dashboards.

Slicers can handle multiple selections, offering a truly dynamic dashboards.Slicers work together, which makes it a very dynamic dashboard.Slicers can be connected to multiple PivotTables or PivotCharts, that is the ultimate dynamic dashboard.

Best practices on “PivotTable plus”Rule of thumb (and it is an important one):Use Dim tables columns from for the filtering section (Row, Column, filter sections, slicers and timelines).Use Measures for calculations in the ∑ value section.

It is better not to use the Excel drag method to the make calculations in the ∑ value section.

PivotTables have two directions in which the can expand: to the right and to the bottom. You can use space above and on the left of the pivot table to add information, like slicers and timelines.

Best practices on Timelines and SlicersTo be sure of the results you expected, use columns from Dim tables in slicers and timelines!

Use for timelines only the date column from the table you have marked as Date table.

The more slicers and timelines, the more slower your response time will be, because Power Pivot need to calculated the filter settings.

If your slicer / timeline doesn’t work, check the Report connections and the relations between the tables.

DemoMaking a professional looking dashboard

Best Practices. Managing metadata

You get all columns and measures in your PivotTable Field list. If you do not have the discipline to hide unused items, like columns, measures or tables, you will be drowned by the sheer volume of items.

Hide all items you don’t use in the PivotTable. You do that in Power Pivot.

DemoCalculations in time

DemoBuilding an P&L hierarchy tree

Sharing & Upsizing Power Pivot

“Publishing” workbooks with Power PivotEverything is in the workbook, so same sharing as before.. • Email, file shares,• SharePoint 2013 (with Power Pivot for

SharePoint),• SharePoint Online with O365.SharePoint provides scheduled data refreshes & friendlier views on top of expected document management features.

Can also import into Power BI Service (another session)

Power Pivot has a growth pathThere is a huge difference between a personal Desktop model versus a corporate Server application.

When data security issues are involved, consider the step towards SQL Power Pivot.Excel Power Pivot keeps the extracted data from databases. That raw data is thus available without the safety boundaries of the server.SQL Power Pivot dashboards only passes the aggregation data to the Excel dashboard, so the raw data stays on the server.There are structural differences between the two Power Pivot versions, but not in the way you work with it!If you want to use this road, prototype in Excel and, when you are satisfied, port it to SQL to make it a corporate application.

Power Pivot LinksDAX Whitepaper and samples http://www.microsoft.com/en-gb/download/details.aspx?id=28572

EdX course “Analyzing and Visualizing Data with Excel” by Dany Hoter

Other good resources:www.Power Pivotpro.com Praktijkboek Power Pivot in Excel by Henk in Dutch“Power Pivot and Power BI: The Excel User's Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016 by Rob Collie, Avichal Singh“The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI” by Alberto Ferrari, Marco Russo

Analyzing Uncertain Data

The Flaw of AveragesPlans Based on Average AssumptionsAre Wrong on Average

A classic example involves the statistician who drowns in a river that is on average 3 feet deep.

But the Flaw of Averages also explains why so many things are: ● Behind Schedule● Below Projection and● Beyond Budget

• Communicating uncertainties

• Calculating with uncertainties, across people & tools

• Credibility of Uncertain Estimates

Needs when dealing with uncertain data

The SIPmath™ Standard from ProbabilityManagement.org cures the Flaw of Averages by representing uncertainties as arrays of simulated trials called SIPs.

The Data Table function in native Excel is powerful enough to run thousands of trials interactively before your finger leaves the <Enter> key.

Models, Free Tools and Videos are available at www.ProbabilityManagement.org

The SIPmath™ 2.0 Standard Stores Arrays of Simulated Outcomes Plus Meta Data

• Actionable• Additive• Auditable• Agnostic

ProbabilityManagement.org is a 501(c)(3) nonprofit devoted to rethinking uncertainty

Bring your data to life at the inaugural Microsoft Data Insights Summit, a two-day event for business analysts to connect with each other and with industry experts. Discover best practices, new tools, and new ways to get things done.

Visit DataInsightsSummit.com to register.

Questions?

Thank you.

© 2015 Microsoft Corporation. All rights reserved.