If a field in a cube view (a CDS view defined with annotation @Analytics.dataCategory:#CUBE) is associated to a dimension view (a CDS view defined with annotation @Analytics.dataCategory:#Dimension), all values in this field should be available in the dimension view.
If some values in the field is missing in the dimension view, query might get unexpected result.
Here is an example:
A dimesion view is defined on table ZGLNAMES like this:
Here we have name values PETER PAN and TINKERBELL.
The dimension view ZGL_NAME is defined like this:
As the representative key is Name, it means this is the master data view for Name. It has a text association and a hierarchy association.
The text view ZGL_NAMET is define on table like this: (See details about Text CDS view definition）
Here we can see Name value PETER PAN and TINKERBELL have text.
The hierarchy association provides a hierarchy STORY1 like below in RSH1: (See details about hierarchy CDS view definition)
Here we can see value TINKERBELL is not in hierarchy STORY1, which means TINKERBELL is in 'not assigned' node.
Now the cube view is defined on table ZGLSUMMER
Cube view ZGLSUM is associated to the dimension view ZGL_NAME
Attention: Value SNOOPY doesn't exist in the dimension view ZGL_NAME.
Here is the summary of important facts of the data model above:
- In the cube view ZGLSUM, there are name values PETER PAN, SNOOPY and TINKERBELL
- Value SNOOPY is not available in dimension view ZGL_NAME, this is a data model inconsistency.
- Value PETER PAN and TINKERBELL have text defined in text veiw ZGL_NAMET
- Value PETER PAN and TINKERBELL have display attribute Hobbie as listed in table ZGLNAMES
- Value PETER PAN is in hierarchy STROY1 as a node, value TINKERBELLis not in the hierarchy STORY1 definition, thus it is in 'not assigned' node
The Effects of the Inconsistency
A query is defined on the cube view like this:
Text and Display Attributes
When run query 2CZGL_NAME2QV without hierarchy active, that is, no input in the variable screen, the query result is as below.
Here we can see all three values are returned from the cube view.
However, as value SNOOPY is not in the dimension view, it gets no text and the display attribute Hobby is '#' (not assigned).
In the navigation Pane, if we click the filter button on Kids (that is, field Name), SNOOPY is not listed although it is in the query result:
The variable screen F4 would have the similar effect.
When run the query by input STORY1 in the variable screen, hierarchy STORY1 is activated. Expand node 'Not Assigned', the 'Not Assigned' node gets a inconsistent result due to the data model inconsistency.
As SNOOPY is not in the dimension view, the query gets data for SNOOPY from the cube view and it is aggregated into 'Not Assigned' node since it is not in the hierarchy definition. When expand node 'Not Assigned' or keep filter on node 'Not Assigned', an explicit selection of hierarchy node 'Not assigned' is in use, which doesn't contain value SNOOPY according to the dimension view data.
In HANA Studio, run sql statement as below:
The inconsistent value SNOOPY is found out.
Then add this value into the dimension view.
See Note 2835242 - More Values in Cube CDS View than in Dimension View.