SAP BW Connection: MDX statement invalid for key date SAP VARIABLE

[arnezsng]

Hi,

We are experiencing the following issue:

Issue Description
When connecting to SAP BW and running a query that contains a date as a parameter, Power BI is generating a seemingly invalid MDX statement.

The error message in German reads:

DataSource.Error: Der SAP BW-Server hat einen Fehler gemeldet: Für Merkmal 0CALDAY bitte Wert in zulässigem Format eingeben. Weitere Informationen zu diesem Fehler erhalten Sie, indem Sie die SAP-Supportwebsite besuchen und nach "BRAIN 599" suchen. Details: DataSourceKind=SapBusinessWarehouse DataSourcePath=192.168.102.20/00/100 ID=BRAIN NUMBER=599 MESSAGE_V1=0CALDAY MESSAGE_V2=20200229 SYSTEM=LGEBWM100 Command=SELECT { [Measures].[00O2TK9R8HCEIGPSEO1AGZLQI] }ON 0, NON EMPTY DISTINCT( [0RENTOBJECT].[LEVEL01].ALLMEMBERS ) PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1 FROM [RMBMFORM/A31_A85_RUECKSTAND] CELL PROPERTIES VALUE SAP VARIABLES [ZVA_PERI] INCLUDING [002.2020] [ZVA_STIC] INCLUDING [20200229]

Culprit
The reason for the issue is the SAP VARIABLES statement "[ZVA_STIC] INCLUDING [20200229]". If it would read "[ZVA_STIC] INCLUDING [0CALDAY].[20200229]", the MDX statement would be valid. The ZVA_STIC is the "key date" of the SAP BW query. Currently, we are not sure if this has anything to do with this issue or not.

Side note: There are other queries containing the same ZVA_STIC variable on another cube in the same SAP BW for which a query syntax like "[ZVA_STIC] INCLUDING [20200229]" works perfectly fine. But in this particular query, it does seem necessary.

Workaround
The know workaround is to use the corrected query in the MDX statement window of the SAP BW connection like this:

Better solutions

Option 1: If Power BI allowed us to enter the value [0CALDAY].[20200229] into the date field directly, that would be a fine enough solution for us. See the next screenshot for an example.
Option 2: Even better would be if Power BI automatically detected which "dimension" would need to be added in front of the "member" for a variable, check https://wiki.scn.sap.com/wiki/display/BI/MDX+syntax+and+concepts+%284%29+-+SAP+variables for reference.

We had also tried editing the Power Query M syntax in the extended editor of Power Query but to no avail as Power BI expects the input for the variable to be a date.

[v-chuncz-msft]

@arnezsng 

Thanks for sharing the workaround. To get faster and better technical support for this issue, you may directly create a support ticket.

[arnezsng]

For anyone stumbling upon this issue:

It appears that the SAP Note https://launchpad.support.sap.com/#/notes/2998169 fixes this issue on the SAP side.