Just wanted to share my experiences which might be very useful for you to pull you out from a crises situation when you are surrounded by the users in order to fix an issue ASAP.
We had a situation where suddenly we started getting cases from the BW users that BW system is responding very slowly and their queries are responding in minutes rather than seconds 🙁 .We started doing the basic health check of the system. Initial investigation showed that there are too many dialog work process occupied in SM 66 and all were stuck from around more than 1500 secs for majority of users. To fix this situation we killed all the processes that were stuck for a lot of users but since users were logged in to the systems after some time the situation became the same.
Contacted Basis team because we were now almost sure that this is a system/memory related issue and basis team can help us. Our Basis Consultant suggested us that he wants to lock all the users for some time and then restart the application and the database server to see if helps.
But No luck, when we the system was brought up still same issue. Hence it again came to BW queue from Basis. Suddenly while investigating the root cause we noticed that 17 cubes in the system were showing inactive status in BW Accelerator. We checked the RFC connection. Checked if all the services were up and running fine.
So where is the problem? What caused this cubes to be inactive in BWAcceleralor? Any Body???
Started thinking to rebuild indexes for all the 17 cubes again, which would mean an escalation and obviously would be a time consuming activity 🙁 .Suddenly remembered the field TECHINA (Technical Reason for Inactivity). This field did all the magic for us. We went to table RSDDTREXDIR (Administration of the TREX Aggregates) and checked for the cubes with Object Status as Inactive (OBJSTAT as INA). We were able to find all the 17 cubes here in the list. Then we had a look at the field TECHINA (Technical Reason for Inactivity), value there was ‘C’ (Adjusted during change run). This gave us a hint and hopes ,yes we can do it quickly 🙂 .We then went ahead and logged to the change run monitor (TCODEàCHANGERNMONI) and were able to a see a terminated change run. This was the cause of all problems. We restarted this change run and the problem got resolved. Suddenly all the cubes had active BWA indexes.
Here are the various reasons for which a BWA index could get inactive. Hope this field becomes handy for you in future.
So as learning from this please see the field Technical Reason for Inactivity (TECHINA) of the table RSDDTREXDIR.This will give you a fair idea why the cube is showing as inactive in BWA and can also do the magic like it did for us. Thanks to SAP for providing such nice tables :).