Introduction

Using AtScale with Tableau provides powerful big data performance advantages coupled with industry-leading data visualization.  The AtScale architecture allows Tableau authors to:

• Centralize calculations in an AtScale cube, eliminating Data Extract proliferation and version control headaches.
• Directly access source data without having to create subsets and 3rd party ETL processes, keeping the data consistent across multiple reporting platforms.
• Utilize AtScale’s automated data aggregations, or access their own user-defined aggregations. 

However, there are nuances when creating Tableau dashboards connected to AtScale with a live SQL connection. If you have worked with Microsoft Analysis Services (MS SSAS), then using that knowledge will help you understand how AtScale works with live connections.

• AtScale is a virtual OLAP cube not a relational database, with a live connection to your source data.  It is designed to support a subset of SQL generated by leading BI tools.  Think of your data fields simply as dimensions and measures. 
• Measures are already aggregated by the cube designer in AtScale (and not in Tableau).  This approach takes advantage of AtScale's aggregation technology and consistent semantic layer.  Tableau’s documentation states that Tableau’s Calculated Fields do not support aggregation functions or binned data (on multidimensional data sources) because the data are already aggregated by the OLAP cube.  AtScale interprets Tableau’s aggregation of measures in one of two ways: Disregard the aggregation function or override.  For details on this behavior see the “Cube Measure Aggregation Functions Override Rules” section in the Appendix.
• Connections are “live”, so any time calculated fields are used in the view, Tableau translates the query into Hive SQL or MDX if using SSAS. If the workbook author chooses to use Tableau in MDX mode, it is strongly advised to familiarize yourself with the respective section of this document.  Please note that because Tableau pushes most of the expressions written in calculated formulas to the server they can have significant impact on query performance.  See “Best Practice 2” below for more details.
• When converting Tableau workbooks originally in another data source, but now connecting to Hadoop Hive, be aware of how source-specific functions are handled.  For example, if you are using the NOW() function in Excel, it uses the local machine clock.  When migrated to AtScale, that same function, NOW(), will use Hive’s unix _timestamp()function. Tableau SQL connections do not support AtScale Drill-Through Sets.  It is recommended that Tableau Dashboard Designers use `Tableau Actions` to expose detailed information about an aggregated cell or mark.

Supported Tableau Versions

For the latest information on Tableau support, visit “AtScale Supported Tools and Platforms” documentation (after logging into the documentation, click Product Requirements > AtScale Supported Tools and Platforms).

How to Connect to AtScale: Prerequisite

Before you can connect to Tableau using the generated tds file from AtScale, please make certain you have installed the version of the Hive ODBC driver that is compatible with your version of Tableau and AtScale. See Installing Hive ODBC Drivers in the AtScale documentation within our Customer Portal.

Tableau Support Matrix Section

This section describes Tableau functions, operators and data types that are supported by the AtScale platform at this time. The matrix is broken out into different sections consistent with the Tableau Desktop Help Guide. Within each set, support per item is further broken down into SQL (using the tds from Cloudera Hadoop or Hortonworks Hadoop drivers) modes. Should a particular feature or function not be supported at this time and it has been planned, the estimated timeframe will be populated in the “Planned” column. Any additional notes pertaining to the support of the functions, operators and data types will be documented in the COMMENTS column.

Functions

Calculation functions are grouped into categories that are consistent with the ones listed in the Tableau calculation editor.  Support indication refers to whether or not the calculation works as expected in Tableau as a function only.  Any exceptions are listed in the COMMENTS column.

NOTE: Aggregation functions are supported on cube measures. However, they are not supported on calculated fields with AtScale. This is because AtScale is a multidimensional data source with a SQL interface. Per Tableau documentation on calculated fields, multidimensional do not support aggregations and binned data. For more information on Tableau calculated fields, please click here.

Note: For performance reasons, it is recommended that all date math is transitioned to AtScale (refer to Best Practices 7 and 8 within this document). 

Additional recommendations:

• Workbook authors can use partitioned user-defined aggregates in AtScale in order to speed up the query performance. Once defined the aggregate can be partitioned by a specified dimensional attribute. However one has to keep in mind that the behavior is not supported for all platforms. Exceptions at the moment are PostgreSQL, Redshift, InterSystems IRIS, and Teradata. For Google BigQuery, AtScale supports partitioning using columns of type Date or DateTime for partition columns. Data architects should be aware of tool usage within the intended audience in order to adopt a partitioning strategy that maximizes their query performance. If a cube services a mix of BI Tools, it is recommended to either partition by both name and key columns or use the same physical column for the AtScale Key and Name attribute bindings.
• Distribution keys for cloud-based databases: from AtScale 2020.5.1 onwards there are additional settings which will allow the system-generated aggregates to select the most appropriate column to distribute the aggregate. This will lead to enhanced performance of queries.

