Native Query issues

[Frbelotto]

Hello guys!
I am starting to construct a dashboard based on a analitical table on a DB2 server.

I´ve created the datada source from using Power query:

let
    Fonte = DB2.Database(BBDB2, BDB2P04, [HierarchicalNavigation=true, Implementation="Microsoft"]),
    QUERY = "SELECT  T1.STATUS ,
	T1.PRODUTO ,
	T1.PARCEIRO,
	CAST(""data_transação"" AS DATE) AS DATA_TRANSACAO,
	CAST(""data_contabilização""  AS DATE) AS DATA_CONTABILIZACAO,
	CAST(MCI AS CHAR(10)) AS MCI,
	T1.MARCA ,
	T1.COD_TRANSACAO,
	T1.FORMA_PAGAMENTO, 
	T1.GMV	AS VALOR_TRANSACAO,
	T1.RECEITA ,
	T1.CASHBACK 
FROM DB2I023A.SHOPPINGBB t1
ORDER BY
	DATA_TRANSACAO asc",
    SHOPPINGBB1 = Value.NativeQuery(Fonte, QUERY)
in
    SHOPPINGBB1

So far, this query runs smoothly inside power query (and much faster than using the commom power query reading)

But as soon as I try to load it to my dashboard, I keep getting this error on any kind of data retrieve:

I really don´t know what is happeing. I am trying to use native query because I want to create a secondery data source of such data grouped by some columns and use PWBI aggregations!

Sometimes, just after editing the native query parameters, I get this error :

But it does disapear as soon as I ask for a data refresh

[Anonymous]

Hi @Frbelotto ,

Please try the following methods and check if they can solve your problem:

1.Ensure that the account used to access the DB2 has the correct permissions.

2.Verify that the credentials are configured correctly.

3.Consider simplifying the query into smaller parts.

4.Try to import the data from DB2 into the power bi instead of using DirectQuery.

Power Query IBM Db2 database connector - Power Query | Microsoft Learn

5.You can also view the following link to learn more about DB2.
Solved: Power BI Direct query connection to IBM DB2 - Microsoft Fabric Community

Solved: Power BI connection to IBM DB2 - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Frbelotto]

1) I have read and write permissions on such schema (I am writing the data using an python script)

2) The credentials are ok. Are the same from the python script and the native query even works if I click on "refresh"

3) I´ve tried a simple "Select * from DB2I023A.SHOPPINGBB" and the error are all the same. 

4) Using import does not seen to be a great option as the DB has millions of rows and are updated daily (usually)

5) I will check the suggested links. 

[commercial_user]

Hi can you translate the error verbatim? Not sure if you are having a direct query issue based on restrictions of composite modeling or if its an error related permissions. 

Thanks,

[Frbelotto]

"Unable to load data for this visual
DirectQuery error: DirectQuery cannot be used with this data source. It is recommended that you migrate to a supported data source or upgrade your SQL Server data source to the latest available version"

I am using a IBM DB2 data source.