04-10-2023 01:11 PM
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!
Solved! Go to Solution.
04-11-2023 05:24 AM - edited 04-11-2023 05:27 AM
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)
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:
04-11-2023 05:24 AM - edited 04-11-2023 05:27 AM
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)
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:
04-11-2023 05:37 AM
Hi,
to add my 2 cents.
Object DataCellValidationMsg has also function GetLocalizedText which will do all the error formatting
@ChrisLoran thanks for sharing.
04-12-2023 10:36 AM
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.
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
04-18-2023 06:19 AM
HI, I saw Stage Validation solution error released in Open Place 🙂
04-03-2024 11:27 AM
Hi Chris, is there a solution for the Transformation Validations? Thanks in advance!
04-12-2023 07:35 PM
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
04-12-2023 07:54 PM
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?
04-12-2023 09:32 PM
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
...
04-13-2023 01:29 AM
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.