Date Calculations and Comparisons

This section contains important information regarding the use of date calculations in Tableau workbooks.  AtScale strongly recommends the alternative approach of using cube-side time calculations rather than using workbook-specific date calculations.  Using AtScale's cube-side calculations reduces the amount of code that must be written because calculations will work seamlessly for every level in a time hierarchy.  Additionally, results will be consistent across workbooks and reporting tools.  AtScale's suite of functions make it easy to compute metrics on: Parallel Periods, Moving Windows, and Cumulative "x-to-date" time periods.   For more information, visit:  Creating and Sharing Cubes > Creating Cubes > Modeling Cube Dimensions > About Cube Dimensions > Requirements for Modeling Dimensional Hierarchies

If you still want to perform date calculations in Tableau, then it is strongly recommended that you follow these best practices regarding date arithmetic to achieve the most performant results.  

• For comparisons between today’s date, it is more performant for both AtScale and Tableau to call NOW().
• DATEADD() Guidelines (See below)

If you want to add days to a date in a Tableau Calculation, you should use [date] + DAYS_AS_INTEGER, instead of DATEADD(‘day’, -1, [date]). Tableau will treat [date] - 1 as the same as DATEADD(‘day’, -1, [date]) but is more efficient. lt could look like the following when using DATEADD():

An example would be if you have a parameter called [Day interval] and a Date parameter called [Calc-Start date].  A typical calculated field could look like the following when using DATEADD():

When using Tableau with AtScale using DATEADD(‘day’...) adds unnecessary overhead on query performance.  A more efficient calculation for the above sample is as follows:

* NOTE: You may still use DATEADD(‘month’..) and DATEADD(‘year’...) as these are more efficient than writing your own due to leap year and month logic.

Filters

In addition to filtering your data through AtScale, we also support Tableau filters on dimensions and measures (filters shelf or in filter cards in the view).  

LOD—Level of Detail Expressions

Tableau’s Level of Detail (LOD) expressions provide a way for authors to compute aggregations that are not at the level of detail of the visualization. It is essentially a way to allow authors to customize where they want Tableau to stop its aggregations. Unlike table calculations, totals, or reference lines, LOD expressions are computed in the data source. LOD expressions can be dimensions or measures and are often denoted with the use of { } in the Tableau calculation.

• A general LOD expression where no keywords are used
• FIXED
• EXCLUDE
• INCLUDE 

In this section, we will provide some examples of these as they are mapped to Tableau’s “Top 15 LOD Expressions” blog.

No Keyword Measure based LOD: Percent of Total

We would like to understand how each country has contributed to global sales based on our data set.  The calculated Tableau LOD expression used in this case is:  {SUM([Sales Amount])}. This LOD expression prompts Tableau to calculate the total sales amount for the entire data set it is connected to.  The approach allows the author to filter on other dimensions, such as Customer Occupation, to help understand how each of the occupational areas has contributed by country to global sales.

Fixed Keyword Dimension based LOD: Cohort Analysis

We would like to understand how our customers have contributed to the global sales over the years. The calculated Tableau LOD expression used in this case is:  DATE({FIXED [Customer_Key]: min([Order Day])}).   This LOD expression prompts Tableau to set the first order date of each customer in the data set as the date on which a shopper became a customer. The approach allows the author to show how longer-tenured customers have contributed to sales over the years.

Fixed Keyword Dimension based LOD: New Customer Acquisition

Leveraging the customer acquisition calculation from the “Cohort Analysis” scenario, we use the same LOD expression to understand our trends in New Customer Acquisitions. The calculated Tableau LOD expression used in this case is again: {FIXED [Customer_Key]: min([Order Day])}.  This time, we will want to view a running total on the DC of [Customer Count] by the continuous DAY of [Order Day] based on only “New” customers.

To set up the visual:

• Bring in [Order Day] and set it to a continuous DAY
• Bring in the [Customer Count].  Notice the automatically assigned function call of CNTD().  This is an AtScale DC (Distinct Count) so Tableau automatically assigns CNTD().  Add the Running Total table calculation to this measure.
• Change the Y-axis to use a logarithmic scale to generate the curved line chart
• Filter on “New” for the [New or Existing Customer] dimension  
• Bring in [Country] into the color code mark

Fixed Keyword Dimension based LOD: Customer Order Frequency

We want to understand the number of customers who made 1,2,3,...,N orders.  This requires a count of customers by the total number of order groups.  The calculated Tableau LOD expression used in this case is: 

