SAP BW BEx Dynamic Parameters

[Anonymous]

I wanted to see how others are querying SAP BW through BEx and see how and if you are leveraging dynamic parameters. I realize that what I'm about to ask could be controlled at the BEx level but I like to have more control at the Power BI Layer. 

The first time you connect to a BEx query you are prompted to enter required parameters (And optional if needed)

Once you select all your fields and input your parameters you M script looks something like this: 

 

However there are times when I have date parameters and I'd like to Power BI to automatically pull in today's date by dynamically setting the date. So I do something along the lines of this where I create variables within M to build the parameters 

I then update the parameters in the Mscript to reference the variables I've created

This has worked thus far but I wanted to see how others may be going about this and see if maybe there was a more robust way to manager this utilizing the Manage Parameters, Parameter tables, and potentially functions to look up parameter values and pass them in. 

Thoughts? 

[Anonymous]

Maybe I am missing something here. The issue still persists. I have a test Power BI analytic that "direct queries" SAP BW. It works fine in Power BI desktop client and I can change BEx query parameters within the Power BI desktop environment. However, the moment I publish it, I no longer can see the BEx query parameters in order to change the parameter values at runtime. Am I missing something here?

Thanks

Jagannathan Santhanam

[Anonymous]

Not sure how you've set this up but the idea here is that the paramters are dynamic, there is no need to change them. The should populate for you based on the logic that you've written. Look at the last post above your original, you'll see that a paramter was created called TodaysDate and then this value is what gets passed into your M script. Nothing in this post deals with manually changing paramters but rather the paramters are dynimacally/self populated for you. 

Now I say this without ever having done a direct connect to SAP BW. So maybe your case is different. That said, could you post your mscirpt or some screenshots of what your tried to achieve so I can have a better idea. 

[Anonymous]

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? 

[Anonymous]

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

[Anonymous]

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. 

[Anonymous]

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. 

[james_batchelor]

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

[Anonymous]

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!!

[Anonymous]

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! 

[Anonymous]

We have a BW Query on Accounts Receiveable and use a dynamic filter for today's date. Rest of the logic in the report is based on this variable. 

I am able to create a report on this query, issue is when i refresh next day the dynamic variable date does not change to today's date as it happen in Bex. 

What is the solution for this.

thanks,

MAT.

[Anonymous]

Are you using this variable in your M script like the example I posted? Or are you creating a parameter and driving your data refresh with the parameter? 

If your using M script, similar to how I posted in the initial post then post you M code so we can see how your doing this. 

[Anonymous]

this is what i have and need to pass today's date. I am new to PowerBI.

[Anonymous]

@Anonymous , 

Look at my original post, it has this exact example. 

There are a couple of ways you can do it but this should work. Based on this code you could create a todays date value and pass that value in or create another field that contains the formatted value that needs to be passed to sap bw. 

You can then use this two different ways. One way is to create a paramter with the parameter name that you want to pass to SAP or just pass in todays date. Here is how both of those look

{Cube.ApplyParameter, "[!V000002]", {Parameter}},

or 

{Cube.ApplyParameter, "[!V000002]", {"[ZINV_DUE].[" & TodaysDate & "]"}},

Play with both of these and see which one works for you. You could even create a function where the function would take a date and a parmater name and spit out the results. The function could be created so that it's generic and could work with many different data types or variables. That said, it would still follow this same type of implementation. 

{Cube.ApplyParameter, "[!V000002]", {fnCreateParameter("ZINV_DUE", TodaysDate}},

which would return the following (Of course you would have to create the function in this scenario). 

[ZINV_DUE].[20190930]

Hopefully this helps. 

[v-yulgu-msft · Accepted solution]

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