The point of PowerPivot

[mcalex]

If I can do a sql query that performs a pivot, and I know how to do VLookups, do I need/want PowerPivot? There has never been a desire in the org to produce a 'dashboard' report which looks like the main output PP does. If there is no call for one of these reports, do I need/want PowerPivot?

It seems to be a manager's current flavor, and I'm not entirely sure that there's a lot of point to it for the work we do. I rarely need to pull out the pivot toolbox for sql queries, and we generally only use VLookups to ensure correct spelling on some of our lists (customers, regions, town names, etc). (Does it make 'Where not exists' queries easier, by any chance? I hate working out that syntax.)

I've got myself a PP workbook in Excel that was created from a sql server pivot query, but it doesn't seem to do much. When I close it, the data left in excel has no formatting prettiness and can't be used as a document to send to other users. Am I supposed to send them the PP workbook, and if so, does that mean everybody is going to have to download it in the organization?

Sorry for the disjointed questions, but I'm currently having a big struggle with what exactly it is that PowerPivot does.

thx
mcalex

 

[pete234]

It's a bit difficult to address everything but in my research and practice, Powerpivot works best when utilized with Sharepoint (Powerpivot enabled in Excel Services---dashboards with slicers) or connected to an SQL server.
I'm not able to use either but I still find it very useful, maybe for reasons you don't need.
In the case of vlookups, simple relationships in one of my reports replaces what would be 80-150k vlookups.
But if your working with very standard and labelled data, then your mileage will be different in that aspect.
Depends on how much transformation is needed from the source.
My advice would be to check out some sources out there like Marc Russo's blog and powerpivotpros website.
And yes, users need PowerPivot installed to be able to refresh the data or use the slicers.
Otherwise they just have a static sheet to view.

 

[Laurent C]

PowerPivot allows you to create cubes directly in Excel. Cubes were basically invented to answer a very common business scenario: "Remember those sales figures by country you provided last month? I would like the same figures by channel."

So, PowerPivot is basically a data source to build pivot tables and allows you to create calculated fields that are actually useful, contrary to the good-old pivot tables.

PowerPivot will probably not make your "Where not exists" queries easier, but you should only have to define your measures once and people will not keep asking you for small variations of the same report over and over again.

As noted by Pete already, taking full advantage of that requires PowerPivot for SharePoint: with it, you can publish your PowerPivot workbooks to SharePoint, so they can be reused as data sources for pivot tables in other workbooks. Without it, you will have to distribute the workbook containing the model. Even then, PowerPivot is useful for these very common scenarios.

You probably are the only that can assess whether your job requires PP, but remember: Who needed smartphones, 10 years ago?

Your current reporting requirements might seem too simplistic now for PP to be useful, but once people in your organization have been presented with more flexible pivot tables, they might well ask for more.

 

[mcalex]

Thanks for that guys, I now have a clearer understanding. We don't have lists of orders, products, campaigns, etc as we are more of a research/policy than sales based organization. OTOH, sharepoint is on the horizon, so I'll watch that space. I can see the usefulness in printing the green version of the Excel/PowerPivot spreadsheet in an online format.

It looks like some aspects of the db's job will change from creating views, stored procedures etc, to educating users in tables, relationships, and other DBA101 stuff, so they do this stuff themselves. Users get the flexibility of creating their own data structures (including adding lists from data that may not come from the central sql server), and (the db gets) the comfort/security of not messing with the official data store.

Does this sound about right?

Lol, as for smart phones, apart from the instantaneous communication aspect (which is really <i>cell</i>phones), I'm still to be convinced that the main point of them is not as a time-killer.

mcalex

 

[Laurent C]

That sounds absolutely right on point.