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 blog series will summarize the various additions and enhancements that Microsoft is introducing to their BI stack from the perspective of a BI developer.
Microsoft first released the SQL Server Analysis Services (SSAS) Tabular server mode with SQL Server 2012. Before that, the SSAS Multidimensional server mode was the only online analytical processing (OLAP) solution within the Microsoft BI stack. The Tabular model offers an in-memory, high performing OLAP solution. It is often used for ad-hoc analysis and reporting within tools such as Excel or Tableau for an organization’s BI power users, but it can be used for a variety of analysis and reporting use cases. After not seeing much improvement in its second release in SQL Server 2014, the Tabular model was certainly due for a facelift. Here is a summary of the most exciting additions and enhancements that Microsoft is rolling out for the Tabular model in SQL Server 2016:
Out-of-the-box support for many-to-many relationships
Unlike SSAS Multidimensional models, Tabular models in SQL Server 2012 and 2014 did not easily support the modeling of many-to many relationships. Since many business models inherently have many-to-many relationships in their data, this limitation added complexity to Tabular modeling and increased the development effort. Previously, a lot of fancy and complex Data Analysis Expressions (DAX – the language used to define logic and query Tabular models) were needed to ensure that measures and calculations worked properly for many-to-many relationships. This greatly impacted query performance. In SQL Server 2016, Microsoft has added bi-directional relationships in the Tabular model to support many-to-many relationships. This enhancement will allow Tabular developers to easily define many-to-many relationships, thereby decreasing the development and testing effort needed in a Tabular implementation.
Built-in actions and translations
SSAS Multidimensional models in SQL Server 2012 and 2014 also topped Tabular models by having out-of-the-box actions and translations. In order to gain these capabilities in a Tabular model, you needed to install third party software such as BIDS Helper on your SSAS server. In SQL Server 2016, these features will come built-in for the Tabular model. This will reduce the time and complexity to spin up an advanced SSAS Tabular instance and eliminate the reliance on third party software for these features.
50+ new DAX functions
Microsoft has added over 50 new DAX functions in SQL Server 2016. These additions range from all kinds of statistical and mathematical functions (for the data scientists out there) and various new table functions such as UNION. These new formulas will hopefully expand the use cases for a Tabular model and allow for more advanced analytics beyond your standard sums, counts, and averages.
Previously in SQL Server 2012 and 2014, the concept of a calculated table did not exist in Tabular models. Just about every organization’s data contains multiple dates within their transactional systems: start dates, end dates, process dates, ship dates, receive dates, visit dates, and many more. Therefore, in order to create role-playing dimensions to model all of these possible date analysis scenarios, the same date dimension had to be created in the Tabular model for each scenario. This hurt model processing performance and increased model maintenance effort. Another option was to create a single date dimension in the Tabular model, create multiple role-playing relationships between the one date dimension and the fact tables, and again, write a lot of fancy and complex DAX statements to activate/inactivate the various relationships depending on the measure or calculation. With the addition of calculated tables in SQL Server 2016, additional tables can be derived from an existing table within the Tabular model by using a DAX table function. For example, in order to create role-playing date dimensions in SQL Server 2016, you would create a single date dimension in the model and then create calculated role-playing date dimensions off of the single date dimension. This will still add to model processing time, however the model will not have to go back to the data source to create each role-playing date dimension since they are calculated off of the original. This will also improve model maintenance if say a new date attribute needs to be added to the model – only the original date dimension will need to be updated as opposed to updating every role-playing date dimension. With the new DAX table functions such as UNION, these new calculated tables can satisfy lots of different use cases.
New and simpler scripting language
If you’ve ever tried to understand XML for Analysis (XMLA) to script out deployments or processing for a Tabular model like I have, then you’ll probably be very excited about this change. Although they might be lacking creativity in the naming, Microsoft has replaced XMLA with Tabular Model Scripting Language (TMSL). I have yet to mess around with TMSL myself, however I am excited about the replacement of XMLA. TMSL is a JSON based scripting language, which seems like a great move considering the variety of programming disciplines and languages that involve JSON.
Microsoft seems to have hit the nail on the head on addressing some of the Tabular model’s biggest limitations and pain points. Only time will tell if these additions and enhancements will continue to encourage organizations to consider, implement, and adopt it as an OLAP solution.