Tableau – An Incomplete Choice for Microsoft Analysis Services
By Michael Raam , Principal Data Analytics, Pyramid Analytics
Tableau connects to many data sources, including Analysis Services (SSAS). What isn’t widely known though, is that there are a number of key limitations when it comes to SSAS. Some can be found at http://kb.tableau.com/articles/knowledgebase/functional-differences-olap-relational.
Beyond this list there are a variety of other key functionalities that BI Office provides that Tableau does not - specifically when connected to Analysis Services models or cubes. Some of these scenarios are highlighted below.
In BI Office data driven parameters are a built in part of the product. Importantly, these selections can be generated through functionality and logic - instead of using a hand picked list. This allows the end users to create dynamic sets (groupings of elements) that can be driven by user selections.
Cascading parameters with logic, basket analysis
Dynamic set evaluation allows end users to create cascading and circular reference logic. Basket analysis is a classic example. Here we select a state which generates a list of top performing sales persons which generates a list of products sold by these sales persons. Once we select the product from the list we generate a list of the other products purchased in the same transactions as the selected products.
As BI Office leverages all of the SSAS functionally there are types of analysis that are available only via BI Office on top of MDX and N of N analysis is such an example. Here we see the top 5 products relative to each manufacturer (you can see the unique list of the products per manufacturer).
Advanced analysis, binning / user value input driven clustering
BI Office provides easy to use (no code) collection of advanced analytic wizards on top of SSAS data sources: binning / clustering / forecasting and prediction are classic examples. Here we see a wizard driven binning of the products by the cost of production.
Allowing users to control the data granularity is a very common scenario and BI Office allows such control via its variable set wizard. In this example each selection controls the level of the date hierarchy. Here we see an example for selecting the “Year” and “Month” levels of the data.
MDX allow a wide range of data manipulation without the need to actually change the “real” data structure. An example could be the use of Tuples (inner selections). Here we see a calculation that shows the sales for the year of 2008 side by side with the sales across all the years. This type of calculation could be used for growth percentage, percentage of total etc.
Data Model formats
Many organizations are using the cube and data model to format the data in a way that provide clarity and assists the users in understanding the data in a quick and simple way. It is critical that the font end application that connects the users to the data reflects these data formats.
BI Office allows the formats to be reflected exactly as designed without the need for individual end users to do so for each new report.
SSAS allows you to slice and dice data in a variety of ways. BI Office supports all of these options. An example would be to filter the data by a calculation (Current Year). In this example, this allows the end users to get to the required analysis in a minimal number of clicks and standardize reports and analysts across the organization.