{ FIXED [Customer_Key] : COUNTD([Total Sales Orders]) } where [Total Sales Orders] is a DCE (Distinct Count Estimate) of Customers in the data set.  This LOD expression prompts Tableau to calculate a distinct count of sales orders by customer.  This will give the number of orders made by each customer.  You can then use the LOD calculation into a dimension to provide the view of the number of customers who have purchased 1,2,3,...,N orders.

To set up the visual:

• Bring in [Order Day] and set it to a continuous DAY
• Bring in the LOD calculation, [Number of orders per customer LOD], into the worksheet and change the measure into discrete dimension (Figure 1).  
• [Customer Count] is a DCE (Distinct Count Estimate) measure from AtScale.  When bringing in this measure, you will notice that Tableau automatically chose the CNTD() since it recognizes this AtScale measure as a DCE.  
• To get a total count of customers for each total order number, you will need to override the pre-assigned CNTD() function with SUM() (Figure 2).

Figure 1. Changing a Measure to a Discrete Dimension

Figure 2. Overriding pre-assigned measure function.

The final visual will look like the following:

EXCLUDE Keyword Measure based LOD: Comparative Sales Analysis

We would like to understand how the rest of the product lines have performed against a particular product line. The calculated Tableau LOD expression used in this case is:  { EXCLUDE [Product Line] : SUM([Selected Sales Amount]) }. This LOD expression prompts Tableau to set a custom aggregation on a target product line’s sales total to use as a comparison to other product lines’ sales totals. The approach allows the author to show the difference in sales between a target product line and the company’s other product lines.

INCLUDE Keyword Measure based LOD: Largest Average Customer Purchase

We would like to understand the largest sale made by customers. The calculated Tableau LOD expression used in this case is:  {INCLUDE [Customer_Key]: MAX([Sales Amount])}. This LOD expression prompts Tableau to find and set the max sales amount per customer. This approach allows the author to show an average of the largest sales by customers for each country sorted in descending order based on the LOD calculation.

In addition to showing the largest average customer purchase, the author can also allow the user to choose to view by a parameterized Top N states that have the average largest customer purchase which is the LOD calculation.

Parameters

Below is AtScale’s support for Tableau parameters.

Data Menu and Data Source Features

Below are the AtScale support details for the Data menu in Tableau.

Sets

The use of Tableau sets are supported, the creation of sets is only available in Tableau Desktop.  To create sets in Tableau, please reference Tableau Online Help for Sets. There are essentially two types of sets a Tableau author can create.

CONSTANT SETS

These types of sets are generated from selecting particular marks or headers in the Tableau visualization.  The members in a constant set are fixed and do not change.  Usability support for this type of set includes only using it as a filter.  You may also bring the set into your columns and rows after it has been placed in the filters card. 

COMPUTED SETS

These types of sets are dynamic sets and change when the data is changed.  According to Tableau, computed sets “can only be on a single dimension”.  You may set conditions on this type of a set through a constraint-based calculation, parameter, or a conditional statement. 

1. Conditional statement sample:
   1. Set the Sum of Sales Amount to over $15,000 by using the CONDITION tab of the measure [Sales Amount]

2. Constraint-based calculation sample:  
   1. A-Named Customers = STARTSWITH([Customer Name], ‘A’)

3. Parameter sample:
   1. TOP OR BOTTOM N CUSTOMERS based on Sales Amount
      1. N CUSTOMERS is a parameter
      2. TOP or BOTTOM conditions are set in the TOP tab of the measure [Sales Amount]

COMBINED SETS

Tableau allows authors to combine two sets to compare the members from the sets.  You may only combine sets that have like data.  For instructions on how to combine sets, please reference the Tableau Online Help for Combining Sets.  When combining two sets together, there are 4 options you may choose from:

1. All members in both sets
2. Shared members in both sets
3. All members in the first set but NOT in the second set or shared between the two sets
4. All members in the second set but NOT in the first set or shared between the two sets

Usability support for combined sets include using the combined set as a filter, parameters or constraint-based calculations used as filters or in the marks card options such as color coding, label, details, etc and viewing it as a column or row dimension.

Tableau Subtotal and Grand Totals Feature

If you would like to use the Tableau Subtotals and Grand Totals feature on AtScale measures, Tableau will only calculate these correctly as long as the Tableau calculated fields are using AtScale the functions of sum, count, or count distinct estimate functions. Using this feature on AtScale measures such as averages will result in incorrect calculations. Furthermore, AtScale calculated measures such as averages and percent change will produce NULL or incorrect values for this feature because Tableau requires the underlying data to calculate subtotals and grand totals properly.

