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.
we're not using link nodes in any of the hierarchies. We apparently had exception aggregation in 2 key figures (that weren't displayed at all), but after removing those, the problem still persists. So we can rule out exception aggregation & link nodes.
Are you sure you have valid combination of data for your KF with Sold to Part(H), Product Hier, Company code and Inter Company as well in your infoprovider?
If yes, then your report should work well with drill down.
This is some data accuracy issue but not SP or technical issue.
yup we are very sure we have valid data combinations. In fact when we recreate the "workbook" (based on the query) we do get correct results the first time we run it. Afterwards it gets somehow "corrupted" until we remove the sold_to party drill-across.
Could you please put your correct results screenshot for same input criteria? I would like to see how the values are shown (in correct results).
My doubt on Sold To Party (H) hierarchy design. If possible put hierarchy screenshot also. Is there any relation maintained between CompCode1 and Sold To Party (H). In other way, does Sold To Party(H) has CompCode* has child nodes?
There could not be issue with data, hierarchies is used just to display data in specific format.
Provide inputs to analyze further.
Sorry, I can't put actual customer data out on the web (bound by contracts).
As far as the sold_to party hierarchy goes: it's basically a split between "intercompany" customers and the rest, so the hierarchy itself has 1 node called "intercompany" which has 30 customers underneath it. All the other sold_tos are thus shown as "Not Assigned" in the report (upon the business' request).
Ok, after a couple of hours of testing we nailed it down to the following:
So we nailed it down, now we need to come up with an explanation... OSS 1786054 looked promising, but didn't help :-(
Message was edited by: Raf Boudewijns
We where on 7.31 SPS6 until two weeks ago and had expirienced a similar issues with active display hierarchies that just won't sum correctly and by changing and monkeybusiness with the dimension all of a sudden it would. I had a note open with SAP for about 6 weeks and it went through all departments and all sort of developers. They didn't figure it out we had to move to SPS8 in the meantime and I closed the note. I hope you have more luck.
No, did you miss mine?
Our issue has nothing to do with exception aggregation, but merely with hierarchies (and more specifically the REST_H node). I found out (surfing on the Sercice Marketplace) that SAP has changed the way hierarchies are treated in reporting since release 7.30. There are a few notes out there already with issues, and I'm pretty sure this is a not-yet-described one.
Wouldn't it be nice if all issues were reported like this?
I've encountered something similar involving compounded InfoObjects and hierarchies (still not solved), let's see if SAP_DROP_TMPTABLES could help in that.
P.D.: Also added to my reading list Using SQL Server 2012 Column-Store with SAP BW