PARENTH1 Hierarchy data lost after UJA_REFRESH_DIM_CACHE

[former_member187113]

Hi,

I wonder if anyone has also experienced this problem before.

We have a Time-Dependent ACCOUNT Dimension. We have recently (less than one month ago) added PARENTH2 and PARENTH3. We have also recently added Member Formulas to the TIME Dimension

There have been a fair amount of manual input to the ACCOUNT Dimension in the last three months. Mostly to the H1 hierarchies (we don't get Master Data from ECC).

We started getting AmbiguousMemberID errors (last two weeks) when refreshing EPM Reports although none of our Dimensions have ambiguous members across them. Performing an EPM Refresh Dimension Cache seems to work, but the error does re-appear. Not always consistently.

As a result we ran UJA_REFRESH_DIM_CACHE, UJX0_CLEAN_DIM_CACHE and UJX0_CLEAN_TDH_DIM_CACHE to try and solve this.

After running these, all the PARENTH1 hierarchy data from the last three months was lost. This happened in the ACCOUNT, PCENTRE and SUPPACC (user-defined) Dimensions, too.

Anyone else experienced something similar? We can't seem to replicate on DEV / QA at this point.

Setup:

1. SAP BPC 10.1 on BW 7.51 SP03 on HANA 1.00.122.11.1499758750
2. Classic Model
3. We use EPM SP29 or SP30.

Kind Regards

Nick

[former_member186338]

If you have multiple hierarchies in one dimension it's easy to make an error configuring nodes (using common nodes in different hierarchies). Please check!

In general you can load master data from text file instead of manual changes in web admin.

[former_member187113]

Thanks Vadim,

I will check again. (I didn't personally do them, but will check with the person who did.)

Thanks for the tip re loading from a text file. Perhaps a much better idea. I am not a great fan of the Web Admin GUI. Especially for large amounts of data / changes.

Appreciated.

Kind Regards

Nick

[former_member186338]

You can adjust vba code in this blog: https://blogs.sap.com/2014/08/21/excel-add-in-for-bpc-75-to-test-dimension/

to check text file before upload.

[former_member187113]

Very neat, Vadim!

Thanks. Let me give it a try.

Appreciated.

Nick

[SeerOfSig]

Nick,

Are you able to isolate the deletion of the hierarchy to the execution of one of the three UJA_REFRESH_DIM_CACHE, UJX0_CLEAN_DIM_CACHE and UJX0_CLEAN_TDH_DIM_CACHE, or did you run all three in sequence and only notice the deletion afterwards?

It is most likely that this issue occurred after executing the UJA_REFRESH_DIM_CACHE program. While it is fairly common to see this recommended in the forums and even in incidents, I normally don't suggest running this specific program unless there is a clear disconnect between the BW system and BPC AND the BW system is verified as being complete and correct.

The reason for this is that UJA_REFRESH_DIM_CACHE deletes data from the BPC cache tables and then refreshes this data from the BW tables. Normally, if there is any disconnect between the BPC specific tables and the BW tables we would expect the BPC tables to be the most current, therefore issues can occur when running this program if the BW tables are not correct. See for example note 2011283 regarding a similar issue with UJA_REFRESH_DIM_CACHE deleting member descriptions.

I think the most likely case here is that the hierarchy was not in sync in BW and executing this program copied the out of date hierarchy back to BPC.

From memory I believe there are several notes that correct issues of this sort. Without knowing your exact version and SP I cannot comment on if any of these apply to your case.

If you cannot find any notes regarding this please open an incident with SAP Support and clearly specify the details of how you update the master data in the front end, if you have ever done any direct updates in BW, do you run any Data Manager packages to do so, do you normally transport master data from QA to Prod, etc and we can investigate for you.

Regards,

Peter Kreutlein

[former_member186338]

BPC version is clear: BW 7.51 SP03 (BPC as a part of BW) and it's fresh

[former_member187113]

Thanks Peter,

That's really informative!

So I chatted with our system administrator. The order he ran the programs in was:

1. UJXO_CLEAN_DIM_CACHE
2. UJXO_CLEAN_TDH_DIM_CACHE
3. UJA_REFRESH_DIM_CACHE

One of the reasons we do this was because we were getting a lot of "Wrong csv format" errors upon logon (again, sporadic) and so we were told to use this as a way to solve it. So we've been kind of used to using it. I know that it can mess with the Dimension Editor (HTML) and sometimes we need to Reprocess the Dimension.

We can't really isolate which of the programs it was as they were run one after the other, but I think I tend to agree that the UJA_REFESH_DIM_CACHE could be part of the problem (as you mentioned).

As I mentioned (and Vadim), we're on SP03 of BW 7.51 so that's pretty current (BPC is part of BW 7.51). I'll check with the BASIS team how far behind we are on patches and Notes and see if we can get further updates.

We don't run any Master Data updates via Data Packages or the like. All manual. Not even Transports (not ideal, I know).

I can open an Incident with SAP Support - albeit slightly reluctantly. The problem is that I cannot (at this point) replicate the problem on DEV or QA, so it's going to be very tricky for them to have a starting point. Maybe I should do it anyway?

Kind Regards

Nick

[former_member186338]

I can recommend the following:

1. Export Master data to csv.

2. Check consistency of hierarchy nodes.

3. Import corrected master data from file back.

4. Run all UJx

5. Process dimensions.

[SeerOfSig]

I agree that without a reproduction case it won't be easy to pinpoint the root cause.

The best we likely can do at this point is to check for notes that may prevent further occurrences, but without a test case we won't be sure.

For your version and SP, the following notes have been identified that have an impact on UJA_REFESH_DIM_CACHE. While the symptoms are not exactly the same, I would recommend applying them (in Dev and QA first for testing) and then transport to Prod. The first note in particular sounds relevant.

2477835 - Fix the inconsistence of hierarchy cache table

Note 2543504 - "Invalid parent ID xxx in hierarchy xxx in /CPMB/XXXXXXX" when running UJA_REFRESH_DIM_CACHE

[former_member187113]

Hi Peter and Vadim,

Many thanks. Both really useful suggestions!

vadim.kalinin. We have now got backups (.csv) of all the Dimensions, so I think we need to apply your approach.

peter.kreutlein. Noted. I actually want to get us to SP05 as soon as possible, but if we can't then I will get the BASIS team to apply the Notes you suggested. Naturally, through the landscape (Dev -> QA -> Prod).

Much appreciate both your prompt feedback.

Kind Regards

Nick