How to pass SAP Variables via MDX query

[OUP]

Hi all,

I am trying to pull a significant chunk of data from an extremely large SAP BW InfoCube. Currently I am pulling the data from SAP BW via the inbuilt Power BI connector and applying a filter at the MDX query stage which helps significantly reduce the size of the data pulled.

Example taken from Microsoft's MDX documentation:

SELECT  
    { [Measures].[Sales Amount],   
        [Measures].[Tax Amount] } ON 0,  
    { [Date].[Fiscal].[Fiscal Year].&[2002],   
        [Date].[Fiscal].[Fiscal Year].&[2003] } ON 1  
FROM [Adventure Works]  
WHERE ( [Sales Territory].[Southwest] )  

The caveat to this is that I am querying an InfoCube which requires a value to be passed through a parameter. I can do this when importing not via MDX:

How would I go about doing this via MDX query? The above process generates the Power Query M code below:

{Cube.ApplyParameter, "[0PERIV]", {"[0FISCVARNT].[O1]"}}

[Anonymous]

Hi @OUP ,

Please try this:

Define the parameter in your MDX query:

 WITH 
   MEMBER [Measures].[ParameterValue] AS '[0FISCVARNT].[O1]'
   SELECT 
       { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS,
       { [Date].[Fiscal].[Fiscal Year].&[2002], [Date].[Fiscal].[Fiscal Year].&[2003] } ON ROWS
   FROM [Adventure Works]
   WHERE ( [Sales Territory].[Southwest], [Measures].[ParameterValue] )

Apply the parameter in Power Query M code:

let
       Source = Cube.ApplyParameter(
           Cube = SAPBW.Cube("YourServer", "YourCube"),
           ParameterName = "[0PERIV]",
           ParameterValue = {"[0FISCVARNT].[O1]"}
       ),
       FilteredData = Cube.Transform(Source, {
           {Cube.AddMeasureColumn, "Sales Amount", "[Measures].[Sales Amount]"},
           {Cube.AddMeasureColumn, "Tax Amount", "[Measures].[Tax Amount]"}
       })
   in
       FilteredData

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[OUP]

Thanks for your response @Anonymous.

I'm running into the following error and have tried both variations of Cube = and Cubes = :

Steps I followed:
1. Defined parameter via query -> error outlining that I need to specify parameter appears
2. Advanced editor; applied parameters (ignored FilteredData)

Expression.Error occurs.