-posted in Planning Analytics Group as well

Can anyone shed more light on what CA does when it connects to and queries TM1?

We assumed CA would be relatively harmless to connect to our production server, but we were very wrong. Within minutes CA threads were causing a large number of waits, blocking perspectives/pax reports, TI runs, and other connections. Plus none of the CA threads could be canceled. 

Cognos Analytics reports and dashboards should simply read data from TM1, what else is it doing? 

Does someone have a document that lays out what CA is doing to TM1 when it runs a report, establishes the first connection, or other common interactions?

------------------------------
Ryan Clapp
------------------------------

#CognosAnalyticswithWatson

Hi Ryan

4 things that you can look at:

1. Our tests show that the CA content store database was the most active database among all the different databases. We had to get the content store database on the fastest server to get stability.
2. Check the connection properties on CA configuration. The setting should be "Read uncommitted".
   
3. I presume you are running a MS SQL database. Check that you switch the "Is Read Committed Snapshot" set to True. It removed quite a lot of deadlocks for us.
   
4. We used the framework manager to build packages on top of views. The package had 3 layers and there were some reports that had joins too. That meant that a single table can be joined repeatedly at each of those levels to the results within the generated SQL. We found that the waits start when these joins, to the same table, are executed at the same time and causes these threads to suspend or wait for each other. It was described at the query waits for itself. These were most visible on our long running reports.

I hope this gives you some start point to look at various options.

Good luck.

​

------------------------------
Jan Vermeer
------------------------------

​Hi Ryan

4 things that you can look at:

1. Our tests show that the CA content store database was the most active database among all the different databases. We had to get the content store database on the fastest server to get stability.
2. Check the connection properties on CA configuration. The setting should be "Read uncommitted". This is available in classic view.
   
3. I presume you are running a MS SQL database. Check that you switch the "Is Read Committed Snapshot" set to True. It removed quite a lot of deadlocks for us.
   
4. We used the framework manager to build packages on top of views. The package had 3 layers and there were some reports that had joins too. That meant that a single table can be joined repeatedly at each of those levels to the results in the generated SQL. We found that the waits start when these joins, to the same table, are executed at the same time and causes these threads to suspend or wait for each other. It was described at the query waits for itself. These were most visible on our long running reports.

Good luck.

------------------------------
Jan Vermeer

------------------------------

Hey Ryan,

What version of TM1 and CA are you on?  Are you using the PA data source connection or TM1 data source connection in CA?

We've seen the PA connection in CA cause locking.  You may want to try switching to the TM1 connection.

------------------------------
Jeffrey Qureshy
------------------------------

We are running PA 2.07 and CA 11.1.1. We are using the PA connector because IBM has told us that the TM1 connector is going out of support and will not be developed anymore.

------------------------------
Ryan Clapp
------------------------------

Original Message:
Sent: Thu January 30, 2020 10:40 AM
From: Jeffrey Qureshy
Subject: CA threads causing lock contention

Hey Ryan,

What version of TM1 and CA are you on?  Are you using the PA data source connection or TM1 data source connection in CA?

We've seen the PA connection in CA cause locking.  You may want to try switching to the TM1 connection.

------------------------------
Jeffrey Qureshy
------------------------------