SAP HANA offers multiple access options to query SAP HANA views available in database:
- The HANA SQL access, the primary HANA access, allows you to query HANA views using the SQL query language. This access is optimized for on-premise solutions.
- The HANA MDX access, that offers multi-dimensional concepts to HANA models, allows you to query HANA views as cubes (including HANA hierarchies) through the Multi-Dimensional eXpressions (MDX) query language.
- The HANA InA access, designed for Web applications or solutions, allows you to provide real time access to information stored in HANA database using the SAP HANA Info Access (InA) HTTP Service. The SAP HANA info access HTTP service wraps SQL queries and exposes them through an HTTP interface.
This document describes the difference in query results when you run the same query specification on a same HANA view from HANA SQL/InA and HANA MDX.
Use case description
To illustrate this difference, let’s consider a HANA view (named FINANCE here) containing the following dimensions and measures:
In this HANA view, you also create and associate the following HANA hierarchies to the dimensions:
In HANA SQL, to get the query results for
Amount per
Organization, you write the following script:
And get the following result:
In HANA MDX, you write the following MDX script to express the same query specification:
As a result, you receive:
The two queries return different results in the
Amount values (Note that the HANA InA access provides the same query results as HANA SQL).
Why this difference between the SQL and MDX queries?
Paradigm difference between SQL and MDX
The query results displayed above are both corrects for HANA SQL and HANA MDX. However, SQL and MDX have different paradigms that need to be understood.
In the query specification above, you only want to query the
Amount per
Organization.
In the SQL paradigm, this means to ignore all other dimensions (i.e.
Account,
Department,
Scenario and
Time) and to just aggregate implicitly the
Amount over them.
In the MDX world, the paradigm is different. Indeed, the MDX paradigm is like the cartesian (x, y) axis coordinates. Suppose you have a function
f such as
Amount =
f (x, y) (i.e.
Amount is a function of x and y). What would be the
Amount for x = 10? It cannot be defined because
Amount is a function of x and y and y is unknown.
Amount cannot be determined since values for x and y are both required. The MDX standard works in this mode.
Thus, in MDX, for the HANA view defined above, the
Amount measure depends on the dimensions
Account,
Department,
Organization,
Scenario and
Time. Because your MDX query above includes explicitly only the
Organization dimension to get
Amount, the query result is therefore indeterminate.
For this use case, in MDX, when a dependent dimension is not explicitly included in a query axis to evaluate a measure, the
default member of that dimension is implicitly included in the slicer axis to complete the coordinates (see
https://msdn.microsoft.com/en-us/library/ms146047.aspx )
Determining the default member in SAP HANA
In HANA MDX, each dimension has a default hierarchy associated to it. These default hierarchies can be implicitly generated by HANA MDX; or authored in the HANA view by the designer. And each of these hierarchies may have a default member.
In HANA, the default member of a hierarchy is, by default, the member that has the property
MEMBER_ORDINAL = 0.
For hierarchies implicitly generated by HANA MDX, the default member corresponds to the hierarchy root node i.e. the “ALL” member. However, for hierarchies authored in the HANA view, the default member depends on hierarchy settings.
To know the default member associated to hierarchies in your HANA view in MDX, you can run the following query:
That returns the following result:
In the query result above, you can observe that all hierarchies have a root node (i.e. the ALL member) as default member; excepted for the
Accounts and
Departments hierarchies that have respectively
ACCOUNTS.&[1] and
DEPARTMENTS.&[1] as default members.
This means that your MDX query above corresponds to the
Amount for
ACCOUNTS.&[1] and
DEPARTMENT.&[1] i.e. the
Amount will be aggregated over all
SCENARIO and
TIME dimension members (as in SQL) but it will be NOT aggregated over all
ACCOUNTS or
DEPARTMENTS.
That explains why the MDX query result for Amount is less than the one you observe in SQL – but this is correct.
Solution
The HANA modelers (HANA Studio or Web IDE For SAP HANA) offer various ways to set explicitly or implicitly default members for HANA hierarchies authored in HANA views.
To produce the same query results when run from HANA SQL and HANA MDX, the solution consists of having authored hierarchies with default members that place all other hierarchy members as their descendants.
The simplest workaround is to add a root node as default member for your authored hierarchies:
- Edit your hierarchy
- Go to Advanced settings
- Clear the Default Member field (if non-empty)
- Set “Add Root Node” for the Root Node Visibility option
Indeed, when you set
Add Root Node for the
Root Node Visibility option, then an ALL member is added as default member if the
Default Member field is empty. For parent-child hierarchies, if you set
Add Root Node If Defined for the
Root Node Visibility option, then a root node is added only if you have defined a root node value while creating the parent child hierarchy.
Thus, if you set these options for
ACCOUNTS and
DEPARTMENTS hierarchies, then your MDX query returns the same result as your SQL query.
Have fun with HANA!