# What am I missing? - Power Pivot is slow



## Kyle123 (Jan 7, 2013)

Ok, I think I am really missing something here.

I just "don't get" what's so good about PowerPivot, as far as I can tell it's slower to pivot on Data than a regular pivot table and doesn't let me drill down to raw data when I've got numerous filters and restrictions set up.

It might be the way I'm using it however. I use SSMS to write all the queries I need (joining relevant tables etc), summarise data into a usable format (aggregation, calculations etc) and exclude irrelevant data. Then I use this as the source of the data in Power Pivot from which I build pivot tables.

The pivot tables I then build tend to be slower (with the same amount of data as an Excel Pivot table) when I pivot (move stuff around), and they also prevent me from drilling down when I have page level filters. I also can't rename the fields on the pivot tables (by typing over them) without there being all sorts of run time errors when I try to create a new table or refresh.

The PowerPivot functions are handy, but only really seem to do what the regular table functions in Excel do and I tend not to use them anyway and perform calculations in SQL.

So far all I've really found that is useful are reduced file sizes, but that's not really an issue, I don't really use formulas so there's little to no recalculation to slow down my workbook and all my workbooks are saved in binary which halves the file size anyway - (saving power pivot workbooks as binary seems to kill them).

However I'm probably using it all wrong since I read so many good things about it; I'm really not sure what I should be doing differently. As far as I can see it's just a self service BI tool useful if you can't interact with the database and write SQL, but as I said I'd genuinely like to understand how to get better use out of it and see what I'm missing.

I'm also interested in rolling it out to heavy duty data users who currently request a lot of reports from our service desk. Which I think would work best by creating large static views that are created every night. Power Pivot could then be used as kind of a better MS Query where users could filter the views and do their own number crunching as an interim to getting Reporting Services set up. I'd be really interested in hearing the feedback of anyone who's rolled it out in this type of project and what issues were encountered (database performance, user acceptance etc).

Sorry for going on a bit 

Any suggestions are more than welcome


----------



## RoryA (Jan 7, 2013)

As far as I can tell from what you describe, you start by removing the purpose of using PowerPivot on your data and go from there. 

If you can do what you need to with regular pivot tables then there is generally no point in using PowerPivot as far as I can see. You're just adding a layer of processing and abstraction for no benefit.

From an Excel Power User perspective I think it's a mixed bag because you very quickly reach a point where if you don't understand DAX/MDX and cubes really well, you get very confused very quickly!


----------



## Kyle123 (Jan 8, 2013)

Thanks Rory, so is it predominantly used as a self service BI tool for advanced users then?

Considering Pivot tables and autofilter is considered a dark art to most of our users, then I think PowerPivot would be way out of scope - there are a people people though that could get to grips with it and for those few alone it may be worth doing based on the amount of work they create for the IT service desk.


----------



## RoryA (Jan 8, 2013)

Basically yes - the Excel version anyway. (I have no experience of the server-side version)

When we upgraded to Office 2010 at work I was asked by IT who I thought should get PowerPivot installed and it was tricky to answer (I initially oscillated between nobody-except _maybe_ me- and everyone so that at least anything that was built with PP would work for everyone). I finally settled on about 6 power users but I'm not sure any of them are actually using it at all. We did at least get v2 rather than v1 where the drilldown wasn't there at all. I've only really used it where I had very big data sets that I wanted to store in the file for portability but that's mostly because I still haven't had a chance to get my head round DAX at all.


----------



## miguel.escobar (Jan 8, 2013)

you know...even with simple datasets Powerpivot really shines, why is that? because of the handy functions and dynamics that you can't achieve with a regular pivot table.

But it's a case by case scenario, if you feel that you can achieve what you want with just a normal pivot table....then please do (keep it simple). But if you really need something more that you can't get from a regular pivot table then it would be easier to use Powerpivot.

One of the most desired functions in a regular pivot table it's the distinct count but it only appears in Powerpivot so it's something that generally attracts new audience and then it comes the TOTALMTD or TOTALYTD functions that really add value to what you're trying to show in your report.

My advise, give Powerpivot another shot =)...and if you don't like him, then you should meet his sister...Powerview.

Best!
Miguel


----------



## Laurent C (Jan 8, 2013)

Basically, PowerPivot is an SSAS instance inside of Excel. This means you can do all the fancy stuff you could do with a Pivot table based on an OLAP source.

A regular Pivot table is very limited in terms of calculations. Some simply cannot be done with regular Pivot tables. (Basically, those that depend on the query context. Consider a distinct count, for example). 

Joins at the datasource level become unnecessary. This allows you to build a cleaner model for your data.

Assuming "Self-Service BI" means everybody in the organization may/will do BI is, in my opinion, a common misconception. It would indeed be unrealistic.

IT departments in many organizations will focus on delivering reports to managers, disregarding the few advanced users that heavily work with data. Since managers will often turn to these data professionals in order to go beyond what the BI system provides out of the box, this can lead them to create their own extension of the BI system. At that point, IT departments will usually not provide any support, mainly because they do not know much of business tools (Excel). PowerPivot was introduced to solve this problem by giving a common tool to the BI pro and the business analyst.

