When PBI (desktop or service) performs its query-folding when sending a query to BigQuery, it converts date values (DATE in BigQuery and detected as such in PBI) to TIMESTAMP. Because of the conversion, BigQuery cannot perform data pruning, that is, only scan the relevant date partitions. See below when using a date slicer in PBI on budat: Query folded (translated to BQ dialect) by PowerBI): select `matkl`,
sum(`C1`) as `C1`
from
(
select `budat`,
`matkl`,
`wrbtr` as `C1`
from `hs-airflow-1`.`final`.`a2`
where cast(`budat` as TIMESTAMP) < TIMESTAMP('2015-12-04 00:00:00')
and cast(`budat` as TIMESTAMP) >= TIMESTAMP('2014-04-30 00:00:00')
) as `ITBL`
group by `matkl`
LIMIT 1000001 OFFSET 0 Stats from BigQuery: Bytes Processed 174 MB Bytes Billed 175 MB Slot Time (ms) 51.3 K Same query edited in BigQuery UI to remove the casting to timestamp select `matkl`, sum(`C1`) as `C1` from ( select `budat`, `matkl`, `wrbtr` as `C1` from `hs-airflow-1`.`final`.`a2` where budat < '2015-12-04' and budat >= '2014-04-30' -- same result with "where budat < DATE('2015-12-04') and budat >= DATE('2014-04-30')" ) as `ITBL` group by `matkl` LIMIT 1000001 OFFSET 0 Stats from BigQuery: Bytes Processed 79.2 MB Bytes Billed 80.0 MB Slot Time (ms) 19.2 K As you can see from the bytes processed, the second query *did* use the data partitions whereas the first, built by PowerBI, *did not*. The table scan here is limited because this is just a subset to illustrate but the performance hit can be considerable on large datasets. PBI should *not* cast dates to timestamps - especially in WHERE predicates - when there is no need to. E.g. the following works perfectly fine for limiting date-based partitions in BigQuery: WHERE CAST( budat AS DATE) < DATE('2015-12-04') and budat >= DATE('2014-04-30') On the bigger picture, it would be very useful if query-folding logic could be configurable or somehow editable (e.g dictionary file). I understand MSFT cannot keep up with all SQL-based source changes.
... View more
v-yetao1-msft
on:
New Card Visual Eraser and Alert toggle missing
on:
Setting "Stepped" as False in a theme file for Mat...