Forum Discussion
SAP BW BEx Dynamic Parameters
Hi Anonymous,
You can follow below blogs to use query parameter in M query.
Power BI Desktop Query Parameters, Part 1
Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters
Regards,
Yuliana Gu
I will admit, I am absolutely new to Power BI. We use SAP Business Objects, Tableau etc., in our environment. All of these reporting, visualization tools allow the user to dynamically interact at report/visualization run-time and change the underlying BW BEx parameters. For example, I as the report author could have created and published company profitability visualization for the 1st Quarter of FY 2020. An end-user would like to refresh this visualization for the 2nd Quarter of FY2020. This is NOT possible in Power BI?
I've never taken the approach that your going after, we typically just load 1-2 years worth of data into Power BI and provide report filters to allow the user to filter by year, qtr, month, etc. In fact all of our power bi reports are exactly like this and we've had no performance issues or issues with report growing consuming to much storage space. That said, I believe there is a way to create paramters that allow you to adjust those at run time and then refresh the data based on those. You probalby want to look at the 'Manage Paramters' on the Home tab in power query. I believe that you can create paramters that you can wire up into your query and they allow you to change those at run time. You'll most likely not find any examples of this based on SAP but you should find some examples utilizing SQL Server.
Try this link:
https://channel9.msdn.com/Blogs/MVP-Azure/Pass-parameter-to-SQL-Queries-statement-using-Power-BI
Thanks for your post! It doesn't really answer my question. Once a report, visualization is published, I don't want to change anything. I would rather let the user change BW BEx query parameters at run time while interacting with the published version of Power BI visualization.
You can look at this:
https://www.youtube.com/watch?v=JlUfz18cx_w
I just followed these instructions and it works, however, I don't like how you have to manage the parameters once published to the service. You can still manage and update the parameters but my users wouldn't be allowed to because you have to change the parameter from the dataset settings and my users don't have access to this. Although, if you wacth the entire video it shows you how you can create a list of paramters that you can then drop on your page and then that should drive your refresh (I didn't wire it up this far).
The other option that I would suggest is just loading all your data into power bi (To a degree). We run BW on Hana and the direct connect is pretty painfully slow and I wasn't querying a lot of data. If you have to use power bi then I would look at this as on option if you don't go the route above.
Here I loaded all data from 2018 - 2020 (And this I can control dynamically from M Script so I'm only loading say the last year, last 2 years worth of data, or a rolling 12 or 24 months worth of data). I don't have a lot of users running reports for anything outside the last year (Usually just looking at the previous month). Typically, we pull 2 years of data so we can do year over year comparisons. Then I can add my filters to the page, and rather than querying the data and waiting for a refresh to happen, it's instant, were just filtering the dataset that has been loaded into Power BI.
In the images here I've added a year and a quarter filter. Now my users can easily filter data from 2018 to 2020 and select which quarter they want . In this scenario, it's instant, there is no refresh as the data has already been loaded into your power BI model.
That's all I got.
We try to use Bex Exit variables for time. I would rather control all the dynamic things in one place, Bex. Then all the tools we have run the same way if using the same query. We setup YTD/QTD/MTD ranges as exits and I build out preconfigured measures based on this.. ie YTD Sales $, YTD Order Qty... Sales Today, Sales Yesterday, YTD PYr.. etc. and let the backend do all the work. Coming from BOBJ tools, PBI doesn't play well with BW.
james
