Amit (BI Tech Lead): We have a large Multi-Dimensional Cube (MDM) based BI solution. With the advent of tabular models, should we convert our MDM cube to tabular analytical models?
Annie (Power User): I am familiar with data and attributes. Can the technology team quickly provide me with the mechanism for self-service BI?
Mark (in the process of designing a BI solution): With options among MDM, tabular and PowerPivot, which analytical data model should I leverage?
For over a decade, the BI world has been relying on dimensional modeling and using SSAS cubes for analytical models. With the introduction of semantic modeling, the entire paradigm of analytical modeling has changed; but does it imply that this is the end of the road for SSAS MDM cubes?
Let’s dig deep into the different analytical methodologies Microsoft provides and try to answer the above questions. I will divide this article into three parts:
Timeline: Microsoft Analytical Modeling History
Comparison of features : Solutioning Perspective
Timeline: Microsoft Analytical Modeling History
Let’s dwell in the past and understand how analysis services have evolved over time
1998: Microsoft entered the analytics domain by releasing SQL Server Analysis Services (SSAS) with MDX as query language under SQL Server 7.0
2000: Released an improved version of SSAS with SQL SERVER 2000 but had major business implementation hurdles – e.g. only one fact table per cube was allowed
2005: SQL SERVER 2005 had an enhanced version of SSAS OLAP with data mining capability, adding support to advance dimension types. This was rolled out as Unified Dimension Modeling. SSAS 2005 was a well-received product and soon it started to host large MDM models.
2008: SQL SERVER 2008 added enhancements to Data Mining but no major upgrade to SSAS was introduced.
By 2010, SSAS made a strong base in analytical capabilities and provided a robust tool to build enterprise level multi-dimensional analytical solutions. But along with its great features, it had a few shortcomings:
Developer learning curve was steep
SSAS MDM solution implementation had a long turnaround time
Power users where dependent on the technology team to create the cube and expose data
2010: PowerPivot was released as an Excel add-in, which was a significant stride in Microsoft’s entrance into the self-service BI paradigm. PowerPivot enabled users to create their own data models (Mini-Cube) inside a familiar Excel environment. It offered users the ability to create custom hierarchies, relations, and custom KPIs with ease. PowerPivot could be e-published on SharePoint and allowed other users to interact/consume the same model.
2012: With the success and development interface ease provided by PowerPivot, Microsoft released the SSAS Tabular Model with SQL SEVER 2012, which provided the same interface used by PowerPivot inside SSDT, and allowed creating the server side analytical database.
The tabular model delivered the following advantages:
Ease of the development interface: The tabular model shared SSDT as the development environment but provided a PowerPivot style interface, which resulted in faster development cycles
Methodology similar to relational modeling: The tabular model took a step away from dimensional modeling and increased the proximity to relation modeling, which lead to a faster learning curve
In-memory capability: With the xVelocity engine, now the model could achieve a 10:1 compression ratio and reside in-memory all the time. This enabled large data sets to be heavily compressed and fit inside the memory.
Azure Machine Learning (AzML) caters to predictive analysis and supports R language. It can be exposed as Public REST APIs for external consumption. AzML enables you to build, test, and deploy predictive analytics solutions that operate on your data. The machine learning service and development environment is cloud-based, provides compute resource and memory flexibility, and eliminates setup and installation concerns because you work through your web browser.
Azure Stream Analytics is an event processing engine that helps uncover real-time insights from devices, sensors, infrastructure, applications and data. It will enable various opportunities including Internet of Things (IoT) scenarios such as real-time fleet management or gaining insights from devices like mobile phones or connected cars.
The Key Differences between Analytical Models
With the evolution of analysis services, the Microsoft BI suit now offers three paths to create an Analytical Model
PowerPivot published on SharePoint
SSAS Tabular Modeling
I will list down the key differences among them from the perspective of solution development:
It is apparent that the focus and vision of the Microsoft BI Development team is on In-Memory and Easy-to-build Analytics with respect to the SSAS Tabular model and PowerPivot. But with the current feature and functionality set, the SSAS MDM model still holds its position as a front runner and is here to stay, at least for a few years more.
From my experience, I can say that most of the needs of the analytical models can be addressed by current feature sets provided by tabular models, and the benefits provided by the tabular model such as In-Memory and Easy-to-Build, can be leveraged.
PowerPivot could be considered a Team BI Solution catering to Power Users, providing self-service BI capabilities.
Before going into a project, I recommend looking into all the features provided by the three methodologies and then picking the one that suits best.