Introduction

In Part I of the SSAS Tabular vs. SSAS Multidimensional – Which One Do I Choose? series, we began to set the stage for looking at 5 high level considerations to answer this question.  In Part II, we explored the Scalability and Performance considerations between the two technologies.  In this post we will contemplate the Complex Business Problem aspects by comparing and contrasting SSAS Tabular and SSAS Multidimensional in terms of characteristics of the data and the model required to deliver the analytics and reporting required.

Data Model – Data Relationships

The characteristics of your data model are a core consideration in your choice of modeling experience.

A fundamental requirement of any data model is correctly representing how the data elements within that model interrelate and connect, much like the pieces of a jigsaw puzzle. Both tabular models and multidimensional models require you to define relationships among your source data tables. Common relationships that you see in data modeling are one-to-many, many-to-many, and reference relationships.

Data Relationships – One-to-Many

 

In a one-to-many relationship, a single record from one table relates to multiple records from another table.

An example of a one-to-many relationship is a customer who has multiple sales orders.

Both tabular and multidimensional data models natively handle one-to-many relationships.

 

Data Relationships – Many-to-Many

In a many-to-many relationship, many records from one table relate to many records from a second table. For example, a single customer has a one-to-many relationship with sales orders; however, each customer can be categorized into one or more customer profiles (such as Sports Enthusiast, Casual Gamer, and Fitness Expert.). Analyzing orders by customer profile is a many-to-many challenge where double-counting may occur: An order for one bicycle by a customer who is both a Sports Enthusiast and a Fitness Expert could easily be counted twice when orders by customer profile are summed to get total orders. Typically many-to-many relationships are managed by breaking them down into two one-to-many relationships using a bridge or intermediate table.

In multidimensional models, you can define and build many-to-many relationships directly in the data model by identifying the bridge table and then relating that bridge table to other tables in your model. When aggregating, Analysis Services applies a distinct sum to ensure that data totals are correctly summarized and not improperly inflated.

SQL Server 2012 Analysis Services tabular models do not natively support the definition of many-to-many relationships. However, you can use the DAX language to create formulas that handle the many-to-many challenge.

Data Relationships – Reference Relationships

A data model may contain a set of common attributes that are related to multiple entities. For example, geographical attributes are related to customers, suppliers, and stores. In multidimensional modeling you must create a dimension containing the common attributes and then create reference dimension relationships to each of the related dimensions.

In tabular modeling there is no need to create reference relationships. In a tabular model, all you need to do is create relationships between the table containing the common attributes and the tables containing the related entities.

 

Hierarchies

Hierarchies categorize data into a tree structure to facilitate drill-down analysis.

Standard Hierarchies

Standard hierarchies are composed of ordered levels that come from columns in your source data. For example, a product hierarchy may organize products into subcategories, which can be further organized into categories. In this case, you would have a hierarchy with three levels, where each level comes from a separate column in your source data. Simple hierarchies like the product hierarchy described here are supported in both tabular and dimensional models.

Note that within multidimensional models, there is an added step of creating attribute relationships, which is explicitly identifying the one-to-many relationships between attributes in each dimension. Defining attribute relationships is strongly recommended because they enable more efficient design of pre-calculated aggregations and because MDX semantics rely on attribute relationships. Tabular modeling is more straightforward because you do not create attribute relationships. Tabular models do not pre-calculate aggregations and DAX semantics do not rely on identifying the one-to-many relationships between attributes, so in tabular modeling there is no equivalent to multidimensional modeling’s attribute relationships.

Ragged Hierarchies

Ragged hierarchies occur when a given data element is missing in the hierarchy tree. For example, a ragged product hierarchy occurs if there are products that are never assigned a subcategory, yet they have product category assignments. In these situations, rather than showing the gap in the tree, you may choose to hide the gap to facilitate drill-down analysis. Multidimensional models provide out-of-box support for ragged hierarchies; however, tabular models do not natively support this capability.

Parent Child Hiearchies

Parent-child hierarchies offer a more complicated hierarchical design. The branches in a parent-child hierarchy don’t all have the same number of levels. For example, a parent–child relationship between employee and manager could produce a hierarchy in which some managers only have direct reports while other managers have direct reports who each also have their own direct reports. This kind of hierarchy is modeled by creating a relationship between two columns in a source data table

Multidimensional models provide out-of-the box functionality that enables you to define and build parent-child hierarchies based on relationships in your source data.

In tabular models, you can leverage DAX functions to create formulas that navigate and use the parent-child structure in calculations.

FYI – I am working on a post to demonstrate how this can be handled in a tabular model.  In the meantime, there is a tool I wanted to recommend if you are facing this issue.  The tool is BIDS Helper that is available at bidshelper.codeplex.com.

Data Relationships – Additional Modeling Features

In addition to data relationships and hierarchies, there are additional modeling features that can help you choose the best modeling experience.

Perspectives enable you to define a subset of a data model for simplified browsing by end users. Perspectives are available in both multidimensional models and tabular models.

Translations enable multidimensional models to display dimension, attribute, measure, calculated member, and other object names and dimension member values in the language specified by the computer’s localization settings. Enabling this feature requires the model developer to provide the translated object names and to reference the columns in the source data that contain the translated dimension member values. Tabular models do not provide this functionality.

Actions empower end users to run a Reporting Services report, navigate to a URL, or initiate an external operation based on the context of the cell where the action occurs. For example, using an action, an end user can launch a webpage that displays the company’s product catalog automatically filtered to the product or products that the user was browsing. Actions are natively supported in multidimensional models and many client tools (like Excel and Reporting Services) allow users to execute actions. In SQL Server 2012 the ability to create actions in a tabular model using SQL Server Data Tools is not natively supported.

Drillthrough enables you to navigate to the detailed data stored in your model. Drillthrough is available in both multidimensional and tabular modeling. Multidimensional models also enable you to create drillthrough actions so that you can customize the drillthrough experience by specifying the columns that will be returned by the drillthrough action and the cube space where the action will be enabled.

Write-back is a feature that is typically necessary in budgeting and forecasting applications. In these scenarios, business users typically want to perform “what-if” analysis where they change and update data values in the model and then publish those for others to see. Multidimensional models provide native support for data write-back. In SQL Server 2012 tabular models do not support this functionality.

 

Complex Business Problems Summary

In this post we have explored the Complex Business Problem aspects by comparing and contrasting SSAS Tabular and SSAS Multidimensional in terms of characteristics of the data
and the model required to deliver the analytics and reporting required.

From a data modeling perspective, the edge goes to SSAS Multidimensional.

 

Conclusion

It is important to take the time and consideration to determine what kind of data modeling capabilities are necessary to deliver the desired analytics and reporting capabilities that need to be provided by your SSAS solutions.