Determining whether multidimensional or tabular is the optimal solution for your organization is a key step when working with SQL Server Analysis Services 2012/2014/2016. During last week’s free webinar, our Senior Consultant Alan Faulkner introduced the common pitfalls with each solution and how to avoid them. He also highlighted new features of SQL Server Analysis Services 2016 and how they play a role in deciding the best solution to use.
Expanding on his presentation, Alan has addressed additional questions to help provide a clearer understanding of the strengths and weaknesses of each model and how your organization can leverage the strengths of SSAS 2012/2014/2016.
Based on the proven analytics engine in SQL Server Analysis Services, Azure Analysis Services is an enterprise grade OLAP engine and BI modeling platform, offered as a fully managed platform-as-a-service (PaaS). Azure Analysis Services enables developers and BI professionals to create BI semantic models that can power highly interactive and rich analytical experiences in BI tools (such as Power BI and Excel) and custom applications.
Azure Analysis Services is currently in preview and supports tabular models at the 1200 (SSAS Tabular 2016) compatibility level only. DirectQuery, partitions, row-level security, bi-directional relationships, and translations are all supported.
A clustered instance of SSAS is the full installation of Analysis Services. That means everything that you can do in a stand-alone instance can be done with the clustered instance, regardless of whether the server is installed in tabular or multidimensional mode.
For example, SSAS databases on a cluster can use any storage mode, including DirectQuery or ROLAP. Similarly, for multidimensional solutions, feature behaviors that reach back to the external data source, such as drill through and write-back, are supported in an SSAS cluster.
Don’t mix tabular and multidimensional server modes in the same clustered instance. SSAS can be installed in either tabular or multidimensional mode, and in a cluster, all nodes must support the same operational context.
There are two methods for making SSAS highly available: load balancing or using a Windows Server Failover Cluster (WSFC). While both ways achieve the desired end result, the configurations are very different.
More often than not, load balancing is considered the best approach for SSAS. You can use either the built-in Windows load balancing feature called Network Load Balancing (NLB) or a hardware-based load balancer.
A calculated table is a computed object, based on either a DAX query or expression, derived from all or part of other tables in the same model.
This feature is only available in SSIS Tabular 2016 (Compatibility Level 1200).
In the context of the presentation, it was provided as one of the options where an instance of SSAS can be installed.
Relevancy would be dependent upon if SharePoint and the use of PowerPivot is part of architecture within your environment.
We had an estimated 440 people attend the webinar. There were 212 people or about 48% who responded to the polling question – “Are you currently using multidimensional or tabular?”
Multidimensional models would implement a date dimension table within the relational dimension model. In multidimensional models, date tables often are considered as a ROLE PLAYING DIMENSION. A role playing dimension is a single dimension that can be referenced multiple times in a fact table, with each reference linking to a logically distinct role for the dimension.
For instance, a fact table can have several dates, each of which is represented by a foreign key to the date dimension. It is essential that each foreign key refers to a separate view of the date dimension so that the references are independent.
Tabular models support the idea of ROLE PLAYING dimensions but is a little different than in SSAS multidimensional. In a multidimensional model, I can have three relationships from my fact table (OrderDateKey, ShipDateKey, DueDateKey) to a single date dimension (DateKey). All of these relationships would be “active”. However, with this implementation in a SSAS tabular model you can have only one active relationship at a time, while the others are inactive.
Inactive relations can only be accessed using DAX expressions, the syntax used in tabular model. This means that if we create three relations between the ‘Internet Sales’ table and the ‘Date’ table, only one would be available for drag-and-drop in pivot tables inside Excel, the other two would only be accessible through custom DAX expressions.
The alternative would be to implement two additional copies of the date dimension and naming it according to the ROLE (Order Date, Ship Date, Due Date) it plays.
In a tabular model this would be a good use of a calculated table.
It is important in a tabular model to specify (mark) the table you will be using as your date dimension as ‘Date Table’. This is necessary for the DAX TIME INTELLIGENCE functions to work properly in a tabular model. You must also specify the column that will be used as a unique identifier for the date table. The selected column must be of the date data type and must contain unique values.
Both the presentation and copy of the decision matrix can be found on my blog at falconteksolutionscentral.com
Yes, the queries are sourced from the connections that are present within the PowerPivot solution. Therefore, when performing a data refresh on the tabular cube, these queries are executed and the data is refreshed.
The important thing to remember is that the service account that is used to run the tabular service must have READ access to any data source referenced in the PowerPivot workbook. Otherwise, you will likely receive errors indicating that the service account does not have access to the data source (an SSAS cube).
A KPI in a tabular model includes:
More than likely, you will be creating a calculated measure using the DAX (data analysis expression) language for the base and target values.
Since KPIs must be associated with a measure, you create a KPI by extending a measure that evaluates to a base value, and then either creating a measure that evaluates to a target value or by entering an absolute value. After the base measure (value) and target value is defined, you can then define the status threshold parameters between the base and target values.
The status is displayed in a graphical format using selectable icons, bars, graphs, or colors. The base and target values, as well as the status can then be added to a report or PivotTable as values that can be sliced against other data fields.
To ‘dimensionalize’ a calculation may be possible in SSAS Tabular, but you would have to write a DAX statement in such a way that the measure evaluates properly each time. You could potentially use a KPI configuration table approach by which it may allow for dynamically assigning values based on your specific needs. I do not believe that SSAS Tabular 2014 dynamic KPI configuration exists.
With the latest release of SQL Server 2016, Microsoft made significant investments into SSAS Tabular. I think an indicator of Microsoft’s trend with SSAS can be evaluated from the perspective of what are they doing with it in Azure. Azure Analysis Services is currently in preview and supports tabular models at the 1200 (SSAS Tabular 2016) compatibility level. DirectQuery, partitions, row-level security, bi-directional relationships, and translations are all supported.
There hasn’t been much improvement to SSAS Multidimensional in the last several releases (SQL Server 2012 – 2016). However, I love SSAS Multidimensional, but that doesn’t mean it is always the best fit.
I recently read a post of a person who was upset about the fact that they had made significant investments into SSAS Multidimensional and it seemed that Microsoft was no longer investing in upgrading it (like they have with tabular).
Again, I think it comes down to several factors, but if I had to make a recommendation I would start with SSAS Tabular first if there has been zero or very little development of SSAS Multidimensional in your environment.
Additionally, look at some of the factors covered in the webinar to make sure (depending on your SSAS version) the solution you provide will be able to meet the requirements.
Interested in expanding your knowledge on your own terms?