Jugding by your post, this may not apply to your organization.


----------



## Kyle123 (Jan 8, 2013)

Thanks for your comments, much appreciated.



			
				RoryA said:
			
		

> I finally settled on about 6 power users but I'm not sure any of them are actually using it at all.



That tends to be the way with a lot of things, people ask for stuff/have it given to them and end up never really using it - any ideas as to whether large queries hammer the database as much as people using msQuery do?

I wasn't aware of the Server Version, but we've only recently upgraded from 2005 and got reporting services so I haven't had chance to do much digging.



			
				[COLOR=#574123 said:
			
		

> miguel.escobar[/COLOR]]you know...even with simple datasets Powerpivot really shines, why is that? because of the handy functions and dynamics that you can't achieve with a regular pivot table.



Thanks Miguel, I haven't in my (admittedly limited) usage found an awful lot (if any) functionality that I can't do faster and more efficiently in SQL. In my work, a Pivot-Table without drill down is about as useful as a chocolate teapot - and I think that this is what I find most unforgivable about it. Don't you find it slow in comparison to a regular pivot table?



			
				Laurent C said:
			
		

> Assuming "Self-Service BI" means everybody in the organization may/will do BI is, in my opinion, a common misconception. It would indeed be unrealistic.



Couldn't agree more  but the ones that do demand a lot of time from my help desk and lightening this load is always appreciated.



			
				Laurent C said:
			
		

> Joins at the datasource level become unnecessary. This allows you to build a cleaner model for your data.


I'm guessing this is only true when OLAP cubes have been properly set up however? Since we've yet to get fully onboard with data warehousing (we're getting there!) I don't see how this would be true otherwise. Users wouldn't know what relates to what.

Thanks again


----------



## Laurent C (Jan 8, 2013)

Kyle123 said:


> any ideas as to whether large queries hammer the database as much as people using msQuery do?



That is an easy one: since PowerPivot imports the data in the workbook, everything will be done locally once the model has been refreshed, which ideally should only happen once for each workbook. 

Also, the queries should be less demanding, provided the datamarts have been built accordingly.


----------



## Kyle123 (Jan 8, 2013)

> That is an easy one: since PowerPivot imports the data in the workbook, everything will be done locally once the model has been refreshed, which ideally should only happen once for each workbook.



Not quite, it's the initial/reload of data that is painful - some imports can take ~20minutes based on the data that is being requested. It seems to be an Excel thing, other applications have no issues in pulling large amounts of data.


----------



## Laurent C (Jan 8, 2013)

Indeed, the same sentence could be used for Excel without PowerPivot. What I had in mind was that your unique JOINed query would be replaced by a sequence of simpler SELECT statements, hence delegating part of the workload to the client.

Of course, if the same query is significantly slower when run from Excel than in, say, SSMS, then this is something that must be looked into.


----------



## Kyle123 (Jan 7, 2013)

Ok, I think I am really missing something here.

I just "don't get" what's so good about PowerPivot, as far as I can tell it's slower to pivot on Data than a regular pivot table and doesn't let me drill down to raw data when I've got numerous filters and restrictions set up.

It might be the way I'm using it however. I use SSMS to write all the queries I need (joining relevant tables etc), summarise data into a usable format (aggregation, calculations etc) and exclude irrelevant data. Then I use this as the source of the data in Power Pivot from which I build pivot tables.

The pivot tables I then build tend to be slower (with the same amount of data as an Excel Pivot table) when I pivot (move stuff around), and they also prevent me from drilling down when I have page level filters. I also can't rename the fields on the pivot tables (by typing over them) without there being all sorts of run time errors when I try to create a new table or refresh.

The PowerPivot functions are handy, but only really seem to do what the regular table functions in Excel do and I tend not to use them anyway and perform calculations in SQL.

So far all I've really found that is useful are reduced file sizes, but that's not really an issue, I don't really use formulas so there's little to no recalculation to slow down my workbook and all my workbooks are saved in binary which halves the file size anyway - (saving power pivot workbooks as binary seems to kill them).

However I'm probably using it all wrong since I read so many good things about it; I'm really not sure what I should be doing differently. As far as I can see it's just a self service BI tool useful if you can't interact with the database and write SQL, but as I said I'd genuinely like to understand how to get better use out of it and see what I'm missing.

I'm also interested in rolling it out to heavy duty data users who currently request a lot of reports from our service desk. Which I think would work best by creating large static views that are created every night. Power Pivot could then be used as kind of a better MS Query where users could filter the views and do their own number crunching as an interim to getting Reporting Services set up. I'd be really interested in hearing the feedback of anyone who's rolled it out in this type of project and what issues were encountered (database performance, user acceptance etc).

Sorry for going on a bit 

Any suggestions are more than welcome


----------



## Kyle123 (Jan 8, 2013)

> if the same query is significantly slower when run from Excel than in, say, SSMS, then this is something that must be looked into.



That's the issue, the data is pulled from a static table that is dropped and re-populated every night (it's intensive and takes around and hour and a half to run). It's this static table that takes a long time to import (it is filtered by msquery), but still locks Excel and the CPU on the server spikes the whole time the data is being extracted by Excel. Though it should be straightforward to establish is PowerPivot is any more efficient - even if it is though, the drill down problem still remains.


