Get datacell audit in business rule

Question

Hi,Is there any way to retrieve the user and timestamp associated with a data cell via a Business Rule?I'm building a Business Rule to generate a file from cube data. To do this, I'm using DataBuffers, but the cell audit information is not available in the DataBuffer cells. Given the cell POV, is there any way to retrieve the audit information (user and timestamp) for that cell?Thanks.

MarcusH · Accepted Answer

The solution Close Manager has a query that displays the audit information (except the user id which is easy enough to update). It is in the BR CMR_HelperQueries. You could tailor that to add the POV in a WHERE clause. I have cleaned it up as a standard SQL statement:

SELECT src.UserID, aec.Timestamp, (CASE WHEN aec.ScenarioId = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.ScenarioId AND M.DimTypeId = 2) END) AS ScenarioName, aec.TimeId, (CASE WHEN aec.EntityId = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.EntityId AND M.DimTypeId = 0) END) AS EntityName, (CASE WHEN aec.AccountId = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.AccountId AND M.DimTypeId = 5) END) AS AccountName, (CASE WHEN aec.FlowId = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.FlowId AND M.DimTypeId = 6) END) AS FlowName, (CASE WHEN aec.ICId = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.ICId AND M.DimTypeId = 8) END) AS ICName, (CASE WHEN aec.UD1Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD1Id AND M.DimTypeId = 9) END) AS UD1Name, (CASE WHEN aec.UD2Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD2Id AND M.DimTypeId = 10) END) AS UD2Name, (CASE WHEN aec.UD3Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD3Id AND M.DimTypeId = 11) END) AS UD3Name, (CASE WHEN aec.UD4Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD4Id AND M.DimTypeId = 12) END) AS UD4Name, (CASE WHEN aec.UD5Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD5Id AND M.DimTypeId = 13) END) AS UD5Name, (CASE WHEN aec.UD6Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD6Id AND M.DimTypeId = 14) END) AS UD6Name, (CASE WHEN aec.UD7Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD7Id AND M.DimTypeId = 15) END) AS UD7Name, (CASE WHEN aec.UD8Id = - 999 THEN 'None' ELSE
                 (SELECT M.Name
                 FROM    Member AS M
                 WHERE M.MemberId = aec.UD8Id AND M.DimTypeId = 16) END) AS UD8Name, aec.IsNoData
FROM   DataEntryAuditCell AS aec LEFT OUTER JOIN
             DataEntryAuditSource AS src ON aec.AuditSourceID = src.UniqueID
ORDER BY aec.Timestamp DESC

simon · Answer

Thanks Marcus

Sign up

Login with SSO

Login to the OneStream Community

Login with SSO