Question

Looker Explore adding extra functions impacting performance and accuracy

  • 8 May 2022
  • 1 reply
  • 15 views

Hi,

After adding the dimensions and measures in Looker explorer when looked at the generated SQL I see lot of functions are added for the measures. When compared the simple version of it, this explorer generated version is impacting the accuracy and performance.

 

FYI When ran in sqlrunner using the simple agg functions data is returned fast and correct.

Wondering if there is an option to avoid this generation

 

Eg :

Expected :  Sum( Cost ) = 100

Looker Explorer generated SQL  :     COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE( COST ,0)*(CAST(1000000 AS DOUBLE PRECISION)*1.0)) AS DECIMAL(38,0))) + CAST(to_number(SUBSTR(hash_md5……………………..  = 80


1 reply

Userlevel 7
Badge +1

These functions are part of a feature called Symmetric Aggregates in Looker and they pertain to fields that are defined as sum_distinct, average_distinct, and so on.

https://help.looker.com/hc/en-us/articles/360023722974-A-Simple-Explanation-of-Symmetric-Aggregates-or-Why-On-Earth-Does-My-SQL-Look-Like-That-

They allow to aggregate the same value only once per key if the data table is fanned out by a child records.

 

The question is whether you do need sum_distinct? If you change your measure defintion to sum, the SQL will be much simpler.

Reply