What am I missing? - Power Pivot is slow

[Kyle123]

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]

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]

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]

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]

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]

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]

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.