Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
At the TechEd North America 2011 conference in Atlanta this week, the SQL Server team is providing details on the SQL Server “Denali” release, including the upcoming public availability of a Community Technology Preview (CTP) this summer. As part of these announcements, we are providing an update on the SQL Server Analysis Services roadmap that was disclosed during the PASS 2010 conference last November (detailed here). In the past six months, we have been talking to many people in the Microsoft BI community – customers, partners, developers, and MVPs – to get feedback on the roadmap. We are seeing lots of excitement about the breakthrough innovations coming in SQL Server “Denali” and the new class of BI applications that it will enable. At the same time, we have learned that we need to be clearer about how customers can carry forward their existing BI applications and benefit from these innovations. Based on this feedback from the community, we’ve made a couple of adjustments and clarifications to the roadmap and vision, outlined below.
Analysis Services is an industry leading BI platform and the most popular OLAP engine in the market (see Gartner Magic Quadrant for BI Platforms). It has a large ecosystem of developers, partners, BI tools, and solutions, and it has broad adoption from small businesses to large enterprises. It is the foundation of the Microsoft BI stack, serving as the semantic model for Microsoft BI tools including Excel, SharePoint Insights and Reporting Services. Our vision is to build on the strengths and success of Analysis Services, expand its reach to a much broader community, and enable the next generation of BI applications. We will do this by embracing the relational data model and bringing it together with the multidimensional model under a single unified BI platform – providing the best of both worlds. Analysis Services, by virtue of its OLAP roots, has tremendous strengths in multidimensional modeling which is capable of tackling the most advanced of BI applications. On the other hand, the relational data model is widely understood and accepted by developers and IT professionals around the world. By bringing the two data models together, we will provide a powerful yet flexible platform that can tackle the diverse needs of BI applications – needs such as advanced analytics, sophisticated business logic, professional developer tools, choice of end user tools, performance, scalability, ease of use, and time to solution. The Business Intelligence Semantic Model is being introduced in Analysis Services for SQL Server “Denali” with the goal to fulfill this vision.
The BI Semantic Model is one model for all end user experiences – reporting, analytics, scorecards, dashboards, and custom applications. All client tools in the Microsoft BI stack – Excel, PowerPivot, SharePoint Insights and Reporting Services (including Crescent) – operate on this model. BI professionals can create the model in Visual Studio and deploy it to an Analysis Services server. Interestingly, the model can also be created by business users with PowerPivot for Excel and shared via PowerPivot for SharePoint. PowerPivot and Analysis Services are two different products; one targets business users and the other targets BI professionals and IT. However both use the BI Semantic Model under the covers. In the case of PowerPivot, the model is embedded inside an Excel workbook. In the case of Analysis Services, the model is stored and managed on the server. Having the same model behind these experiences enables seamless transition of BI applications from Personal BI to Team BI to Organizational/Professional BI. For example, a PowerPivot workbook built by a business user can be used as a starting point by a BI professional building an Analysis Services application. The SQL Server professional tools (Management Studio and BI Development Studio) make it very simple to import a PowerPivot workbook into Analysis Services.
Before we get into the details of the BI Semantic Model, let me first address an important question: What about existing Analysis Services cubes (also known as Unified Dimensional Model or UDM)? The answer is very simple: when you upgrade your Analysis Services project or server to SQL Server “Denali”, every cube automatically becomes a BI Semantic Model. The BI Semantic Model is an evolution of the UDM from a pure multidimensional model into a hybrid model that offers all of the capabilities of the UDM and more. As such, the term Unified Dimensional Model is no longer used with SQL Server “Denali” and is subsumed by the BI Semantic Model. This is a key difference in the roadmap from what was disclosed last November at PASS 2010.
Now let’s get into the details of the BI Semantic Model – how to build a model and the technology that powers the model. The BI Semantic Model can be viewed conceptually as a three-layer model:
As you can see, the BI Semantic Model is a rich, scalable, and flexible model. The richness lies in the sophisticated data modeling capabilities and complex business logic that can be expressed using MDX and DAX. The performance and scalability comes from the MOLAP and VertiPaq storage engines that enable data volumes all the way from a few megabytes up to multiple terabytes. Flexibility is probably the most interesting aspect of the model. Model developers can choose between the multidimensional and tabular data modeling experiences, between MDX and DAX for business logic, and between cached and passthrough modes for data access and storage. Regardless of the model developer’s choices, client tools see a single model and can consume it using the multidimensional or tabular interfaces and send MDX or DAX queries.
SQL Server “Denali” is the first release on the Analysis Services roadmap towards realizing the BI Semantic Model vision stated above. As such, there will be some limitations in this release in the flexibility we offer to model developers. BI Development Studio comes with two projects for building a BI Semantic Model – a multidimensional project and tabular project. The multidimensional project lets model developers use the multidimensional modeling experience along with MDX and MOLAP/ROLAP (this is what existing UDM projects get upgraded to). The tabular project lets model developers use the tabular modeling experience along with DAX and VertiPaq/DirectQuery. It is important to note that these restrictions in the two projects are not rigid or permanent; they could very well change in future releases based on customer requirements. For example, we could offer model developers VertiPaq as a storage option for multidimensional projects or MDX scripts for tabular projects. 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.
Finally, I’d like to invite you to try out the upcoming Community Technology Preview (CTP) of SQL Server “Denali” and send us your feedback. The product team has been working very hard on it over the past year and we are excited to share it with you. You can check for the availability of the CTP by visiting http://www.sqlserverlaunch.com. In the upcoming months, we will be putting out blog posts and whitepapers that dive into the details of the BI Semantic Model and provide best practices and guidance on building and deploying models. The Microsoft BI community has been instrumental in helping us shape our product vision and strategy and we look forward to your close engagement and partnership as we look to redefine the BI landscape with these new innovations in SQL Server “Denali”.
T.K. AnandPrincipal Group Program ManagerSQL Server Analysis Services
Now, OLAP community gain confidence over the changes happening in the SSAS or BI world. Because, I believe this is BI evolution.
Very good overview of the vision and roadmap. Is there a chance to start with BISM and then migrate to an Analysis Services cube? My idea is to reopen a BISM model in Analysis Services and it gets transformed to an UDM model? If this is possible, will this only be possible for multidimensional BISM model or also a tabular moel?
Extremely boring blog post. Microsoft must be desperate having so many technologies floating around. Maby in a few years they'll know which direction to take? I'm sure the lot of tech-kids are excited but in my opinion the technology itself is not worth wasting time on.
That's a nice post for a technical issue that requires deep tech information. Since BISM supports both multidimensional and tabular data modeling, and you mentioned in the auto-upgrade of SSAS UDMs to BISM, are we going to offer something similar for Report Models?
Interesting evolution of BISM - I wasn't quite sure from the latter part of the post if the limitations in flexibility for model developers applied only to the next CTP release of 'Denali' or to 'Denali' as a whole?
This is great news - the slight change in direction to make all existing UDM OLAP cubes BISM is huge and welcome. Thanks to the team for listening to the feedback. And +1 for gettting Crescent to work against multi-dimensional models.
It kind of feels like with Denali we now truly have the "Universal" part of UDM.
Mmm ,looks ans smells like more or less the same concept as a BO universe to me
Microsoft just buy Tableau Software and you can have world domination wit the perfect BI stack.
Thanks for sharing the details. Also watched your presentation from Tech Ed and lots of cool features. Its going to be next generation BI. Looking forward to the next release.
T.K. Anand this is a great blog. Thank you for making it sound so clear!
Hi, do we have any way to Test the Performance of BISM model from peroformance testing point of view ? Thanks !
This post is right at a year old now. Is there an updated vision, or any changes now that Denali has been available for a while?
Well, now the vision turns into some reality : PowerView can access "Classic" Multidimensional cubes . AVAILABLE NOW! : blogs.msdn.com/.../power-view-connectivity-for-multidimensional-models-released.aspx
This post is now over two years old. Is this still the best expression of the vision & roadmap for SSAS?
Hi, very good article. However, I am unable to find good KBs or blogs on SSRS and Windows 8 Mobile deployment. Do you have any resources which i can look into?