Return Codes of ExecuteMapping Functions

Hi,

I'm using the APE-Functions ModelExecuteFactMapping and ModelExecuteHierarchyMapping to load cubes and build dimensions, which in general works fine.

However, big challenge is, that these functions do not return any error code to the calling process, so there is no way to check, if the mapping was successful, had warnings or failed. Only in the Administration-dashboard the status can be checked, but this is a manual task and cannot be handled for automatic jobs running in batch overnight.

Also in the dashboard i can only see the last status and if a mapping is executed several times during an import process the last one might have been successful, but earlier ones could have failed.

I think there must be a way to control the status of executed mappings, shouldn't it?

Are there any experiences or best practices on this?

What is best way to check the status of executed fact- or hierarchy-mappings in APE?

Regards,
Ralf

Find more posts tagged with

Infor EPM Discussions

Comments

unknown

Hi Ralf,

if you click on dashboards -> Logs -> view application logs

You will see a collection of all logs available to your model. I believe this information can be accessible via AEP or perhaps is stored in a cube/dimension. I recall seeing a "work-around" where an application studio was built to show the logging information.

Hope that helps

Ibrahim

Ralf Zerfass

Hi Ibrahim,

I appreciate a work-around how to filter the logging tables, thanks in advance.

But wouldn't an appropriate return code of the function call be much easier and more helpful, so I can validate the result of a mapping right after its execution without searching in the logging tables?

And this seems to be for all kind of modeling functions, also the "new" ModelingPublishToOLAP-Functions do not return a function result, all functions are of type "void".

I understand that it might be some development work to change all existing modeling-functions to return its result, but at least for new functions added this should be a standard.

Ralf

unknown

Absolutely, a return code or a function to query the result of an 'Execute' process is the most optimal solution, though how long would we be waiting for this to be implemented. It's clearly being captured somewhere, should be immediately available after triggering the function.

Log this as a feature request, I will gladly upvote!

unknown

Hi Ralf,

currently using Application Logs is the only option. But we are aware that the process should be easier and therefore we are working on new functions which allow you to receive such information. If everything goes fine, they should be available in the October release.

Best,

Standa

Ralf Zerfass

Hi Standa,

very good news, I'm looking forward having it :-)

Thanks,

Ralf

depm-636-Secure Relational Reporting_2.mp4

Robert Thomas

We ran into similar situation and wrote an App Engine script to "scrape" the logs so our mapping processes could get the results. The exact code published in separate reply. The code sample returns an array of error counts for each level - but you could modify the code to meet your particular requirements.

We call the utility after executing mapping passing in the following parameters:

string strEvents = "7006,10001,10002,10016";
string strLevels = "1,2,3";
string strObjects = "*";
string strResults = "*";
bool blnNotify = true;
bool blnLog = true;

IntArray iaLevelCounts = CFO_GetDEPM_FWK_LOG_ENTRYV3(strUser, intLoadNum, strProcessGUID, dtStartDateTime, dtEndDateTime, strEvents, strLevels, strUser,
strObjects, strResults, blnNotify, blnLog, blnDebug);

Robert Thomas

#define EngineVersion 5.0
#define RuntimeVersion 5.0
#include "CFO_WriteProcessHistoryV3","*"
#include "CFO_Write_CUS_AppEngineLogV3","*"

IntArray CFO_GetDEPM_FWK_LOG_ENTRYV3(string pUser, int pLoadNum, string pProcessGUID, DateTime pStartDateTime, DateTime pEndDateTime, string pEvents, string pLevels, string pFilterUsers,
                                     string pObjects, string pResults, bool pNotify, bool pLog, bool pDebug)
