Parallel ODBC loads

Post Reply
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Parallel ODBC loads

Post by JohnO »

I have been doing some testing using RUNPROCESS with a SAP Hana ODBC sourced TI where the SQL passed is the same other than a week parameter. So we gave a main process which calls runprocess several times running the same TI, each call having a different week parameter. The called TI itself only has a counter on the data tab and an asciioutput in the epilog to write that counter.

It works but the performance is not what I was hoping. I was hoping that I would get benefits by using a parallel process but the run time experienced with each called TI being similar and being close to the run time of running everything in series.

We have used RunProcess for cube to cube TI's and have experienced great time benefits so I'm wondering whether we can achieve a similar result with ODBC sourced TI processing.

Has anyone done something similar and obtained the result I was hoping for? Any suggestions/ comments?
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Parallel ODBC loads

Post by lotsaram »

Either you have a resource bottleneck on the TM1 server side or the SAP side. Or you have a commit queue issue due to all threads finishing at the same time.

How many cores does the TM1 server have versus how many RunProcess threads are you firing? Does the server that the TM1 instance is running on have sufficient resources to actually process all the threads in parallel versus are you forcing the OS to allocate sparse resources accross the threads (and basically throttle all of them back).

Have you tested the query time by running the equivalent set of queries on Hana in parallel with a query tool? It's possible that Hana doesn't have the resources to support multi-threading queries.

Lastly what about the runtime portion verus the commit time? TM1 supports concurrent write action but this goes to temporary object(s) that are only visible to the thread making the updates. At the conclusion of the transaction the new data is merged. Commit actions are SINGLE THREADED. If all queries are of the same size and all finish at the same time (give or take a few seconds or milliseconds) then a commit queue will be formed and the threads will commit one at a time on a first come first served basis. If your target cube is quite large or the dimension order isn't optimized for update partitioning based on week then the increases in commit time (which is always in serial) could easily eat up any savings in processing time. You can relatively easily see if this scenario applies to your case by viewing the threads in a session monitor ("tm1top"). There are strategies to deal with this (chunking parallel queries into different sizes so they don't all finish at once, optimizing dimension order for quicker commits https://cubewise.com/blog/what-exactly- ... imization/)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Parallel ODBC loads

Post by declanr »

In addition to the good points from Lotsaram, I will throw in an additional possibility that is easy to rule out.

The problem can be the time taken querying the data source.
Particularly if the source is a view and takes 1 minute to query all, sometimes (depending on keys and so such) you can find that even adding a where clause to only return 10% of records can still take 1 minute (or almost that.)
So if a large portion of the processing time is spent running the initial query, splitting it out may not save much time overall.

These are the processes that you notice when running manually can seem to hang on finishing the prolog tab.
This is an easy one to check and rule out by adding a LogOutput to update the transaction log when (only) the first record is read.
If there is a long time between process kick-off and that output, then it could be an issue and you can address it by fine-tuning the source query.
Declan Rodger
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: Parallel ODBC loads

Post by JohnO »

Thanks for the replies

1) Cores = 30. So no issue with capacity
2) Parallel Hana queries using a different query tool are fast, but that query tool is not on the TM1 server
3) No commits as not writing to anything, just reading and doing a count then writing the total to a text file. Trying to keep it simple

I'll try out some other scenarios to see if I can identify anything.
ardi
Community Contributor
Posts: 148
Joined: Tue Apr 02, 2013 1:41 pm
OLAP Product: tm1, cognos bi
Version: from TM1 9.4 to PA 2.0.9.6
Excel Version: 2010
Location: Toronto, ON

Re: Parallel ODBC loads

Post by ardi »

JohnO wrote: Mon Jan 10, 2022 11:21 pm Thanks for the replies

1) Cores = 30. So no issue with capacity
2) Parallel Hana queries using a different query tool are fast, but that query tool is not on the TM1 server
3) No commits as not writing to anything, just reading and doing a count then writing the total to a text file. Trying to keep it simple

I'll try out some other scenarios to see if I can identify anything.
Is the "Use Unicode" checkbox selected? If so, can you try to deselect it? I have had experiences that deselecting this option has made a big difference

Also, how long does it take for one single thread to finish, try running only one thread manually

How many threads are trying to run in parallel. As others have already mentioned, you have to find the sweet spot when it comes to determone how many threads to run in parallel.
Ardian Alikaj
JohnO
Posts: 92
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: Parallel ODBC loads

Post by JohnO »

Removing unicode makes no difference

1) Run time for one week = 172 seconds
2) Run time for each of 3 parallel processes (One main process calling 3 processes using RunProcess, one process per week) = 550 seconds.

So no benefit from parallel processing using runprocess. When I have time I will try tm1runti to see if that has any benefits but I don't really want to go down that path.
MarenC
Regular Participant
Posts: 346
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: Parallel ODBC loads

Post by MarenC »

Hi,

I am sure others have used RunProcess with odbc soucres. I think Tomok used it on the datatab in one of his processes that used a stored procedure as the datasource.

Anyhow, bit of a leftfield approach but could you try creating 3 identical processes, one for each week and running those in parallel?

Maren
Terramup
Posts: 16
Joined: Tue Nov 23, 2010 4:12 pm
OLAP Product: Cognos TM1
Version: 10.1.0
Excel Version: 2007
Location: Portland, OR USA

Re: Parallel ODBC loads

Post by Terramup »

I do use ODBC multi-threaded and have seen it work great and not so great. It really depends on how you are hitting the indexes on your target like was mentioned earlier.

Do all the sub processes finish at the same time or do they finish in sequence with a large gap between? If you cut it to two threads running a week each does it cut down the time, or if you expand it to 4 threads?
What I am looking for here is an indication that some part of the process is still serial instead of parallel. I got stuck once because I was turning off the logging in the sub process instead of outside of the process.
holger_b
Posts: 131
Joined: Tue May 17, 2011 10:04 am
OLAP Product: TM1
Version: Planning Analytics 2.0
Excel Version: 2016
Location: Freiburg, Germany

Re: Parallel ODBC loads

Post by holger_b »

Hi John, I did a lot in that field and had big benefits from using TM1RunTI together with Cubewise's hustle.exe. Google for that, it's easy and I love it.

And: You may have to think about object locking. Especially if your processes update dimensions, instances will block each other so they will run sequentially. And you will need ParallelInteraction turned on in the tm1s.cfg.

Holger
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Parallel ODBC loads

Post by PavoGa »

No locking reported in the server log for these separate threads?

One other thing not mentioned specifically above but we've seen is if the loading is applying a lot of feeders and those feeders apply other feeders, etc., will cause very slow commit times. On some processes, we remove the rules from a cube and then re-apply them after the loading for significantly faster commit times.

Might be worth checking how long the commit is taking by putting a logout in the Epilog and comparing that to the process complete time. As Lotsaram pointed out, un-optimized cubes can seriously (!) impact TI performance.
Ty
Cleveland, TN
Post Reply