Page 1 of 1

ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Thu Aug 08, 2019 12:33 pm
by ofintm1
All,

We have a TI process that reads from a flat file, which contains the names of elements and an attribute value. On the data tab there is simply an ATTRPUTS to update the element attribute in the database. On 10.2.2 FP7 this took around 1 minute and on Planning Analytics Local 2.08 (using Architect) it takes 10 minutes. The dimension it is updating has around 250,000 elements. Hardware is the same spec. Tried on 2.07 as well.

Has anyone else seen such a degradation in performance? What would you recommend to troubleshoot?

Thanks
Oli

Re: ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Thu Aug 08, 2019 12:48 pm
by PavoGa
I have not seen that type of performance difference in this type of update and have a dimension of over 475,000 elements updating four attributes. The TI is reading from an Oracle datasource and runs the update in approximately 90 seconds give or take.

Do you have a rule file on the attributes cube and does include feeders?

Re: ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Thu Aug 08, 2019 2:29 pm
by ofintm1
Yes there is a rule file attached to the attributes cube with feeders.

The attribute being updated (attribute 1) is a string attribute that then feeds another string attribute (attribute 2). Attribute 2 is rule-calculated based on the value of attribute 1.

I deleted the rule file, reran the process and it took the same time as 10.2.2.

I then recreated the rule file with the exact same rules and feeders as before and it also has taken the same time as 10.2.2.!

I'll perform some further tests with some other cubes to see if this is consistent behaviour, perhaps I will just need to recreate all the rule files...

Thanks
Oli

Re: ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Thu Aug 08, 2019 5:21 pm
by PavoGa
Okay, possible problem with the feeder.
  1. When the load was slow, did you test a subsequent update immediately after the first? If so, what were the results?
  2. When the process ran quickly after adding back the rules, was the cube unloaded prior to the run?
  3. Are you using persistent feeders? (if yes, may start more questions)
  4. Since you are not performing consolidations on the attributes, maybe consider not using SKIPCHECK and the feeder. Others may have advice along this line as well.

Re: ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Thu Aug 08, 2019 9:10 pm
by paulsimon
Hi

It looks like your problem is more related to feeders

However, I have seen cases where using CellPutS to the }ElementAttrbutes cube can be faster than AttrPutS to the Dim.

One other thing to check is whether you have turned off logging on the }ElementAttributes cube during the update as logging will slow down the update.

If you haven't re-compiled your rules since you upgraded to PA, that looks like it might be any idea. We had to change all our major rules anyway when we upgraded so we might have avoided this issue.

I am wondering if this is something to do with the odd situation we have in PA where some TI functions are Hierarchy specific and others only apply at the dimension level even if they have a Hier Name parameter. This seems to be true of Element Attributes and Security.

Regards

Paul SImon

Re: ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Mon Aug 12, 2019 9:22 am
by ofintm1
After some further tests I found the way to get the TI process to run as fast as 10.2 was:

1) First reprocess the feeders - we had persistent feeders switched on and don't think the rules had been recompiled after initial upgrade
2) Change AttrPutS to CellPutS

I had originally tried 2) first but it had no impact until reprocessing the feeders. Reprocessing feeders on its own didn't have any impact either so looks like both changes are required.

I will be changing all instances of AttrPut to CellPut throughout the database now knowing it can make things 10x slower :shock:

Thanks All

Re: ATTRPUTS much slower on Planning Analytics vs 10.2.2

Posted: Mon Aug 12, 2019 1:29 pm
by lotsaram
FWIW it is also slower in rules to use AttrS rather than DB to the }ElementAttributes cube. This is (in my view) a bug as Attr functions should be properly optimized to have no performance defect versus querying hte background cube, but it is a real effect and worth knowing about if performance is important.

Just be aware that for aliases there can be differences in the result of AttrS vs DB. In the case that the alias is the same as the principal name it can be that no string is stored in the attribute cube and DB will return blank string whereas AttrS will return the (correct) result of the principal name.