For the past couple of months, my team and I have been busy with QA testing and UAT of our Planning Analytics project, which is getting ready to Go-Live.
Also see Planning Analytics PAW and PAX workflow / execution flow
During our QA process, we uncovered a couple of performance issues which we have since resolved, I figured I would write a small post about those hoping it helps others.
Hidden rows and white spaces in PA for Excel(PAx) Reports / Embedded Web sheets published to PA Workspace (PAW) cause performance issues(i.e., take longer to run).
Some of the embedded web sheets our power users had created were taking a ton of time to come back. They were somewhat complex views, but similar views when executed from Architect took 10-20 seconds to finish, whereas they were taking 4-5 minutes in PAW. Upon performing RCA, we found out that our users had a lot of rows hidden in the sheet, which was causing degradation of the performance in PAW. Deleting those rows instead of deleting improved the performance.
TI process causing TM1 Server Lock Contention.
We noticed there was a lock contention happening at the TM1 server level. Some of the user’s thread were going into wait state waiting for some other thread to finish.
The issue in our case was that our daily data refresh TI process was causing the lock. The TI process keeps running checking the DB Date/time flag, and if the flag is set, the process then refreshes the actuals data in the TM1 cubes. To resolve this issue, we moved the TI process to be kicked off our corporate scheduler rather than having it to run continuously.
I have listed an article from IBM, which contains useful information on troubleshooting lock contentions.
How Business Intelligence Works 
One comment