Timeouts While Another Process Is Retrieving Data

Post Reply
raeldor
Posts: 23
Joined: Thu Jun 11, 2020 11:08 am
OLAP Product: IBM PAW
Version: Unknown
Excel Version: 365

Timeouts While Another Process Is Retrieving Data

Post by raeldor » Tue Nov 16, 2021 12:16 pm

Hi,

We are experiencing an issue that is making me question if TM1 is a multi-user system at all. We have a process written in Python that uses TM1PY (Rest API) to pull data from TM1 into our data warehouse. It's a fair amount of data and takes about 10 minutes. During this time, users are intermittently experiencing timeouts when using the PAW front end. Not only that, if users modify data while the process is running the runtime increases exponentially, to 30 or maybe more minutes. I read on the CubeWise website that when pulling from a view, if any data is changed then the view is invalidated and the retrieve starts again. Surely that can't be true; that would be madness. At first I thought it was a CPU issue since the view pull pegged the CPU at 99%, so we changed the max used cores from 8 to 1. The CPU usage went down to about 20%, but the timeouts continued to happen.

I'm really hoping someone else has had some experience of this and found a resolution or configuration option that fixes this. I'm starting to get really worried that this might be a fundamental engine design problem.

Thanks

burnstripe
Posts: 84
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Timeouts While Another Process Is Retrieving Data

Post by burnstripe » Tue Nov 16, 2021 2:09 pm

How frequently is data being pulled into Powerbi, does it need to be during working hours or could it be performed overnight. If updates do need to be loaded mid day, you could consider pulling changes incurred during the day from the transaction log and perform a complete refresh overnight?

Tm1py can interrogate the transaction logs if logging has been enabled for the cube.

Or just pull a smaller slice a data during working hours that's less likely to be changed

raeldor
Posts: 23
Joined: Thu Jun 11, 2020 11:08 am
OLAP Product: IBM PAW
Version: Unknown
Excel Version: 365

Re: Timeouts While Another Process Is Retrieving Data

Post by raeldor » Wed Nov 17, 2021 1:08 am

Yes, it needs to be pulled frequently, as the data mart powers our reporting so as to reduce the load on the transactional PAW server. At least, that's what we thought, but we had no idea performance would be degraded to such an extent by a single simple query.

I think you're right, we will have to look at pulling incremental changes from the transaction log instead.

Coming from relational databases, a single query causing timeouts for other users is just shocking.

andreykadysh
Posts: 7
Joined: Fri Aug 27, 2021 5:41 pm
OLAP Product: IBM Cognos Planning Analytics
Version: 2.0.9.9
Excel Version: 2016

Re: Timeouts While Another Process Is Retrieving Data

Post by andreykadysh » Thu Nov 18, 2021 7:29 am

raeldor, hi! Facing similar problem with PAW... But difference is that timeouts happen when TI-process is working, not TM1PY. Rest api queries from PAW to tm1 are waiting this process, so nobody can work at this moment.

I noticed that locks do not happen when you open the same cube slice in architect or tm1web. It happens only in PAW.

raeldor
Posts: 23
Joined: Thu Jun 11, 2020 11:08 am
OLAP Product: IBM PAW
Version: Unknown
Excel Version: 365

Re: Timeouts While Another Process Is Retrieving Data

Post by raeldor » Thu Nov 18, 2021 3:58 pm

Does anyone know if there's an article anywhere explaining exactly how locking works internally in TM1? It might shed some light on what's going on here. I've read the IBM page about locks, but it only talks about 'object' locking. I seriously hope that when I'm pulling from a view that it's not placing a read lock on the ENTIRE cube. I'm pretty sure that's not the case, as when my view is pulling I AM able to modify data in the same cube. So, at what level is data locking occurring?

burnstripe
Posts: 84
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Timeouts While Another Process Is Retrieving Data

Post by burnstripe » Thu Nov 18, 2021 6:57 pm