To use this feature in Tableau for averages or percent change, average calculations should be performed in Tableau rather than calculated measures in AtScale. Please note:  any calls to Count Distinct or Count Distinct Estimates in Tableau will result in a query execution back to AtScale for data at each level of subtotal and grand total. This process is kicked off by Tableau as a result of this feature and the process runs sequentially — so it will add waiting time to dashboard refreshes, filter, and action interactions.

Sort

Tableau implements its own default sorting logic even if the sort column is selected within AtScale. 

For example, in the use case of viewing your data by Year> Quarter> Month, Tableau will take the data from AtScale and format it into a nested display. Notice that Month is sorted by alphabetical order even though it is sorted by the Month_int dimension within AtScale. This indicates that Tableau recognizes the Month string dimension as a string type and not necessarily defined as Month as a part of the date dimension. This requires the author to specify the dimension as the month format as part of date so that Tableau will sort the month in order of calendar months.  

Additional references:

https://onlinehelp.tableau.com/current/pro/desktop/en-us/dates_custom_date_formats.html

https://community.tableau.com/thread/118958?start=0&tstart=0

Tableau in MDX Mode

AtScale can be connected to Tableau in MDX mode, however, the mode is not officially supported. Additionally, the workbook author needs to keep in mind that Tableau Desktop supports MDX mode only for Windows. This means that the author can successfully connect to AtScale through Tableau using the MS SSAS driver. Nonetheless, please be advised that some features and functions that will not work the same way and/or are not supported in MDX mode.  Exceptions are documented on the Online Help for Tableau.

Constraint-Based Measures and Tableau Formula Syntax Section

AtScale is an OLAP–based system that offers significant advantages in the areas of data governance and query performance on big data workloads by pre-aggregating data according to predefined dimensional models and query patterns generated by users. These substantial benefits come at the cost of preventing direct access to the underlying table data. This fact may be a surprise to BI tool users who connect to AtScale through its SQL interface because they think they are connecting to a regular RDBMS and can access table rows in their workbook formulas. The fact that they cannot access disaggregated data becomes obvious when they try to execute a client-side calculated formula that was originally written against an RDBMS. This section explains what kinds of Tableau Calculated Field Formula and AtScale Calculated Measure expressions are supported as of AtScale version 2021.1.0 as well as their limitations.

Tableau Calculations Performed at Row-level Data

Let’s begin by examining a common method of summing a subset of row-level data in a Tableau workbook originally written to talk to a SQL database. A common method of summing a subset of data in Tableau is to use an IFTHEN() statement that is evaluated for every source data row. For this example let’s assume that we have a transaction fact table with a column called SalesSource meant to indicate the source of a sales transaction (values for SalesSource include: “referral”, “email”, “social media”, “website”). The following Tableau calculation from a workbook retrieves the count of sales that came from “referrals”. For each fact table row, it checks the SalesSource column, if its value is “referral”, then it includes its “Sales Transaction ID” value in the aggregation count, otherwise it does not.

Example 1. Typical Tableau Calculated Field Formula to Constrain Measure Aggregation

Assuming that the source transaction table has sales records for each SalesSource value, the intent is to create a result set that returns rows for each SalesSource value, but displays zeros for the sum of ReferralSalesCount column, except for “referral”.  If you were to display the result in a table, it would look something like Table 1.

Table 1. Output of a Tableau query that uses the expression from Example 1 and groups by SalesSource.

It is common practice for the workbook author to use this calculation as a dimensional filter to count the number of “referral” sales without displaying the other sales sources. In general, this is the easiest approach the Tableau author had at his/her disposal without requesting a change to the underlying data source. However, with AtScale, the Tableau author does not have to do this.  

AtScale handles this use-case very efficiently by using a dimensional model to deliver interactive query speeds on billions of rows of data. This is accomplished by modeling the fact table’s SalesSource column as a dimension and replacing the Tableau–side formula with a simple filter on the dimensional attribute.  

Unfortunately, AtScale can’t always discriminate legitimate calculated fields from “bad” calculated fields so if your Tableau calculation doesn’t make sense you might see a cluster-specific error message.  

The following sections explain what forms of the above example AtScale supports. It is recommended that the cube designer implement something like ReferralSales as an AtScale Calculated Column on a dataset and define a measure for it, i.e. ReferralSalesCount.  When expressed as a Measure backed by a Calculated Column, ReferralSalesCount will be calculated at the row level as either SalesCount or 0 and summed according to the group-by dimensions.  

The AtScale engine supports client and cube-side expressions that perform SUM and NDC (Non-Distinct Count) calculations at the lowest level necessary to calculate the expression. The level of grouping corresponds to the lowest level of grouping in your workbook visualization (or Tableau LOD expression). Only simple expressions, such as switching between measures (Example 2) or linear transformations like multiplication/division of SUM or NDC measures (Example 3) are supported at this time. This is true for both a Tableau calculated field and a cube-based calculated measure:

