In the previous blog, we saw the Open Hub connector and its corresponding use cases and limitations. The major limitation is that it brings the data in a flat format instead of a multi-dimensional format.

This limitation can be overcome by using the MDX connector.

MDX (MultiDimensional eXpressions)

MDX is a query language that is like SQL and is used for querying and manipulating data in OLAP cubes.  It is useful for bringing the output in multi-dimensional format and though sometimes the same MDX query can be written in SQL, MDX is much simpler to write and easier to maintain.

The following is an example of an MDX query where we fetch data from the BEx query which has a key figure on columns and two dimensions on Rows.

Let’s look at MDX Connector in ADF for connecting BW systems.

Features available in ADF Pipeline activities

• Query: This option allows us to input the MDX Query directly. This is useful as the UI provided by the MDX connector has limitations. For example, conditions are not possible in UI but is possible by providing in the Input box.
• Additional columns: We can create additional columns here with the values.
• Browse SAP cubes: This option provides a UI from where we can select the required dimensions (and hierarchy if any) and measures.

• Parallel copies: This option allows parallel copying of data through multiple requests. Each parallel copy will load a data partition based on DTP Request ID and Package ID.
• Staging: When copying a large amount of data, this option allows us to store and compress data in Blob Storage.

Use cases

1. Since the MDX connector brings the data in a multi-dimensional format, this allows us to replicate data exactly as defined in the BW providers.
2. Most features of the BEx query like filters, conditions, aggregations are exposed via the ‘Query’ option, which allows to get data similar to the output of a BEx query.

Limitations

1. Compared to Open Hub Connector we saw in the previous blog, only BEx queries are supported. Any other type of infoproviders cannot be connected via this connector.
2. Since there is no Delta option in BEx Query, this cannot be used where Delta load is required. We can use a request ID filter in the BEx Query design for delta loading.
3. Values for variables cannot be passed in MDX. Static values will work, but if user input is required, even if mandatory, it will bring the result considering the variable has not been input.
4. We cannot use a dimension in Filter as well as Rows/Columns which is a limitation of MDX.
5. Depending on the MDX Query written, the result differs from the BEx output. If we use ‘*’ in-between dimensions, we get the result matching with BEx output, but we have some additional rows which are “All [dimension]” values.

If we use ‘,’ instead of ‘*’ (this happens if we select dimensions from the UI), this brings a result where each member has a separate row with aggregated measures.

The hierarchy values are brought with values – ‘0’, ‘1’, ‘2’… (hierarchy levels) instead of text or key values of the characteristic.

In addition to the Open Hub connector, Azure provides us with an MDX connector that brings data in a multi-dimensional format. This is useful in scenarios where retaining the modelling done in SAP BW is necessary.

Learn more about Visual BI’s Microsoft Azure offerings here.