Read activity can still cause locking as the view will need to calculate and load those values to memory and until tm1 knows what values are in the cube, it won't be able to update it hence the lock. Breaking the process/view into smaller chunks should help with locking but it's likely the problem is from the model design.

Without seeing your model it's difficult to make suggestions but anything to reduce calculation time of the view will help. From optimization of rules, converting unnecessary rules to static data, and optimising cube ordering.

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Timeouts While Another Process Is Retrieving Data

Post by Steve Rowe » Sat Nov 20, 2021 7:30 am

This is conjecture.

I'm just wondering if this is the difference between using the RestAPI to extract the data rather than using the native TI process to export the view.
There was a recent thread on this where the understanding was that a RestAPI extract has to calculate the whole view and then extract where as the native TI process method means that the view is calculated incrementally while the extract is running. The RestAPI Cellset being generated by MDX and the TI view being generated by some unnamed lower level method.

This is a fundamentally different sequence under the hood, over the years I've probably built 1,000s of extracts using TI and never seen issues of this type where the server is locking up when exporting data.

I was in a partner session with IBM, years back now, where it was publically stated that the RestAPI was slower than TI and if you wanted to optimise for performance then you should use a TI process. I know that around this time a large amount of work had gone into minimising the locking that was taking place in the database when TI was running, but its not clear if all of these changes apply equally to TI view extracts and the RestAPI. It is clear that they are a fundamentally different sequence so I'm not surprised to see fundamentally different behaviour.

I'll caveat all of the above to say that we don't do much with the RestAPI so I could be wide of the mark here.
Technical Director
www.infocat.co.uk

raeldor
Posts: 23
Joined: Thu Jun 11, 2020 11:08 am
OLAP Product: IBM PAW
Version: Unknown
Excel Version: 365

Re: Timeouts While Another Process Is Retrieving Data

Post by raeldor » Sat Nov 20, 2021 10:57 am

That's very interesting, thank you. Certainly worth investigation. TI is a little clumsy for pushing data to external databases, especially for large sets of data. I guess we could spit out to CSV and consume from there, but our infrastructure makes it difficult to have shared directories on the server. I'm also thinking to use the log to see which dimension elements have been updated and use that as a filter to dynamically modify the MDX of the view to limit the data pulled across. Not sure how easy it will be to modify the MDX, but if it's feasible I think it might reduce read locks.

User avatar
Steve Rowe
Site Admin
Posts: 2232
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Timeouts While Another Process Is Retrieving Data

Post by Steve Rowe » Sat Nov 20, 2021 11:09 am

Just in case and so you don't waste your time testing an alternative approach, I would build the source for the TI using the traditional ViewCreate method, rather then ViewCreateByMDX which I think would put you back in the same place as running via the RestAPI.

I recently found a significant performance benefit from switching from a using MDX views to the standard view type, so IMO, doing views with MDX should be approached with caution, though it is of course nice to have a new method to play with.

If your MDX view is sufficently complex (for example, filtering values on two different dimensions at once), which can't be done with the ViewCreate method (since we can only put MDX expressions against one dimension at a time), consider putting a ruled measure into the cube that performs the equivalent of your complex MDX filter and then using this to drive the ViewCreate extract.
Technical Director
www.infocat.co.uk

raeldor
Posts: 23
Joined: Thu Jun 11, 2020 11:08 am
OLAP Product: IBM PAW
Version: Unknown
Excel Version: 365

Re: Timeouts While Another Process Is Retrieving Data

Post by raeldor » Sun Nov 21, 2021 7:20 am

I used architect to create the view, so I think it's a traditional view, but when I trace through TM1PY (that uses restful API) it seems to create MDX anyway. Maybe that's another downside of using the restful approach. However, the benefits of being able to use Python are great, so if I can trim down the dataset to a reasonable size, I would like to stay with that approach.

Really appreciate all your insight, thank you.

Post Reply