Performance Implications of Dimension Reordering in Turbo Integrator Processes
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Performance Implications of Dimension Reordering in Turbo Integrator Processes
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
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
-
- MVP
- Posts: 3124
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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 ?
What version are you using for the PA server ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Hi Wim,
Thank you for your quickly answer,
we are on cloud 2.0.93
Best regards,
Zied
Thank you for your quickly answer,
we are on cloud 2.0.93
Best regards,
Zied
-
- MVP
- Posts: 3124
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
That's not the full picture. You need the version of PA, not (necessarily) the version of PAW.
2.0.9 something probably
2.0.9 something probably
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Hi sorry,
Yes we are using
11.8.00800.5
best regards,
Zied
Yes we are using
11.8.00800.5
best regards,
Zied
-
- MVP
- Posts: 3124
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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.
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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
-
- MVP
- Posts: 3124
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
"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).
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).
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3663
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Why would you reorder to a worse dimension order?! When reordering the idea is to REDUCE memory footprint
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Yes it is the same cube.Now, what is it, same cube or different cube ?
Not that it makes a lot of difference, but still.
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 :/If I were you, I would first look at why it takes 35 minutes to copy data. That seems like a long time.
11 dimensions, without rules, and yes we have 3 big dimensions, with an SKU dimension with 7 levelIs it very heavy on rules and feeders ? How many dimensions ? Big dimensions ? Cache invalidations ? MDX subsets that are calculated over and over again ?
it s not the case we don't use this config !The dreaded ForceReevaluationOfFeedersForFedCellsOnDataChange=T in TM1s.cfg ? Etc.
so we wanted to try something and the result is still disturbing,First exporting to a flat file then importing again in a cube could also be possible (Bedrock TM1 has boilerplate code for that).
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
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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
-
- MVP
- Posts: 3124
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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 ?
You don’t have to put the whole TM1 model in 1 cube.or are you recreating the DWH in a cube ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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
- Community Contributor
- Posts: 348
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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?
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?
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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
-
- MVP
- Posts: 3124
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Elessar
- Community Contributor
- Posts: 348
- Joined: Mon Nov 21, 2011 12:33 pm
- OLAP Product: PA 2
- Version: 2.0.9
- Excel Version: 2016
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
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
When searching for optimal dimension order, it does not just minimize ram, but finds optimal balance between speed and ram
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Elessar wrote: ↑Wed Mar 20, 2024 7:42 am 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
-
- Posts: 21
- Joined: Thu Mar 28, 2013 10:12 am
- OLAP Product: IBM Planning Analytics
- Version: 2.0.93
- Excel Version: 2 0 94 7
- Location: Paris, France
- Contact:
Re: Performance Implications of Dimension Reordering in Turbo Integrator Processes
Hi all,
and thank you again for your diffirent answers,
i write to you the comment of IBM for my case!
Zied
and thank you again for your diffirent answers,
i write to you the comment of IBM for my case!
Thank you[/i]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,
Zied