BEx query producing "wrong" results when adding a drill-across
Today I got overwhelmed by requests from end users of one of my clients regarding "wrong" figures being reported.
The queries involved are all on the COPA InfoCubes and have at least 2 hierarchies in them. The one I checked to the bone actually had 3: 2 in the rows and 1 in the columns. A simplified version of the query result looks like this (the rows/columns in red indicate that a display hierarchy is active):
|Sold-to party (H)||INTERCOMPANY||INTERCOMPANY||…|
|Prod.hierarchy (H)||Company code||Country (H)||KF 2012||KF 2013||…|
When we run the query as is (in the above layout), the results (totalling the values of the key figure columns) are incorrect.
After removing the drill-down on Sold-to party, the results "suddenly" are correct.
I have found some OSS notes that could solve this (1843847 & 1868066), however I currently cannot make a connection to the service marketplace from our development BW system (has been forwarded to the system admin team) so they have not been implemented just yet.
Anyways, since I was "stuck", I decided to play around some more. It seems that drilling across Sold-to party as such does not change the figures, but it's by activating the hierarchy that things go wrong.
According to the end users this did work correctly before our upgrade (to 7.31 SP06 - which dates back a few months actually), as for myself, I haven't really worked with these queries (on that level) before so I don't know whether this is upgrade related or not
Recently we activated Column Store (MS SQL2012) for the underlying InfoCube. I don't see how that could cause this issue, but I'm just stating the facts.
This behaviour is seen using BI AddOn 7.x (based on 7.30) Support Package 2 Revision 1887 (end users) as well as BI AddOn 7.x (based on 7.30) Support Package 5 Revision 1960 (myself).
Has anyone experienced this as well? If so, what was your solution (if at all). The current work around (I added a structure with two fields filtering on Sold_to party respectively for "intercompany" customers and "others", which is the main reason why they use the hierarchy) is fine for now, but the business would like the original to work again by next month.