By now you may have already heard the news that, as part of SQL Server 2012 SP1 CU4, new functionality has been released that means that Power View now works with Analysis Services Multidimensional (ie cubes, as opposed to the Tabular Model, which always worked with Power View). I won’t bother to repeat the technical details which you can read about here:
http://blogs.msdn.com/b/analysisservices/archive/2013/05/31/power-view-connectivity-for-multidimensional-models-released.aspx
…but the main points are that Analysis Services Multidimensional can now be queried in DAX, and this plus some tweaks to Power View mean that the two can be used together for the first time. Unfortunately Power View in Excel 2013 doesn’t work with Analysis Services Multidimensional yet, but I hope that will also be fixed very soon.
I’ve been playing with the public CTP of this for a while and done a few presentations with it, and from a technical point of view it’s a solid bit of work by the Analysis Services dev team. It just works, and while there are a few limitations they’re trivial. Arguably it should not have been necessary to do it in the first place – why didn’t Power View speak MDX when it was built, which would have meant it could have queried both Tabular and Multidimensional? But it’s here now, and that’s what counts. It also opens up some interesting possibilities for using DAX queries to create detail-level reports on cubes, and also for defining DAX calculations inside those queries.
However I think its real importance is strategic. This is the first significant bit of new functionality in Analysis Services Multidimensional for a long while, and it acts as a bridge between the classic SQL Server BI stack that most of us are using and the brave new world of Office/Sharepoint-led BI. It is also the first time in a long time that Analysis Services Multidimensional users have had a dedicated client tool for data analysis from Microsoft that isn’t Excel. Don’t get me wrong, I love Excel as a client tool for SSAS but I’ve always thought (and I think industry trends over the last few years support this view) that even though Excel is a great way to bring data analysis to the masses, there’s still an important niche among power users for a more advanced data analysis and data visualisation tool.
You may be thinking at this point that pretty graphs and charts are all very well, but your users don’t need anything other than the SSRS reports and basic PivotTables that they’ve been using for the last few years. I say that you ignore Power View at your own risk. Microsoft’s competitors in the BI space are hungry for new customers and are interested in migration projects. You might well arrive at the office next Monday morning to find that there’s a new CFO who used QlikView in his last job, and who wants the same pretty graphs and charts he had there again. It’s not going to be any use arguing that you’ve spent years developing this cube, that it’s lightning fast and has all sorts of tricky business logic coded into thousands of lines of MDX – if your BI solution’s user interface looks and feels dated, then whatever its technical merits it will have the musty smell of legacy software about it. If, however, you can fire up a VM with Sharepoint 2013 and Power View on and show off some slick dashboards created from your existing cubes, even if this is something the majority of your end users wouldn’t really be interested in (and you may be wrong, they might love it), you’re going to be showing the business two important things:
- Microsoft can do sexy dashboards and visualizations too, and while they come at a price, that price is probably a lot less than it would cost to rip and replace what you’ve got with a competitor’s software. So the option’s there if you want to spend the money and do the upgrade.
- Analysis Services cubes are not a dead-end, and Microsoft has made a significant investment here to prove this. I’d still love to see a coherent roadmap that explains where Microsoft is heading with its BI tools and how it expects its existing customers to get there, but I doubt we’ll get one. This functionality was, however, delivered in response to popular demand, so I’m hopeful that if we as customers can make our voices heard as to what we want in the future then we can influence Microsoft’s direction.
So go forth and Power View. Both Rob Kerr and Koen Verbeeck have recently published some excellent, detailed guides to setting up a Sharepoint 2013 demo environment; you have no excuse for not testing this out and being ready to face the competition.
I agree with most of what you say Chris but I would be cautious putting too much emphasis on PowerView as a viable client tool. Its functionality is extremely limited. Building an expectation that this is QlickView or Tableau from Microsoft will only hurt the tool as users discover its shortcomings. I suspect we are stuck with the “platform” (Excel, SSRS, powerview, SharePoint) for some years still each complementing the other, but with significant overlap.
Fair point, Peter. I guess I was trying to say that Power View ticks the box for pretty graphs and charts, and therefore impresses the people who have a desire (often superficial) to see these things. You’re right that Power View is still fairly limited, and there’s still way too much overlap, but the Power View/Excel story (and especially the Power View *in* Excel story) is a strong one I think.
Your second point summarizes quite well my current feelings: I’m inside a car whose driver listens to me when I shout loud enough, but I still don’t exactly know where he’s heading me. And even if I’m not sure he knows precisely the route – who knows it anyway in software eng? – i’d love to get some insight about our next place 🙂
“I love Excel as a client tool for SSAS …”
Really Chris? I have begun to encounter out of memory issues with some pivots that users are trying to create. If I write the MDX myself to create an equivalent report it runs immediately, which suggests to me that the problem lies not with my cubes but with the MDX that Excel generates.
Really – no client tool generates perfect MDX, but I think Excel 2010 and 2013 now generate as good MDX and any other client tool out there. Certainly you will always be able to write better queries yourself, but if you’re getting out of memory exceptions I suspect the problem might well be with your cube…!
Maybe you might want to check whether the users refresh an Excel sheet having outdated pivot tables, meaning the structure of the OLAP cube changed in the meantime. For me, this lead to serious performance issues.
Another possibility is that the users created user-defined groupings within the pivot tables, and Excel is issuing the “CREATE SESSION CUBE” statement (a temp cube on the server). For me, the users were not aware of this, as it was created by a user who left the company, and they used copy/paste on the pivot, which resulted in multiplying the problem by filling the temp space on the server; see http://msdn.microsoft.com/de-de/library/ms144734.aspx
This certainly is a good step forward for Microsoft and helps remove some confusion from their BI solution. Unfortunately we are still won’t be able to use Power View for some time yet due to the versions of sharepoint/ SQL server we are using (we are a large company who doesn’t upgrade these things easily).
We have never using Excel as BI client, favouring ProClarity. We recently ran into the “pretty chart” problem when someone in the company started showing off QlickView. But, of course, we didn’t want to start redeveloping all our cubes and retraining all our users just because our solution looked dated.
We have recently moved to Pyramid Analytics which has given us the best of both worlds. We can use our existing cubes, but now we have “pretty charts” (and dashboards). I am not associated with Pyramid (only as a customer), but I think it is worth a look for anyone using the Microsoft BI stack especially if using ProClarity (It also works with tabular cubes!).
So will I have to convert my MDX measures to DAX?
No – you won’t need to do anything apart from upgrade (well, there are a few scenarios where things won’t work but I guess 99% of cubes will work fine). You keep your MDX and your cube design as it is today, upgrade, and Power View will work.
any information about updates to it ? in original annoucement it said that CU4 has bring some enhancements to CTP but doesn’t list which one’s, things like exporting to excel, using cube hierarchies (not droping each field), drillthroughs etc, any news about these ? Thanks
Does this answer your question: http://msdn.microsoft.com/en-us/library/dn198310.aspx ?
Thanks a lot for your article …Mr Chris Webb.
I hope a day it will be possibile to use PowerView in Excel 2013 against a MULTIDIMENSIONAL CUBE.
Durinh this time i think i will test it on a PowerPivot model or is not possible too ???
Thanks a lot …
Power View in Excel works against data in PowerPivot and SSAS Tabular at the moment. I guess they are waiting for a service pack or something to put Multidimensional support on there too.
Love your work Chris, you make great points and have pinned many things on the head. and it’s great to see the outlines of a full BI stack.
Here is a reply from Siva on question asked in other forum about enhancements in CU4
The key enhancements since the public CTP are – supporting dynamic format string, language cell property and behaviour when default members are set for an attribute
AK
Power View is OK – it has some shortcommings. The biggest for us is the dependency on Silverlight. That means no iPad. The other is preserving filters across pages.
The issue with preserving filters across pages is fixed in this release of Power View, incidentally. It’s still Silverlight though 🙁
I’m not too convinced by the logic that leads up to your conclusion: “… Analysis Services cubes are not a dead-end, “. Perhaps you are just jumping all over this thing because of wishful thinking.
Maintaining complex solutions in Analysis Services multidimensional/classic is a massive pain, just as it was five years ago. Microsoft isn’t fixing any of the obvious issues and bugs; I could write down a very long list.
I wish we could get a formal statement from Microsoft on the future direction/investment (or lack there-of) in multidimensional/classic so that I can make the arguments to convince management that we need to kill it off and look elsewhere when doing new BI projects.
Hmm, well, maybe I was a bit over enthusiastic in this post, I admit…
I had started my rant on MSDN newsgroups before finding your enthusiastic post. I definitely wanted to believe you, but I just can’t because I’ve been banging my head against the Analysis Services development IDE all week… Multidimensional projects in VS are pretty bad as far as VS IDE’s go… It makes me happy to spend more of my time as a .Net programmer than as a BI database administrator…
My rant:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/623e94b8-8d66-4e59-a467-06659111353c/multidimensional-is-left-out-in-the-cold
It is discouraging that many BI developers are making the same types of observations that I am.
http://richardlees.blogspot.com/2012/12/whats-future-of-sql-server-analysis.html
SSAS multidimensional is in need of some significant TLC. And/or maybe even CPR. It’s too bad Microsoft gives multidimensional technology some lip-service and nothing more:
http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx