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
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
I have to conclude that SAP doesn't expose BW to other vendors such as Microsoft, Tableau the same way it does to other SAP applications including SAP BOBJ, since we have no issues doing this basic stuff in SAP Business Objects. I don't want to pull in years of data into Power BI and then let the user slice and dice within this data set. It should rather be dynamically controlled at run time. Thanks for all of the replies!!
You may want to start a new post and see if you can get some better answers. I will agree that no one other than SAP plays nice with SAP. We tried Tableau 5 years ago and that was a disaster and have been using Power BI for about 4 years without any issue. However, we were not a mature company with a lot of BEx reports or BOBJ development. So for us we build as much as we can in our BEx query and then typically just have Power BI pass in the date filters. We also use Power BI to create dashboards (charts/graphs) and not for table based reporting, anything users need in table form or excel still go through BEx Analyzer.
Good luck!