------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------

Hi Tin,

Let me start with some good news, albeit not helpful right now, and that is that in an upcoming release of TM1, likely TM1 v12.6 but definitely 1st half of 2025, we'll be replacing how/where we store updates, these transactions, to cubes. I'll spare you the details but net result is, without loosing any capability that TM1 v12 has today, that it'll be even [much] faster then TM1 v11 with logging on, and comes with the benefit of always having stored everything on disk after every transaction in a way that also improves load times, dramatically, if logs exist (completely obsoleting the need for SaveDataAll, if there even where a need in v12).

The 'bad' news therefore is that there is, currently, not much you can do as, as you've already referred to above, part of the high available architecture of v12 we have to store all transactions and, the way that's done today, is, whilst fault tolerant, not even as performant as v11 with transaction logging on. Only thing you might be able to do, if within your control and if you happened to be updating the same cell multiple times to begin with, is making sure you calculate the increment for a cell and update it only once. Also, if you happen to be incrementing with the value 0, a case that was brought to our attention earlier, that still has the full overhead of an update and should be avoided as well.

Happy holidays!

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Mon December 23, 2024 04:30 AM
From: Tin Nguyen
Subject: Performance Issue on TM1 Database 12 Without CubeSetLogChanges or unable to turn off logging

------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------

Hi Hubert,

Thank you for your detailed response and for sharing these insights. It's much appreciated! Apologies for the delay in getting back to you; between the holiday season and some testing I've been doing on my end, it took me a bit of time to circle back to this discussion.

The upcoming improvements in TM1 v12.6 sound like a game-changer, and I'm looking forward to seeing how they will address performance challenges. In the meantime, I appreciate your suggestion to calculate increments more efficiently and avoid redundant updates.

That said, with over 10 years of experience working with IBM Planning Analytics, I've already implemented such optimizations in my processes. Unfortunately, in scenarios like copying data from one version to another, this approach doesn't work effectively. The performance overhead due to logging remains significant in such cases, as the process inherently involves massive data movements.

Additionally, in many of the projects I've worked on, we rely heavily on the cube log and transaction logs to monitor and analyze changes. With the current limitations in TM1 v12, the transition to this version seems nearly unattainable for these types of use cases.

Do you have any specific recommendations or alternative approaches for managing large data movements and maintaining log integrity in TM1 v12? Perhaps something beyond standard optimization techniques?

Thank you again for your time and support, and happy holidays!

Best regards,

Hi Tin,

Let me start with some good news, albeit not helpful right now, and that is that in an upcoming release of TM1, likely TM1 v12.6 but definitely 1st half of 2025, we'll be replacing how/where we store updates, these transactions, to cubes. I'll spare you the details but net result is, without loosing any capability that TM1 v12 has today, that it'll be even [much] faster then TM1 v11 with logging on, and comes with the benefit of always having stored everything on disk after every transaction in a way that also improves load times, dramatically, if logs exist (completely obsoleting the need for SaveDataAll, if there even where a need in v12).

The 'bad' news therefore is that there is, currently, not much you can do as, as you've already referred to above, part of the high available architecture of v12 we have to store all transactions and, the way that's done today, is, whilst fault tolerant, not even as performant as v11 with transaction logging on. Only thing you might be able to do, if within your control and if you happened to be updating the same cell multiple times to begin with, is making sure you calculate the increment for a cell and update it only once. Also, if you happen to be incrementing with the value 0, a case that was brought to our attention earlier, that still has the full overhead of an update and should be avoided as well.

Happy holidays!

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Mon December 23, 2024 04:30 AM
From: Tin Nguyen
Subject: Performance Issue on TM1 Database 12 Without CubeSetLogChanges or unable to turn off logging

------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------

Hi Tin,

I'm glad you like the idea! I completely understand that, in the current setup, there are scenarios where the approach takes its sweet time - but that will be addressed.

Data copying is an interesting case. I actually have something else in the works specifically for that, but it's a bit early to go into detail. That said, once it's ready, copying leaf-level data will be a breeze. Even without these enhancement, these improvements will already make a significant difference for your copy use case.

You aptly described the issue as "performance overhead due to logging," and that has always been a key factor. Historically, we maintained both the final end-state (via the overlay tree for parallel interactions) and the detailed transactions. During commits, we merged the overlay tree with the base cube and then serialized the transactions to a text file - a process that added significant overhead.

