Bug P2
Status Update
No update yet.
Comments
[Deleted User] <[Deleted User]>
After looking further into this, we've discovered that data studio is issuing queries to BigQuery in a way that duplicates numbers. - there seems to be others with the same issue https://stackoverflow.com/questions/56120679/is-it-possible-to-use-bigquery-repeated-fields-in-data-studio-calculations/56122443 .
The possible solution presented in that post, although good for showing numbers in separate charts in the same report (since DS will then run separate queries). We can't aggregate and divide/multiply two numbers on different levels. - If we want to aggregate two numbers from parent and child nests and divide them together, it won't work. e.g. SUM(transactions.ecommerce_purchases) / COUNT(DISTINCT transactions.items.item_id).
DS will cross join the two nests together and duplicate the results of transactions.ecommerce_purchases
The possible solution presented in that post, although good for showing numbers in separate charts in the same report (since DS will then run separate queries). We can't aggregate and divide/multiply two numbers on different levels. - If we want to aggregate two numbers from parent and child nests and divide them together, it won't work. e.g. SUM(transactions.ecommerce_purchases) / COUNT(DISTINCT transactions.items.item_id).
DS will cross join the two nests together and duplicate the results of transactions.ecommerce_purchases
[Deleted User] <[Deleted User]>
Yes, it is related. That's the same bug.
ho...@google.com
As a side comment, I usually avoid Data Studio with nested rows for a different reason: BI Engine doesn't accelerate nested rows either - and BI Engine does amazing things for BQ+DS dashboards.
Instead, I create flat tables just for BQ+BI+DS, and things work really well.
Instead, I create flat tables just for BQ+BI+DS, and things work really well.
[Deleted User] <[Deleted User]>
Thanks for the advice, but that's not a fix to the bug :).
There are other benefits to nested tables that outweigh BI engine benefits. Such as the ability to have pre-joined data in one table, in a nested format, minimise the number of tables we hold in BQ and the number of data sources in DS. Also, I'm aware that nested tables support for BI engine is being worked on by you guys - see this issue here that I raised previously (about BI engine not optimizing)https://issuetracker.google.com/issues/146359808#comment2
But anyway, that's off-topic to what this ticket's issue is about
There are other benefits to nested tables that outweigh BI engine benefits. Such as the ability to have pre-joined data in one table, in a nested format, minimise the number of tables we hold in BQ and the number of data sources in DS. Also, I'm aware that nested tables support for BI engine is being worked on by you guys - see this issue here that I raised previously (about BI engine not optimizing)
But anyway, that's off-topic to what this ticket's issue is about
Description
It's not easy to reproduce, I've attached two examples. The issue is intermittent, one some pages it will output the correct results most of the time. On other pages, the incorrect result is there permanently and can be reproduced like shown in "example-2.png"
Some details about the setup:
- Data Source: BigQuery
- BigQuery Table type: nested and partitioned
- BigQuery Field Data types: Numeric / Integer