Microsoft’s newest version of SQL Server, previously designated as Code Name Denali, won’t be released until next year, but the good folks in Redmond have made a CTP (Community Technical Preview) available for trial. This is the third and likely last preview prior to the rumored release sometime in the first quarter of 2012. SQL Server 2012’s enhancements are wide ranging, from new T-SQL functionality to the AlwaysOn capability that has DBA’s raving. Most important to my role, however, are the upgrades it brings to Microsoft’s Business Intelligence platform. This will be a two part series delving into more detail on Microsoft’s Business Intelligence Semantic Model and Project Crescent (since renamed Power View), the company’s new web based reporting tool which is a part of Reporting Services that targets the information worker audience that might have a less technical background.
Perhaps the most significant SQL Server 2012 BI enhancement revolves around the Business Intelligence Semantic Model or BISM. Microsoft is billing the BISM in SQL Server 2012 as “one model for all end user experiences.” It allows aspects of the traditional multidimensional model to coexist with the relational model in a format called the tabular model and can be used with all client tools in the Microsoft BI stack.
While the traditional multidimensional model, also known as a cube or UDM (Unified Dimensional Model), has many advantages, it can involve a steep initial learning curve that can deter non IT users from taking full advantage of its functionality. The tabular model has its roots in the relational model and can be easier to comprehend for users without a BI Development background. The tabular model can still be created by a professional BI developer using Microsoft Visual Studio (also known as BIDS or Business Intelligence Development Studio) and Analysis Services as well as by a business user with PowerPivot for excel and then shared using PowerPivot for Sharepoint. The diagram below shows how the flexibility of the tabular model allows for an easy transition between Microsoft’s personal BI tool – (PowerPivot) and their Professional BI tools – (Analysis Services, Reporting Services) and the new reporting tool (PowerView). The end user, depending on the client tool and the needs of the application, can choose between the multidimensional or tabular interface.
As with everything hailed as new or improved, there are downsides. One potential drawback of the new tabular model is the perceived loss of processing efficiency created by the pre aggregation of data and measure creation that gives multidimensional models a performance advantage. Microsoft has countered this by introducing a new columnstore index in the SQL Server 2012 database engine. A columnstore index stores index data in columns, not rows. For example, an index page would be used to store FirstName and another index page would be used to store LastName. If a table’s columnstore index contained 4 columns and a query only needed to return 2 of those columns, then the query could be completed by only hitting the index pages with the 2 necessary columns, not every data page that stores index data as would be the case when using a non-columnstore index. This new index in combination with the VertiPaq engine that was introduced with PowerPivot in SQL Server 2008 R2 allows for higher data compression which in turn allows exponentially more data to be stored in memory. In memory data reduces the need for disk I/O operations and increased performance. Of course, caution needs to be exercised as the use of VertiPaq can lead to higher memory requirements and expenses. For larger datasets, or smaller memory capabilities, an alternative to the VertiPaq engine is the Direct Query mode where the data resides in database tables. This is also known as passthrough mode as the query processing is passed down to the data source. Hybrid VertiPaq/direct query modes can also be used to satisfy a solution’s specific data availability and processing needs.
There are many more features and enhancements in SQL Server 2012 but a deeper dive into those features is out of the scope of this post. The goal of this post was that Microsoft has opened up their Business Intelligence stack to a much wider array of end users. Allowing for the traditional multidimensional model while supplementing it with a tabular model gives businesses implementing a BI solution scalability and flexibility choices they didn’t previously have. SQL Server 2012 shows that Microsoft has devoted significant resources to their BI tools and will continue to not only support but also grow their presence in the BI arena.
Reference: Analysis Services – Vision & Roadmap Update by T.K. Anand; http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx
ICF Ironworks is always on the lookout for experienced professionals who believe in hard work, having fun, and great client service.
Sign up to receive Ironworks' quarterly e-newsletter for the latest news, events, client successes, industry insights and more!
