Business Vault and Microsoft PowerPivot

we all know excel (at least in the business world), and we all agree it & business alike, that this tool is the most commonly used bi tool in the industry today.  we may not like it, it may have issues, but it is certainly by far, the most well-distributed and well used tool in business intelligence.  in this blog i’ll explore something new, something the business users have been pushing for, for years.  the idea of getting the business rules / business changes, ie: the power to move the business forward, back in to their own hands.

in case you haven’t heard…

the new release of sqlserver2008 r2, along with sharepoint, and excel 2010 includes functionality called powerpivot.  i’m not sure if it’s a plug-in, or core functionality, but it seems to be an incredible advancement in the world of bi – and it also seems as though the bi world has been ignoring it (although why that is so, escapes me at the moment).

powerpivot is an explosion of functionality on the business users’ desktop.

powerpivot for excel is an authoring tool that you use to create powerpivot data in an excel workbook. you use excel data visualization objects such as pivottables and pivotcharts to present the powerpivot data that you embed or reference in an excel workbook (.xlsx) file.

powerpivot for excel lets you import, filter, sort many millions of rows of data, far beyond the one million row limit in excel. sort and filter are extremely fast because the operations are performed by a local analysis services vertipaq processor that runs inside excel.

this is the engine behind excel.  couple that engine with powerpivot in sharepoint, and you’ve just quadrupled your desktop bi power.   powerpivot really does have a tremendous amount of connectivity available, and this is wonderful.  if you look at the supported databases for native connectivity you will find all the common vendors there.

as good as the business users are, they still wish to connect to one place to get their integrated enterprise data – although they realize that providing a mash-up might still be necessary, given the extent of external data systems and access protocols.   powerpivot provides the capability of producting or acting as the business vault.  that’s right, it has the mechanisms, the transformations, the sql access, and all the capabilities needed to run business rules from the business users’ desktop against raw integrated data warehouses.  this means that the need for a back-end data vault model is bigger than ever.

with a raw-integrated data store containing all the history across many different source systems, the business users (throgh views on top of the raw vault) have the opportunity to build, manage, check-in/out, and deploy business rules for manipulating the data on the way in to excel.  now, through sharepoint, it can maintain their governance principles and practices – along with access restrictions.  they can also see who is using what, and who is sharing with whom….

the business vault “can now be” a front-end bi application.  ok, this is the panacea – it may not be there quite yet technologically speaking – but you can do many of these things today. this is already in place (to a degree) at ing real estate in the netherlands.

it’s an incredible solution that no longer requires “yet another data store” for managing the data, and by putting the business rules directly in the hands of the business users, they now have major power over interpolating the information for business needs.  it is relagated to moving, connecting, and acquiring the information in to the raw data vault.  what’s more is that powerpivot server side enables access to microsofts olap/cube engine, that is: bi-directional access.  yes, read and write-back.  very cool indeed.

i think, we will continue to see a rise in business-empowered bi, with the business rules moving more and more toward their hands-on environment.  i think the power of the raw data vault has yet to be unleashed.

what do you think?  is this a trend you see? do you totally disagree with what i’m saying?  i’d love to hear your viewpoints, and / or criticisms.  what problems do you see arising in this approach that it might have to mitigate?

dan linstedt
ps: learn more about the business vault in my on-line classes at:

Tags: , , ,

No comments yet.

Leave a Reply