EPMAutomate & Powershell – Data Management & Kickouts

Posted on by

When we used to load data on-premise, via MaxL + Data Load Rules we were able to redirect any kickouts to a log file. We could then act on that log file or at a minimum notify the administrator that their were kickouts.

When it comes to the cloud, if you are loading via Data Management, unfortunately there is no “out of the box” way to grab these kickouts from an automation standpoint. The admin would have to be notified that the load “failed” (even if its just kickouts), and then login to EPM Cloud and review the log.

As I was building out an automation library in Powershell I assumed I would have to run all DM loads via the REST API as it would provide me with the job number and from that I could download the log.

However, as I was running some tests I noticed that when you run via EPMAutomate and there are kickouts, DM “fails” and produces a log such as : runDataRule_<DateTime>.log, this is generated in the directory in which the script was run. If you review this log it gives a whole lot of information, but it does provide one crucial thing, the name of the DM Log!

OK, so we can get the DM log, now lets build this process out in powershell to download & parse this log.

The Setup

Before we can start to grab the log and parse it, we need to setup some items just to make this post complete:

  • Set Login / Path Variables
  • Login to Cloud, Delete Old File, Upload New File

Set Login / Path Variables

_
$EPM_PATH_AUTO = Split-Path -Path $PSScriptRoot -Parent
$EPM_PATH_SCRIPTS = "$EPM_PATH_AUTO\01_SCRIPTS"
$EPM_PATH_FILES_IN = "$EPM_PATH_AUTO\03_FILES\INBOUND"
$EPM_USER = "YourUserName"
$EPM_PASSFILE = "YourPassFile"
$EPM_DOMAIN = "YourDomain"
$EPM_URL = "YourURL"
$EPM_IMPORT_FILE = "TEST_DM_NUMERIC_ERR.txt"
$EPM_LOG_KICKOUTS = "$EPM_PATH_SCRIPTS\LOG_KICKOUTS.log"
_

Login to Cloud, Delete Old File, Upload New File

_
# Login
epmautomate login $EPM_USER $EPM_PASSFILE $EPM_URL $EPM_DOMAIN

# Delete File Before Upload & Ignore error in case file doesn't already exist
epmautomate deleteFile "`"inbox\$EPM_IMPORT_FILE`"" | Out-Null

# Upload New File Before Import
epmautomate uploadFile "`"$EPM_PATH_FILES_IN\$EPM_IMPORT_FILE`"" inbox
_

Shameless plug here.. In the Powershell Framework I have setup, this is all handled in the EPM_Execute-LoadRule function (including testing & parsing the DM Log). You can review the function on github here.

Getting the Log File Name & Download

OK now we have our file uploaded we need to :

  • Execute the DM Load
  • Check if there was an error
    • If there was, parse the DM Log File Name
    • Download DM Log
_
# Execute Load - runDataRule <DataRuleName> <StartPeriod> <EndPeriod> <ImportMode> <ExportMode> <FileName>
epmautomate runDataRule "LR_OP_TEST_NUM" "Oct-15" "Mar-16" "REPLACE" "STORE_DATA" "$EPM_IMPORT_FILE"

