ORA-00904 Error when loading batches using Direct Query on a Oracle Datasource

[Anonymous]

I'm running some tests to get familiar with the software and encountered an error which I suspect is a bug in how batch loading of data into the table visualization.

The setup I'm using is that I've created a direct query, using my own query, datasource to a in-house oracle database. I've then added a table vizualization containing 2 columns with simple data (itemnumber, item description). The data loads into the table fine but when I scroll down and surpass a certain number of records the vizualisation breaks with a "Couldn't load the data for this visual" followed by an oracle error code "ORA-00904: "t0"."DESCRIPTION": invalid identifier. The exception was raised by the IDbCommand interface."

This had me quite perplexed. The data is fine when viewing it in the query editor and when running it directly against the database using SQL Developer. Clearly there was something going on with query transmutation done by Power BI (t0 is not an alias I've used in my query).

So I went sleuthing and found the FrownSnapShot for this instance and opened up the FlightRecorderCurrent.trc file in Notepad++. Not being an expert not a lot of it made much sense to me and it looked like parts of it was machine code but at the end I found the following query:

SELECT * FROM (
SELECT
"t0"."CONTRACT","t0"."PART_NO","t0"."DESCRIPTION"
FROM
(
(select
  inventory_part.contract,
  inventory_part.part_no,
  inventory_part.description,
  inventory_part.skc_no,
  inventory_part.type_designation,
  inventory_part.DIM_QUALITY,
  inventory_part.note_text,
  inventory_part.unit_meas,  
  inventory_part.accounting_group as commoditygroup,
  inventory_part.create_date,
  inventory_part.c_manuf as manufacturer,
  inventory_part.part_no||contract as itemkey
from ifsapp.inventory_part)
)
 "t0"
WHERE
(("t0"."CONTRACT" > '001D') OR
(("t0"."CONTRACT" = '001D') AND
(("t0"."PART_NO" > '3093716') OR
(("t0"."PART_NO" = '3093716') AND ((SELECT (CASE WHEN Op1 IS NULL THEN CASE WHEN Op2 >= ' ' THEN 1 ELSE 0 END ELSE CASE WHEN Op1 < Op2 THEN 1 ELSE 0 END END) FROM (SELECT "t0"."DESCRIPTION" AS Op1, 'SKJORTA' AS Op2 FROM DUAL) AuxTable) = 0))
)
)
)

GROUP BY "t0"."CONTRACT","t0"."PART_NO","t0"."DESCRIPTION" ) WHERE ROWNUM <= 1000001+

Running this query in SQL Developer I managed to recreate the ORA-00904 and it seems to be caused by the 4th row in the where statement:

(("t0"."PART_NO" = '3093716') AND ((SELECT (CASE WHEN Op1 IS NULL THEN CASE WHEN Op2 >= ' ' THEN 1 ELSE 0 END ELSE CASE WHEN Op1 < Op2 THEN 1 ELSE 0 END END) FROM (SELECT "t0"."DESCRIPTION" AS Op1, 'SKJORTA' AS Op2 FROM DUAL) AuxTable) = 0))

The problem I suspect is that this code is embedding a subquery (See the red text) at a level where the alias "t0" is no longer recognized by the oracle query syntax. From what I can tell the purpose of this query is to load additional rows of data going from the latest (?) loaded record (ie next data batch) and this would explain why I'm seeing this if I scroll through the records rather than immediately.

I suspect this might be caused by me using the software in an unaggregated manner (which I suspect it's not set up for looking at those group by's) but since this is to me seems to be a bug I figured I'd submit it.

I managed to work around it by applying a report-level filter to reduce the record count fetched to the table vizualisation.

[v-haibl-msft]

@Anonymous

I think this should be the same issue as the one mentioned in this thread. Same exception is thrown in the similar part of sql statement. Please take a look at the response from the product team.

Best Regards,
Herbert

[Anonymous]

@v-haibl-msft

I believe you're right it looks to be the same issue. I have yet to locate the search function...I apologize for double posting.

Thank you for your response.