SYMPTOM:
In Strategy Enterprise Manager 10.x various errors or issues may occur when performing the Data Load including Primary Key Violation database errors, unexpected database locks, missing data from the Enterprise Manager tables, and other Data Load failures.
CAUSE:
One potential cause for this behavior is having multiple Enterprise Manager services configured to access the same Enterprise Manager warehouse. The Enterprise Manager environment is designed to have one running service per warehouse. In this configuration, the single service will run Data Loads according to the schedules defined in the Enterprise Manager configuration and should not encounter any issues with Primary Key Violations or database locks since it will be the only database client performing the database operations.
If multiple Enterprise Manager services are configured to access the same Enterprise Manager warehouse, the services will not be aware of each other and will both attempt to run scheduled Data Loads at the same time according to the schedules defined. This can result in Data Load queries being run multiple times simultaneously which can cause query contention on the database, attempts to insert duplicate data, etc.
ACTION:
Ensure that each Enterprise Manager warehouse has only a single Enterprise Manager service configured to access it. This may require particular attention when configuring an Intelligence Server cluster, as Configuration Wizard will attempt to automatically configure the Enterprise Manager service if a default Statistics repository is selected during Intelligence Server Configuration.
If it is found that multiple Enterprise Manager services are configured to access the same warehouse, select one to leave running and immediately stop all of the others. It may be desirable to intentionally enter incorrect connection information (like a wrong username or password) to ensure that if the services are inadvertently started they will not be able to connect successfully.
If it is not known whether multiple Enterprise Manager services might be running at the same time, a query against the Enterprise Manager warehouse can help to verify. When Enterprise Manager services trigger data loads, sessions on the Intelligence Server(s) are created with a "Source" of "Enterprise Manager". This session source is also recorded in the session related tables in the Enterprise Manager warehouse, along with the hostname or IP of the machine where the session originated. Thus, if Enterprise Manager sessions are recorded at the same time from multiple different machines, it indicates that multiple Enterprise Manager services are running.
Example (when EVENTSOURCE = 14, the session was created by Enterprise Manager):
SELECT MAX(CONNECTTIME), CLIENTMACHINE FROM IS_SESSION_STATS WHERE EVENTSOURCE = 14 GROUP BY CLIENTMACHINE;
