I was intrigued why Tableau and PowerBI have a different behavior when operating in a Direct Query Mode ( Tableau call it Live Mode), I always assumed it is just a driver difference, but it seems it is a little bit more complicated than That.
It is a long weekend, and Tableau released the next version as a beta (which is free), so it is a perfect opportunity to do some testing, Hopefully it will be a series, but let’s start with the fundamental difference, Query Results Cache
Again, this is not about import mode, also known as extract in Tableau, which generally speaking works the same way (PowerBI can have mixed mode, which is not supported in Tableau as far as I know)
The Data Model
The Model is very simple Star Schema, 1 Fact ( 5 years of electricity Data) and two dimensions, Calendar Table and Power Generation Plan attribute
I built this simple report, Total Mwh by substation
Tableau generate an Inner Join, same behavior as PowerBI
SELECT `DUID_DIM`.`StationName` AS `StationName__DUID_DIM_`, SUM(`UNITARCHIVE`.`Mwh`) AS `sum_Mwh_ok` FROM `test-187010.ReportingDataset`.`UNITARCHIVE` `UNITARCHIVE` INNER JOIN `test-187010.ReportingDataset`.`DUID_DIM` `DUID_DIM` ON (`UNITARCHIVE`.`DUID` = `DUID_DIM`.`DUID`) GROUP BY 1
I noticed filtering data is basically instantaneous, it is hard to believe it is using Direct Query Mode, you can see it here.
Tableau cached the results of the first Query, when you filter a substation, the data is already there, it does not need to send another SQL Query
Built the same report in PowerBI, every selection will fire a new SQL Query, yes it is still fast ( under a 2 second), but it is not instantaneous.
Here is an example in BigQuery Console
That was a simple example, but imagine 100 of users with a lot of Visuals, I suspect it will create a massive Workload on the source system, I think Tableau behavior, as a lot of other BI tools (Superset, Looker etc) make a lot of sense, and maybe it will be useful too for PowerBI.