Data Warehousing and Data Science

8 December 2009


Filed under: Business Intelligence — Vincent Rainardi @ 10:45 pm

PowerPivot is an in-memory OLAP, like TM1 from IBM and QlikView from QlikTech. It is a SQL Server 2008 R2 add-on installed on Excel. Like Analysis Services, using PowerPivot we can create a ‘cube’ in Excel, which we can then slice and dice. The data for this ‘cube’ can be from database tables, Excel files, SSAS cubes or a combination of them. That, I think is the most important feature of PowerPivot: it allows us to combine data from databases, Excel and cubes so that we can analyse the data.

Users opens Excel, fill in a few sheets with data (or import it from database or files), then build the ‘cube’. It looks like the normal PivotTable, but it has slicers, which enable us to ‘cut’ the cube. The ‘cube’ can be published on Sharepoint so other users can ‘browse’ it. It is striking how similar that ‘cube’ to a pivot table. Under the concept of self-service BI, PowerPivot is aimed not for IT department, but for business users (data analysts/power users), e.g. MIS / MI department. It is so important I will repeat it here: PowerPivot is not for BI developers, it is for business users.

The ‘cube’ is called “PowerPivot pivot table”. To contruct a PowerPivot pivot table we need to a) create some tables in Excel, and b) relate those tables. A ‘table’ in Excel, is an area of cells that we mark as table. There are 2 ways of creating a ‘table’ in Excel: a) type it in Excel then ‘format as table’ (it’s in the Home toolbar), or b) import from database, file, or other sources.

I am of the opinion that PowerPivot will increase the demand for SSAS, rather than reducing it. This is because:

a) it encourage users to create OLAP and use it. Then after a few months, there will be a mess: cubes everywhere. And there will be a need for project to pull that scattered mess into a big SSAS cube (or two, or three).

b) because you don’t need to spend 6 months (or a year) to build a data warehouse. If the users have the data in Excel, they can immediately analyze it with PowerPivot. They can even combine it with data from any database. How powerful is that? This will only go to 1 direction: more need for a data warehouse. Imagine a company with a hundred data islands, from New York to Singapore, in the form of PowerPivot tables and charts sitting on users’ Excel files. As sure as eggs is eggs, they have a greater need for a data warehouse. Then naturally, SSAS cube follows. Sitting on top of the DW. Accessed via http from everywhere, from New York to Singapore.

c) because of the limitation of PowerPivot. There is no hierarchy. If you can’t compose a hierarchy, how do you drill down? True, we can create calculated member using DAX, but the capability is very limited. As soon as this limit is reached, there is will be a conversation similar to this: [user to IT]: how can I do this in PowerPivot? [IT to user]: eerrm, you need MDX.

PowerPivot is deliberately intended/targeted for business users, probably with no background on warehousing. People who never heard about Kimball or Inmon (that debate is really getting more religious than rational isn’t it?). Listen well to this: “Create a lookup relationship between two tables”. That’s the terms PowerPivot use to refer to the relationship between fact and dimension. Perhaps it’s me who got it at the wrong end of the stick: I have a data warehousing background, but PowerPivot is not for DW. It can take any tables, not just fact and dim. (And so can SSAS btw – see my post here). If I do get it wrong, please remind me, correct me (, or via comments). I’d be happy to be corrected.

Below I’ll illustrate how to compose a ‘PowerPivot pivot table’ — aah can’t we have a shorter name? Four words! Sod it I’m going to use the word cube, to differentiate to normal pivot table. To get a flavor of what PowerPivot is, I’ll illustrate how to compose a cube in PowerPivot, from Excel data. Of course the source can be replaced by query from DB tables, etc. Please don’t think it as a ‘how to’ but to enable us to see in a glance what it is. For ‘how to’ there are lots of resources, please refer to the links at the bottom of this post.

First, install Excel 2010 (download from here), then install PowerPivot (download from here). Open Excel, create some data, like this for example:

Format each ‘table’ as table by clicking on ‘Format as Table’ on the Home toolbar. Then, PowerPivot tab (see below) and for each table click on Create Link Table, which means ‘make this Excel table available in PowerPivot’.

Then we click on ‘PowerPivot window’ and rename the tables at the bottom of the screen: (this is so that we can identify which is which when we create the pivot table later on)

Then create relationship between the tables by clicking Table tab, Create Relationship:

PowerPivot resources:

  1. PowerPivotPro: one of the first sites on PowerPivot. Contains introduction to PowerPivot, case studies, examples, and lots of blog posts by Rob Collie.
  2. the most comprehensive portal about PowerPivot containing links to (almost) every single articles, post, news, author, books, etc about PowerPivot. Thanks to Vidas Matelis for creating this site.
  3. Microsoft site. We can download the November CTP version of PowerPivot, try it in the virtual lab, and watch the video clips.
  4. PowerPivot on Excel team blog: Introduction to PowerPivot by Excel team.
  5. Bob Duffy presented PowerPivot at SQLBits5. His presentation is here (2.5 GB, pptx)

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

%d bloggers like this: