Obtaining MDX data in SAPUI5 via XMLA

1. Introduction

SAP UI5 developers often struggle with obtaining data from the backend. The common way to access backend data is the OData protocol.

The OData interface provides such advantages as selecting. binding, filtering and sorting data.
On the other hand, there are some flaws in OData protocol as to handling OLAP Data for analysis.

MDX (Multidimensional Expressions) has established itself as a common query language for OLAP data. XMLA (XML for analysis) is an open standard for requesting OLAP Data with MDX.

Below find my description of  how MDX Data can be obtained using XMLA in javascript for SAP UI5.

SAP offers the ISCF node /sap/bw/xml/soap/xmla/ as a web service data provider interface.

This web service offers two methods: Execute and Discover. In the following article is the focus on the Execute Method.

A JQuery post request will send the MDX query envelop in a XML structure as a web service request.

2. Create and test the MDX Query

Transaction MDXTEST allows us to create, test and execute a MDX Query.

The MDX query can be directly executed.

Execution of MDX Query

In this example, I used a Query which is provided in the trial SAP 7.52 Developer Edition.

https://developers.sap.com/trials-downloads.html?search=7.52

MDX example

SELECT
      NON EMPTY
        { [48CSWO6CA6TXXHDU2Q35OLJW0].Members }
      ON COLUMNS,
      NON EMPTY
        { [0D_FC_COUN                    0D_FC_COUN_H001].Members }
      DIMENSION PROPERTIES
        LEVEL_NUMBER
      ON ROWS
      FROM
        [0D_FC_C02/0D_FC_AE_EOIBV_Q001]

Key Figures are selected in the columns, the Country hierarchy is selected in the rows.
Additionally the property of the hierarchy level is included in the row dimension.

3. Preparing the MDX Statement for the XMLA call

The MDX statement needs to be embedded in a xml structure for the web service post request.

    var _sRequest = `
    <Execute>
      <Command>
        <Statement>
          ${_sMdx}
        </Statement>
        <Properties>
          <PropertyList>
            <Format></Format>
          </PropertyList>
        </Properties>
      </Command>
    </Execute>`;

The webservice request will be initialized by the JQuery post request to the endpoint.

 $.post("/sap/bw/xml/soap/xmla/Execute",_sRequest , _fProcessData);  

4. Processing the XMLA response

_fProcessData is the callback Method for processing the response of the request.

The response can be analysed in the debugger.

From the response, the output in Axis0, Axis1 and CellData is relevant to obtain the MDX data..

Axis0 delivers the columns Information about the key figure.
The element Caption contains the key figure name in the text node.

Axis1 contains the rows with the requested attribute of the hierarchy level of the row dimension.
The first attribute of the member, here “Hierarchy” contains the technical name of the row column.

Axis 1

At first the response for the root element needs to be checked. If this element is not present, it indicates an error present. The error message will be passed in the rejection of the promise.

      var _oRoot = oData.querySelector("ExecuteResponse>return>root")
      if(!_oRoot){
            debugger;
       var _sError =
          oData.querySelector("Error")?.getAttribute("Description") || 
                "undefined Error";
 
       _fReject(_sError)
       return;
      }

Column nodes will be extracted from the Axis0 and the row nodes from Axis1

_ColumnNodes = _oRoot.querySelectorAll("Axes>[name='Axis0']>Tuples>Tuple");
_RowNodes    = _oRoot.querySelectorAll("Axes>[name='Axis1']>Tuples>Tuple");

Data nodes are extracted from CellData

_DataNodes =  _oRoot.querySelectorAll("CellData>Cell");

The rows of the output table will be determined from the row nodes, including the requested properties like “hierarchy level”. We assume that the properties always occur starting from position 6 from the child nodes. If the technical name (remember 1. attribute of Member) contains blanks, replace them with one underscore.

      _RowNodes.forEach(pRow=>{
        let _oMemberNodes = pRow.querySelectorAll("Member")
        let _aRow = [];
        _oMemberNodes.forEach(pMember=>{
          let _sName = pMember.attributes[0].value.replace(/\s+/g,"_");
          _aRow[_sName] = pMember.querySelector("Caption").textContent;
          pMember.querySelectorAll(":nth-child(n+6)")
          .forEach(pProperty=>_aRow[_sName+pProperty.nodeName] = pProperty.textContent);                  
        })
        _aRowName.push(_aRow);
      });

Keyfigures will be determine by text node of the caption element.

      _ColumnNodes.forEach(pRow=>{
        let _oMemberNodes = pRow.querySelectorAll("Member")
        _oMemberNodes.forEach(pMember=>{
          _aColName.push(pMember.querySelector("Caption").textContent);
        })
      });

Finally the data has to be divided between the rows and columns

      _iCntRow = _aRowName.length;      
      _iCntCol = _aColName.length;
      
      _DataNodes.forEach((pData,pIndex)=>{
        let _sValue = pData.querySelector("Cell>Value").textContent;
        let _iRow = Math.trunc(pIndex / _iCntCol)
        let _iCol = pIndex % _iCntCol;
        _aRowName[_iRow][_aColName[_iCol]] = _sValue
      }); 

5. Binding the OLAP output to the Datamodel

As a result a promise is returned. Data from this promise can be bound to the model.

    this.getMdxData(this.getMdx())
    .then(aData=>{
      this.getView().getModel("viewModel").setProperty("/mdxData",aData);
    })
    .catch((oError)=>{
      debugger
    })

5.1 Result of the MDX Query in SAP UI5

After obtaining data from the XMLA web service, it can be bound directly to a table.

Final result

6. Conclusion

XMLA enables SAP UI5 developers to benefit from the dynamics of
Multidimensional Expressions (MDX).

Multidimensional Expressions (MDX) is the query language you use to work with and retrieve multidimensional data. This includes the definition of calculated members, named sets, scoped assignments and key performance indicators (KPIs).

MDX offers the flexibility to fetch data, pivot data in dynamic structures based on your needs,
without restriction of structured data types in the backend.

With MDX you use the flexibility of your multidimensional OLAP queries.

Thank you for reading this blog post. I want to encourage you to share your knowlege about SAPUI5 using this tag link https://answers.sap.com/tags/500983881501772639608291559920477

Appendix: Project Code

Three files are required to test the project_

zxmla/index.html
zxmla/Test.view.xml
zxmla/Test.controller.xml

File: zxmla/index.html

<!DOCTYPE html>
<html>
   <head>
      <meta http-equiv="X-UA-Compatible" content="IE=edge">
      <meta charset="utf-8">
      <title>XMLA-Test</title>
      <script
      
         id="sap-ui-bootstrap"
         src="resources/sap-ui-core.js" 
         data-sap-ui-theme="sap_bluecrystal"
         data-sap-ui-libs="sap.m"
         data-sap-ui-compatVersion="edge"
         data-sap-ui-preload="async"         
         data-sap-ui-resourceroots='{
            "zxmla": "./"
         }' >
      </script>
      <script>
         sap.ui.getCore().attachInit(function () {
            sap.ui.xmlview({
               viewName : "zxmla.Test"
            }).placeAt("content");
         });
      </script>
   </head>
   <body class="sapUiBody" id="content">
   </body>
</html>

File: zxmla/Test.view.xml

<core:View xmlns:core="sap.ui.core" xmlns:mvc="sap.ui.core.mvc" 
   xmlns="sap.m"
   controllerName="zxmla.Test">

  <Table id="idProductsTable" items="{viewModel>/mdxData}">
    <headerToolbar>
      <Toolbar>
        <content>
          <Title text="Products" level="H2"/>
          <ToolbarSpacer />
         <Button text="Go" press=".onPress"/>
        </content>
      </Toolbar>
    </headerToolbar>
    <columns>
      <Column><Text text="Level" /></Column>
      <Column><Text text="Land" /></Column>
      <Column><Text text="konsolidiertes Netto" /></Column>
      <Column><Text text="netto Umsatz" /></Column>
    </columns>
    <items>
      <ColumnListItem>
        <cells>
           <Text text="{viewModel>0D_FC_COUN_0D_FC_COUN_H001LEVEL_NUMBER}" />
           <Text text="{viewModel>0D_FC_COUN_0D_FC_COUN_H001}" />
           <Text text="{viewModel>Konsolidierter Netto}" />
           <Text text="{viewModel>Nettoumsatz}" />
        </cells>
      </ColumnListItem>
    </items>
  </Table>
</core:View>

File: zxmla/Test.controller.js

sap.ui.define([
     "sap/ui/core/mvc/Controller",
    "sap/ui/model/json/JSONModel"
  ], function(Controller, JSONModel) {
  "use strict";
  
  return Controller.extend("zxmla.Test", {


  onInit: function() { 
    var _oModel = new JSONModel({mdxData: []});
    this.getView().setModel(_oModel, "viewModel");
  },
  
  onPress: function(oEvent){
    this.getMdxData(this.getMdx())
    .then(aData=>{
       debugger
      this.getView().getModel("viewModel").setProperty("/mdxData",aData);

    })
    .catch((oError)=>{
      debugger
    })
  },
 getMdxData: function(sMdx){
    var _fResolve, _fReject, 
        _oPromise = new Promise((resolve,reject)=>{
        _fResolve = resolve;
        _fReject = reject;
        });
    var _sMdx = this.getMdx();
    var _sRequest = `
    <Execute>
      <Command>
        <Statement>
          ${_sMdx}
        </Statement>
        <Properties>
          <PropertyList>
            <Format></Format>
          </PropertyList>
        </Properties>
      </Command>
    </Execute>`;
    
    var _fProcessData = function(oData, sStatus){
    
      var _oRoot = oData.querySelector("ExecuteResponse>return>root")
      if(!_oRoot){
            debugger;
       var _sError =
          oData.querySelector("Error")?.getAttribute("Description") || 
                "undefined Error";
 
       _fReject(_sError)
       return;
      }

      var _aColName = [], _iCntRow = 0, _iCntCol = 0;
      var _aRowName = []
      var _ColumnNodes =
        _oRoot.querySelectorAll("Axes>[name='Axis0']>Tuples>Tuple")
      var _RowNodes = 
        _oRoot.querySelectorAll("Axes>[name='Axis1']>Tuples>Tuple")
      var _DataNodes =  _oRoot.querySelectorAll("CellData>Cell")
    
      _RowNodes.forEach(pRow=>{
        let _oMemberNodes = pRow.querySelectorAll("Member")
        let _aRow = [];
        _oMemberNodes.forEach(pMember=>{
          let _sName = pMember.attributes[0].value.replace(/\s+/g,"_");
          _aRow[_sName] = pMember.querySelector("Caption").textContent;
          pMember.querySelectorAll(":nth-child(n+6)")
          .forEach(pProperty=>_aRow[_sName+pProperty.nodeName] = pProperty.textContent);                  
        })
        _aRowName.push(_aRow);
      });

      _ColumnNodes.forEach(pRow=>{
        let _oMemberNodes = pRow.querySelectorAll("Member")
        _oMemberNodes.forEach(pMember=>{
          _aColName.push(pMember.querySelector("Caption").textContent);
        })
      });
      
      _iCntRow = _aRowName.length;      
      _iCntCol = _aColName.length;
      
      _DataNodes.forEach((pData,pIndex)=>{
        let _sValue = pData.querySelector("Cell>Value").textContent;
        let _iRow = Math.trunc(pIndex / _iCntCol)
        let _iCol = pIndex % _iCntCol;
        _aRowName[_iRow][_aColName[_iCol]] = _sValue
      }); 
      debugger
      _fResolve(_aRowName)
      // check _aRowName
    };
  
  $.post("/sap/bw/xml/soap/xmla/Execute",_sRequest , _fProcessData);  
  return _oPromise  
  },
    
  getMdx: function(){
    var _sMdx =
    `SELECT
      NON EMPTY
        { [48CSWO6CA6TXXHDU2Q35OLJW0].Members }
      ON COLUMNS,
      NON EMPTY
        { [0D_FC_COUN                    0D_FC_COUN_H001].Members }
      DIMENSION PROPERTIES
        LEVEL_NUMBER
      ON ROWS
      FROM
        [0D_FC_C02/0D_FC_AE_EOIBV_Q001]`
     return  _sMdx;
  }

  });
});