Project Botticelli

Introduction to PowerPivot for SQL Server 2012 Purchase this course

12 March 2012 · 3 comments · 6933 views

PowerPivot Fundamentals & Newest Features

The second module in this online training course, offers a 1 hour long introduction to PowerPivot for SQL Server 2012, sometimes referred to as PowerPivot “2”. This time, we introduce PowerPivot from scratch, so if you are new to this technology, you will learn how to work and how to create PowerPivot workbooks from your own data, or how to mash-up publicly available data.

Following this, we introduce PowerPivot for SharePoint, including the enterprise manageability features, such as the Workbook Activity Chart (sometimes called the PowerPivot bubble chart). After introducing Data Analysis Expressions (DAX), we focus on the newest features, introduced in SQL Server 2012 version of this tool: Diagram View, Multiple Relationships, Hierarchies, Measure Grid and KPIs, Perspectives, and integration with BI Semantic Model.

This module is of importance to those of you who need to model tabular data for analytics, or who simply want to explore a data set, before making a decision if it is worthwhile analysing it further. Also, if you plan on learning about BI Semantic Model later in this series, you need to know enough about PowerPivot, as Tabular Mode of SSAS, and BI Semantic Model, make extensive use of the new PowerPivot features, naturally extending them for enterprise needs.

This video is presented as a series of slides, graphics, discussions, and 15 demos, recorded in high-resolution, so that you can follow the steps yourself.
To make your learning more interactive, you can jump to a chapter that interests you the most by using the chapter markers shown in the right-hand sidebar. Ideally, however, you would follow this module from start to finish, to build a good foundation for learning from the remaining modules in this course.

Log in or purchase access below to the premium version of this content.

Purchase A Full Access Subscription

Subscription Best Value


Access all content for 1 year.
  Purchase on Tecflix

  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.


joseusul · 17 October 2012

Hi Rafal,

I would like to comment a question about the the way powerpivot engine (vertipaq or xvelocity now) works.

We are preparing a document to one customer about the new capabilities of PowerPivot and SQL Server 2012.

This customer, who apparently is a Qlikview supporter, has recently view a presentation comparing the difference between the traditional “drill-down” BI experience, and the associative experience of QlikView.

This is the link to the video:

According to the video the way of navigate or drill-down between dimensions are much more flexible than the traditional OLAP way.

But my point is, Do you understand what is the difference between they called “associative logic” or “associative data modeling” and what powerpivot does?

I mean, in my opinion they are typical relationships behind the scenes of the front-ends tools (qlikview browser, or Excel pivottable) and slicers to select or deselect members of the different dimensions, aren’t they?

Thanks again for your time, regards


Rafal Lukawiecki · 17 October 2012

Dear José, thanks for asking this question, and for sharing the link to the Qlik video. Indeed, you are correct, that the way of navigating data, in order to explore it, that QlikView calls associative logic is similar to navigating data using Excel slicers, and having the aggregates calculated in real time by the xVelocity engine: both tools allow you the freedom not to have to follow an OLAP hierarchy and to be able to see, live, the underlying relationships between attributes, or dimension members related through facts, or dimension relationships in a snowflake-like schema. Before xVelocity (PowerPivot Vertipaq) arrived, Microsoft analytics relied on precalculated aggregates using MOLAP, (or live ROLAP) but with the restriction of a fixed hierarchy. This was very good for performance (MOLAP) and for having control over what aggregates should be available to query, but it was restrictive to the user, leading to a lack of agility of the solution. Bear in mind, however, that MOLAP can still outperform real-time, in-memory aggregations for certain types of complex data sets, especially in the financial world.

I am glad you asked about the term associative, as there are several concepts, which share a similar name. Associative Logic is a QlikView-patented technology. Association Rules is a generic, industry-standard data mining technique, that is often used for associative data modelling, which relies on a concept of itemsets (or baskets) and rules that describe, by means of probabilities, the likelihood of certain combinations of items existing in a basket, given other items’ presence. This technique, part of SQL Server Data Mining, can be used for Market Basket Analysis, demographic analysis, and trend analysis. It works by detecting which individual items are related to which other items by analysis of all itemsets. This can be a computationally complex task for larger data sets, and SQL Server comes with a Microsoft-patented AR Algorithm that does a good job of putting it within reach of many users, as well as it comes with a visualisation tool, that works in Excel and Visio.

On the other hand, slicers in Excel, or associative logic in QlikView require you to already have known relationships between items. Those have to be present in the underlying data, by means of rows that relate the items to each other, or by means of key-based relationships between tables. The tools do a great job of helping the user navigate and explore such an existing set of relationships, with the magic of in-memory speed, live calculation of aggregates and, in the case of BISM, your own DAX function formulas. In other words, Association Rules discovers the patterns, while the  other two techniques help you explore already known patterns.

joseusul · 19 October 2012

Hi Rafal,

Thanks a lot! Really impressive your explanation.

Only to clarify two important points you have commented:

- I can see the use of associative data modelling algorithms for Market Basket Analysis but when you talk about apply to demographic and trend analysis I can’t imagine the scenario. What do you would predict with this information?

It sounds really fascinating but I would need a simple example to imagine how to apply it.

- And finally, when you say using Excel or Visio to apply the Microsoft-patented AR Algorithm are you talking about the The Shopping Basket Analysis Tool from the Excel data mining add-ins?

Thanks in advance. Kind regards.