TI performance drops dramatically during execution

[PavoGa]

Migrated a TI process from 10.2 that processed approximately 900,000 records in that system. It averages between 3:20 and 3:30 minutes run time.

1. It reads from an Oracle database and loads a cube
2. It processes the dataset through both the Metadata and Data tabs
3. It clears the target cube
4. CubeSetLogChanges is set to 0 prior to the ViewZeroOut
5. It writes both numeric and string data to the cube
6. Target cube has no rules/feeders

For the Planning Analytics environment we are reading approximately 975,000 records (different database environment, same table structure/SQL query). And experiencing performance problems of this nature:

1. It process the records and completes the Metadata procedure in approximately 1:20 minutes
2. It processes the first 160549 records in the Data procedure at a rate just shy of 11,000 records per sec.
3. Starting with record 160550, the processing speed drops to between 16 and 20 records per sec. Yeah.
4. This behavior is repeatable.

We have tried all of the following, in various combinations, to no avail. The performance problems continue to start with record 160550 in the Data procedure.

1. Dropped the Metadata procedure by removing its code
2. Flipped the Use Unicode checkbox
3. There were some CELLPUTS that were writing to a consolidation. Removed those.
4. Removed all CELLPUTS statements.
5. Used colorful metaphors. Used non-colorful metaphors. Talked bad about the process' mama.

Here is an excerpt of the records before and after (record number is the first column).

Code: Select all

"160547.000  200,   Base,   AAA-AAA-002,   01.XYZ.123456789.00,   LOL-222-UCF,   435,   Total Cost,    01.XYZ.123456789.00"
"160548.000  200,   Base,   AAA-AAA-002,   01.XYZ.123456789.00,   LOL-222-UCF,   0,   Total Base,    01.XYZ.123456789.00"
"160549.000  200,   Base,   FIG-100-BOP,   01.ABC.000000000.00,   LOL-222-OHC,   460,   Total Cost,   01.ABC.000000000.00"
"160550.000  200,   Base,   FIG-100-BOP,   01.ABC.000000000.00,   LOL-222-OHC,   0,   Total Base,   01.ABC.000000000.00"
"160551.000  200,   Base,   FIG-100-BOP,   01.ABC.987654321.00,   LOL-222-OHC,   460,   Total Cost,    01.ABC.987654321.00"
"160552.000  200,   Base,   FIG-100-BOP,   01.ABC.987654321.00,   LOL-222-OHC,   0,   Total Base,    01.ABC.987654321.00"

Any ideas on what could be causing this?

[blackhawk]

[*] It processes the first 160549 records in the Data procedure at a rate just shy of 11,000 records per sec.
[*] Starting with record 160550, the processing speed drops to between 16 and 20 records per sec. Yeah.

That is unusual to be sure. It is behaving like the first 160k records are being cached and then after that it is trying to commit as it goes. Have you opened up TM1Top to see what it is doing at both segments?

One way to check this, is if you change the query to start at record number 150k, does it slow down at record 10550 or does it still slow down at the 160kth record (i.e. rec number 310550)?

If it slows down at the 10550th record, it sounds like a cell value update is triggering something (rule/feeder). If it slows down again at 160550th record, then it sounds like a cache/buffer issue.

What if you were to temporarily remove all rules from the cube, load the data, then re-enable the rules. What happens then?

[PavoGa]

Almost embarrassed to say it. The problem was the cube needed the dimensions re-ordered. Took care of it. One of those things I just do not think about often enough. Thanks, @bgregs, for looking over my shoulder on this one...

[blackhawk]

Almost embarrassed to say it. The problem was the cube needed the dimensions re-ordered. Took care of it. One of those things I just do not think about often enough. Thanks, @bgregs, for looking over my shoulder on this one...

Glad you have it resolved, however, I am not sure I follow why that would change between versions of TM1. If it was good in 10.2 it should have been good in PA. I didn't think they did anything between those versions where altering the dimension order made a difference. Power up the same cubes with two different versions should behave pretty much the same.

.... Unless you are saying that the dimension order was not the same between them when it was migrated.

If this is a change where in PA you need to re-do the ordering from a 10.2 upgrade, that is new to me and worth investigating a bit further.

[PavoGa]

The cube was created in PA (only the TI was migrated) and actually, years ago, the 10.2 cube had its dimensions reordered; I just did not check or consider that initially in PA. We kind of worked our way into by taking the DATA procedure code out and putting in back in one section at a time until we added a CELLGETN that caused the problem. We have a couple of fairly large dimensions in this cube (400K+ and 22K+ members) so that kind of triggered the question about the dimension order. Run time now is just over three minutes including both Metadata and Data procedures.

[blackhawk]

The cube was created in PA (only the TI was migrated) and actually, years ago, the 10.2 cube had its dimensions reordered; I just did not check or consider that initially in PA. We kind of worked our way into by taking the DATA procedure code out and putting in back in one section at a time until we added a CELLGETN that caused the problem. We have a couple of fairly large dimensions in this cube (400K+ and 22K+ members) so that kind of triggered the question about the dimension order. Run time now is just over three minutes including both Metadata and Data procedures.

Ah! That sounds more reasonable. I thought you had just copied the files over to the new system and powered it up under PA.

Still though...that is a big shift from 11k/sec to 20/sec. I have seen some spikes that have been bad between dimension ordering, but not that bad.

Good to see that you have it worked out; and thanks for the refresher on what NOT to forget about.

[PavoGa]

Good to see that you have it worked out; and thanks for the refresher on what NOT to forget about.

You have that right! Every now and then we need it.