@Description: "This process will log the messages in the DEPM_FWK_LOG_ENTRY log table";
@Category: "CFO_Utilities";
@Parameter[pUser]: "User name";
@Parameter[pLoadNum]: "Load Num = 0 is fine";
@Parameter[pProcessGUID]: "pProcessGUID; if blank sent, one will be generated";
@Parameter[pStartDateTime]: "Filter for rows after StartDateTime (required)";
@Parameter[pEndDateTime]: "Filter for rows before EndDateTime (required)";
@Parameter[pEvents]: "Filter for specific EVENT_ID (see DEPM_FWK_LOG_EVENT); * for all; separate multiple with comma";
@Parameter[pLevels]: "Filter for specific LEVEL (1,2,3); * for all; separate multiple with comma";
@Parameter[pFilterUsers]: "Filter for specific USER_ID; * for all; separate multiple with comma";
@Parameter[pObjects]: "Filter for specific OBJECT_NAME; * for all; separate multiple with comma";
@Parameter[pResults]: "Filter for specific RESULT (blank, 0, 1, 2); * for all; separate multiple with comma";
@Parameter[pNotify]: "Notify for each record found (true/false)";
@Parameter[pLog]: "Log for each record found (true/false)";
@Parameter[pDebug]: "Debug - additional logging or not (true/false)";
@Returns: "Integer array of LEVEL count";
{
	/* MAINTENANCE LOG
	 **********************************************************************************************************************************************************************
	 * 
	 * Date			Responsible							Description of Change
	 * -----------	-----------------------------------	------------------------------------------------------------------------------------------------------------------
	 * 07/29/2021	Bob Thomas, CFO Solutions			Initial Release
	 * 07/30/2021	Bob Thomas, CFO Solutions			Found that rows in log were not being found; added a five second sleep to see if this helps
	 *													Also add 1 second to end time passed
	 * 08/01/2021	Bob Thomas, CFO Solutions			Split logging into 2 separate rows in order to minimize message size; also shorten parameter messages
	 *													Rows in DEPM_FWK_LOG_ENTRY not being found; try to fix by subtracting 1 second from start time passed
	 * 08/19/2021	Bob Thomas, CFO Solutions			Updated to use CFO_Write_CUS_AppEngineLogV3 & CFO_WriteProcessHistoryV3
	 *													Also turn off NotifyTrue for parameters; update LogTrue on some steps
	 * 08/23/2021	Bob Thomas, CFO Solutions			Use new feature for incrementing counters
	 **********************************************************************************************************************************************************************
===========================================================================================================================================================================
Test Params
robert.thomas@cfosolutions.com
0

<startdatetime>
<enddatetime>
7006,10001
<levels>
<users>
<objects>
<results>
true
true
	 */

	// Declare Constants
	string consStagingDatabase = "DEPM_Staging";
	string consStagingTable = "DEPM_FWK_LOG_ENTRY";
	string consProcessName = GetProcessName();
	string consDebug = "Debug";
	string consInfo = "Info";
	string consWarning = "Warning";
	string consError = "Error";
	bool consNotifyTrue = true;
	bool consNotifyFalse = false;
	bool consLogTrue = true;
	bool consLogFalse = false;
	string consStatusLevel_Starting = "Starting";
	string consStatusLevel_Finished = "Finished";
	int consSleepMilliseconds = 5000;

	// Declare processing variables
	string strUser = StringTrim(pUser);
	int intLoadNum = ToInt(GetDateTimeAsString(CreateDateTime(), "MMddyyHHmm"));
	if (pLoadNum != 0)
	{intLoadNum = pLoadNum;}
	string strProcessGUID = Guid();
	if (StringTrim(pProcessGUID) != "")
	{strProcessGUID = StringTrim(pProcessGUID);}
	string strMessage = "";
	string strErrorMessage = "";
	int intErrorCode = 0;
	int intWarningCount = 0;
	int intErrorCount = 0;
	int intNumRows = 0;
	IntArray iaLevelCounts = CreateIntArray();
	int intFoundLevel1Count = 0;
	int intFoundLevel2Count = 0;
	int intFoundLevel3Count = 0;
	int intFoundLevel4Count = 0;
	DateTime dtProcessStartDateTime = CreateDateTime();

	Write("Step: Open SQL connection");
	SQLConnection sqlConnect = SQLCreateNamedConnection(consStagingDatabase);

	strMessage = "****** " + consProcessName + " starting******";
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consInfo, strMessage, consNotifyTrue, consLogTrue);

	// Get Parameters
	DateTime dtStartDateTime = pStartDateTime;
	DateTime dtEndDateTime = pEndDateTime;
	string strEvents = StringTrim(pEvents);
	string strLevels = StringTrim(pLevels);
	string strFilterUsers = StringTrim(pFilterUsers);
	string strObjects = StringTrim(pObjects);
	string strResults = StringTrim(pResults);
	bool blnNotify = pNotify;
	bool blnLog= pLog;
	bool blnDebug = pDebug;

	// Log Parameters
	string strAllParameters = "";
	strMessage = "pStartDateTime: " + ToString(dtStartDateTime);
	strAllParameters = strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pEndDateTime: " + ToString(dtEndDateTime);
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pEvents: " + strEvents;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pLevels: " + strLevels;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pFilterUsers: " + strFilterUsers;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pObjects: " + strObjects;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pResults: " + strResults;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pNotify: " + blnNotify;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pLog: " + blnLog;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "pDebug: " + blnDebug;
	strAllParameters = strAllParameters + "; " + strMessage;
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyTrue, consLogFalse);
	CFO_WriteProcessHistoryV3(sqlConnect, intLoadNum, strProcessGUID, consProcessName, strUser, consStatusLevel_Starting, "Process starting", strAllParameters);

	try
	{
		// build cross reference tables
		strMessage = "Step: Building cross reference tables";
		CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyFalse, consLogFalse);

		StringDictionary sdEVENT_ID_NAME = CreateStringDictionary();
		string strSQL = "SELECT [EVENT_ID], [NAME] FROM DEPM_FWK_LOG_EVENT";
		SQLData sdDEPM_FWK_LOG_EVENT = SQLExecuteQuery(sqlConnect, strSQL);

		foreach (SQLDataRow row in sdDEPM_FWK_LOG_EVENT)
		{
			double dblEVENT_ID = SQLDataRowGetDouble(row, "EVENT_ID");
			string strNAME = SQLDataRowGetString(row, "NAME");
			string strEVENT_ID = ToString(dblEVENT_ID);
			sdEVENT_ID_NAME[strEVENT_ID] = strNAME;
		}

		StringDictionary sdLEVEL_NAME = CreateStringDictionary();
		sdLEVEL_NAME["1"] = consError;
		sdLEVEL_NAME["2"] = consWarning;
		sdLEVEL_NAME["3"] = consInfo;
		sdLEVEL_NAME["4"] = consDebug;

		strMessage = "Step: Sleeping for " + consSleepMilliseconds;
		CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consDebug, strMessage, consNotifyFalse, consLogFalse);
		Sleep(consSleepMilliseconds);

		StringArray saSQLWhere = CreateStringArray();

		DateTime dtStartDateTimeMinus1Second = AddSeconds(dtStartDateTime, -1);
		string strStartDateTime = GetDateTimeAsString(dtStartDateTimeMinus1Second, "yyyy-MM-dd HH:mm:ss");
		// Write("strStartDateTime: " + strStartDateTime);
		DateTime dtEndDateTimePlus1Second = AddSeconds(dtEndDateTime, 1);
		string strEndDateTime = GetDateTimeAsString(dtEndDateTimePlus1Second, "yyyy-MM-dd HH:mm:ss");
		// Write("strEndDateTime: " + strEndDateTime);

		Append(saSQLWhere, "[ADDED_DATE] >= '" + strStartDateTime + "'");
		Append(saSQLWhere, "[ADDED_DATE] <= '" + strEndDateTime + "'");

		if (strEvents != "*")
		{
			Append(saSQLWhere, "[EVENT_ID] IN (" + strEvents + ")");
		}

		if (strLevels != "*")
		{
			Append(saSQLWhere, "[LEVEL] IN (" + strLevels + ")");
		}

		if (strFilterUsers != "*")
		{
			StringArray saFilterUsers = StringSplit(strFilterUsers, ",");
			StringArray saQuotedFilterUsers = CreateStringArray();
			foreach (string strFilterUser in saFilterUsers)
			{
				Append(saQuotedFilterUsers, "'" + strFilterUser + "'");
			}

			string strQuotedFilterUsers = StringJoin(saQuotedFilterUsers, ",");
			Append(saSQLWhere, "[USER_ID] IN (" + strQuotedFilterUsers + ")");
		}

		if (strObjects != "*")
		{
			StringArray saObjects = StringSplit(strObjects, ",");
			StringArray saQuotedObjects = CreateStringArray();
			foreach (string strObject in saObjects)
			{
				Append(saQuotedObjects, "'" + strObject + "'");
			}

			string strQuotedObjects = StringJoin(saQuotedObjects, ",");
			Append(saSQLWhere, "[USER_ID] IN (" + strQuotedObjects + ")");
		}

		if (strResults != "*")
		{
			Append(saSQLWhere, "[RESULT] IN (" + strResults + ")");
		}

		string strSQLWhere = StringJoin(saSQLWhere, " and ");
		strSQL = "SELECT * FROM DEPM_FWK_LOG_ENTRY WHERE " + strSQLWhere;
		// Write(strSQL);

		intNumRows = 0;

		SQLData sdDEPM_FWK_LOG_ENTRY = SQLExecuteQuery(sqlConnect, strSQL);
		foreach (SQLDataRow row in sdDEPM_FWK_LOG_ENTRY)
		{
			intNumRows = intNumRows + 1;
			string strID = SQLDataRowGetString(row, "ID");
			DateTime dtADDED_DATE = SQLDataRowGetDateTime(row, "ADDED_DATE");
			double dblEVENT_ID = SQLDataRowGetDouble(row, "EVENT_ID");
			string strEVENT_ID = ToString(dblEVENT_ID);
			double dblLEVEL = SQLDataRowGetDouble(row, "LEVEL");
			string strLEVEL = ToString(dblLEVEL);
			string strUSER_ID = SQLDataRowGetString(row, "USER_ID");
			string strOBJECT_NAME = SQLDataRowGetString(row, "OBJECT_NAME");
			double dblRESULT = SQLDataRowGetDouble(row, "RESULT");
			string strEVENT_DATA = SQLDataRowGetString(row, "EVENT_DATA");
			strMessage = "strID: " + strID +
				" dtADDED_DATE: " + ToString(dtADDED_DATE) +
				" dblEVENT_ID: " + dblEVENT_ID +
				" sdEVENT_ID_NAME: " + sdEVENT_ID_NAME[strEVENT_ID] +
				" dblLEVEL: " + dblLEVEL +
				" strUSER_ID: " + strUSER_ID +
				" strOBJECT_NAME: " + strOBJECT_NAME +
				" dblRESULT: " + dblRESULT;
			string strMsgLevel = sdLEVEL_NAME[strLEVEL];
			CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, strMsgLevel, strMessage, blnNotify, blnLog);
			strMessage = "strEVENT_DATA: " + strEVENT_DATA;
			CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, strMsgLevel, strMessage, blnNotify, blnLog);

			if (dblLEVEL == 1)
			{intFoundLevel1Count = intFoundLevel1Count + 1;}
			else
				if (dblLEVEL == 2)
			{intFoundLevel2Count = intFoundLevel2Count + 1;}
			else
				if (dblLEVEL == 3)
			{intFoundLevel3Count = intFoundLevel3Count + 1;}
			else
				if (dblLEVEL == 4)
			{intFoundLevel4Count = intFoundLevel4Count + 1;}
		}
	} catch (strErrorMessage, intErrorCode)
	{
		strMessage = "Error: " + strErrorMessage + " Error Number: " + intErrorCode;
		CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consError, strMessage, consNotifyTrue, consLogTrue);
		intErrorCount = intErrorCount + 1;
	}

	strMessage = "Found " + intNumRows + " rows in DEPM_FWK_LOG_ENTRY for specified criteria";
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consInfo, strMessage, consNotifyTrue, consLogFalse);
	strMessage = "Found " + intFoundLevel1Count + " Errors " +
		intFoundLevel2Count + " Warnings " +
		intFoundLevel3Count + " Info " +
		intFoundLevel4Count + " Debug ";
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, consInfo, strMessage, consNotifyTrue, consLogFalse);

	DateTime dtProcessEndDateTime = CreateDateTime();
	int intDurationSeconds = ToInt(GetDurationAsDouble(dtProcessStartDateTime, dtProcessEndDateTime));
	strMessage = "****** " + consProcessName + " finished with " + intWarningCount + " Warnings and " + intErrorCount + " Errors; Duration (seconds): " + intDurationSeconds + " ******";
	string strErrorLevel = consInfo;
	if (intErrorCount != 0)
	{strErrorLevel = consError;}
	else
	{
		if (intWarningCount != 0)
		{strErrorLevel = consWarning;}
	}
	CFO_Write_CUS_AppEngineLogV3(sqlConnect, consProcessName, strUser, intLoadNum, strProcessGUID, strErrorLevel, strMessage, consNotifyTrue, consLogTrue);
	CFO_WriteProcessHistoryV3(sqlConnect, intLoadNum, strProcessGUID, consProcessName, strUser, consStatusLevel_Finished, strMessage, "");

	SQLDisconnect(sqlConnect);

	Append(iaLevelCounts, intFoundLevel1Count);
	Append(iaLevelCounts, intFoundLevel2Count);
	Append(iaLevelCounts, intFoundLevel3Count);
	Append(iaLevelCounts, intFoundLevel4Count);

	return iaLevelCounts;
}

Copyright © 2025 Infor. All rights reserved.