I frequently work on projects where we have multiple tiers on which our solution is deployed to using continuous integration / continuous deployment (CI / CD) pipelines in Azure DevOps. Once everything is deployed, you also need to monitor these different environments and check the status of the data or ETL pipelines. My tool of choice is usually Power BI desktop as it allows me to connect to e.g. SQL databases very easily. However, I always ended up creating a multiple Power BI files – one for each environment.
Having multiple files results in a lot of overhead when it comes to maintenance and also managing these files. Fortunately, I came across this little trick when I was investigating in composite models and aggregations that I am going to explain in this blog post.
To be honest, I barely used Power BI Direct Query in past and so maybe this feature has been there for quite some time without me realizing it but It may also be that it was introduced just recently with composite models.
So the “feature” is, that you can also use Query Parameters to parameterize your Direct Query queries. This is pretty awesome if you think of it for a second:
- easy switching between databases
- use one file for all environments
- only maintain a single file
- no need to import/load any data
The configuration within Power Query is also quite easy – simply replacing the hard coded values with the ones from the parameters:
And that’s it already! you can now easily switch between different databases by just using Power BI parameters and the Direct Query connection will change automatically to the new server/database.
Of course, all the target servers/databases have to have the same schema otherwise, you will get an error.
Caveats:
Even though this looks quite trivial, there are some caveats which makes me believe this is not fully supported yet. You may noticed above already that in Power Query, when going to the step that actually queries the database, it complains about that this step would cause the whole table to be converted to Import Mode. However, you can just ignore it and go on with the next step to remain in Direct Query Mode.
It seems like Power BI keeps track from where a table was originally imported. So if you want to add a new table, make sure to copy an existing Direct Query table and change it accordingly instead of going to “New Source > …” !
Also, you need to make sure that you have entered the credentials for the different source databases at least once – otherwise Power BI will ask you when you query the database the first time. This is also the reason why this does not work so well in the Power BI service as changing the parameters there is not as simple as it is in Power BI desktop.
As I said, I do not know if this is a new feature (or a feature at all), but it is definitely helpful for certain scenarios.
Downloads:
Power BI Workbook: DirectQuery_wParameters.pbix
Pingback: Parameterized Direct Query In Power BI – Curated SQL
The file you provided couldn’t be open in my Power BI Desktop:
Version: 2.68.5432.841 64-bit (April 2019)
Have you succeeded in changing parameter values at Power BI Service? I haven’t. Each time I go to Parameters section I get the following message:
“Parameters haven’t been defined for this dataset yet. If you want to set parameters, use the Query Editor.”
regarding opening the file locally: as for every Direct Query file, it tries to connect to the data source and this fails in your case as you probably do not have you SQL server running at localhost\V2017 and/or do not have a database called AdventureWorksDW2012 or AdventureWorksDW2014
You can try changing this after ignoring the initial error message and fill in your values in the parameters
I have not played with the PBI service and this approach yet. In my case I was using a local SQL Server. Deploying this to the service would make it much more complicated. If you have an Azure SQL DB instead, this would make sense
You cannot use parameters with DirectQuery mode in Power BI Service ? Dont know why that is not allowed
Please vote here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/35609599-change-parameter-values-within-direct-query-source
Hi
DirectQuery with parameters is great as an idea but the real obstacle is
that you cannot populate parameters using filters and criteria from the
PBI report.
So the typical reporting scenario where you need to pass parameters to
the database to get back results is simply not possible.
Is there any workaround for that ?
Thanks
As of now you cannot change parameters from the report canvas but I totally agree that would be really useful.
Maybe they add this feature in the future
-gerhard
Using parameters in PowerBI desktop seems to work for Import and Direct Mode but after deploying your report to PBI Service it seems that the parameters used in the DirectQuery are converted to strings and will not be recognized as parameters any more.
yes, thats definitely possible
I did not test this yet but it just shows that this approach is not fully supported yet I guess
-gerhard
Can we write back data from power bi to the database . For instance lets say I have a slider to select one of the parameter and after selection I get min and max value . Can this value be written back in a database .
Could you please let me know the feasibility .
Thank you in Advance
Hi Aravind,
no, thats not possible.
If you want to do write-back from within PowerBI you may have a look at PowerApps
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/powerapps-custom-visual
regards,
-gerhard
you can include PowerApps in PowerBI. This has the advantage that you can provide values from your PowerBI report where you have made a selection bases on your slider / filter settings to your PowerApps.
true, but thats a different scenario
even with PowerApps you cannot change parameters in PowerBI which would have impact on the Direct Query connection which is the idea of blog post
Hi
I have tried the same exact setup as you and I get the following error message when I try to apply query changes:
“Connecting to tables from more than one database isn’t supported in DirectQuery Mode”
so in other words I can’t simply ignore the warning message regarding import, since it won’t let me apply the query changes.
I am using Power BI Report Server (May 2020) version. Not sure if this could have something to do with it?
As PBI Report Server uses older builds of what the regular Power BI uses, it may be that this is not supported with PBI RS
Opening the .pbix in my regular PBI Desktop still works just fine
Hi
Thanks for your post!
In fact, my question is this solution can be a substitution for passing parameters like what we have in a ssrs reports for sql server data source.
partially
It may work “OK” in PBI Desktop where you can easily change the parameters but is not feasible once the report is deployed to the PBI service where you need to change the parameters on the dataset.
did you have a look at paginated reports? thats basically the SSRS integration in PowerBI
-gerhard
Is it possible to make it work for direct query SAP HANA?
I guess, just give it a try!
please also update us here once you have some results