Defining MDS Cubes

Consider you want to prepare a model in SAP Analytics Cloud (SAC) for a few business users. This is based on the SAP HANA Calculation View technology. One business user needs to evaluate net sales amount and gross sales amount by business partner, distribution channel, and month. Other business users want to see the same information, but sometimes they need details by product, distribution channel, and date for these measures. Instead of creating two different calculation views, it is easier to create a single view with all these drill-down options. However, the more complex the calculation view becomes, the more processing resources will be needed. This is especially true when remote data access is involved. To improve the performance, you could allow caching for this view. However, the first dialogue would then take a long time because the cache is not yet filled. Additionally, it might not be possible to serve such different drill-down queries with the static cache. It's better to store pre-processed data in a format that can serve all these complex drill-down queries and allows to quickly answer these most frequently required drilldown situations. For this purpose, calculation views offer an additional property called multidimensional services cube (MDS cube).

Screen captures of the steps to create an MDS cube: Create or open a Calculation View. Create a new MDS Cube. Open its details and add attributes and measures. Deploy the view.

An MDS Cube is defined for a specific underlying calculation view and represents a submodel of this view, this means, a subset of its measures together with a subset of its attributes. When you define an MDS cube as the basis of a model in SAP Analytics Cloud, you must include all fields into the model that are required in your SAC model.

Note

MDS cubes can only be defined for calculation views of Data Category Cube.

You can define multiple MDS Cubes for the same calculation view. Deployment of the calculation view builds the view and generates all MDS cubes that are defined for it, initially without data. After loading them (see section below, The Management of MDS Cubes), preaggregated data is stored in MDS Cubes in a highly optimized manner to speed up analytical queries, like a predefined persisted cache. But in contrast to a cache, the data is not stored as a flat datastructure, but in a multidimensional, star-join-like structure that is optimized for tailored data access.

MDS hints can optionally be added for loading and processing the MDS Cube, but they are typically not required. Use them only if you are advised by incident processing.

Note

MDS Cube and MDS Cube Cache are different things. Do not confuse them.

Two MDS cubes based on the same source data with different levels of aggregation. For example, amount per country in one MDS cube, but amount and quantity per product in a second one.

Data in MDS Cubes reflect the state at the time that the MDS Cube was loaded. Only data that were visible to the user who loaded the MDS Cube are included. When the source data changes after populating the MDS cube, the loaded data is not automatically updated. This can lead to differences when comparing reporting based on MDS Cubes and based on the online calculation view. To reflect data changes in reporting, it is necessary to manually reload the MDS Cube. This means that if you need real-time data, MDS cubes can't be used.

Comparing Static Cache, Query Snapshot and MDS Cube regarding performance optimization and flexibility. MDS Cube is faster than a query snapshot. MDS Cube can be slower or faster in comparison to static cache. MDS cube serves for more queries than these options. A Calculation View without performance optimization is much slower.

In comparison to caching, caches can be faster, but then the query must exactly match the cached data, which is rarely the case. MDS cubes can also perform complex postcalculations, such as restrictions, ranking, formula, and standard or exception aggregation as they are typically defined in SAP Analytics Cloud. MDS Cube could even be faster than Static Cached views for which only intermediate results are already cached. If the cache provides the data on a higher granularity than is requested by the query an aggregation needs to happen. MDS Cubes can aggregate faster due to their optimized data structure for OLAP analyses.

This optimized data structure can be used in analytical reporting based on MDS metadata, such as SAP Analytics Cloud, but is not accessible by SQL. Therefore, the SQL Analyzer or Explain Plan view does not show if an MDS Cube is used. As of today, MDS Cubes are not listed in the Database Explorer.

In SAP Business Application Studio, the document generated by the Generate Document function also includes information about the MDS cubes.

The Management of MDS Cubes

To query metadata, load, or delete the data of MDS Cubes, specific APIs exist. The API works with a built-in procedure, which accepts a single JSON input parameter and returns a single JSON object as an output.

Execute an SQL statement, such as:

Code Snippet

1
MANAGE_MDS_CUBE('<JSON MDS Cube API>',?)

Examples can be found here: API for Administrating MDS Cubes | SAP Help Portal

This JSON statement describes what you do with which object. Here is how it looks like for (re-)loading the cube.

Code Snippet

1234567891011
{
   "Cube": {
      "Command": "Reload",
      "Target": {
         "DataSource": {
            "SchemaName": "<Schema>",
            "ObjectName": "<MDS Cube Name>"
         }
      }
   }
}

The target here is the MDS cube. An MDS Cube is always tied to the calculation view on which it is defined. Therefore, the calculation view is automatically determined as the source. If the command returns successfully, the state of the MDS Cube changes to 'Ready', indicating that the MDS Cube is ready to be queried. If the state was already 'Ready' before loading the data, the MDS Cube will be reloaded. During the reload, the MDS Cube can still be queried and will display the old data until the reload finishes with success. For monitoring, check the _SYS_EPM.MDS_METADATA table.

Limitations

Currently, the following limitations exist:

MDS Cubes cannot contain calculated measures. Instead, you can generate the calculation in SAC.
MDS Cubes support variables, but no input parameters. If the calculation view with an MDS Cube contains a mandatory input parameter without default value, deployment of the calculation view will fail. If you use input parameters in a restriction, we recommend that you convert them manually to variables.
Joins with two or more join condition fields are not supported.
Columns of the following datatypes can be used:
- Integer Types: TINYINT, SMALLINT, INT, BIGINT
- Floating Types: REAL, DOUBLE, FLOAT, FLOAT(p)
- Decimal Types: DECIMAL; DECIMAL(p,s)
- Boolean Types: BOOLEAN
- Character Types: VARCHAR, NVARCHAR, CHAR,NCHAR
- Date/Time Types: DATE, DAYDATE, TIMESTAMP
But, VARBINARY columns can not be used.

Note

In an SAC model that is based on an MDS Cube, you see only the fields that are part of the MDS cube. So, make sure that you add all required fields.

If MDS Cubes does not support a calculation view feature, the MDS Cubes tab is inactive, and a list of features is displayed that prevents the usage of MDS Cubes. Removing these features from the calculation view activates the MDS Cubes tab. For example, a join with more than one join field prevents the usage of MDS cubes. If an MDS Cube is already defined in a calculation view, features of the calculation view that MDS Cubes don't support are deactivated. When hovering over the feature, you are informed that it is inactive due to the existence of an MDS Cube definition. If the MDS Cube definition is deleted, the feature becomes available again.

Authorizations

To load or delete an MDS cube, you need the EXECUTE privilege for procedure MANAGE_MDS_CUBE. You can grant it as shown in the code snippet:

Code Snippet

1
GRANT EXECUTE ON MANAGE_MDS_CUBE TO <DB_USER>;

Additionally, you need privilege CREATE ANY for the HDI schema in which the Calculation view of the MDS Cube resides. Moreover, you must have SELECT privileges for the data source of the MDS Cube.

You can either create roles with the authorizations or directly assign them to users.

Analytic privilege settings of a calculation view are applied automatically also to all MDS Cubes that apply to within the calculation view. If the calculation view is secured by analytic privileges, users also need analytic privileges to access the data of the respective MDS Cubes. Analytic privileges that are defined on a calculation view automatically apply to the MDS Cube as well. Analytic privileges that are assigned to users who report on the MDS Cube must include only columns that are included in the MDS Cube.

Defining MDS Cubes