Example 2. AtScale-Supported Constraint-Based Measure Expression

In Example 2, the ReferralSalesTotal calculation does a simple IFTHEN() conditional statement where the [Sales Sum] returns when the [SalesSource] is “referral” and returns a 0 when the condition fails. Similar calculation types would work if they reference measures that use Non-Distinct Counts (NDC).

Example 3. Arithmetic operators in a Constraint-Based Measure Expression

In Example 3, you can use simple linear transformations like arithmetic operations (multiplication and division). The calculated field Commission does a simple IFTHEN() conditional statement where the product of ([Sales Sum] * 0.05) returns when the [SalesSource] is “referral” and returns a 0 when the condition fails.

Restrictions

Below are the restrictions on Constraint-based calculated fields used in Tableau Formulas or AtScale Calculated Measures:

Automatic Grouping: If the expression is defined in Tableau it is grouped at the level of the visualization or the nested LOD query.

No Nested Functions: Constraint-Based Measures can only reference other measures or simple mathematical operations. You cannot include additional functions inside of it. 

1 Fact Table Per Expression: Expressions can only reference measures that come from the same fact table. (ENGINE-2324, ENGINE-2357)

Overriding the Default Aggregation Function in Tableau: When using a cube-based AtScale Calculated Measure as a field in Tableau, it will most likely be classed differently than you want (and most likely a Distinct Count Measure). This is done to prevent Tableau from automatically caching and aggregating results on the client side. However, if the intent is to sum the calculated measure expression then it is safe to use the SUM function and you must set the measure’s aggregation function to “SUM” in the tableau workbook manually.

Object Names vs Query Names: When writing Constraint-Based Measures in Tableau, use the AtScale “Object Name” to reference dimensions and measures. By contrast, when writing an equivalent syntax in the AtScale cube, you must use the AtScale “Query Name” to reference dimensions and measures.

Measure Aggregation Functions

Although we seek to support all aggregation types in the future, as of v 2021.1.0, Constraint-Based Measures may reference measures that use the following supported calculation types.  

Table 2. Measure Aggregation Function compatibility with AtScale Constraint-based Measure Expressions

Aggregate Usage

Queries containing Constraint-Based Measures may only use very fine-grained aggregate tables resulting in longer query execution times.

Nested Functions

Constraint-Based Measures cannot use nested functions. For example, the following expression is not supported:

Example 4. Example of an unsupported Constraint-Based Measure that uses a nested function

The above calculation requires an unsupported additional stage of processing to evaluate the AVG function on the SalesAmount data at a different scope than that of the rest of the expression.  

Similarly, you cannot include IF (or IIF) statements inside of Tableau-specified aggregate functions because this would require access to pre-aggregated data.  For instance, Example 5 is not supported:

Example 5. Example of using an IIF function Inside of a Tableau Aggregation Function

Common Error Messages

If you encounter one of the following errors, you should check for one of the following common causes.

Table 3. Common Error Messages

Tableau Best Practices

Best Practice 1: Avoid Using Calculated Fields in Filters

When defining Tableau Filters you should avoid using calculated fields (Tableau Calculated Field, AtScale Calculated Column/Measure) that evaluate every source row.  

In the following example, Order Year and Order Quarter are concatenated as strings and then converted to integers so the field can be used in a filter range.

To retrieve values since 2007 Q1 we drag this field to the Filters card and set the following Condition Formula:

Executing a query for the sum of sales by year and quarter with the above filter results in evaluating 4 CASTs and 1 CONCAT operation for every row. This kind of calculation prevents the SQL engine from partition pruning. Instead, Tableau authors should break the filter up into a Year filter and a Quarter filter— eliminating row level calculations and taking advantage of partition pruning. If your table is partitioned by YearQuarter, then this query will execute much more quickly. 

Best Practice 2: Measure Tableau’s Concurrent Query Execution

If you have a dashboard that displays lots of visualizations you should use Tableau’s Performance Recording feature to measure the execution time and number of concurrent queries submitted by tableau to render the dashboard.  Follow these steps to measure your dashboard’s performance:

1. Decide what dashboard you want to analyze
2. Open workbook, stop executing queries 
3. Turn on Performance Recording: Help ⇒  Settings and Performance ⇒ Start Performance Recording
4. Navigate to the dashboard of interest
5. Refresh the dashboard
6. Turn off Performance Recording: Help ⇒ Settings and Performance ⇒ Stop Performance Recording
7. Review the Timeline report, looking for queries with a delayed start (see Figure 1)

Figure 1.  Tableau Timeline Report that Identifies Concurrency Related Wait Time 