----------



## miguel.escobar (Jan 8, 2013)

What I really feel is that Microsoft gave us, for the past 2 and a half years, a really good teaser and even a trailer of a really great movie that I like to call, the Self-service BI revolution packed with Powerpivot and Powerview all inside Excel 2013. In Excel 2013 you can drill down the data and it's something that really bugged me that I couldn't do before...but the future looks bright and the Powerpivot approach might not be the best one for every scenario. Even some queries using report builder can actually run faster than what you see on Powerpivot v1.0 and even in v2.0.

It's a case by case scenario but I truly believe that if they started this way, in a couple of months the next versions of Powerpivot will be extremely important to all the data analysts that use Excel as their main tool and in the end, it's a tool aimed for the Excel pros.

About the importing process, I read a while back a post from Alberto where he provided his experiences when dealing with these issues....one of them was to preview and filter the data and actually sort it (no matter what column) and that should help you TONS while dong the initial loading of the data.

I'm going to search that post and give you the link.


----------



## Siraj.Samsudeen (Jan 9, 2013)

miguel.escobar said:


> I'm going to search that post and give you the link.


Hi Miguel, Incidentally, I just read this post a few hours. Here it is
Alberto Ferrari : PowerPivot: improve the performances by sorting tables


----------



## Siraj.Samsudeen (Jan 9, 2013)

Kyle123 said:


> I use SSMS to write all the queries I need (joining relevant tables etc), summarise data into a usable format (aggregation, calculations etc) and exclude irrelevant data. Then I use this as the source of the data in Power Pivot from which I build pivot tables.


By doing all the aggregations in the SQL query, I think that you are taking away the usefulness of Powerpivot. What if your users want to do aggregation on a different set of columns? 

I have introduced Powerpivot to a few data analyst who are Excel pros and the unanimous feedback/impression is that it reduces their need to go back to IT and empowers them to do many things themselves. If you want to reduce your load, expose the atomic data in a schema that is friendly to your demanding customers (meaning, do the joins that absolutely have to be done and for the rest, expose the tables directly). then, they can do a lot by themselves


----------



## Kyle123 (Jan 9, 2013)

Siraj.Samsudeen said:


> By doing all the aggregations in the SQL query, I think that you are taking away the usefulness of Powerpivot. What if your users want to do aggregation on a different set of columns?



That's kind of the point though I think, I don't have any users, there's only me using it.. so far  We also have extremely complicated business logic, so expecting users to know what to join to get standardised results would be nigh on impossible - I don't relish the thought of the service desk guys having to decrypt what people have done with PowerPivot to be able to explain the pitfalls they have made in order to replicate published stats.

That's not to say however, I don't understand its usefulness to some users - I'm happy to admit that I'm just not the target market of the tool - I think it is a useful resource, but I'm not sure I can see the benefit in spending the huge amount of time that would be required to build the relevant schemas that would be required for a very small amount of users. I think that we are probably too small an organisation for this to be worthwhile and that a standardised set of static tables derived from views that would answer 80-90&% of the queries we get.


[QOUTE=miguel.escobar]one of them was to preview and filter the data and actually sort it (no matter what column)[/Quote]
Thanks Miguel, that makes sense, but I'm not sure it helps much, the tables the data is pulled from has a clustered and numerous non-clustered indexes for this reason. I'll give it a try anyway though  always worth a shot! Not sure if it helps with the standard Excel import though.


----------



## miguel.escobar (Jan 9, 2013)

Kyle123 said:


> That's kind of the point though I think, I don't have any users, there's only me using it.. so far  We also have extremely complicated business logic, so expecting users to know what to join to get standardised results would be nigh on impossible - I don't relish the thought of the service desk guys having to decrypt what people have done with PowerPivot to be able to explain the pitfalls they have made in order to replicate published stats.



Same scenario as mine minus the extremely complicated business logic...and all I can tell you is that the UPPER UPPER UPPER management (that's the boss of my bosses boss) it's actually loving Powerpivot since he can just drag and drop and it's almost like an ad-hoc tool and he wasn't a pivot guy at all.

Don't get me wrong, I'm not an IT guy, I come solely from an excel background but thanks to powerpivot they are now able to understand the business even more since they know what's hidden in the database/applications that we use and all the impacts that they have. Also, end-users really don't need to know everything that happens behind courtains...like importing data, all the relationships, etc....the perspective view should be the only field list that they should see and they should only select from those fields (in the best case scenario).

Also, timewise, a powerpivot data model can be created within hours (after hiding the columns and tables that you don't want to show and create perspectives and measures) and once you have that ready then it's just time to play with the pivot table! 

Note: the true beauty of powerpivot/powerview comes when you have it in sharepoint and with a data refresh schedule.


----------

