KB275427: In MicroStrategy 9.4.1, Enterprise Manager Data Load fails on Oracle databases with error "ORA-01400: cannot insert NULL into..."

SYMPTOM: 
In Strategy version 9.4.1, when using an Enterprise Manager Warehouse and Statistics repository in an Oracle database, the data load process might fail with the following error message:
 

ORA-01400: cannot insert NULL into ("IS_SESSION"."EM_CLIENT_MACHINE...

 
 
 
The following entry can also be found in the MSTRMigration.log file:
 

Fact Migration: Transaction 4 of 14; Sequence 2 of 2.
Error executing SQL :Execute Query failed. 
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [23000:1400: on SQLHANDLE] [Strategy][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("MSTR_STAT"."IS_SESSION"."EM_CLIENT_MACHINE")INSERT INTO IS_SESSION (EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, IS_SESSION_TM_SEC, EM_CONNECT_SOURCE, EM_DISCONN_REASON, DAY_ID, HOUR_ID, MINUTE_ID) SELECT EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, (CAST(IS_TMP_DISCON_TS AS DATE) - CAST(IS_CONNECT_TS AS DATE)) * 86400, EM_CONNECT_SOURCE, EM_DISCONN_REASON, DAY_ID, HOUR_ID, MINUTE_ID FROM IS_SESSION_TMP1

 
CAUSE: 
This issue has been observed when an Oracle database does not differentiate the blank spaces from the NULL values. The EM_CLIENT_MACHINE column in the IS_SESSION table does not allow for NULL values which causes the errors to be encountered.
 
 
ACTION:
All the queries meant to run during the Enterprise Manager Dataload process exist on the table EM_SQL. The corresponding query can be modified to do a validation on the null values before they are inserted in the IS_SESSION table.
 
The "Fact Migration: Transaction 4 of 14; Sequence 2 of 2" can be found by using the following SQL statement:
 

SELECT * FROM dbo.EM_SQL WHERE EM_TRANS_ID = 4 AND EM_SEQ_ID=2;

 
Once the above row has been identified, it can be noticed that the content of the "EM_SQL" column is as follows (no null validation):
 

INSERT INTO IS_SESSION (EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, IS_SESSION_TM_SEC, EM_CONNECT_SOURCE, EM_DISCONN_REASON, DAY_ID, HOUR_ID, MINUTE_ID) SELECT EM_RECORD_TS, EM_LOAD_TS, IS_SESSION_ID, IS_SERVER_ID, EM_APP_SRV_MACHINE, EM_WEB_SRV_MACHINE, EM_USER_ID, EM_CLIENT_MACHINE, IS_CONNECT_TS, IS_DISCONNECT_TS, IS_TMP_DISCON_TS, IS_CONNEC_DH_ID, IS_DISCON_DH_ID, DATEDIFF(SS,IS_CONNECT_TS,IS_TMP_DISCON_TS), EM_CONNECT_SOURCE, EM_DISCONN_REASON, DAY_ID, HOUR_ID, MINUTE_ID FROM IS_SESSION_TMP1

 
In order to add the Null validation, the content of the "EM_SQL" column identified above needs to be replaced with the following content (note the null validation added in bold):
 

INSERT INTO is_session

            (em_record_ts, em_load_ts, is_session_id, is_server_id,

             em_app_srv_machine, em_web_srv_machine, em_user_id,

             em_client_machine, is_connect_ts, is_disconnect_ts,

             is_tmp_discon_ts, is_connec_dh_id, is_discon_dh_id,

             is_session_tm_sec, em_connect_source, em_disconn_reason, day_id,

             hour_id, minute_id)

   SELECT em_record_ts, em_load_ts, is_session_id, is_server_id,

          em_app_srv_machine, em_web_srv_machine, em_user_id,

          NVL (TRIM (em_client_machine), 'N/A') em_client_machine,

          is_connect_ts, is_disconnect_ts, is_tmp_discon_ts, is_connec_dh_id,

          is_discon_dh_id,

            (CAST (is_tmp_discon_ts AS DATE) - CAST (is_connect_ts AS DATE))

          * 86400,

          em_connect_source, em_disconn_reason, day_id, hour_id, minute_id

     FROM is_session_tmp1;

 
Once the values on the EM_SQL column for the Transaction 4, Sequence 2 have been updated with the null-validated contents, once the query in Transaction 4, Sequence 2 is called by the Enterprise Manager Dataload process, the null insertion will no longer happen.
 
IMPORTANT NOTE: It is strongly suggested to do a backup of the database before attempting to manually update any values on it.