SUMMARY
This Knowledge Base article describes an issue classified as a defect by Strategy. In Enterprise Manager 10.0 - 10.6, the CLOSESESSIONS command does not close orphan sessions during the data load resulting in inflated Connection Duration for those sessions. This issue has been fixed in Enterprise Manager 10.11 and 10.4 Hotfix 7.
SYMPTOM
When using the Connection Duration metric in Enterprise Manager 10.0 - 10.6, it may be seen that the duration of certain user sessions appears to be significantly inflated.
In the above example, the session that started at 5:45 PM appears to last for 199 hours (around 8 days). This orphan session contains a disconnect time of the date and time of the next data load which occurred 8 days after the connect time (9/11/2017).
When added to a data load, the CLOSESESSIONS command is intended to properly close these orphan sessions by updating the disconnect time to 24 hours after the connect time. If the CLOSESESSIONS command was to close the orphan session in the above example, the session at 5:45 PM would have a disconnect time of 24 hours later and, therefore, a Connection Duration of 24:00:00 rather than a disconnect time of 8 days later and a Connection Duration of 199:44:04. However, after use of the CLOSESESSIONS command, the disconnect time for these orphan sessions is not updated to 24 hours and the Connection Duration remains inflated.
CAUSE
There is a defect in the Close Orphan Sessions script in Enterprise Manager 10.x that causes orphan sessions to remain open after the CLOSESESSIONS command is used in the data load.
Additionally, in Enterprise Manager 10.7 and newer, there is another defect where adding the CLOSESESSIONS command to the data load updates the session disconnect time to 24 hours for all sessions rather than just the orphan sessions. This causes the Connection Duration to show up as 24 hours for every session. The following Knowledge Base article provides further information about Connection Duration showing up as 24 hours for every session after the use of CLOSESESSIONS in Enterprise Manager 10.7 and newer:
Enterprise Manager metrics for Connection Duration show 24 hours for every session in MicroStrategy 10.4 Hotfix 5, 10.7 and newer
ACTION
This issue has been fixed in Enterprise Manager 10.11 and 10.4 Hotfix 7.
An updated script has been created and attached to this article which can be used to fix the CLOSESESSIONS command in Enterprise Manager 10.0 - 10.6. This updated script will allow orphan sessions to properly close with a disconnect time of 24 hours later and will not change the disconnect time for already closed sessions.
To apply the fix:
1. Locate the Enterprise Manager close orphan session scripts on the Enterprise Manager machine. By default on Linux, these files will be located in /{MSTR INSTALL PATH}/ EnterpriseManager/Scripts. On Windows, C:\Program Files (x86)\MicroStrategy\Enterprise Manager\Scripts.
The files are named in the following format:
em_close_orphan_session_{database type}
2. Unzip the file attached at the bottom of this article. Inside the zip file, there are replacement scripts for each of the em_close_orphan_session_{database type} scripts.
3. Replace each of the existing em_close_orphan_session scripts for every database type with the scripts in the zip file.
4. After replacing the scripts, the CLOSESESSIONS command can be added to the data load in order to properly close any future orphan sessions that are created.
CREATE DATA LOAD "Data Load close sessions" FOR ENVIRONMENT "ServerName" AND PROJECT "Strategy Tutorial" DO ACTION UPDATEWAREHOUSE CLOSESESSIONS BEGIN DATE "07/06/2017 10:00:00 +0000" FREQUENCY WEEKLY ON MONDAY AT 17:30:00 ENABLED IN ENTERPRISE MANAGER "EM machine name" IN PORT 9999;
EXECUTE DATA LOAD "Data Load close sessions" WITH DATA FROM "12/22/1970 22:00:00 +0000" TO "4/15/2018 20:00:00 +0000" IN ENTERPRISE MANAGER "EM machine name" IN PORT 9999;
