After yesterday’s stream of consciousness on how PowerPivot could be used in SSRS, here’s a follow-up post on how PowerPivot and ‘traditional’ SSAS could be integrated. Hold on, you say, surely that’s a no-brainer? Surely all that would need to happen would be that Vertipaq would become a new storage mode inside SSAS, along with MOLAP, ROLAP and HOLAP, and everyone would be happy? Well, maybe. But here’s alternative idea that I bounced off some friends a while back and got good feedback on, which I thought I’d air here.
Before I go on, let me state my position on some things:
- I like PowerPivot, and the more I use PowerPivot the more I like it.
- I really like the power of the Vertipaq engine, and I want to be able to use it in a corporate BI environment.
- I really like DAX, and I want to be able to use it in a corporate BI environment.
- BUT SSAS as we have it today is a very mature, rich tool that I don’t want to lose. PowerPivot models will always be a little rough-and-ready; a good SSAS cube is a lot more ‘finished’ and user-friendly (I always liken building a cube to building a UI). SSAS dimension security is, for example, an absolute killer feature in many corporate BI solutions; PowerPivot won’t have anything like this until at least the next version, whenever that will be.
- I also love MDX and I don’t want to lose it. MDX Script assignments, calculated members on non-measures dimensions, all of the things that PowerPivot and DAX can’t do (and probably won’t ever do) are things that I use regularly and in my opinion are essential for many complex, enterprise BI implementations.
- I don’t want the dev team to abandon corporate SSAS, and neither do I want the dev team to waste time re-implementing things in PowerPivot that we already have in corporate SSAS. Already people are asking when they can have security and partitioning in PowerPivot. I want new stuff though!
So, like all users I want absolutely everything possible. How could it be done? Here’s my basic idea: let us be able to build regular SSAS cubes using PowerPivot models as data sources, with SSAS working in something similar to ROLAP mode so every request for data from the cube is translated into an MDX (or SQL – remember SSAS, and presumably PowerPivot, supports a basic version of SQL) query against the PowerPivot model.
In more detail, let’s imagine we have an instance of SSAS running in Vertipaq mode and an instance of SSAS running in normal mode. You’d be able to do the following:
- Fire up BIDS and create a new SSAS project.
- Create a data source, which was a PowerPivot database on your Vertipaq instance of SSAS.
- Create a new Data Source View, which showed all of the tables in your PowerPivot database already joined. Alternatively, here I can imagine connecting to other data sources like SQL Server, creating a Data Source View as normal and then taking the DSV and deploying it as a PowerPivot model onto the Vertipaq instance of SSAS. So in effect, the DSV designer becomes a development environment for PowerPivot models.
- Create a regular SSAS cube in the usual way, only using the PowerPivot tables in the DSV.
- Set the storage mode of your dimensions and partitions to the new ROLAP-like storage mode; each SSAS partition could then be based on a separate PowerPivot table. This would mean that when you queried the cube, the SSAS instance issued MDX or SQL queries against the Vertipaq instance of SSAS, just as it issues SQL queries in ROLAP mode today. I suppose though there would be an overhead to making an out-of-process call, so maybe it would be better if you only had one instance of SSAS that could host both Vertipaq and regular SSAS databases at the same time, so all these requests could stay in-process.
The first, obvious, point here is that with this approach we get the traditional, rich SSAS cubes that we know and love and the raw speed of Vertipaq. So one objective is achieved. But I think there would be a lot of other benefits:
- You’d get two cubes for the price of one: the PowerPivot cube and the SSAS cube. You could choose which one to query depending on your needs.
- The ability to turn DSVs into PowerPivot models also gives you a proper development environment for creating PowerPivot models, integrated with BIDS and Visual Studio (so you also get source control). The current Excel-based UI is all very well, but us developer types want a nice visual way of creating relationships between tables.
- You’re able to use all of the new data sources that PowerPivot can work with in traditional SSAS. Imagine being able to create a planning and budgeting solution where users wrote values into an Excel Services spreadsheet, which then fed into PowerPivot via the new Excel Services REST API, which then in turn fed into a SSAS planning and budgeting cube complete with custom rollups and all the complex financial calculations you can only do in MDX.
- If your users have already built an existing PowerPivot model that they like and want to turn into an ‘official’ BI solution, you can very easily take that model as the starting point for building your cube by importing it into a DSV.
- It would also make it relatively easy to upgrade existing SSAS projects to use PowerPivot storage – you’d just convert your existing DSV into a PowerPivot model.
- SSAS drillthrough would be much, much faster because you’d be drilling through to the PowerPivot model and not the underlying relational source.
- You’d also have the possibility of working in something like HOLAP mode. Vertipaq may be fast, but with really large data volumes some pre-calculated aggregations are always going to be useful.
- You could define calculated measures in DAX in the PowerPivot model, and then expose them as measures in the SSAS cube. Probably you’d need some special way of handling them so they didn’t get aggregated like regular measures, but in some cases you’d want to take a calculated measure and sum it up like a regular measure (kind of like SQL calculations defined in named calculations today); many more calculations, like year-to-dates, can be treated as semi-additive measures. Effectively this means you are performing some multidimensional calculations outside the Formula Engine, in the SSAS Storage Engine (which in this case is PowerPivot), in the same way I believe that measure expressions work at the moment.
- For such additive and semi-additive calculations, it also opens up the possibility of parallelism since these calculations can be done in parallel in each partition and the result summed at the end. It also means you get the option to use either DAX or MDX, and can choose the right language for the job.
- There’s no duplication of dev work needed. For users of PowerPivot who want features like security, partitioning or parent/child relationships, you tell them they have to upgrade to regular SSAS; PowerPivot becomes something like SSAS Express. For users of SSAS who want the speed of Vertipaq, you tell them they have to use a PowerPivot database as their data source. The two complement each other nicely, rather like twins… now where have I heard that analogy before?
- You also have a convincing story for BI professionals who are sceptical/hostile to PowerPivot to win them over: traditional, corporate SSAS does not go away but is able to build on the new features of PowerPivot.
So there we have it, another fantasy on the future of the MS BI stack sketched out. You may be wondering why I’ve taken the time to write these two posts – after all, I don’t work for Microsoft and I’m sure plenty of people on the dev team have their own ideas on what features they want to implement for Denali. Well, as the saying goes, if you don’t ask you don’t get! And with Kilimanjaro almost out of the door now’s the time to ask. If you agree with what I’ve said here, or you disagree, or you have a better idea, please leave a comment…
The idea is interesting but I think the biggest issue is about dimensionality. We know that PowerPivot needs dimensions as tables in his model, so how do you handle several powerpivot tables as different partitions of the single cube in SSAS? But before that, if you think about it, dimensions are the key of many issues. The most compatible way I can imagine for handling it is to make SSAS cube querying PowerPivot by using the attribute key level of any involved dimension, including only the fact table in the query sent to PowerPivot. And this would mean having not a real ROLAP here, just a ROLAP fact table with MOLAP dimensions. Thus, some improvement would be needed to PowerPivot implementing inside it the capabilities to handle more complex queries from a dimensional point of view (i.e. handle hierarchies?).Moreover, I\’d like to see how PowerPivot will work with large amount of data that cannot be stored in RAM. Today, we don\’t know how faster it could be againsta a traditional MOLAP cube.Anyway, I agree with you about the need to improve SSAS (and PowerPivot!) for Corporate BI. Absolutely!Marco
Thanks for the feedback Chris.First, I want to assure you that the team is very focused on Corporate BI scenarios as we move forward. Yes, we took some time-off to nail self-service BI, and we are very happy with what we got. But as we are wrapping SQL 2008 R2 and PowerPivot V1 we are moving in full force to address corporate BI needs.As we move back to corporate BI focus, we are taking with us major assets from PowerPivot. The new spreadsheet –like modeling experience, the DAX expression and the VertiPaq storage engine are going to star in our corporate-BI centric release that is coming up, not too far off in the future.You are right that AS is a very mature platform which is very feature rich and you are worried about the investment to redevelop all of those for PowerPivot. The good news is that PowerPivot is built on the AS platform and the engine inherit all of these mature capabilities. For example, PowerPivot got MDX support “for free” just because it was built into AS. A lot of the AS features that are missing in PowerPivot are actually hiding under the surface waiting for a good UI to be added to expose them. But the way all these things are going come together are likely to be different than the architecture and experience you outlined above.So what to expect? 1. Big push on Corporate BI for the next release2. VertiPaq to have a very central role in large scale implementation. I and mean LARGE.3. DAX is moving to center stage with tons of innovations so I advise you all to study your DAX as much as your MDX.4. A PowerPivot-like experience to build the most massive & sophisticated corporate BI systems. Faster time to results, and much more fun on the way. 5. SharePoint is going to play a big role in both Corporate BI in addition to Self Service BI. So all you BI Pros better get intimate with this beast (BTW, I heard there is tons of money to be made around SharePoint)6. A lot of new power and coolness that I am not going to discuss for now. Big things are coming.7. A fairly quick release cycle (at least in Microsoft time).Amir.
Thanks for your comment, Amir – I\’m excited to see what you have in store. However I do have some comments on what you\’ve outlined…1) I\’m not the only person that\’s a bit worried by the new emphasis on Sharepoint, but I\’ll mention it again. From a technical point of view I agree that it makes a lot of sense; from a commercial point of view I think there could be a lot of problems. It\’s a classic case of one dependency too far: selling a Microsoft BI solution to a customer is one thing, but selling a BI solution *and* getting them to install/upgrade to Sharepoint 2010 is a much bigger task. I\’m afraid too many customers will rule out buying a Microsoft BI solution because they need something immediately and their corporate strategy either rules Sharepoint out completely, or only plans for the implementation of 2010 at some distant point in the future. Other competing BI tools with no such dependencies – where you can just install them and go – will have an advantage.2) I know PowerPivot supports MDX as a query language, but it\’s MDX calculations that I worry about losing. DAX is great but it has very specific capabilities and there are a lot of things it can\’t do yet like create calculated members on non-measures dimensions and scoped assignments. If support for this kind of stuff is coming, then great; if not then we have a problem. Corporate, \’enterprise class\’ BI isn\’t just about raw scalability, it\’s just as often about complex analytical calculations. If you can\’t implement a financial application or do the calculations that business users want (and this is an area that SSAS wins on today), you again risk losing the deal.3) I\’m not sure I actually want a more \’spreadsheet-like\’ modelling experience for corporate BI, especially if it means we move away from BIDS/Visual Studio. What\’s right for power users isn\’t necessarily right for BI developers; don\’t forget that we need source control and all the other good things that VS integration gives us.
I agree with Chris on that SharePoint 2010 will be a huge elephant to swallow for a customer that would like to implement a MSBI solution. If the customer can get SharePoint 2010 Enterprise Edition cheap, which I doubt, Sharepoint 2010 will increase consultings fees to maintain Sharepoint 2010. I do not know why it is so hard for MS Officials to understand that it is much cheaper, for the customer, to buy presentation /portal software that can be installed and maintained by the BI-consultant? This was the story with ProClarity Analytics.Sharepoint 2010 is great but it comes with a lot of applications and support/maintenance cost that is not the primary focus of a BI presentation portal.Strip Sharepoint 2010 down to a BI presentation portal server with the option to upgrade with the document management and content management parts if the customer is interested in that.
I understand the feedback on SharePoint and I can see the kind of pressure it puts on the engagement. But I hope you realize it is a double edged sword.We believe that the tight SharePoint integration makes it much easier to win the BI deals in SharePoint-centric organizations. In fact, our great integration with SharePoint will likely mean that Microsoft BI is often the only viable contender in a SharePoint-centric org. As a consultant – I would look for these SharePoint centric orgs and start picking up the easy deals. These orgs are your captive market!But even in the non-SharePoint org, when it comes to corporate BI, you will still have the ability to deploy the Microsoft stack without SharePoint. Applications of the type of the SQL 2005-2008 generation will work very well without SharePoint – so you don’t really lose anything and you should be as competitive as before.That said, a lot of the new innovations in our BI offering and the new class of apps they enable are based on the value of SharePoint. Some are pretty important. For example – all of Self-Service BI (PowerPivot) is based on the document centric approach of SharePoint. BI Search is based on the SharePoint FAST search engine. The IT dashboard is SharePoint based. And a host of new features we are working on will also require it. So as a BI consultant I would study and master SharePoint with vigor. A lot of new coolness will need it.I can also reveal that we are also doing some work to make the SharePoint dependency a lesser issue even for the new class of applications. Yes – SharePoint will be required, but it should not be this giant elephant you need to sell to the org to win the BI deal. But it is too early to discuss at this point.As for Chris’s additional comments:1. On multidimensional calcs – we hear you. We understand. We love both DAX and MDX, and MDX is not going away. The challenge for us is to bring them together. We have some promising directions. That said – I think you’ll see DAX evolving in new important directions that MDX will never cover, and a large and growing portion of the calc work will be done in DAX. So my advice stands: all you guys need to become DAX gurus ASAP.2. On BIDS and Source control – BIDS does not necessarily conflict with the “Spreadsheet like experience”. We have ideas.Amir.
Thanks for the comment Amir. The problem here in the cold northern parts of Europe is that the MS BI Stack rules but the MS Sharepoint stack does not have the same market share.From your comment I can see that you have second thoughts about Sharepoint 2010 and that is good news.
Hello,the SSAS, BI integration story so far:Information at your fingertips, Bi for the Masses, Pro Clarity, Data Analyzer, Softartisan, Dundas, "tight SharePoint integration", PowerPivot, Reporting Services,Excel Services.One example about upcoming Excel Document centric integration:We had recently a workshop with a customer and tried to show something like thishttp://blogs.msdn.com/excel/archive/2010/03/03/powerpivot-alpha-geek-challenge.aspxThe Story should be Excel 2010, cube functions, Excel Services 2010 without VBAProgramming.-We were not able to create cascading reportsbecause there is no built-in function to return the uniquename of a cubemember:function mdx(r as range) mdx = r.mdx.,With this information we would be able to create a MDX Set to present more informationbased on intermediate results. Top 3 Product of each Top3 Customer. This is a common request.-CubeValue still returns an empty string which results in #Value Errors if we calculate with the result.-We are missing the possibility to query builtin member properties likeLevel.ordinal, Key, etc. for navigation and interpretation or results.We all know how to get this working. But this is not integrated.I love SSAS and right from the start I missed integration. but I dont want to complain, because I got the best engine to sell.But I sell the engine. Thank you for listening
Thomas, I don\’t really agree with you. Sharepoint have had a tremendous uptake here in Sweden. Most major corporations own the Standard CAL and a lot of them have the Enterprise CAL as well; now to be fair not everyone have implemented it for BI. When I sell the BI stack I try to figure out what the customer wants but I always put Sharepoint in one flavour or another as part of the solution. If the customer would like to have standard reporting, I lead with reporting services but I recommend that they use it in sharepoint integrated mode either with MOSS or WSS. The reason for this is that it is easy for the customer to add more functionality to their portal. For example they can start sharing Excel workbooks in a document library, if they would like to view them in a web browser they obviously would need to go to a full fledged MOSS infrastructure. If they want to build a dashboard like report they can build it in SSRS but if they need to get more interaction like the analysis charts that comes from PerformancePoint services they would need to go with a full MOSS install. With this approach they can grow their solution as needed. Another thing that I tend to do is to talk about Sharpoint as the BI center instead of as a portal. The reason for this is that the customer may have another portal strategy and the word portal in the brand name may get the customer into a religous debate.
To be honest, where I REALLY want the advanced in-memory column store capabilities is….. in SSISFor lookups during a transform, the in-memory option is awesome, but limited to A) SQL and B) takes a lot of RAM.Being able to lookup from multiple sources, and efficiently would be Nirvana
Thank you for this post. Couldn’t pass by without mentioning that some of the functionality you are looking is available with Panorama NovaView on top of PowerPivot BI solution. (we plan to release it for GA together with Microsoft Office 2010 release, but we already discussed and presented it in a joint webinar with Microsoft last week. (watch it here http://tinyurl.com/NovaView-for-PowerPivot)- NovaView exposes DAX functions in a standard Panorama user friendly template driven formula interface. The functionality is exposed to the end user and becomes available in a corporate BI environment;- NovaView slicer security (query-level data security) provides an alternative or addon to SSAS roles data security and makes data security available natively for PowerPivot published cubes. NovaView provides a unified and centralized data security management system on SSAS and PowerPivot. – MDX functionality instantly available in NovaView products, exposed to power users and information workers through the same formula interface. Both template driven and pure MDX (free MDX) modes available, bringing the power of MDX directly to the user via friendly web based interface;You can watch 2 very shorts videos presenting NovaView for PowerPivot in SharePoint 2001. – Connect and analyze in SharePoint: http://www.youtube.com/watch?v=-aydfm5vI4w – Enable security on PowerPivot data: http://www.youtube.com/watch?v=g2oSNhz22Lw Navi