Figure 1 shows the rendering timeline for a test dashboard. Starting from the top of the chart and reading down, we see that at any given time a maximum of five queries are executing concurrently (i.e. bars overlap).  The boxes in red show time spent waiting before the rest of the queries start. The queries in the blue boxes must wait for execution threads to free up before they can even start.  

The default number of concurrent Tableau desktop queries is 16 (for Tableau Server, the number is set by your administrator). If your dashboard requires more than the number configured max parallel queries to render, then some of your queries will wait until other queries finish executing. This is a metering feature in Tableau to prevent a few workbook users from overwhelming the database. For information on increasing the number of parallel queries see the following Tableau Knowledge Base articles: 

Tableau Desktop http://kb.tableau.com/articles/HowTo/Configuring-Parallel-Queries-in-Tableau-Desktop

Tableau Server
http://kb.tableau.com/articles/howto/configuring-parallel-queries-in-tableau-server

Best Practice 3: Tableau’s Automatic Subtotal and Grand Total Features Submit Serialized Queries when Displaying Distinct Count Measures (COUNTD() Aggregation Functions)

Be aware that Tableau’s Automatic Subtotal and Grand Total features submit sequential queries for each subtotal/total level you want to compute if the view includes “Count Distinct” or “Count Distinct Estimate” fields. These sequentially executed queries will slow down worksheet rendering. As of this writing, there is no way to force Tableau to execute these queries in parallel. Simply be aware of the load-time cost of this combination of features so that you can design around them if you wish.

Best Practice 4: Understanding How Dimensions and Measures Are Passed From Tableau to AtScale and Affect Performance

Tableau offers different features to allow authors to create the visualizations and interactions they need.  These features often affect the performance of the query that Tableau issues to AtScale to retrieve data.  In this section, we will review several Tableau features that have an effect on query performance during data retrieval and provide recommendations on how to best optimize your visualization.

FILTERS

With each dimension or measure that is dropped into Tableau’s Filters Card, the field and its values get passed into the query statement’s WHERE clause. The more dimensions and measures that are added to the filters card, the greater the number of fields the WHERE clause will include. 

Because the query that Tableau sends back to AtScale can get very complex very quickly depending on the number of dimensions and measures you have added to the Filters card, it is recommended that you simplify your filter selections as much as possible.  For example: if you would like to filter on when a particular dimension or measure is true, consider creating a calculation that results in a TRUE/FALSE instead of making selections from a list of values of that particular dimension or measure.  Similarly, you should consider using a preprocessed AtScale date dimension (such as Year of Date, Week of Date, etc) when filtering on a particular year, quarter, month, week, or weekday. Calling on Tableau to calculate parts of a date will often generate much more complex queries that will affect query performance (Figure 2).

Figure 2. Query complexity when using Tableau to calculate parts of a date (i.e. Week Number and Weekday).

Figure 3: Simplification of query when using preprocessed AtScale date dimensions (i.e. Week of Date, Weekday of Date).

In addition, it is always best practice to evaluate the filters you need to bring into the dashboard.  

SORT

When sorts are applied to dimensions within a Tableau visual, the measure that the sort is also included in the query statement’s SELECT clause that Tableau sends to AtScale. So even though the measure that Tableau is using to sort the visualization on is not explicitly displayed, the measure is still added to the query that is sent to AtScale during data retrieval. The more measure-based sort rules that you specify on the visualization’s dimensions, the more measures you end up adding to the query.

PARAMETERS

Measures and dimensions that are called from the parameter feature will be added to the “SELECT” clause of the query statement upon usage in the Tableau visualization. The number of measures the query will include is directly dependent on how many are listed in the parameter calculation. By comparison, the dimensional parameter will pass in the particular dimension selected by the user in the parameter.  

In the following screenshots (Figure 1), we’ve set up a couple of parameters. One dimensional parameter where it will allow the user to choose a dimension to slice the visualization by. One measure-based parameter where it will allow the user to choose the metric to display. When a user interacts with these parameters, the resulting query that is issued includes all of the measures from the measure-based parameter calculation and the single dimension selected from the dimensional parameter calculation (Figure 2).

Figure 1: Dimensional and measure parameters and their dependent calculations

Figure 2: Resulting query statement issued from Tableau and sent to AtScale. Notice the “SELECT” clause. It contains the 4 measures called from the [display metric] parameter calculation and the single selection made from the [display dimension slice] parameter calculation.

Best Practice 5: Use AtScale created Date Dimensions for better query performance

If Date dimension attributes are to be used in filters (i.e, Year = 2016, 2017), you will get better query performance by creating an AtScale dimension from the date field in the fact table, versus using Tableau’s built-in time intelligence features.  

The example below uses the Order Date from the fact table and creates a hierarchy, with attributes for Year, Month and Day.

