The Future of Microsoft BI

  • Article

PASS  collided with TechEd Berlin this year so I only got to see all the Denali announcements, on the video streams.  Business intelligence (BI) is once again front and centre and while there are  significant improvements to scale and performance, the main effort has been to improve agility and ease of design to both reporting and analysis.  This is most obvious in the new BI Semantic Model (BISM) which is a three layer mechanism for describing data and business logic to be consumed in Excel, Reporting Services and SharePoint, the three layers being:

  • the Data Access Layer which describes where the data comes from
  • the Business Logic Layer with all the calculations in as well as describing the hierarchies in the data (e.g. week –> quarter –> year –> all years)
  • the Data Model consumed by the end user tools

This approach is an extension to PowerPivot introduced in SQL Server 2008 R2, and is complimentary to but not a replacement for the existing analytical engine in current and previous editions of SQL Server namely Analysis Services (SSAS) and its associated Unified Dimensional Model (UDM). You maybe wondering why there are two different methods for doing BI as per the diagram below where the new BISM is on the left and the SSAS/UDM is on the right ..

This is simply a recognition that there is a tradeoff between ease of use and complexity.  On the one hand large and complex businesses like Yahoo need BI solutions to model this complexity and and scale so they have developed cubes in SSAS which can handle this.  However these require considerable expertise  and effort to create. On the other hand even in the first iteration of PowerPivot is is fairly easy for an end user (or information worker as Microsoft calls them)  to create a large model with some complex logic in it from a variety of sources.  In Denali some of the design work will be more of an IT function  for example the work needed in the Data Access Layer of BISM and the whole design experience will be in a new Visual Studio 2010 (project Juneau).  However the BISM design experience will be far more collaborative and I would expect the business of designing hierarchies and building calculations to be more information worker responsibilities wiht the IT team focusing on data qulaity and the data access layer.

I think this will help to make analytics more pervasive, both to a new audience of users and to non BI IT Professionals who have found analysis services difficult to get to grips with.  This is a good thing and the skills learnt here will still be applicable to SSAS.

For the definitive word on the details of BI in Denali You should refer to this post on the Data Platform Insiders blog.