KB320401: MicroStrategy Enterprise Manager 10.x data load fails with the error "Stored Procedure ‘xxx.SP_CT_REMOVE_DUPLICATES’ does not exist" when the Enterprise Manager Warehouse is on a Teradata database

SUMMARY
This technical note describes a known issue in Strategy Enterprise Manager 10.x that causes the Enterprise Manager Data Load to fail because of an error in the execution of the Stored Procedure “SP_CT_REMOVE_DUPLICATES”
 
DESCRIPTION
The Strategy Enterprise Manager data load fails when the warehouse resides on a Teradata database. Entries similar to the following will be written in the MAEntMgr.xml log file on the Strategy Enterprise Manager 10.x machine: 
 

Java.sql.SQLException: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HY000:-: on SQLHANDLE][Teradata][ODBC Teradata Driver][Teradata Database] Stored Procedure ‘xxx.SP_CT_REMOVE_DUPLICATES’does not exist.

 
Note: xxx prefix is the database name that would appear different.
 
CAUSE
The error is seen because Stored Procedure “SP_CT_REMOVE_DUPLICATES” does not exist. This Stored Procedure does not exist because of an error during the Enterprise Manager Warehouse creation on a Teradata database. 
 
ACTION
This issue is not consistently reproducible and is only seen in a few cases. Follow the workaround to resolve this issue.
 
WORKAROUND
Run the following SQL to create the required Stored Procedure:

CREATE PROCEDURE xxx.SP_CT_REMOVE_DUPLICATES()
BEGIN
declare v_str varchar(4000);
CREATE SET VOLATILE TABLE CT_DEVICE_STATS_VOLATILE AS
(
SELECT *
FROM xxx.CT_DEVICE_STATS
) WITH DATA ON COMMIT PRESERVE ROWS;
DELETE FROM xxx.CT_DEVICE_STATS;
set v_str='INSERT INTO xxx.CT_DEVICE_STATS SELECT* FROM CT_DEVICE_STATS_VOLATILE';
CALL dbc.SysExecSQL (:v_str);
set v_str = 'DROP TABLE CT_DEVICE_STATS_VOLATILE';
CALL dbc.SysExecSQL (:v_str);
END; 

 
 Note: The xxx prefix is the Database name that would need to be modified prior to executing the SQL