In the new approach, we've eliminated the need for dual administration of transactions. Instead, we serialize the overlay tree directly, which is far more efficient. As a result, even if you enable transaction logging (either through pub/sub or the reintroduced pull mechanism via REST API), the serialization process is no longer part of the transaction/commit itself. This effectively removes the performance hit from logging.

Even if you need logging for monitoring or analysis purposes, you should still notice an improvement. The only difference compared to v11 is that transaction logging now captures every transaction when enabled. The ability to perform fine-grained logging by toggling it on and off is no longer available. While this could impact queries against the transaction log when filtering out unneeded entries, but then and again the retrieval of those transaction has been made significantly faster in v12 as well.

Happy New Year!

Hi Hubert,

Thank you for your detailed response and for sharing these insights. It's much appreciated! Apologies for the delay in getting back to you; between the holiday season and some testing I've been doing on my end, it took me a bit of time to circle back to this discussion.

The upcoming improvements in TM1 v12.6 sound like a game-changer, and I'm looking forward to seeing how they will address performance challenges. In the meantime, I appreciate your suggestion to calculate increments more efficiently and avoid redundant updates.

That said, with over 10 years of experience working with IBM Planning Analytics, I've already implemented such optimizations in my processes. Unfortunately, in scenarios like copying data from one version to another, this approach doesn't work effectively. The performance overhead due to logging remains significant in such cases, as the process inherently involves massive data movements.

Additionally, in many of the projects I've worked on, we rely heavily on the cube log and transaction logs to monitor and analyze changes. With the current limitations in TM1 v12, the transition to this version seems nearly unattainable for these types of use cases.

Do you have any specific recommendations or alternative approaches for managing large data movements and maintaining log integrity in TM1 v12? Perhaps something beyond standard optimization techniques?

Thank you again for your time and support, and happy holidays!

Best regards,

Hi Tin,

Let me start with some good news, albeit not helpful right now, and that is that in an upcoming release of TM1, likely TM1 v12.6 but definitely 1st half of 2025, we'll be replacing how/where we store updates, these transactions, to cubes. I'll spare you the details but net result is, without loosing any capability that TM1 v12 has today, that it'll be even [much] faster then TM1 v11 with logging on, and comes with the benefit of always having stored everything on disk after every transaction in a way that also improves load times, dramatically, if logs exist (completely obsoleting the need for SaveDataAll, if there even where a need in v12).

The 'bad' news therefore is that there is, currently, not much you can do as, as you've already referred to above, part of the high available architecture of v12 we have to store all transactions and, the way that's done today, is, whilst fault tolerant, not even as performant as v11 with transaction logging on. Only thing you might be able to do, if within your control and if you happened to be updating the same cell multiple times to begin with, is making sure you calculate the increment for a cell and update it only once. Also, if you happen to be incrementing with the value 0, a case that was brought to our attention earlier, that still has the full overhead of an update and should be avoided as well.

Happy holidays!

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Mon December 23, 2024 04:30 AM
From: Tin Nguyen
Subject: Performance Issue on TM1 Database 12 Without CubeSetLogChanges or unable to turn off logging

------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------

Hi Tin,

I'm glad you like the idea! I completely understand that, in the current setup, there are scenarios where the approach takes its sweet time - but that will be addressed.

Data copying is an interesting case. I actually have something else in the works specifically for that, but it's a bit early to go into detail. That said, once it's ready, copying leaf-level data will be a breeze. Even without these enhancement, these improvements will already make a significant difference for your copy use case.

You aptly described the issue as "performance overhead due to logging," and that has always been a key factor. Historically, we maintained both the final end-state (via the overlay tree for parallel interactions) and the detailed transactions. During commits, we merged the overlay tree with the base cube and then serialized the transactions to a text file - a process that added significant overhead.

In the new approach, we've eliminated the need for dual administration of transactions. Instead, we serialize the overlay tree directly, which is far more efficient. As a result, even if you enable transaction logging (either through pub/sub or the reintroduced pull mechanism via REST API), the serialization process is no longer part of the transaction/commit itself. This effectively removes the performance hit from logging.

