Attaching Intersection Errors to an Email During Automated Load Processing

MattRussell
New Contributor

Does anyone have example code they'd be willing to share here that allows the Intersection Error report to be attached and sent via email when errors are encountered during *automated* load processing?

Our users are being notified that an error has been encountered, but they have to log into OS to look up what the error was (new unmapped account, etc.). It would be nice to provide the error detail as an attachment or in the body of the email.

Also just curious how others in the community are notifying users that Load Errors have been encountered during automation, and providing the error detail to them?

Any input is appreciated.

Thanks!

8 REPLIES 8

ChrisLoran
Valued Contributor

To do this, there are two main steps
1) find a suitable dashboard report that replicates what you see in Intersection Validations
2) run it from a BR

For step (1): There is a report, bundled with the Standard Application Reports (downloadable from Marketplace) which shows invalid intersections (see below)

ChrisLoran_0-1681204571476.png

The trouble is with this report, is that it doesn't substitute the member names into the Intersection Error message column.  So you get messages like this:
         {0} member {1} is not within the constraint settings for account {2}
and even that gets chopped off on the Standard Application Reports.


This makes the report far less useful, and probably not good enough for this purpose. We'll come back to this in a moment.

For step (2) , how to run a PDF from a dashboard from a Business Rule, there are two options:
 - BR runs a Data Mgmt step to export the dashboard report to a place in the FileShare, and then attach it through the email , or
 - Use the brapi.Dashboards.Process.ExportReportsForMultipleDashboards) to generate the file bytes for a PDF report, save the file bytes as a PDF , and attach that to the email.


However, even by implementing step (2) there isn't a ready-to-use dashboard report that will correctly show the intersection errors and show you what members are involved.

To resolve the issues from Step 1,  I would go for a simpler solution, and simply embed the intersection validation messages (with the member names) into the body of the email , as text or HTML.

The following Extender Rule will report the Intersection Validation messages for the current workflow/time/scenario.
The other notable feature of this example (unlike any dashboard report) is that it will correctly substitute the correct member names into the Validation Error Message, by using a DataCellValidationMsgList class.
Make sure your workflow is on the import step when running this example in isolation, and this sample code must be created as an Extender rule.

 

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.Extender.ValidationReport
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try
				If args.FunctionType = ExtenderFunctionType.Unknown
					
					Dim gWFid 		As Guid    = si.WorkflowClusterPk.ProfileKey
					Dim idScenario 	As Integer = si.WorkflowClusterPk.ScenarioKey
					Dim idTime 		As Integer = si.WorkflowClusterPk.TimeKey

					Using dbConnApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
						Dim strSQL As String = "SELECT * FROM StageToFinanceValidationError WHERE WorkflowProfileKey=@wfk AND WorkflowScenarioKey=@wsk AND WorkflowTimeKey=@wtk"
						Dim lstParamInfo As New List(Of DbParamInfo)
						lstParamInfo.Add( New DbParamInfo("wfk", gWFid))
						lstParamInfo.Add( New DbParamInfo("wsk", idScenario))
						lstParamInfo.Add( New DbParamInfo("wtk", idTime))
						
						Using dtValErr As DataTable = brapi.Database.ExecuteSql(dbConnApp, strSQL, lstParamInfo, False)	
							Dim sb_ValErrorsReport As New Text.StringBuilder()						
							For Each drVal As DataRow In dtValErr.Rows
								Dim strValMsg = drVal("ValidationmessagesXml").ToString()
								Dim validationMsgList As DataCellValidationMsgList = XmlObject.ReadXmlString(si, GetType(DataCEllValidationMsgList), strValMsg, True)
								For Each err As DataCellValidationMsg In validationMsgList.Messages									
									sb_ValErrorsReport.AppendLine( String.Format(err.ErrorMsg,err.StringParams) )
								Next
							Next
							brapi.ErrorLog.LogMessage(si, "Validation errors (see detail pane)", sb_ValErrorsReport.ToString() )
							
						End Using
						
					End Using
					brapi.Dashboards.Process.ExportReportsForMultipleDashboards
				End If

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

 

 Output:

ChrisLoran_1-1681205085337.png

 

Hi,

to add my 2 cents.

Object DataCellValidationMsg has also function GetLocalizedText which will do all the error formatting

@ChrisLoran  thanks for sharing.

I had this on my to do list since months... so I had a look to the SQL query.

I want to work on a simplified version but the one below should be an starting point for parameter replacement. I don't think there is any validation error using more than 3 params (anyway adding new one is not a big deal). This query handled validation errors which might not using {i} params like validation message when a member m is invalid in dimension d.

franciscoamores_0-1681310135149.png

 

This would need to be tested where there are multiple validation messages in the same cell.

