BPC 7.5 SP3 ; MS SQL Server 2005, single DB server, 3 Web/App servers
We deployed our 5th application recently. It behaves differently then the last 4. When BPC Excel DRE report user connects to this app and then refreshes the report for the first time the web server is loaded to 100% CPU utilization for about 2 - 3 minutes. The database server is active periodically during this 2 -3 minutes, but not very loaded. When the report is complete, subsequent refreshes of the report are very fast. Significant changes to the expansions, filtering, and dimension members using this report also result in fast performance. Opening other reports and running them are relatively fast.
If the reporting user is idle for about 30 minutes or more and then refreshes the same report the slow behavior is repeated. It takes 2 - 3 minutes and the webserver is saturated. The W3WP.EXE process (the IIS worker process) is the process that is very busy running BPC during this 2 - 3 minutes.
Any ideas about why this application behaves this way? I am guessing that BPC is creating a cache of some kind during this 2 - 3 minutes. Can anyone confirm this?
The two features of this application that makes it different from the other 4 are:
1) It has a large customer dimension (20K base level elements) with 4 hierarchies
2) It has a time dimension that has days as the base level (the others have months)
Any ideas about what causes this initial slowness and how to control it would be appreciated.