Even if you need logging for monitoring or analysis purposes, you should still notice an improvement. The only difference compared to v11 is that transaction logging now captures every transaction when enabled. The ability to perform fine-grained logging by toggling it on and off is no longer available. While this could impact queries against the transaction log when filtering out unneeded entries, but then and again the retrieval of those transaction has been made significantly faster in v12 as well.

Happy New Year!

Hi Hubert,

Thank you for your detailed response and for sharing these insights. It's much appreciated! Apologies for the delay in getting back to you; between the holiday season and some testing I've been doing on my end, it took me a bit of time to circle back to this discussion.

The upcoming improvements in TM1 v12.6 sound like a game-changer, and I'm looking forward to seeing how they will address performance challenges. In the meantime, I appreciate your suggestion to calculate increments more efficiently and avoid redundant updates.

That said, with over 10 years of experience working with IBM Planning Analytics, I've already implemented such optimizations in my processes. Unfortunately, in scenarios like copying data from one version to another, this approach doesn't work effectively. The performance overhead due to logging remains significant in such cases, as the process inherently involves massive data movements.

Additionally, in many of the projects I've worked on, we rely heavily on the cube log and transaction logs to monitor and analyze changes. With the current limitations in TM1 v12, the transition to this version seems nearly unattainable for these types of use cases.

Do you have any specific recommendations or alternative approaches for managing large data movements and maintaining log integrity in TM1 v12? Perhaps something beyond standard optimization techniques?

Thank you again for your time and support, and happy holidays!

Best regards,

Hi Tin,

Let me start with some good news, albeit not helpful right now, and that is that in an upcoming release of TM1, likely TM1 v12.6 but definitely 1st half of 2025, we'll be replacing how/where we store updates, these transactions, to cubes. I'll spare you the details but net result is, without loosing any capability that TM1 v12 has today, that it'll be even [much] faster then TM1 v11 with logging on, and comes with the benefit of always having stored everything on disk after every transaction in a way that also improves load times, dramatically, if logs exist (completely obsoleting the need for SaveDataAll, if there even where a need in v12).

The 'bad' news therefore is that there is, currently, not much you can do as, as you've already referred to above, part of the high available architecture of v12 we have to store all transactions and, the way that's done today, is, whilst fault tolerant, not even as performant as v11 with transaction logging on. Only thing you might be able to do, if within your control and if you happened to be updating the same cell multiple times to begin with, is making sure you calculate the increment for a cell and update it only once. Also, if you happen to be incrementing with the value 0, a case that was brought to our attention earlier, that still has the full overhead of an update and should be avoided as well.

Happy holidays!

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Mon December 23, 2024 04:30 AM
From: Tin Nguyen
Subject: Performance Issue on TM1 Database 12 Without CubeSetLogChanges or unable to turn off logging

------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------

Hi Hubert,

I've been testing different approaches to address the performance challenges in my current setup. My source data comes from a cube view, and in the Data tab of my process, there are nested loops that process the data. To optimize this, I tried the old-school export-import method: instead of writing values directly to the cube, I use AsciiOutput to export the data into a file and then reimport it in a second process.

This approach significantly improved performance. What originally took a lot of time now only takes about 20 seconds. While it's not the most elegant solution, it effectively circumvents the performance overhead caused by transaction logging.

Given the nested loops in my case, I'd be curious to hear your thoughts on whether this export-import method is a viable long-term solution, or if there are potential downsides to using it in production environments.

Once again, I'm looking forward to seeing the upcoming enhancements for copying leaf-level data, as they sound like a game-changer for scenarios like this.

Thank you for your time and support.

Best regards,

Hi Tin,

I'm glad you like the idea! I completely understand that, in the current setup, there are scenarios where the approach takes its sweet time - but that will be addressed.

Data copying is an interesting case. I actually have something else in the works specifically for that, but it's a bit early to go into detail. That said, once it's ready, copying leaf-level data will be a breeze. Even without these enhancement, these improvements will already make a significant difference for your copy use case.

You aptly described the issue as "performance overhead due to logging," and that has always been a key factor. Historically, we maintained both the final end-state (via the overlay tree for parallel interactions) and the detailed transactions. During commits, we merged the overlay tree with the base cube and then serialized the transactions to a text file - a process that added significant overhead.

