BigQuery: NUMERIC does not trigger query folding

[yan]

PowerBI does not recognize NUMERIC data type in BigQuery as truly decimal for query folding. It does autodetect as decimal in Query Editor but it does not send a native SUM() when it should. 

No issue - meaning query folding works - if data type in BigQuery is FLOAT or INTEGER.

Unfortunately, GoogleBigQuery.DataBase configuration options are very limited e.g. SoftNumbers=true would have solved the problem but it's only available in ODBC.

How to reproduce using public dataset

1. use this M code in Query Editor. Make sure to connect in DirectQuery mode.

   let
       Source = GoogleBigQuery.Database(null),
       #"bigquery-public-data" = Source{[Name="bigquery-public-data"]}[Data],
       crypto_zcash_Schema = #"bigquery-public-data"{[Name="crypto_zcash",Kind="Schema"]}[Data],
       transactions_Table = crypto_zcash_Schema{[Name="transactions",Kind="Table"]}[Data]
   in
       transactions_Table

2. Create a bar chart for sum of an integer e.g. input_count by block_number. This renders in a few seconds as PBI has sent the aggregation calculation to the data source. BigQuery UI query history shows indeed:
   

   select `block_number`,
       sum(cast(`input_count` as FLOAT64)) as `C1`
   from `bigquery-public-data`.`crypto_zcash`.`transactions`
   group by `block_number`
   LIMIT 1000001 OFFSET 0

   Not sure why input_count is casted to FLOAT as INTEGER can be aggregated as-is. This should not happen as it consumes unnecessary resource (hence time).
3. Now add to the chart the sum of a numeric field e.g. input_value. PBI errors out, "Can't display the visual".

   Error Message:
   OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

[v-qiuyu-msft]

Hi @yan,

Based on my test with Power BI desktop version 2.67.5404.581 64-bit (March 2019), connect to Google Bigquery bigquery-public-data-> crypto_zcash-> transactions table, there is no column named "input_block". I Group By the [block_number] column then sum [input_count] column, there is no option for us to view native query. 

I have checked with the steps Source and Navigation, the View Native Query option is greyed out as well. 

Which Power BI desktop version do you run? Please update to the latest version as our then test again. 

Best Regards,
Qiuyun Yu 

[yan]

Thanks Qiuyun for your prompt answer. I am using the latest release. I have restated the original issue - have a look, it is easier to reproduce.

Also if that helps the product team - this is from the trace:

DataMashup.Trace Warning: 24579 : {"Start":"2019-03-19T20:24:21.9371959Z","Action":"OdbcQuery/FoldingWarning","HostProcessId":"3308","ErrorMessage":"ODBC SQL Type DECIMAL was not found in the SQLGetTypeInfo data from the ODBC driver. You can override the type information using SQLGetTypeInfo.","ProductVersion":"2.67.5404.581 (19.03)","ActivityId":"0bc97aee-ec1e-4eef-acdb-c5654cbb3f59","Process":"Microsoft.Mashup.Container.NetFX45","Pid":13256,"Tid":1,"Duration":"00:00:00.0003655"}

so it seems PBI does get the info that this is a numeric field apparently - hence the error.

[v-qiuyu-msft]

Hi @yan, 

I have reported this issue internally: CRI 112472161. Will keep you update once I get any information. 

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @yan, 

Current ETA: Power BI desktop May version. 

Best Regards,
Qiuyun Yu 

[yan]

Issue resolved indeed in May '19 release. The query sent as per initial post is 

select `block_number`,
    sum(`input_value`) as `C1`,
    sum(cast(`input_count` as FLOAT64)) as `C2`
from `bigquery-public-data`.`crypto_zcash`.`transactions`
group by `block_number`
LIMIT 1000001 OFFSET 0

hence NUMERIC is now correctly recognized as a Decimal Number in PBI