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

 

[RoryA]

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]

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]

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]

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]

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]

Thanks for your comments, much appreciated.

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.

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?

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.

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]

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]

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]

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.