In the new approach, we've eliminated the need for dual administration of transactions. Instead, we serialize the overlay tree directly, which is far more efficient. As a result, even if you enable transaction logging (either through pub/sub or the reintroduced pull mechanism via REST API), the serialization process is no longer part of the transaction/commit itself. This effectively removes the performance hit from logging.

Even if you need logging for monitoring or analysis purposes, you should still notice an improvement. The only difference compared to v11 is that transaction logging now captures every transaction when enabled. The ability to perform fine-grained logging by toggling it on and off is no longer available. While this could impact queries against the transaction log when filtering out unneeded entries, but then and again the retrieval of those transaction has been made significantly faster in v12 as well.

Happy New Year!

Hi Hubert,

Thank you for your detailed response and for sharing these insights. It's much appreciated! Apologies for the delay in getting back to you; between the holiday season and some testing I've been doing on my end, it took me a bit of time to circle back to this discussion.

The upcoming improvements in TM1 v12.6 sound like a game-changer, and I'm looking forward to seeing how they will address performance challenges. In the meantime, I appreciate your suggestion to calculate increments more efficiently and avoid redundant updates.

That said, with over 10 years of experience working with IBM Planning Analytics, I've already implemented such optimizations in my processes. Unfortunately, in scenarios like copying data from one version to another, this approach doesn't work effectively. The performance overhead due to logging remains significant in such cases, as the process inherently involves massive data movements.

Additionally, in many of the projects I've worked on, we rely heavily on the cube log and transaction logs to monitor and analyze changes. With the current limitations in TM1 v12, the transition to this version seems nearly unattainable for these types of use cases.

Do you have any specific recommendations or alternative approaches for managing large data movements and maintaining log integrity in TM1 v12? Perhaps something beyond standard optimization techniques?

Thank you again for your time and support, and happy holidays!

Best regards,

Hi Tin,

Let me start with some good news, albeit not helpful right now, and that is that in an upcoming release of TM1, likely TM1 v12.6 but definitely 1st half of 2025, we'll be replacing how/where we store updates, these transactions, to cubes. I'll spare you the details but net result is, without loosing any capability that TM1 v12 has today, that it'll be even [much] faster then TM1 v11 with logging on, and comes with the benefit of always having stored everything on disk after every transaction in a way that also improves load times, dramatically, if logs exist (completely obsoleting the need for SaveDataAll, if there even where a need in v12).

The 'bad' news therefore is that there is, currently, not much you can do as, as you've already referred to above, part of the high available architecture of v12 we have to store all transactions and, the way that's done today, is, whilst fault tolerant, not even as performant as v11 with transaction logging on. Only thing you might be able to do, if within your control and if you happened to be updating the same cell multiple times to begin with, is making sure you calculate the increment for a cell and update it only once. Also, if you happen to be incrementing with the value 0, a case that was brought to our attention earlier, that still has the full overhead of an update and should be avoided as well.

Happy holidays!

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Mon December 23, 2024 04:30 AM
From: Tin Nguyen
Subject: Performance Issue on TM1 Database 12 Without CubeSetLogChanges or unable to turn off logging

------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------

Hi Tin,

'old school' in TM1 often means it works;-!

Kidding aside, export-import at the end of the day has the same effect on logging, that is if you make the same changes to cell values.

I'm guessing the cube view you are using comes from the same, or dependent, cube then the one you are writing to correct? The real issue therefore is related to the fact that your write invalidates all caches and the effect that has on subsequent reads. It's case specific how much that effect is but it's known to have this downside and exporting and importing has been the answer to this for the last couple of decades+ (read: it has been a viable long-term solution for a long time;-). 

