PowerPivot and DAX papers,posts,FAQs,webcasts,samples

The last few days have been quite emotional for me. I’ve gone from being very angry, to just feeling sad, to being angry again; I’m grateful to the many members of the SSAS dev team who’ve let me rant and rave at them for hours on end and who have patiently explained their strategy – it’s certainly helped me deal with things. So what’s happened to make me feel like this? I’ll tell you: while it’s not true to say that Analysis Services cubes as we know them today and MDX are dead, they have a terminal illness. I’d give them two, maybe three more releases before they’re properly dead, based on the roadmap that was announced yesterday. And this is incredibly hard for me to write because I’ve spent the majority of my working life, about 12 years now, working with them; I live and breathe these technologies; and I have built up a successful consulting business around them. Neither is it true to say that they are struggling in the marketplace: on the contrary they have gone from strength to strength even in spite of the fact that, apart from the important performance improvements in SSAS 2008, we haven’t had any substantial new functionality since SSAS 2005. SSAS has been the most popular OLAP tool on the market for years, has loads of very happy users, and continues to be used on new projects all the time. Hell, on stage the other day at the keynote there was a guy from Yahoo talking about his 12TB cube, which loaded 3.5 billion rows of data per day, and which he was planning to grow to 40TB! The SSD revolution has given SSAS cubes a massive boost. So this is one very successful product and no other company would be allowed to do what Microsoft is proposing to do with it because if they did customers would be up in arms, calling their account managers, and the account managers would go straight to the CEO and demand that the product was not only retained but given the development resources it deserves. But this is Microsoft we’re talking about, and they have the luxury of being able to ignore this kind of pressure from their customers and partners and do whatever they want. And they have quite convincing reasons for doing what they’re doing, albeit ones I’m having severe difficulty coming to terms with.

So let me get round to explaining in detail what was announced yesterday at the PASS Summit. Quite a few BI related things were aired that I won’t talk about in detail: the move to Visual Studio 2010 for all BI development, and the integration of SQL Management Studio functionality into VS2010 too; FileTable; the Master Data Services Excel addin; Data Quality Services; loads of new SSIS stuff including impact analysis and lineage; and there was yet more buzz on Project Crescent. But I’m going to concentrate on what came out in TK Anand’s presentation on the future of Analysis Services. Here are the main points:

• The BISM – BI Semantic Model – is the name for the new type of Analysis Services database that gets created when you publish a PowerPivot model up to the server. It’s SSAS running in the special in-memory mode, and SSAS instances will either work in this mode or in regular MOLAP mode. In Denali we’ll be able to install a standalone instance of SSAS running in in-memory, BISM mode without needing Sharepoint around.
• We’ll be able to create BISM models in BIDS, so we get full support for stuff like source control. The experience is very similar to what we get in PowerPivot today though; one of the points that was made again and again yesterday was that they wanted to make things as easy as possible for BI developers; the implication is that today the learning curve for SSAS is too steep, which is why many database people have been put off using it; I would argue that any rich, sophisticated tool is going to have a learning curve though and I bet nobody would dare to go to the C# community and tell them that C# is too intimidating, and wouldn’t it be nice if they had the friendliness and flexibility of VBA!
• BISM models are the UDM 2.0. Everything that the UDM was meant to do in SSAS 2005, and didn’t, are serious objectives here: BISM aims to replace traditional SSAS and SSRS report models, and be good for the kind of low-level relational reporting that SSAS today simply can’t do as well as the high-level, aggregated data it handles so well today. Business Objects universes were mentioned several times as being a very close comparison. Project Crescent will only work against BISM models.
• BISM models will support MDX querying in some cases (see below) but DAX has grown to become a query language. We only had a brief look at it and basically it seems like you use a CalculateTable DAX function to return a tabular result set. You can also define query-scoped calculations just as you do with the WITH clause in MDX today. That’s a gross simplification, but you get the idea. DAX queries do not do any pivoting, so you only get measures on columns; it’s up to the client tool to do any pivoting. It was remarked that this made it much easier for SSRS to consume. SSRS couldn’t deal with multidimensional resultsets, and so instead of fixing this they made SSAS less multidimensional!
• BISM models are massively scalable. They have no aggregations, there are no indexes to tweak, but they demoed instant querying on a 2 billion row fact table on a fairly average server, roughly the same spec that I see most people using for SSAS installations today. They’re achieving massive compression on the data, often anything up to 100 or more times. Of course all the data has to sit in memory after it’s been loaded but they’re going to support paging to disk if it won’t; we’ll also be able to partition tables in the BISM so we can control what gets loaded when. There will also be perspectives.
• Miscellaneous PowerPivot functionality that was demoed included: a nice new window for creating KPIs easily; new DAX functions for flattening out parent/child hierarchies, similar to what the ‘Parent Child Naturaliser’ does today in BIDS Helper (plenty of people, including me, pointed out that this was not proper support for parent/child hierarchies); a new RANKX function for doing rank calculations; Distinct Count will be a native engine feature, and you’ll be able to have as many distinct count measures on a table as you want; drillthrough will also be supported.
• There will be role-based security in BISM, where you can secure either tables, rows or columns.
• BISM models will also be able to operate in ‘passthrough’ mode. This is essentially ROLAP done right, and a lot of work has gone on around this; in Denali it will only be available for SQL Server and only if you’re issuing DAX queries, not MDX. In the future other RDBMSs will be supported, plus possibly MDX querying of BISM when it’s in passthrough mode. Essentially in this scenario when you query the model your query is translated direct to SQL, and the results returned are (as far as possible) passed back to you directly with the minimum of interference. In some cases, for example where there are calculations, BISM will do some stuff with the resultset before it hands it over to you, but the aim is to push as much of the logic into the SQL query that it generates. If it works well, it sounds like at long last we’ll have a serious ‘realtime’ BI option, though I’m still not sure how well it will perform; I suppose if there are Vertipaq indexes inside SQL Server and/or if you’re using PDW, the performance should be good.
• There are only going to be a few improvements for regular, MOLAP-based SSAS – four bullet points in TK’s presentation! They are: the 4GB string store limit has been fixed; we’ll get XEvents and better monitoring functionality; Powershell support; and there’ll be some performance, scalability and reliability improvements.
• BISM will not handle advanced calculations really in Denali. Yes, you’ll be able to do cool stuff in DAX expressions, but you won’t get the equivalent of calculated members on non-measures dimensions (so no time utility dimensions) or MDX Script assignments. ‘Advanced business logic’ is on the roadmap for post Denali, whatever that means exactly; the aim will be to support things like assignments but not necessarily exactly what we have now. To me this is going to be one of the main reasons why people will not adopt BISM in Denali – most enterprise customers I see have pretty complex calculations.
• Role-playing dimensions, translations, actions, writeback and a better API (AMO will still work for creating BISM objects in Denali, but it is going to be difficult to work with and an object model that’s more closely aligned to BISM concepts will be needed) are all planned for beyond Denali.