WITH XMLValidationErrors
AS (
SELECT *
,cast(cast(validationMessagesXml AS TEXT) AS XML) AS xmlmsg                         
FROM StageToFinanceValidationError
WHERE
StageToFinanceValidationError.WorkflowProfileKey = '|WFProfileKey|'
AND StageToFinanceValidationError.WorkflowScenarioKey = |WFScenarioId|
AND StageToFinanceValidationError.WorkflowTimeKey = |WFTimeId|
)
,XMLValidationErrorsStr
AS (
SELECT WorkflowProfileHierarchy.HierarchyIndex
,XMLValidationErrors.AccountId 
,StageSummaryTargetData.SnT AS [Scenario]
,StageSummaryTargetData.Cube AS [Cube]
,StageSummaryTargetData.TmT
,StageSummaryTargetData.Am
,StageSummaryTargetData.EtT
,StageSummaryTargetData.VwT
,StageSummaryTargetData.AcT
,StageSummaryTargetData.FwT
,StageSummaryTargetData.OgT
,StageSummaryTargetData.IcT
,StageSummaryTargetData.U1T
,StageSummaryTargetData.U2T
,StageSummaryTargetData.U3T
,StageSummaryTargetData.U4T
,StageSummaryTargetData.U5T
,StageSummaryTargetData.U6T
,StageSummaryTargetData.U7T
,StageSummaryTargetData.U8T
,VE.Msg.query('.').value('(/message/dataCellValidationMsgType)[1]', 'NVARCHAR(max)') AS ValidationMsgType
,VE.Msg.query('.').value('(/message/errorMsg)[1]', 'NVARCHAR(max)') AS ValidationErrMsg
,VE.Msg.query('.').value('(/message/stringParams/stringParam)[1]', 'NVARCHAR(max)') AS Param1
,VE.Msg.query('.').value('(/message/stringParams/stringParam)[2]', 'NVARCHAR(max)') AS Param2
,VE.Msg.query('.').value('(/message/stringParams/stringParam)[3]', 'NVARCHAR(max)') AS Param3
,VE.Msg.query('.').value('(/message/errorMsg)[1]', 'NVARCHAR(max)') AS ValidationErrorString
FROM XMLValidationErrors
INNER JOIN WorkflowProfileHierarchy ON XMLValidationErrors.WorkflowProfileKey = WorkflowProfileHierarchy.ProfileKey
INNER JOIN StageSummaryTargetData ON StageSummaryTargetData.SummaryRowID = XMLValidationErrors.SummaryRowID
AND StageSummaryTargetData.Wfk = XMLValidationErrors.WorkflowProfileKey
AND StageSummaryTargetData.Wsk = XMLValidationErrors.WorkflowScenarioKey
AND StageSummaryTargetData.Wtk = XMLValidationErrors.WorkflowTimeKey
CROSS APPLY xmlmsg.nodes('/dataCellValidationMsgList/messages/message') AS VE(Msg)
WHERE WorkflowProfileHierarchy.ProfileKey = '|WFProfileKey|'
)
SELECT *
,REPLACE(REPLACE(REPLACE((
ValidationErrorString + (
CASE
WHEN CHARINDEX('{0}', ValidationErrorString) = 0
AND PARAM1 IS NOT NULL
THEN ' {0}'
ELSE ''
END
) + (
CASE
WHEN CHARINDEX('{1}', ValidationErrorString) = 0
AND PARAM2 IS NOT NULL
THEN ', {1}'
ELSE ''
END
) + (
CASE
WHEN CHARINDEX('{2}', ValidationErrorString) = 0
AND PARAM3 IS NOT NULL
THEN ', {2}'
ELSE ''
END
)
), '{0}', COALESCE(PARAM1, '')), '{1}', COALESCE(PARAM2, '')), '{2}', COALESCE(PARAM3, '')) AS FINAL_MSG
FROM XMLValidationErrorsStr
ORDER BY HierarchyIndex
,  AccountId     

HI, I saw Stage Validation solution error released in Open Place 🙂

DanielWillis
Contributor III

Hey Matt

Have just recently automated data loads using BRAPi.Utilities.ExecuteFileHarvestBatchParallel which returns a WorkflowBatchFileCollection object that contains the information needed without having to use SQL.

Regards,

Daniel

Hi,

 

From the WorkflowBatchFileCollection object you can navigate down to ValidateIntersectionProcessInfo objects for each workflow unit processed. In ValidateIntersectionProcessInfo you can get number of invalid intersections but I don't think you can get details about invalid intersections from there. are you getting them?

Another way to get the intersection errors:

Using dbConn As DbConnInfo = BRApi.Database.CreateDbConnInfo(si, DbLocation.Application, Nothing)

	' Get the intersection check errors from table StageToFinanceValidationError
	Dim invalidIntersections As List(Of StageToFinanceValidationError) = New Data().GetStageToFinanceValidationErrors(si, wfClusterPk.ProfileKey, wfClusterPk.ScenarioKey, wfClusterPk.TimeKey)
	' Get list of Tuple(SummaryRowId, ErrorMessage) 
	Dim GetErrorMsg = Function(errors As List(Of DataCellValidationMsg))
		Dim sb As New Text.StringBuilder
		sb.AppendLine("Validation Errors Bypassed:")
		errors.ForEach(Sub(x) sb.AppendLine(x.GetLocalizedText(si)))
		Return sb.ToString
	End Function
	Dim listSummaryRowsAndErrors As List(Of Tuple(Of String, String)) = invalidIntersections.Select(Function (x) New Tuple(Of String, String)(x.SummaryRowID.ToString, GetErrorMsg(x.DataCellValidationMsgList.Messages))).ToList
	...

DanielWillis
Contributor III

Hey you're right I thought I had it in the bottom of the email but must have just been interested in calling it out and not the detail. I just had the raw batch log at the bottom.

 

batch email output.png