But yes, the enhancement I'm looking at, which depends on some other work getting in first however (so don't expect it tomorrow), will get around that (as well as having proper snapshot semantics - but that's a whole other discussion), and still do all the logging one would ever need in [way] less time;-!.

Cheers,

Hi Hubert,

I've been testing different approaches to address the performance challenges in my current setup. My source data comes from a cube view, and in the Data tab of my process, there are nested loops that process the data. To optimize this, I tried the old-school export-import method: instead of writing values directly to the cube, I use AsciiOutput to export the data into a file and then reimport it in a second process.

This approach significantly improved performance. What originally took a lot of time now only takes about 20 seconds. While it's not the most elegant solution, it effectively circumvents the performance overhead caused by transaction logging.

Given the nested loops in my case, I'd be curious to hear your thoughts on whether this export-import method is a viable long-term solution, or if there are potential downsides to using it in production environments.

Once again, I'm looking forward to seeing the upcoming enhancements for copying leaf-level data, as they sound like a game-changer for scenarios like this.

Thank you for your time and support.

Best regards,

Hi Tin,

I'm glad you like the idea! I completely understand that, in the current setup, there are scenarios where the approach takes its sweet time - but that will be addressed.

Data copying is an interesting case. I actually have something else in the works specifically for that, but it's a bit early to go into detail. That said, once it's ready, copying leaf-level data will be a breeze. Even without these enhancement, these improvements will already make a significant difference for your copy use case.

You aptly described the issue as "performance overhead due to logging," and that has always been a key factor. Historically, we maintained both the final end-state (via the overlay tree for parallel interactions) and the detailed transactions. During commits, we merged the overlay tree with the base cube and then serialized the transactions to a text file - a process that added significant overhead.

In the new approach, we've eliminated the need for dual administration of transactions. Instead, we serialize the overlay tree directly, which is far more efficient. As a result, even if you enable transaction logging (either through pub/sub or the reintroduced pull mechanism via REST API), the serialization process is no longer part of the transaction/commit itself. This effectively removes the performance hit from logging.

Even if you need logging for monitoring or analysis purposes, you should still notice an improvement. The only difference compared to v11 is that transaction logging now captures every transaction when enabled. The ability to perform fine-grained logging by toggling it on and off is no longer available. While this could impact queries against the transaction log when filtering out unneeded entries, but then and again the retrieval of those transaction has been made significantly faster in v12 as well.

Happy New Year!

Hi Hubert,

Thank you for your detailed response and for sharing these insights. It's much appreciated! Apologies for the delay in getting back to you; between the holiday season and some testing I've been doing on my end, it took me a bit of time to circle back to this discussion.

The upcoming improvements in TM1 v12.6 sound like a game-changer, and I'm looking forward to seeing how they will address performance challenges. In the meantime, I appreciate your suggestion to calculate increments more efficiently and avoid redundant updates.

That said, with over 10 years of experience working with IBM Planning Analytics, I've already implemented such optimizations in my processes. Unfortunately, in scenarios like copying data from one version to another, this approach doesn't work effectively. The performance overhead due to logging remains significant in such cases, as the process inherently involves massive data movements.

Additionally, in many of the projects I've worked on, we rely heavily on the cube log and transaction logs to monitor and analyze changes. With the current limitations in TM1 v12, the transition to this version seems nearly unattainable for these types of use cases.

Do you have any specific recommendations or alternative approaches for managing large data movements and maintaining log integrity in TM1 v12? Perhaps something beyond standard optimization techniques?

Thank you again for your time and support, and happy holidays!

Best regards,

Hi Tin,

Let me start with some good news, albeit not helpful right now, and that is that in an upcoming release of TM1, likely TM1 v12.6 but definitely 1st half of 2025, we'll be replacing how/where we store updates, these transactions, to cubes. I'll spare you the details but net result is, without loosing any capability that TM1 v12 has today, that it'll be even [much] faster then TM1 v11 with logging on, and comes with the benefit of always having stored everything on disk after every transaction in a way that also improves load times, dramatically, if logs exist (completely obsoleting the need for SaveDataAll, if there even where a need in v12).

The 'bad' news therefore is that there is, currently, not much you can do as, as you've already referred to above, part of the high available architecture of v12 we have to store all transactions and, the way that's done today, is, whilst fault tolerant, not even as performant as v11 with transaction logging on. Only thing you might be able to do, if within your control and if you happened to be updating the same cell multiple times to begin with, is making sure you calculate the increment for a cell and update it only once. Also, if you happen to be incrementing with the value 0, a case that was brought to our attention earlier, that still has the full overhead of an update and should be avoided as well.

Happy holidays!

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Mon December 23, 2024 04:30 AM
From: Tin Nguyen
Subject: Performance Issue on TM1 Database 12 Without CubeSetLogChanges or unable to turn off logging

------------------------------
Tin Nguyen
IBM Planning Analytics and Excel VBA enthusiast
Co-founder at Treekala
------------------------------