“But I still haven’t found what I’m looking for.”
-Bono
“In answer to your query, they’re written down for me.”
-Blade Runner Holden
We’re fortunate to already have a number of great sources of PowerPivot information – the official site, some great blogs, and several forums.
We now find ourselves with a familiar problem, though: given the breadth of the PowerPivot product, finding the answer to a specific question is often difficult unless you’ve been following all of those sources since their inception. Digging through archives isn’t a lot of fun, even when assisted by a search engine.
So, here it is, The Great PowerPivot FAQ.
Contributing to the FAQ
Most of the q’s in the FAQ as of today came from a list I’d been maintaining in Excel, and I’ll of course be adding to it over time, but I hope to not be the only one responsible for all of this 
So if you want to contribute, here are the three ways to do so:
- You can email me a question and answer (or post it in comments). If I agree that it qualifies for the FAQ, I will post it to the FAQ and credit you as the contributor.
- You can send me an answer to a currently unanswered question, and again I’ll credit you. Notice there’s an “Answered?” column in the FAQ, and there are a few “No’s” in there.
- I’m also hoping to have a few co-moderators who have edit rights. I have a few people in mind and will be contacting them directly. Depending on response, I might open it up to volunteers.
I don’t have enough logins to go around, and certainly don’t want to open anonymous editing, so for now that’s gonna have to be the system.
Why SharePoint?
I chose to use SharePoint because it gives me a convenient publishing mechanism – I get a hyperlinked table of contents for free, without manually having to keep that up to date in HTML. It also gives readers the ability to sort and filter, and I can annotate with additional columns as needed. I can use that site to publish all kinds of other stuff, too – other lists, wikis, whatever. (If you have suggestions let me know.)
Also, the Data Grid view let me directly copy/paste my existing list of questions and answers from Excel directly into SharePoint.
And all hosted for $9 a month (as long as I’m ok with anonymous access, which I am). Not bad.
Name 5 things that you can do with PowerPivot Addin that cant be done with a Normal pivot connected to an external data source
1)
2)
3)
4)
5)
Name 3 and I will be truly impressed
My favorite comment of the new year
1) Data volumes. PowerPivot is not limited to 1M rows like Excel. And yes, Excel’s pivotcache can handle more than 1M rows, but if you want to add custom calc columns, you have to drop the data into a sheet, which caps you at 1M. PowerPivot can handle 100 times that. And even better, PowerPivot has incredible compression, so its files are a lot smaller than regular Excel files at the same data volume.
2) Relationships and multi-table pivots. Excel can only pivot one table at a time. If you want to include another table of source data in the pivot, you have to VLOOKUP each column you want from table 2 into table 1. PowerPivot, you make like 3 clicks and now EVERY column in table 2 is available in your pivot field list.
3) Time series calculations in pivots. PowerPivot adds a calc engine capability to pivots that dwarfs the “custom item/custom field” capability that Excel offers. Wanna do a “Year over Year growth” value? A running total? A comparison with the previous period? There are like 40 functions for handling time, alone. This is actually more than 1 item, then, but I’ll count it as one
4) Custom ratio calcs in pivots. Ever wanted to do a custom % of total calc in a pivot that compared the current cell to the grand total? Yep, you can do something like that today, but PowerPivot gives you total control over which fields get set to grand total and which get left in the current cell context. You can even do fixed comparisons like “Growth since 2007″
5) The ability to publish to a server that automatically refreshes your data every night. That same server gives you a rich report gallery with auto-generated thumbnails of your report sheets, and a usage dashboard that lets you see who is using what reports and how often.
Not to mention how much easier it is to design the report itself in PowerPivot. Multiple pivots all laid out nicely with regard to each other, all sharing the same “slicer” graphical filters. This is simply a question of saving your time however, and can technically be done without PowerPivot.
Rob,
1) To Create a Pivot Table I dont have to have the data in the file that has the pivot table.
I can create a pivot table connected to a 3 different tables sitting on an SQL Server or Access Database or an Other Excel File or a CSV File or text file which has a 10 M + rows and it works without any problems… We have been doing this for AGES….
2) All Joins and Unions, relationships can be created via SQL Statements..The Query can be built in the Pivot or in the Database
3)Year on Year Growth etc can be easily handled via Calculated Fields…Running Totals are also possible…. for more complex date funtions there are sevral Date/Time related functions in SQL
4)In Excel 2010 this is directly possible – % of Parent Column Total
“Growth since 2007″ is also possible
5) you can set a pivot table created on an external data source to refresh every “X” minutes… or Refresh on File open – I dont have to “Publish” it anywhere!!
6) Ordinary Pivots can also share slicers and can be laid out nicely
7)”This is simply a question of saving your time however, and can technically be done without PowerPivot.”
8) “And even better, PowerPivot has incredible compression, so its files are a lot smaller than regular Excel files at the same data volume.”
Both 7) and 8) are higly debatable….
Do you have stats to prove this ???
a) I created a “PowerPivot” on a SQL Server Table – 1 M rows – File size 1.9MB
I created a “NormalPivot” on the same SQL Server table – File size 1.5 MB
b) I could not make out a substantial difference in the refresh times
So here I go again… Are there 5 things that the PowerPivot CAN DO that a Normal Pivot CANT!
1) Yes, Sam. I know. I’ve worked on the Excel team for years. The data does get pulled into the file, as a PivotCache. That’s all I meant. But you can’t add calculated columns to a PivotCache. And Excel users like to add calc columns. Apparently you do all of your work in SQL.
2) If you are able to do this in SQL today, good for you. But most people can’t.
3) Again, if you can handle time series calcs in SQL today, excellent. Keep doing it. This product is aimed at Excel users – and only a small fraction of those know SQL.
4) Show me how you can do my “Average sales per day” calc field in Excel 2010 without resorting to SQL. You can’t. There are many, many calcs you cannot do in plain Excel that can be done in PowerPivot. You should dig into and see.
5) If your intent is to share your workbook with others, there is no way to autorefresh what everyone sees.
6) – 7) I counted the clicks one time to create 4 charts laid out nicely that all shared 3 slicers that were laid out nicely as well. It was on the order of 100 clicks. Same thing in PowerPivot is less than 10 clicks.
8) I’ll dig up some examples of compression. Keep in mind that it’s not just on-disk compression. It’s in-RAM compression as well. Excel gets good file compression but completely uncompresses on file open. PowerPivot partly uncompresses into RAM (like 2x) but the majority of compression is preserved in RAM (sometimes we get 15x here).
Sam, I detect in you that you might simply be determined to disagree. You are bordering on impolite. I am here to answer your questions, but this is my personal site that I pay for and maintain. This is not an official MS site. I’m happy to take some flack as long as both sides remain respectful and truly try to understand what the other is saying. So, please decide which one it is. I have not banned anyone yet, but I am here to be helpful, not to provide a public forum for geeks to fight with each other. Make sense?
Rob,
My aplogies if I sounded rude….I have be using Excel for over 10 years and Gemini/Powerpivot every since it lauhched…So I am primarily a Excel user and an Excel Developer.
Your site has done a wonderful job of helping people like me discover this addin
No doubt many things would take lesser time in PowerPivot and the DAX syntax would be simpler that the corresponding SQL for Excel users.
My only intention of the comment was to try and learn if there is something that simply cant be done with the exisiting technology that is now possible with Powerpivot…Sorry if I ended up sounding disrespectful.
No worries Sam. Just wanted to make sure we were on the same “vibe.” Please do not hesitate to ask questions going forward
Hi Rob,
Another FAQ
Why the PowerPivot feed importer support Atom, but not support RSS (that is more extended)?
Regards!
Hi Jose. I guess we just figured that RSS was for content, and wouldn’t often be a useful data stream, and didn’t want users to be confused between content feeds and data feeds.
I’d love to hear more, however, about where/how you would want to use RSS feeds.
-rob
Hi,
Any ideas on how to Install Power Pivot on a Parallels VM in iMac? I have a OS X iMac and am running Win XP with SP3 using the Parallels VM infrastructure. I have Office 2010 Professional Trial running on it. However I tried installing both the 32 bit and 64 bit version of Power Pivotn add ins and I get an error message saying these are not valid installation files. What do I do?
Hi Vish. I’m asking around on Twitter, will respond here when I get an answer.
Powerpivot looks very promising but how does it handle the risk of having people using a copy of data they pulled locally that might have changed on the source due to a bulk update. I mean is there a mechanism that alerts users that their version of the truth has just expired and that they should do a refresh ?
Are there parts of the PP sheet that can be protected from the end user so that they cannot change important parts that might compromise the integrity of the data that they are analysing?
Why is information/posts/forums on PP so scarce as if most of the internet posts are of Q1 2010? Did it not pick up as much as MS hoped for?
Will it burden the source servers to have so many clients pulling millions of raw data onto their laptop every time they hit refresh when with a regular OLAP one would have had all the aggregations done beforehand and placed on a seperate server than the live system?
Hi David. Most of the questions you raise are addressed by PowerPivot for SharePoint.
You can publish an interactive workbook to SharePoint and give consumers the permissions to read/explore/drilldown/filter it, but DENY them the right to download the PowerPivot model inside the workbook.
That way, they are always consuming the up-to-date version. No alerting needed. They cannot modify the workbook at all, except to interact with it. And there is only one copy of the workbook pulling data from SQL.
The majority of posts being from Q1 2010 is somewhat expected, since that was the quarter it released. Personally I’m happy to be the most active blog on the topic
Other than Pivotstream’s own heavy usage, I didn’t see signs of significant real-world deployments until roughly September of 2010. Adoption slowly crept up through the end of the year, and then really took off in January. Blog traffic is up about 30%, and more telling is that new clients reach out to us at about 5x the rate of last year.
The one place where I think adoption is slower than desired, ironically, is with Excel users. What I am seeing is a lot of enthusiastic top-down adoption, and very little bottom-up adoption. By the time PowerPivot reaches its entire target audience (millions upon millions of Excel power users), I expect to see blog traffic at about 100x (or more) of what I have today. So there is a long way to go with that crowd. But the top-down crowd is gaining steam in a big way.
-rob
Thanks for your informative answer. I will spread the word a little more in my domain and you will soon get more hits
. I work in the BI team for a company with 1000+ employees and we deal with 100 million+ records per month serving 200+ users.
We use OLAP to mine our data (SSAS/SSIS/SSRS) but are looking for a selfservice tool. We looked at Qlikview from Qliktech but now we are more focused on PP given that we already pay several SQL Server 2008 licenses.
I am thinking of finding a compromise between dishing out all the raw data in a PP sheet vs aggregating the data say by hour and cleaning it up a little, then placing this on another server for PP to slice and dice with. Performance vs granularity kind of thing.
Hopefully I will have some interesting findings to post here.
You’re quite welcome David, and thank you. Sounds like you have some interesting data sets and approaches to explore there. We’ve done a lot of that exact same tradeoff – perf vs. granularity – on data sets as large as 2 billion rows.
In addition to pre-aggregation, you may also consider partitioning (by department or region for instance).
I tried preparing an excel file using the power pivot and send the same to my friend for review. However, when he tried opening the file, it is throwing this error – “Initialization of data source failed. Check the database server….”. Please let me know how to send a powerpivot file?
I get that error all the time in the Excel addin. It’s actually ignorable 99% of the time – you click OK and then everything works. I don’t even notice it anymore.
And that error never happens on the server.
Hi Rob,
Nice FAQs you have put up here and its really enlightening reading through the Q and A. Thanks for putting this up together!
Coming to the real question that I have in mind. Can you please list down for me 4-5 distinguishing factors between when to use OLAP (SSAS cubes) and when to use PowerPivot? Ours is a Data warehouse environment where we are trying to build a system that will serve the reporting and analytical needs of the customer.
Thanks!
Dear
I have created Power pivot KPI report and i wan to publish to my boss whih he dont have Power pivot add in also he is sitting in china, ould you please help me how to share this file…
Thanks for the help
Check the Cloud PowerPivot page on the blog. Also, stay tuned for something new on this front.
Dear Thanks,
But alrady my company having Sharepoint access 2007 version, is it possible to display our KPI report which we created by using Power Pivot,else we need to upgrade to Sharpoint 2010?
also please advise what is the benift of cloud Power pivot while having sharepoint 2007.
Vinoth
Hi Rob,
I want to create one measure with condition of total shipments with table number1 number of days should be less than or equal to 60 days.
{ Example : Total shipments wil be 400 but out of that only 226 shipments only we recived on time ( based on Numbers of days column)}
Measure 1: Total number of shipments wil be Distinct count of Inbound Numbers of the Inbound Qty.
Measure 2 : Total number of shipments with condition of Number of Days should be less tan or equal to 60 ( oulumn numbers of days already already aailable on my table)
here i want to create measure number 2 which measure already with me.
Tnx
Vinoth Govindaraj