drill down to a database SQL query

[stesappo]

Dear All,

I'd like to know if there is a possibility, starting from a dataset in Power BI, to drill down the aggregated dataset to a database SQL query (like Oracle databasde or whatever RDBMS).

It it possible to use Power BI in this way?

Is it possible to pass a SQL query with filters and then retrieve the result?

Regards

[Anonymous · Accepted solution]

Hi @stesappo ,

If the data source is Oracle, you can refer the following link to get it by filling in SQL statement the Advanced options section just as below screenshot... Please note that Power BI Desktop doesn't support Oracle native queries that execute a stored procedure and Oracle native queries in "begin ... end" block doesn't return any result set.

Connect to an Oracle database with Power BI Desktop

Best Regards

[amitchandak]

@stesappo , In direct query you can use aggregated tables, that can be there in import or direct query mode 

https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced

You can pass value using dynamic M parameter again in direct query

https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/

[stesappo]

So, if I've understood correctly, I can use dynamic M parameter in direct query to retrieve a dataset from a RDBMS (like Oracle) and have it as result in the Power BI in-memory database, correct?

Regards