# Check for Error - $? will be false if the last executed process was unsuccessful. 
if (-not $?) {
    # We have an error, parse to get the DM Log File Name
    # This will look in the script execution folder for an EPMAutomate generated log file with "runDataRule" in the file name
    #   It will then grab the last file updated (in case there are multiple)
    $logError = Get-ChildItem "$EPM_PATH_SCRIPTS" -Filter runDataRule*.log | Sort-Object LastWriteTime | Select-Object -Last 1

    # Parse the logError for a regex matching : "logFileName":<AnyText>_<AnyNumbers>"
    $logDM = [regex]::Match((Get-Content $logError.FullName),"`"logFileName`":`"([a-zA-Z\/\.\:\-_0-9]+)`"").Groups[1].Value
    #logDM returns something along the lines of : outbox/logs/FINRPT_807.log

    #Download the log
    epmautomate downloadFile "$logDM"
}
_

Parse the Data Management Log

Now that we have the DM Log, we can parse it for kickouts.

Note: There are two types of kickouts, IMO. “Import” kickouts and “Export” kickouts. This post is focused on the Export kickouts (i.e. metadata members that do not exist), but could be modified to also check for Import kickouts (i.e. search for [NN] to grab Non-Numeric amounts that were brought in on accident, or [TC] if the amount cannot be converted to a number)

_
# File is downloaded to the directory in which the script is executed
# Parse the log name out, this will return something like : FINRPT_807.log
$logDMName = "$($logDM.Substring($logDM.LastIndexOf('/')+1))"
    
# Parse DM Log for Kickouts
# Loop over the lines of the log file
foreach ($line in Get-Content ("$EPM_PATH_SCRIPTS\$logDMName")){
    # Test if the line contains the Kickout Message ("Error: 3303")
    if($line.contains("Error: 3303")){
        $Kickout = $line.Split('|')
        #Write to Kickout Log
        "Member : `"$($Kickout[2].Trim())`" | Record : $($Kickout[3].Trim())" | Add-Content -Path $EPM_LOG_KICKOUTS 
    }
}
_

You can obviously do whatever you wish with the kickouts… setup a process to add a member to the outline and reload, attach the kickout log to an email etc.

The kickout log will look like : 

_
Member : "511011" | Record : "511011","No Cost Center","Adopted Budget","Funds","Operating Budget","FY16","Mar",600
Member : "No Cost CenterXX" | Record : "51101","No Cost CenterXX","Adopted Budget","Funds","Operating Budget","FY15","Oct",110
_

Note : this will only grab kickouts if it is via a “Numeric” data load. If you are loading anything as text values (i.e. smart list load etc), then you will need to search for 

_
if($line.contains("The member")){
_

Full Code Snippet below :

_
# Set Variables
$EPM_PATH_AUTO = Split-Path -Path $PSScriptRoot -Parent
$EPM_PATH_SCRIPTS = "$EPM_PATH_AUTO\01_SCRIPTS"
$EPM_PATH_FILES_IN = "$EPM_PATH_AUTO\03_FILES\INBOUND"
$EPM_USER = "YourUserName"
$EPM_PASSFILE = "YourPassFile"
$EPM_DOMAIN = "YourDomain"
$EPM_URL = "YourURL"
$EPM_IMPORT_FILE = "TEST_DM_NUMERIC_ERR.txt"
$EPM_LOG_KICKOUTS = "$EPM_PATH_SCRIPTS\LOG_KICKOUTS.log"

# Login
epmautomate login $EPM_USER $EPM_PASSFILE $EPM_URL $EPM_DOMAIN

# Delete File Before Upload & Ignore error in case file doesn't already exist
epmautomate deleteFile "`"inbox\$EPM_IMPORT_FILE`"" | Out-Null

# Upload New File Before Import
epmautomate uploadFile "`"$EPM_PATH_FILES_IN\$EPM_IMPORT_FILE`"" inbox

# Execute Load - runDataRule <DataRuleName> <StartPeriod> <EndPeriod> <ImportMode> <ExportMode> <FileName>
epmautomate runDataRule "LR_OP_TEST_NUM" "Oct-15" "Mar-16" "REPLACE" "STORE_DATA" "$EPM_IMPORT_FILE"

# Check for Error - $? will be false if the last executed process was unsuccessful. 
if (-not $?) {
    # We have an error, parse to get the DM Log File Name
    # This will look in the script execution folder for an EPMAutomate generated log file with "runDataRule" in the file name
    #   It will then grab the last file updated (in case there are multiple)
    $logError = Get-ChildItem "$EPM_PATH_SCRIPTS" -Filter runDataRule*.log | Sort-Object LastWriteTime | Select-Object -Last 1

    # Parse the logError for a regex matching : "logFileName":<AnyText>_<AnyNumbers>"
    $logDM = [regex]::Match((Get-Content $logError.FullName),"`"logFileName`":`"([a-zA-Z\/\.\:\-_0-9]+)`"").Groups[1].Value
    #logDM returns something along the lines of : outbox/logs/FINRPT_807.log

    # Download the log
    epmautomate downloadFile "$logDM"

    # File is downloaded to the directory in which the script is executed
    # Parse the log name out, this will return something like : FINRPT_807.log
    $logDMName = "$($logDM.Substring($logDM.LastIndexOf('/')+1))"
    
    # Parse DM Log for Kickouts
    # Loop over the lines of the log file
    foreach ($line in Get-Content ("$EPM_PATH_SCRIPTS\$logDMName")){
        # Test if the line contains the Kickout Message ("Error: 3303")
        if($line.contains("Error: 3303")){
            $Kickout = $line.Split('|')
            #Write to Kickout Log
            "Member : `"$($Kickout[2].Trim())`" | Record : $($Kickout[3].Trim())" | Add-Content -Path $EPM_LOG_KICKOUTS 
        }
    }
}

#Logout
epmautomate logout
_