Generally, you should use the same hierarchy in the query definition regarding a certain characteristic. But sometimes, due to special requirements you may use a node filter in a structure element defined on a different hierarchy. Such a usage will have memory (also performance) impact since the concept of temporary hierarchy tables cannot be used and the query retrieves the data on leaf level. See Resolve of Hierarchy Node Restrictions for further details.
There are two hierarchy defined on characteristic GLCOUNTRY:
They have different structures:
InfoObject with active Hierarchy in Drilldown
In a simple query, you want to show the query result with GLCOUNTRY in drill down and display with the structure defined in hierarchy WORLD1, only expand to the first level. So hierarch WORLD1 is set as active in the query. Also in hierarchy WORLD1, node "not assigned" is not suppressed:
The query is defined as below:
The result of this simple query is like this:
Here we can see, according to the hierarchy definiton we get 4 lines result back. BW system reads data from the infoprovider (a basic cube) with this statement:
Here the cube data is filtered by the hierarchy temporary table 0BW:BIA:BI0_0800011458. That is the data is aggregated on DB level according to the level 1 nodes defined in hierarchy WORLD1.
The content of table 0BW:BIA:BI0_0800011458 is as below:
For node EARTH ( PREC=-6) there are 3 leaves (CN, AT, DE)
For node MARS ( PREC=-5) there are 2 leaves (C40, C94)
For unassigned node (PREC=-11), the rest leaves are included (this screenshot doesn't show all values).
The data fetched with this statement is:
These data are filled into the OLAP internal data container (the SP data set ) to prepared for the final query result output:
As you can see this internal table is rather small with only 3 rows. It maps exactly with the query output. The yellow line calculated out by aggregated these 3 detail lines.
Additional Local Node Restriction in Query (Filter Hierarchy unequal to Presentation Hierarchy)
Now you want to add a selection into the query to only show cost of those countries you've visited, but in query output it is still displayed according to the georiphical hierarchy WORLD1. So a second query is defined as below, by adding a selection 'amount visted' into the first query:
Such a query definition will get a BRAIN 087 warning when check, save or run the query: Different hierarchies are used for characteristic GLcountry
It is not allowed to use a different hierarchy when define a filter in Default Values section, system will give an error when check or sace the query: No display hierarchy (or hierrarchy is not unique) for dril down characteristic GLCOUNTRY.
The query result looks similar to the first query, we still get 4 rows back in query result:
However the internal handling data is very different. We have to read the most detailed GLCOUNTRY data so that the intersection of display of hierarchy WORLD1 and the local restriction of hierarchy node 'visited' in hierarchy WORLD2. In this simple example, you can see now we can't simplily aggregate data to node EARTH, MARS, 'Not Assigned' any more. Because in that way, we can't get the amount for JP, which is needed for column 'Amount Visited'. In a real user case, the intersection could be much more complicated. So the leaf level data is needed.
Now data is read with this statement:
Here data is not filtered with nodes any more. We have:
f (FEMS) 0001 filter for column 'Amount', which requests all data return.
f (FEMS) 0002 filter for column 'Amount visited', which requests data for GLCOUNTRY SID (2, 3, 6, 28), that is, (CN, AT, DE, JP).
The data returned are much more than the query output, 407 lines (the distinctive GLCOUNTRY booked in the cube) are returned:
Data for SID 2,3,6, 28 (28 is out of the screenshot) are filled into both FEMS 0001 and FEMS 0002.
The OLAP internal data container prepared for the final query result output also contains 407 lines:
Data for SID 2,3,6, 28 are filled into both W001 (column 'Amount') and W002 (column 'Amount visited'). And other data lines such as SID 4, since they only exist in the 'Not Assigned' node in column 'Amount', are only filled into W001.
Then the data is aggregated to get final output for the three nodes in query result.
If GLCOUNTRY is a high cardinality characteristics with millions of values booked in the cube, we will get millions of lines in this internal table although the query output looks quite small. With combination with other OLAP features, further merge of different data partitions may lead to more copies of such internal tables.
When the query has more other characteristics drilled down, or the key figure structure in each line is more complicated and big, the memory consumption could be rather high.
- Be aware of the memory and performance impact of using different hierarchies on a characteristic in a BW query. Avoid such usage as much as possible.
- If it has to use different hierarchies, please set ' Suppress Unassigned Node' in the active display hierarchy attribute. In this way, hierarchy itself will be an implicit filter of data (not assigned node data are filtered out), this will reduce the data volume and save some memory.