I recently attended Microsoft’s quarterly Minnesota BI User Group Meeting, and I particularly enjoyed learning from Will Weber (a Data Platform Solution Architect at Microsoft) about their upcoming release of SQL Server 2016. This is the second installment in a blog series that will summarize the various additions and enhancements that Microsoft is introducing to their BI stack from the perspective of a BI developer. Read the first blog post here.
Microsoft SQL Server Analysis Services (SSAS) has been around for almost 20 years. Up until SQL Server 2012, the multidimensional cube was the only Online Analytical Processing (OLAP) offering within the Microsoft BI stack (I discussed the Tabular model and its 2016 enhancements in Part 1 of this blog series). As such, Microsoft has released multiple iterations of the multidimensional cube to develop it into a mature BI product. The past few releases of SQL Server have yielded minimal changes for the multidimensional cube as Microsoft has turned their OLAP focus towards the Tabular model. In fact, it was starting to look like the cube was approaching a dead end on the road to maturity. So why would Microsoft invest time, money, and development resources into the multidimensional cube now in 2016?
The reality is that many organizations of varying sizes, industries, and needs have implemented Microsoft’s multidimensional model in their enterprise analytics platforms. Attracting existing multidimensional customers to upgrade to SSAS 2016 rather than exploring alternative OLAP solutions could be a win-win for Microsoft and these customers. Microsoft can capture new revenue from existing customers, and these customers can easily gain new features and capabilities within their analytics platforms to suit their growing needs. Let’s see how attractive the new features in SQL Server 2016 are:
Drill-through multi-selection support with Excel 2016
With self-service BI capabilities becoming more and more desired within organizations (especially within Excel), any enhancements that expand and improve these capabilities are a win in my opinion. The ability to select multiple values on a drill-through analysis in Excel seems minor but will improve the multidimensional cube’s case when designing a self-service BI solution.
Distinct count ROLAP optimization for DB2 and Oracle
Within the traditional SSAS cube, the default and most common storage mode utilized is Multidimensional OLAP (MOLAP), in which detail data and pre-aggregated data are stored on disk on the SSAS server. However, in cases where an organization’s data volume exceeds the disk storage capacity on the SSAS server, the Relational OLAP (ROLAP) storage mode can be used. The SSAS cube establishes a direct connection to a relational data source in this storage mode rather than storing any data on the SSAS server. As a result, querying an SSAS cube with ROLAP storage can be slow. SQL Server 2016 provides improved performance on distinct count calculations for SSAS cubes accessing DB2 and Oracle relational data sources in ROLAP storage mode. While addressing performance issues of the ROLAP storage mode will improve its viability and usability, this enhancement serves only a subset of Microsoft’s customers.
Explore and visualize with Power BI
Initial releases of Power BI (Microsoft’s cloud-based visualization and reporting tool) did not support connecting to SSAS multidimensional cubes as a source. The presentation that Microsoft gave at the Minnesota BI User Group meeting marketed support for SSAS multidimensional cubes in Power BI as a new feature of SQL Server 2016, which means organizations can finally explore and integrate their SSAS cube data within their Power BI visualizations. This led me to believe that Power BI only connects to SSAS 2016 cubes, however have no fear if you have an earlier version SSAS cube! According to a Power BI Admin, Power BI supports connecting to SSAS cubes implemented in SQL Server 2012 SP1 CU4 and later versions. Therefore, it is misleading to classify this as a new feature of SQL Server 2016.
In summary, really only the first two SSAS cube features above can be considered new within SQL Server 2016. These features will certainly add value in very specific use cases, but the incremental value added to the majority of organizations currently using SSAS cubes will be minimal. I guess this is to be expected for an already very mature software product. For an organization deciding whether to upgrade to SQL Server 2016 solely on the premise of the SSAS cube enhancements, it should be a quick evaluation to see whether the first two features above will provide justifiable value.
For organizations who are seeking to implement a new OLAP solution, there are a couple key use cases to consider for Microsoft’s multidimensional cube. Within Microsoft’s OLAP solutions, the SSAS multidimensional cube offers the most scalability as it is well equipped for very large volumes of data. Additionally, the multidimensional cube is the best OLAP solution within the Microsoft stack for handling complex business problems. It supports complex data relationships out-of-the-box and supports complex calculations (among many other capabilities).