ProClarity Analytics Server (PAS) is a web site providing storage of OLAP ‘report’ definitions and caching of the resulting reports. The ‘reports’ are organised by a hierarchy of libraries, briefing books and views. The metadata for all of this is stored in a database.
In order to copy these libraries, books and views between servers, ProClarity/Microsoft provide a feature in a tool called PAS Administration. There is a "Migrate Repository" button that leads to a wizard where a source and target database are specified. On the last page of this wizard is a "Migrate" button to begin the work.
Typically the migration should take under a minute. However, after performing the migration many times we were finding the migration took longer and longer – up to six hours in the end!
My colleague Dan Meacham ran profiler to check what it was up to. It turns out there’s a table called SecurityTimeStamp against which it issues a "Select *…". I’m not sure what causes insertions into this table, but on the various PAS servers we checked there were between 70,000 and 1,200,000 rows in this table!
A swift "TRUNCATE TABLE SecurityTimeStamp" on the source database brings the migration time back down to a few seconds again.
We’re using version 6.2. Version 6.3 exists and may fix the issue without resorting to dodgy database hacks. Nevertheless, I hope this saves other people a bit of time.
Â
Josh's Space 