In Tableau, the Order Date Hierarchy appears in the Dimension pane and looks like this:

It is common practice for the Tableau author to drag and drop the “Order Date” field from the dimension pane to the filter and select the YEARS part if the user wants to filter the data between the years 2015-2017. While this still works when Tableau is connected to AtScale, the resulting query that Tableau sends to AtScale will contain a sub-optimal WHERE clause in the query statement.

As an alternative,  the cube designer can pre-process the YEAR from the Order Date (Year of Order)  and make it available as a dimension for the Tableau author to use directly. The “Year of Order” dimension in the filter shelf will present a much cleaner WHERE clause in the query statement.

Best Practice 6: Consideration guidelines when evaluating whether or not to move a calculation into AtScale

Many Tableau authors are accustomed to creating their own calculations based on row level data they are connected to. When migrating existing Tableau workbooks to AtScale, it is recommended that all calculations contained within the workbook be evaluated as a candidate for bringing into AtScale. Tableau sends all Tableau calculations and field requests back to the database when it is a live connection to the data source. So all data type conversions, function calls, filter selections, constraint based calculations, etc are all translated into query form by Tableau and passed to AtScale for processing. If you use nested calculations where one Tableau calculation is leveraging the results of another Tableau calculation, the resulting query that Tableau generates could be quite large and complex.

Figure 2: Resulting query statement issued from Tableau and sent to AtScale. Notice the “SELECT” clause. It contains the 4 measures called from the [display metric] parameter calculation and the single selection made from the [display dimension slice] parameter calculation.

For example:  The Tableau calculation for “TrendDate”  leverages 2 additional Tableau calculations (“CurrentMonday” and “MonToSunWeek”):

where

The “TrendDate” is a look back on 8 weeks based on finding the “CurrentMonday” for the current week and the week set to a  “MonToSunWeek” week.

Doing all of these calculations in Tableau forces Tableau to include these calculations in the query that AtScale receives. A nested calculation like the above results in the following query (Figure 1.):

Figure 1. This complex nested SQL query is generated as a result of nested date calculations that are coming from Tableau.

The resulting query (Figure 1.) completes in about 18 seconds to retrieve 8 weeks worth of data. This is after an aggregate table has been created. If there was not an aggregate table present for this query, the query duration is exponentially longer (often running into minutes per query). In this situation and given the performance of the query, we would evaluate and consider bringing in the Tableau calculations into AtScale.  

It is often an exercise in negotiation and compromise when you are considering bringing Tableau calculations into AtScale. Most Tableau authors enjoy the flexibility of being able to perform custom calculations in the client. However, if you are looking to productionize dashboards and workbooks, you should consider the performance gains that you get when you bring all or part of these Tableau calculations to AtScale. Here are some consideration guidelines:

1. Is this a metric logic that more than one/most/all business team(s) can potentially leverage? 
   If so, standardization of the metric logic would be a best practice for your organization. In our sample case, “TrendDate” is leveraged by many workbooks and different teams. So it would make sense for the calculation and its dependent calculations (“CurrentMonday” and “MonToSunWeek”) to be brought into AtScale. This would not only provide standardization of metric logic but also reduce the query complexity and improve performance of the query (Figure 2.) 

Figure 2. “TrendDate”, “CurrentMonday” and “MonToSunWeek” calculations are standardized in AtScale. The resulting query run time is reduced from 18 seconds to about 2.7 seconds.

2. Do you need flexibility on your primary calculation and still want improved query performance? If so, then a compromise is your best practice.   In our sample case,  our business users wanted to be able to specify a custom number of weeks that “TrendDate” looks back. In this situation, the best practice would be to consider a hybrid approach in calculating  “TrendDate”.   Bringing in the dependent calculations of  “CurrentMonday” and  “MonToSunWeek” will allow Tableau authors the flexibility to determine and change their look back week on  “TrendDate” and still retain improved query performance (Figure 3.).

Figure 3.  “CurrentMonday” and “MonToSunWeek” calculations are standardized in AtScale and  “TrendDate” is a Tableau calculation being passed over in the query to AtScale.  Notice the resulting query run time of 3.5 seconds is a reduction from the original 18 seconds but only slightly higher than the 2.7 seconds from Figure 2.

Best Practice 7: Remove aggregation functions in formulas used for dynamic measures

Dynamic Measures gives Tableau users the ability to switch the measure being displayed across a dashboard and is very useful when there are multiple visualizations using the same measures.  The screenshot below shows a parameter called “Dynamic Measure” where the user can simply change the measure on the fly (from Sales Amount to Order Quantity for example).  

To implement this capability, a parameter must be created first:

