Dynamic Parameters with SAP BW BEX

[ncbshiva]

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.

[v-cherch-msft]

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

[ncbshiva]

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.

[ncbshiva · 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" }}},

[Anmolgan]

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.

[ncbshiva]

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.

[Anmolgan]

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?

[ncbshiva]

yes , everything is working fine without any issues.

Moreover i am using Power BI Premium for this.

[Anmolgan]

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

[ncbshiva]

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

[Anmolgan]

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

[Anmolgan]

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 ?

[ncbshiva]

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]

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?

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

[ncbshiva]

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]

Hi ,

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

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

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

Thanks

[Anonymous]

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

[Anonymous]

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

---

[ncbshiva]

Hi,

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

[Anmolgan]

How did you build this function inside of the PowerBI?