For BW on HANA, it is in particular important that the master data tables (SID, P/Q and X/Y tables) are consistent
In LISTCUBE, you can set options for 'Use DB aggregation' and 'Use mat. aggregates' and choose to display SQL statement during the data read as below:
Click 'Fld Selectn for Output' button, you can choose less fields to output to simplify the issue. Here we can try several times and find out only when select characteristic GLCOUNTR7 into output, the data fetched from the cube with different LISTCUBE settings will be different.
Switch on both 'Use DB aggregation' and 'Use mat. aggregates', you will see data is read with a TREX SQL statement:
It only returns data for GLCOUNTR7=CN:
Switch off 'Use DB aggregation', data is read with an open SQL without group by:
Here we can see the open SQL reads data by joining F table and S table. This SQL returns data as below:
Here we can see, the two CN records is not aggregated since there is no group by clause due to switch off 'Use DB aggregation'. Usually you will get too much records back. To get a clearer view, you could switch on 'Use DB aggregation', but switch off 'Use mat. aggregates'. This will still use an open SQL, but with group by:
It will returns aggregated CN data:
So far, we can see the TREX SQL gets a different result comparing with the OPEN SQL.
This is because TREX SQL (the EXT-AGGR interface) retrieve data from calculation scenario of GLCUBE7 directly. The caculation scenario is generated statically. As characteristic GLCOUNTR7 has a navigation attribute switched on, the calculation scenario will use X/Y table instead of S table. Although the sql statement doesn't request GLCOUNTR7__GL_CONT, internally system still read data with X/Y table. Therefore, if there is an inconsistency between S and X/Y tables, wrong data will be returned for the aDSO, HCPR or HANA optimized cube.
Please check and correct the inconsistent master data of GLCOUNTR7 in RSRV:
The inconsistency is: DE entry exists in S table but doesn't exist in X table for GLCOUNTR7. Therefore when join with X table in the calculation scenario of TREX SQL, country DE is filtered out.
If navigation attribute is selected into output, the result data will be always wrong both in TREX SQL and OPEN SQL due to the inconsistency.
Therefore it is important to keep the master data consistent. In one word, the master data should be consistent.
To find out which characteristic has master data inconsistency, you can run check in: Transaction RSRV-> All Elementary Test -> Master Data -> Compare Size of P tables/Q tables with X tables/Y tables. (See KBA 2271335 )
It is recommended to check all master data in the system and make sure they are consistent.