Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ncbshiva
Advocate V
Advocate V

Dynamic Parameters with SAP BW BEX

Hi Team,

 

I am using the SAP BW for connecting the data. I have 6 years of data . Initially i am pulling only 2 years of data into Power Bi desktop, so that i wont get any memory issues when i try to load all the 6 years of data.

 

And Fiscal Year is a variable in SAP Bex and below is the power quey code for the same with 2 years filtered.

 

{Cube.ApplyParameter, "[0S_FYEAR]", {{"[0FISCYEAR].[K22017]", "[0FISCYEAR].[K22018]"}}},

 

Now i want to create a parameter in Power BI and use that same in my Power Query.

So that i can publish the pbix file with 2 years of data in online and change the parameter value for all the six years , so that it will refresh in online without any issues.

 

Please let me know how to achieve this solution.

 

Thanks in advance.

1 ACCEPTED SOLUTION

I was able to achieve this solution with help of some blogs.

 

Below is the example on how to pass the parameters in the SAP BW query.

 

First you need to define your parameter , in my case it was Fiscal Year

 

#"GetParameterValue" = "[" & (Fiscal_year) & "]",

 

Then you need to use the above parameter in the cubeApplyParameter query as shown below.

 

{Cube.ApplyParameter, "[0S_FYEAR]", {{"[0FISCYEAR]." & #"GetParameterValue", "[0FISCYEAR]." & #"GetParameterValue" }}},

 

 

View solution in original post

19 REPLIES 19
v-cherch-msft
Employee
Employee

Hi @ncbshiva

 

You may follow below articles to use query parameter in query editor.

Power BI Desktop Query Parameters, Part 1

Power BI Desktop Query Parameters, Part2 – Dynamic Data Masking and Query Parameters

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-cherch-msft,

 

This will help me when i am using SQL and other data sources. In my case i am using SAP BW BEX ,so this is different from others.

And that example will not work i think,

 

Please let me know if anyone has implemented with SAP BW with variable as parameters in Power BI.

 

Thanks in advance.

I was able to achieve this solution with help of some blogs.

 

Below is the example on how to pass the parameters in the SAP BW query.

 

First you need to define your parameter , in my case it was Fiscal Year

 

#"GetParameterValue" = "[" & (Fiscal_year) & "]",

 

Then you need to use the above parameter in the cubeApplyParameter query as shown below.

 

{Cube.ApplyParameter, "[0S_FYEAR]", {{"[0FISCYEAR]." & #"GetParameterValue", "[0FISCYEAR]." & #"GetParameterValue" }}},

 

 

Hi I dont think the way that you are doing is ever helped you at all, think for a minute if you can easily fetch 2 years of data without any issues, and you somehow define the parameters for start and end date, and you now want to pull data for 6 years, the powerbi serivce will refetch that amount for 6 years and it will throw some errors rather at the PowerBI Service side, or SAP BW Side, for me I dont have any parameters defined, but I am appending 4 months each in my qureies and going upto 3 years and append all those quries into 1 single query and build my report using that, this works for a report in which I am not selecting too much amount of data.

 

For another report this fails at the SAP BW Side and in powerbi I dont see any errors, but there are several dumps generated at the SAP BW side and those stops the ongoing process. 

 

So how can you say that if I define my start and end date parameters at PowerBI Side this will ever work? For me I am only able to extract 4 months of data, so if you say that I will define my parameters in PowerBI Service and refresh the report for 2 years it will never refresh for me.

 

Above make any sense to you??

Neither we can choose incremental refresh since its not available for SAP-BW.

Hi,

 

For me i have loaded only one year of data in Power Bi desktop. Created the parameters and used them to load 3 years of data in the Power Bi service , which is working fine for me since 8 months. 

 

Initially i tried to load all the 3 years data from SAP BW in the Power BI desktop , but it was failing with some memory issues,

 

So using parameters to load more data from SAP BW will make sense.

So your single query can load 1 year of data into PowerBI, and after that you loaded total of 3 years of data into powerbi service using parameter using that single query? and its working fine every since?

yes , everything is working fine without any issues.

Moreover i am using Power BI Premium for this.

Is there any reason for using powerbi premium? what is your SAP BW System configurations??

To use linked entities i am using Premium service and i dont know about the SAP BW configurations.

That is being maintained by some other team

Do you know of any ways to perform delta load for SAPBW into powerbi service?

I want to achive the same thing, I have 10 Months of data loaded in my BI Desktop, but I want to see 2 Years of data and this causing problems to load in BI desktop, Can you elaborate more in how can I achive this from BI Service ?

Hi,

 

You need to create parameter and pass that into the CubeApplyParamter (BEx Query parameter).

I have created a function by using below code and called that in my Bex Query :

 

let
Source = (Fiscal_year as text) => let
#"GetParameterValue" = "[" & (Fiscal_year) & "]",

 

 

let
GetCurrentYear = Date.Year(DateTime.LocalNow()),
FirstYear = GetCurrentYear - #"YearRange"+1,
GenerateYears = {#"FirstYear"..#"GetCurrentYear"},
#"Converted to Table" = Table.FromList(GenerateYears, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom1" = Table.AddColumn(#"Converted to Table", "Prefix", each "K2"),
Renamed_Columns = Table.RenameColumns(#"Added Custom1", {{"Column1", "Years"}}),
MergedColumns = Table.CombineColumns(Table.TransformColumnTypes(Renamed_Columns, {{"Years", type text}}, "en-US"), {"Prefix", "Years"}, Combiner.CombineTextByDelimiter("", QuoteStyle.None), "Fiscal_Year"),
GetSapBwDat = Table.AddColumn(MergedColumns, "SapBwData", each GetSAPBWData([Fiscal_Year]))

 

 

Anonymous
Not applicable

Hi I also have the same requirement but my source is SAP HANA.  I have created two parameters in PBI Desktop 1. Calday from 2. Calday2. And this parameter I should pass to the Advance editor dynamically. can you explain how to do this?

 

Capture.JPG

 

My requirement is to pass the Calday values dynamically to SAP HANA view and get the data on the fly. so we can load the historic data year by year with an incremental refresh.

 

Thanks

Hi ,

 

You can create a parameter called Calday with some values which is required for you.

 

And replace the current value with Parameter that you have created.

#"GetParameterValue" = "[" & (Calday) & "]",

 

{Cube.ApplyParameter, "IP_CALDAY_FROM",& #"GetParameterValue"}

{Cube.ApplyParameter, "IP_CALDAY_TO",& #"GetParameterValue"}

Anonymous
Not applicable

Hi ,

 

we have implemented as you said but still, we are facing issues as shown in the below.Capture.JPG

In My scenario, Caldayfrom and CaldayTo parameters are defined like this.  

Cap4.JPG

CalDayFrom : Type : Date

                      Suggested Value: Any value 

                      Current value: 01-Aug-16

CalDayTo : Type : Date

                      Suggested Value: Any value 

                      Current value: 30-Aug-16

 

In my SAP HANA spurce data is coming as follows

 

Cap3.JPG

 

Thanks

 

Anonymous
Not applicable

How did you solved this issues? I have the same one

Anonymous
Not applicable

Power BI 11.JPG

this is what i wanted to change. I am new to PowerBI.


Hi,

 

Change your parameters to the required format like 20160108. It should work

How did you build this function inside of the PowerBI?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.