The next step is to create a case statement formula that creates the Dynamic Measure to be used in the view.  So, when used in conjunction with the parameter, the user will see the measure they select  on the view and can quickly change to a different one. 

When creating this case statement, it is imperative that client-side aggregation functions are not used in the definition (see above), so the AtScale defined aggregations can be used.  If client-side aggregation functions are added (see screenshot below), costly queries are generated from Tableau in both the query planning and execution phases.  Extra measures are called into the query, and if these measures are complicated and very deep, then the queries will be very slow.  The correct implementation (above), only calls the selected measure in the query planning and execution steps.

Best Practice 8: Use Tableau"Dashboard Actions" instead of the  “View Data” Feature

Tableau's "View Data..." feature does not produce a query that is compatible with AtScale's "Drill-through" feature to display detailed information about a specific cube cell at an arbitrary level of granularity.  If the "View Data…" option is chosen, Tableau will generate a costly and often incompatible query. 

The recommended alternative is to use Tableau's  “Dashboard Action” to create visualizations that have complete and consistent control over the grouping level and the display columns for detailed records. Additional information on the topic can be found in Tableau Authoring help.

Best Practice 9: Do NOT use “Data Guide” feature, here is why: 

As of Tableau 2022.3 there’s a new feature “Data Guide” available for both Tableau Desktop/Server and Tableau online. Since the change in Tableau Server release schedule (reference here) the WB author needs to keep in mind that certain parts of this feature will not be visible in all cases. 

Given that the feature relies heavily on native Tableau aggregations, it is not supported at the moment. On top of this, parts of this functionality, like drill-through on a certain “Outlier” or “Explanation” will end up in costly and often incompatible queries. 

It is recommended that aggregations within the AtScale cube are used with the appropriate business context in conjunction with Dashboard Actions, in  order to achieve similar functionality.

Best Practice 10: Do NOT use “Accelerators” feature, here is why: 

With 2023.1 Tableau has launched a new feature "Accelerator" Data Mapping, available for both Desktop & Server. The workbook author needs to keep in mind those are generic/reusable reports expecting a specific input data set. By enabling a dedicated accelerator and mapping it to the desired data, Tableau issues unstable complex query patterns, entirely dependent on the Accelerator author. On top of this, the majority of published "Accelerators" are not meant to be used on live database connection, unprepared to respond to rapid changes in the data. Given all of the above, AtScale users are strongly advised to refrain from using "Accelerators" on top of AtScale data sources, as we're unable to certify the performance and lack of data issues.

APPENDIX

Cube Measure Aggregation Functions Override Rules

Semi-additive and Calculated Cube measures cannot be overridden by Tableau. If the workbook attempts to override a measure’s aggregation function in Tableau the AtScale Engine will ignore the workbook-specified aggregation function according to the rules displayed in the following table.  

Limitations

Explain Data feature

The feature offered by Tableau Desktop allows the user to quickly obtain a standardized summary for a data point of interest. 

Since it utilizes a standard set of aggregation functions, some of which currently are unsupported by AtScale (please refer to the aggregation functions table of this document), Tableau authors are advised to refrain from it.

AskData in Tableau Server

Tableau Server and Online have introduced a feature called Ask Data on published data sources.

Given that the feature relies heavily on the native Tableau aggregation functions at the moment is not supported, since not all of the functions used work on AtScale cubes. In addition to this, the feature is not compatible with certain Tableau Desktop constructs (an extensive list can be found here). It is recommendable to utilize the aggregations within the AtScale cube and apply the business context in order to create a set of calculated members which can be used.

Create Bins Functionality

Tableau Desktop supports the functionality to create bins from a measure without referencing a dimension (numeric bins, corresponding to range of values).

At this time, the functionality is not supported with AtScale. Despite not erroring out everytime, the results returned will be inaccurate. 

There are 2 workarounds available:

1. Use AtScale's Percentile functionality to create bins based on the population percentile.

2. Create a calculated column which will assign the numeric value to a bin and then generate either a dimension level or a secondary attribute on top of the calculated column. 

Both of the methods will produce a better query performance when used with AtScale given they will be written into an aggregate table, the original functionality will be executed against the raw data for every query.

DataGuide functionality in Tableau server

As of Tableau 2022.3 there’s a new feature “Data Guide”, available for both Tableau Desktop/Server and Tableau Online,is a new pane that provides information about a dashboard and the data behind it. Given that the feature relies heavily on native Tableau aggregations, it is not supported at the moment. On top of this, parts of this functionality, like drill-through on a certain “Outlier” or “Explanation” will end up in costly and often incompatible queries. 

It is recommended that aggregations within the AtScale cube are used with the appropriate business context in conjunction with Dashboard Actions, in  order to achieve similar functionality.