Calculated Variables Involving Aggregations in Tableau

Most Tableau users would have created calculated variables involving Aggregate functions (Eg, COUNT([City]), which is a straightforward way of creating a count of measures.

When you are connecting to an AtScale data source, if you create a calculated Measure like COUNT([City]:



When you query the above measure against AtScale, you may receive the following errors:



When we check the AtScale Query page, we see the following query:

BEST PRACTICE

1. Even though the Inbound Query generated by Tableau is a valid SQL Query, we must remember that AtScale is not an RDBMS System or SQL Engine.

   SELECT COUNT(`Internet Sales Cube`.`City`) AS `usr_calculation_3569032354520612865_ok` 
   FROM `sales insights`.`internet sales cube` `Internet Sales Cube` GROUP BY 1.1000000000000001

    

2. AtScale is an OLAP Cube exposed like a flat Table to Tableau.
3. Even though Tableau sees AtScale as a SQL Table, AtScale will operate like an OLAP Cube and not as RDBMS System.
4. The correct way to retrieve Aggregated Measures ( Like COUNT(city)) from AtScale is to define the calculation at the Cube Level and request the measure from AtScale.
5. At the Query Planning stage, AtScale will translate the measure requested to a correct aggregate syntax.

SELECT COUNT(`Internet Sales Cube`.`City`) AS `usr_calculation_3569032354520612865_ok` 
FROM `sales insights`.`internet sales cube` `Internet Sales Cube` GROUP BY 1.1000000000000001