Last couple of weeks, I faced tuff time with our solution manager table space growth. Suddenly one Monday morning solution manager's entire PSAPDAT got full. We faced severe performance problem and finally we cancelled our client showcase of CCLM functionality. :sad:
We were not able to identify the cause immediately and planned to extend the table space. But I myself very clear that extending table space is not a solution, it’s a temporary fix. Later we found it was the our BI load causes such trouble in our landscape.
Through this blog I would like to share how the worst maintenance in BI part of the solution manager take entire memory and CPU Utilization and guidance from SAP notes to fix.
Symptoms : Duplication on BI loads
I always thought that extractors has some programing logic behind and it works perfect, ofcourse it has some programing logic, but the input for the logic is editable ( like start and end periods), This is what happened in our case, On Friday while testing CCLM, Mistakenly I changed the extractor periods to 1 min. Alas!! Whole two days all my cclm extractor runs every 1 min and dumps more than 40 GB data in 2 days to solman.
Due to this, We found more than 1000 + BI loads on CCLM related infocubes. We manually cleared all the duplicate request and make the environment stable. Hence please be very careful in editing extractor periods and other changes.
Symptoms : Long SISM:EXEC* Jobs run time
This is another place, you can check whether your BI part of solution manager is proper or not. After the upgrade our EWA jobs SM:EXEC* take max of 3 days to finish and triggers much of time_out error. Earlier I didn’t feel this is the issue, but later I noticed the corresponding fact tables are getting filled huge in size.
SAP Note 1817223 - EarlyWatch Alert (EWA) Job - SM:EXEC SERVICES is taking a long time to complete is dealing with this scenario, I tried to dig deep into this SAP note, tried to match the scenarios with other functionality like BPMon and CCLM.
Symptoms : Report SAP_INFOCUBE_DESIGNS
We can run this report time to time to check the Fact table and Dimension table growth percentage, if you find any time Fact table 100% full, then you must go for compression of the cubes.
Symptoms : T Code TAANA
I come to know this tcode TAANA from SAP Note 1480588. This helps to identify the reason for hash table growth. We can get the lists of infocube and the number of entries. This applicable not only for hash table, even we used this for CCLM tables and found which are the infocubes filling huge data to F tables, later we can look for either aggregation or compression of these cubes.
Fixes: Solman_setup
During the solman_setup, we would have done the default and standard housekeeping as mentioned in the sap note 1839221 - High data volume on the Solman system 7.1 server. But this is not going to help much. We need to look for other manual optimisation tricks too.
Fixes: Deactivation of Trend Analysis Collection
After much effort also we were not able to reduce our EWA job duration by following SAP note 1889457 - EWA Report not generating by throwing the TIMEOUT error . As per the note we deactivated the tread analysis collection in EWA report. But, this is the temporary fix.
Currently SAP working on this issue, soon they bring some permanent solution for this cause.
Fixes : Aggregators
By default, solution manager has very few aggregators, we need to manually activate the aggregators or we need to create our own aggregators based on our custom queries before scheduling any compression.
We created couple of aggregators for CCLM and UPL features to improve the performance. Check the below path for aggregators, RSA1 -> Select the cube -> manage -> right click -> Maintain aggregates.
Fixes : Compression
I followed the guide on SAP Note 1480588 - ST: E2E Diagnostics - BI Housekeeping – Information, which gives the guidance for how to schedule a compression for the cubes 0SMD_*D and 0SMD_*H. Unlike other BW environment we are not required to schedule compression manually. In solution manager, only need to adjust the tables E2E_BI_DELETE for 0SMD_*H cubes and also for 0SMD_D* cubes, later the job E2E BI HOUSEKEEPING which taken care compression and deletion.
We got the help from the below note 1178655. Be aware, there are lots of warning provided in the note.
Fixes Archiving
BW Archiving also possible in solution manager. We are exploring on this topic using the below guide, DATA ARCHIVING PROCESS IN SAP BW
Other Sources
There are lots of Solution manager BI fine tuning shared in the SAP note 1835721 - Troubleshooting performance problems in Solution Manager and Note 1794478 - Monitoring and Reporting: High Redo log volumes.
Please keep eye on SAP notes for more BI corrections and optimization steps son solution manager.
- SAP Managed Tags:
11 Comments