MS are clear that BISM is the priority now. While MOLAP SSAS isn’t deprecated, the efforts of the SSAS dev team are concentrated on BISM and PowerPivot and we shouldn’t expect any radical new changes. I asked why they couldn’t have just kept SSAS as it is today and bolted Vertipaq storage on as a new storage mode (we will, of course, be able to use SSAS cubes in ROLAP mode against SQL Server/PDW with Vertipaq relational indexes) but I was told that it was seriously considered, but didn’t turn out to be easy to implement at all. The other question I asked was why they are abandoning the concept of cubes and explicitly multidimensional ideas in favour of a simpler, relational model, and they told me that it’s because multidimensionality put a lot of people off; I can see that’s true – yes, a lot of people have been converted to the OLAP cause over the years, but we all know that many relational people just can’t stomach/understand SSAS today. The vast majority of people who use SSRS do so directly on relational sources, and as we know while there’s a great demand for things like Report Builder, Microsoft has had nothing that worked really well to enable end user reporting in SSRS; BISM, as I said, is aimed at solving this problem.

So this is a radical departure for Microsoft BI, one that could go badly wrong, but I can understand the reasoning for it. I’ve been impressed with the technology I’ve seen over the last few days and I know that if anyone can pull this off, the SSAS dev team can. However, the fact remains that in the short term BISM models won’t be able to handle many enterprise projects; SSAS cubes, which can, will be seen as a bad choice because they have no long-term future; and we’re all going to have to tie ourselves in knots explaining the roadmap and the positioning of these products to all of our customers. There’s going to be a lot of pain and unpleasantness over the next few years for me and all Microsoft BI partners. Hohum. As I said, I’ve felt pretty angry over the last few days about all this, but now that’s turned to resignation – I can see why it’s happening, it’s going to happen whether I like it or not, and whether I kick up a fuss or not (I did consider trying to whip up a kind of popular rebellion of SSAS users to protest about this, but doubt it would have had any impact), so I might as well get on with learning the new stuff and making sure I still have a career in MS BI in two or three years time.

What do you think? I would really be interested in hearing your questions and comments, and I know the guys at Microsoft who read this blog would also want to see them too. I’m going to be in Seattle for the next two days and I’ll have the chance to pass on any comments that you leave here to the dev team, although I suspect some of them might be too rude to repeat. I certainly feel better just for having written this post and gotten things off my chest, and maybe you will too.