ds-blog-icon.png

THE DATA DOWNLOAD

As a business intelligence consulting company, we pride ourselves on being able
to deliver on our projects as well as provide good quality content to our readers.

Webinar Q&A: SSAS for Tabular Cubes

Posted by Greg Meehan on Jan 22, 2018 11:38:10 AM

View the Q&A discussion from our SSAS for Tabular Cubes webinar. The audience asked insightful questions, focusing on this Microsoft's modeling offering for Self-Service and Gold Standard reporting, and how this technology compares to others. 

SSAS_webinar.png

If you would like to view the entire webinar, continue to the research center under webinars.

PLAY WEBINAR

 

1. Is this somewhat analogous to TM1?

The only aspect that’s analogous to IBM Congos’ TM1 application is the option to use Microsoft Excel to interact with underlying source data. TM1 is an enterprise planning application used to help facilitate and collaborate financial budgeting, planning, and forecasting activities. It’s designed specifically to help enhance the planning process whereas Microsoft SSAS Tabular is intended to support broader analytic needs across any number of business units. The underlying technology is also different, where TM1 is based off multi-dimensional (MDX) source data structures and SSAS Tabular leverages in-memory capabilities (and the DAX language).

 

2. Please speak to aggregation, custom assemblies, custom rollups, and many-to-many limitations of Tabular versus multi-dimensional OLAP cubes.

Aggregations – These are not supported in Tabular, but are also not necessary. No special performance tuning using Aggregations or Indexes is required for query performance in Tabular and that is one of its greatest benefits.

Custom Assemblies – Custom functions or other functionality using Custom Assemblies is supported in Multidimensional but not Tabular at this time.

Custom Rollups – This technique for creating user-defined groups is available in Multidimensional but not currently so in Tabular.

Many-to-Many Relationships – OLAP has native support for Many-to-Many relationships and Tabular does not. However, this article lays out how to effectively implement this structure using DAX.

For a full list of feature comparisons, please see this article from Microsoft.

 

3. You mentioned the 'views' tabular cubes depend on. Could you please specify the purpose of that view layer?

Using views allows you to implement standards and business logic beyond the DW tables that is nimble (you don’t need any ETL changes to modify your views) and can be a single layer for changes across all cubes. We recommend this approach whether using SSAS Tabular, SSAS Multidimensional or other semantic layer tools like IMB Cognos Framework Manager or SAP Business Objects Universe Designer.

Specifically for SSAS, Microsoft recommends defining your friendly names at the view layer. For example, if your DW field name is CUST_NM, you make your view column name ‘Customer Name’ and all cubes will pick that up from one source. This is faster and a better standard than using the SSAS Data Tools. Further, if you have another tool you are leveraging for other departments like Tableau (it has limitations with cubes as a data source that it does not have with relational databases), you will easily retain the standard names.

Additionally, the views can be helpful for implementing things like flags for grouping useful business entities together for user ease in report filters. This approach often has the added benefit of enhancing performance by simplifying execution plans based on simple, binary filter conditions.

 

4. Can you give an example of the kinds of extra ETL required for Tabular vs. OLAP?

This comes down to the developer’s comfort and expertise with DAX and MDX, but one scenario we found where it was easier to move logic down to the ETL layer was allocations that involved complex logic across several entities. While possible in cubes, this involves complex syntax and can sometimes result in a performance degradation versus just referencing a pre-calculated column from the data warehouse.

Another example that is more specific to OLAP versus Tabular would be trying to implement custom rollups as mentioned above. Since Tabular does not natively support custom rollups, logic could be applied in the warehouse representing the needed columns and relationships and could then be exposed as required in the views and cube using SQL and or DAX to get closer to the requested reported functionality.

 

Helpful Links:

  1. OLAP vs Tabular
  2. Many-to-Many in DAX
  3. Microsoft Performance Tuning Whitepaper
  4. DAX Studio
  5. DAX Formatter

 

 

Topics: Tool Selection, Blog, Business Intelligence

Written by Greg Meehan