Performance Implications of Dimension Reordering in Turbo Integrator Processes

[zbhmida]

Hi
Hi All,

Hope you are doing well !!!
I am writing to inform you of our problem when executing a Turbo Integrator process.

Below are the different operations we performed:

1.We have a Turbo Integrator that makes a copy within the same cube. In the source cube, we selected a consolidated level, and in the target cube, we load at a fine level. In the data tab, we use cell increment. We also parallelize with a RunProcess by launching several processes on different parameters (we use the Synchronized function). The execution of the processes takes 35 minutes. However, we noticed a significant increase in memory on the server (200 GB RAM).

2.We reordered the dimensions of the target cube (the system proposed a 42% memory gain). By running the same Turbo Integrator, we went from 35 minutes to over 5 hours of execution.

Is this normal? Does reordering dimensions on a cube normally result in such long execution times?

Best regards
Zied

[Wim Gielis]

Difficult to say with this information but I know that efforts were made regarding RunProcesses in the latest versions.
What version are you using for the PA server ?

[zbhmida]

Hi Wim,

Thank you for your quickly answer,

we are on cloud 2.0.93

Best regards,
Zied

[Wim Gielis]

That's not the full picture. You need the version of PA, not (necessarily) the version of PAW.
2.0.9 something probably

[zbhmida]

Hi sorry,

Yes we are using
11.8.00800.5

best regards,
Zied

[Wim Gielis]

Version 11.8.00800.5 ?

That is 2.0.9.9 from July 2021 and 3 years old.
Plenty of changes and fixes in the meantime. I would try upgrading first.
I also had issues with RunProcess and only in 2.0.9.19 it seemed solved.

[zbhmida]

Okey i wil see for the upgrade

But juste fyi
We ve tried to run the process for one perimeter without runing the parallel TI and and unfortunately we have the same issue

That’s mean that the process takes longer than before the reordering :/

Thanks

[Wim Gielis]

"We have a Turbo Integrator that makes a copy within the same cube. In the source cube, we selected a consolidated level, and in the target cube, we load at a fine level."

Now, what is it, same cube or different cube ?
Not that it makes a lot of difference, but still.

If I were you, I would first look at why it takes 35 minutes to copy data. That seems like a long time.
Is it very heavy on rules and feeders ? How many dimensions ? Big dimensions ? Cache invalidations ? MDX subsets that are calculated over and over again ? The dreaded ForceReevaluationOfFeedersForFedCellsOnDataChange=T in TM1s.cfg ? Etc.

First exporting to a flat file then importing again in a cube could also be possible (Bedrock TM1 has boilerplate code for that).

[lotsaram]

We reordered the dimensions of the target cube (the system proposed a 42% memory gain).

Why would you reorder to a worse dimension order?! When reordering the idea is to REDUCE memory footprint

[zbhmida]

Now, what is it, same cube or different cube ?
Not that it makes a lot of difference, but still.

Yes it is the same cube.

If I were you, I would first look at why it takes 35 minutes to copy data. That seems like a long time.

Yes i see it s too long but we have a lot of data on this cube ! and our problem is that after reordering we are far from 35 minutes :/

Is it very heavy on rules and feeders ? How many dimensions ? Big dimensions ? Cache invalidations ? MDX subsets that are calculated over and over again ?

11 dimensions, without rules, and yes we have 3 big dimensions, with an SKU dimension with 7 level

The dreaded ForceReevaluationOfFeedersForFedCellsOnDataChange=T in TM1s.cfg ? Etc.

it s not the case we don't use this config !

First exporting to a flat file then importing again in a cube could also be possible (Bedrock TM1 has boilerplate code for that).

so we wanted to try something and the result is still disturbing,
we created a new cube with the order proposed by the system (after reorganization)

and there we noticed that the loading time decreased for example

1. old order: 2 minutes
2. new order: 30 minutes
3. New cube with new order: 10 minutes

so we see that the engine does not react the same with the physical order and the virtual order

Zied

[zbhmida]

We reordered the dimensions of the target cube (the system proposed a 42% memory gain).

Why would you reorder to a worse dimension order?! When reordering the idea is to REDUCE memory footprint

Hi lotsaram

we reordered the dimensions to gain RAM,

because before the reorder we have 200 GB on the cube and after reorder we went down to 110 GB


Zied

[Wim Gielis]

200 GB ? No rules and feeders ?

You don’t have to put the whole TM1 model in 1 cube.or are you recreating the DWH in a cube ?

[zbhmida]

200 GB ? No rules and feeders ?

Yes I understand that you are shocked,
and no the ropic is not to use a cube like a DWH ! but we have an SKU level.


We had a cube with 400 GB before.

So during our audit we were able to remove some dimensions and also we used alternative hierarchies instead of rollups to reduce RAM.

and as I said, we have arthmetic operations in the cube where we had to copy the data, we had 200 GB then we reordered the dimensions and you know the rest

[Elessar]

Hello!

Returning to the original question: Yes, the case when cube reorder reduces memory but increases load/copy time (for some views/queries, not for entire cube!) is real. To understand this, you need to visualize cube data as a tree (this is how data is stored in TM1. See https://www.youtube.com/watch?v=DmjL-BYbHhc).

Having this, I assume that dimensions which are used at consolidated level in source view were moved from first ones to last ones in dimension order, am I right?

[zbhmida]

Having this, I assume that dimensions which are used at consolidated level in source view were moved from first ones to last ones in dimension order, am I right?

Yes it is the case ! the main dimension that contains the conso level were moved to last dimension.

Thank s a lot,

IBM support ask to up grade the engine,
And like i said we ve created a new cube with the new Order and the copy takes less than before,

i will test after the upgrade !

Zied

[Wim Gielis]

IBM support ask to up grade the engine,

What a surprise

[Elessar]

It's better to use OptimusPy rather than Architect's automatic dimension reorder: https://code.cubewise.com/open-source/tm1py/optimuspy/

When searching for optimal dimension order, it does not just minimize ram, but finds optimal balance between speed and ram

[zbhmida]

It's better to use OptimusPy rather than Architect's automatic dimension reorder: https://code.cubewise.com/open-source/tm1py/optimuspy/

When searching for optimal dimension order, it does not just minimize ram, but finds optimal balance between speed and ram

Hi Elessar,

cool I saw it on the internet i'll try to use it

Thanks a lots

Zied

[zbhmida]

Hi all,

and thank you again for your diffirent answers,

i write to you the comment of IBM for my case!

Code: Select all

Support IBM a commenté
Hi Zied,

Development responded on a previous case and confirmed this can be expected.

Re-ordering the cube dimensions completely changes our memory layout for the cube data. This can have a dramatic impact on both performance and memory footprint.

Our "Optimization suggest by System" feature only attempts to optimize for memory consumption and makes no attempt to optimize for performance. As the UI text suggestion, it is also only a suggestion and should not be taken as an absolute best ordering.

Best regards,

[/i]

Thank you
Zied