BI Semantic Model – the new solution for SSAS and PowerPivot

May 28, 2011

TechEd North America 2011 wrapped up last week with big news about PowerPivot and SSAS. Just in case you haven’t read it already somewhere else, I’ll give you a very short re-cap. In SQL Server 2005, the UDM was supposed to encapsulate the solution for modeling your project. In SQL Server 11, codename Denali, it’s going to be replaced by BISM – the Business Intelligence Semantic Model. All the reporting tools in the Microsoft BI Stack will work against this model. In Denali, you will be able to use BISM both for tabular model in PowerPivot and for cubes in SSAS. Furthermore, cubes developed in earlier editions of SSAS automatically become BISM models. SSAS cubes will still have storage in form of MOLAP or ROLAP and queries running against them in MDX. PowerPivot will have VertiPaq or DirectQuery with DAX queries running against them. Crescent, the new reporting tool, uses only DAX at the moment and so will only work against PowerPivot.
Ok, so that was a very short summery of a very important post made by T.K. Anand. I strongly recommend you read his post to get a fuller view of what to expect in Denali, both for PowerPivot and SSAS. Additional links and data can be found on Teo Lachev’s post on BISM.

I guess I would also add the most important point made in Mr. Anand’s post:
“Another limitation in the upcoming CTP release is that models built using the multidimensional project will not support DAX queries (and thereby Crescent, which uses DAX to retrieve data from the model). We recognize that removing this restriction is very important for customers with existing Analysis Services solutions to be able to upgrade to SQL Server “Denali” and leverage Crescent. The product team is actively working on it and is committed to making this functionality available to customers.” Which hopefully means we’ll be able to move easily between tabular projects and SSAS cubes!

Currently, when I open a new project in BIDS, I have to make a choice in advance – do I want a SSAS project or a Report Model 1.0? Granted, I can make that choice as before I start developing I have already decided in my system analysis stage what I need to develop. Will it have a Parent Child Dimension? Many to Many Relationship? A need for a complex calculation? If one of those exists, I will use a cube. But if the solution is simple enough, it’s up to me to choose between Report Model 1.0 and a SSAS cube. Hypothetically speaking, I could use the same DSV for both projects (providing the tables and the relation between them are simple enough). But, as I mentioned, I do need to decide early on what it is that I’m developing.

The ability to move from one solution to the other seamlessly would be amazing! So many projects start off simple, and then, as time passes, get more and more complex… the thought that I wouldn’t have to begin development in the BIDS from scratch if I need to move from a Report Model 1.0 to a cube (or rather from PowerPivot to a cube) is fantastic. And I admit that that may finally put an end to me developing in Report Builder 1.0 and move to PowerPivot. After all, I remember when Ariel Netz talked to me (in not so many words) about Project Crescent as a mean to move developers from using Report Builder 1.0. Crescent does seem to have the functionality that Report Builder 1.0 has (and Report Builder 2.0 and 3.0 lacked). But it only works at the moment with DAX queries against a PowerPivot model. If it could work against a SSAS cube as well – it would be the ultimate solution for me. (Especially as Report Builder 1.0 against a cube works quite poorly). That would be like the final seal for me to know that I need to move my models from Report Builder 1.0 to PowerPivot.

I really feel like the solution is near. First of all, I feel like Microsoft already sees it that way if the SSAS team blog switched from being called “The SSAS Team blog” to “The SSAS and PowerPivot Team Blog”. I don’t take those semantics lightly and I’m sure nobody else in Microsoft does either. Secondly, we know that a tabular PowerPivot model, when deployed to SharePoint, can be viewed as a cube (which also enables Panorama NovaView to work against it). So, I am hoping the solution for is close. Maybe even in SQL Server 2011?…  
In the meanwhile – the future is BISM!

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>