Forum Discussion

OUP's avatar
OUP
Frequent Visitor
1 year ago

How to pass SAP Variables via MDX query

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]"}}

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      OUP
      Frequent Visitor
      1 year ago

      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.