Current_Database() function unexpected results when queried with PowerBI

[DrK]

Hi,

I`m creating some views to be queried by PowerBI. In our delta tables we have a column called database name which contains the source systems database name. What I`m doing is using this to filter data WHERE databaseName = current_database(). Now I`ve had to replace the `s that come through this value and all works ok when queried within a worksheet, i.e. if I change database\schema context, my rows get filtered. However, if I query the same view from PowerBI, I got no data at all. I`ve tested the view using a hardcoded database name i.e. WHERE databaseName = 'the database name' and that works fine. The ony thing I could think it could be is priviledges, as the PAT token created for access by PowerBI only has SELECT priviledges on the database, wondering if it can`t use the Current_Database() function or similar.

Drk.

[Kaniz_Fatma · Accepted solution]

Hi @Andy Skinner​, Thank you for contacting us regarding the issue you're experiencing when querying views using the current_database() function in Power BI. I understand that the ideas work as expected within a worksheet, but no data is returned when querying from Power BI.

Based on your description, the issue may be related to privileges or how Power BI handles the current database () function.

Here are a few suggestions to help you troubleshoot the issue:

1. Check privileges: Ensure that the personal access token (PAT) used by Power BI has the necessary rights to execute the current_database() function. If possible, try granting additional privileges to the PAT, such as EXECUTE or USAGE, to see if it resolves the issue.
2. Modify the view: Instead of using the current_database() function in the view definition, consider using a parameterized view or a stored procedure that takes the database name as a parameter. This way, you can pass the database name as a parameter when querying from Power BI, eliminating the need for the current_database() function.
3. Power BI DirectQuery mode: If you are using Power BI's DirectQuery mode, there might be limitations when using certain functions like current_database(). In this case, you could try switching to import mode, which retrieves the data from your database and stores it within the Power BI file. Remember that this might increase the size of your Power BI file and affect data refresh times.
4. Check Power BI logs: Examine the logs generated by Power BI to identify any errors or issues related to the current_database() function. This might provide more insight into the root cause of the problem.

If none of these suggestions helps resolve the issue, please provide us with any error messages, logs, or relevant code snippets so we can investigate further.

[DrK]

Hya,

Responses.

1. User has select and use on the schema, have tried execute the other day but it made no difference.
2. We can`t do this as the database filtered by the view has to be controlled by us as it`s part of a multi-tenanted setup.
3. Tried this. Seems to work!
4. See above.

Thanks for the response!