Blind Date

We use FDQM to pull data from our various ERP systems into HFM.  Whenever possible we use import scripts to pull data directly from the ERP system or an intermediary SQL datamart rather than from flat files.  In addition to integration scripts that do a complete pull of the data from an external source, FDQM can use data pump scripts which provide a way to customize processing just one field when importing a file.

FDQM scripting uses vbScript so scripting features like file access are available as well as ADODB to make database connections and queries.  FDQM also provides its own API libraries to access native features such as the current POV.  These are available via the objects API (application programming interface), DW (Data Window for database access) and RES (FDQM resources).  These can all be seen in the Object Browser in FDM Workbench.

In our source systems the data is stored with fields that identify the period and year.  The RES.PstrPer object returns the period currently selected in FDQM. This is the value in the Text Description field in the Control Table for Periods

To access the period and year we take appropriate substrings of the RES.PstrPer value.

Dim StrPer 'Uses the Date POV to find the current period
Dim StrYr 'Uses the Date POV to find the current year
strPer = Left(RES.PstrPer,3) 'Retrieve the period from the POV
strYr  = Right(RES.PstrPer,4) 'Retrieve the year from the POV

If the periods are referenced by number instead of name there are a variety of ways to accomplish the translation.  I like finding the position of the period in the full list of periods then doing some math.

' The strAllPers contains the abbreviations of all the periods
'   Search for the position of the current period, subtract 1, divide by 4 and add 1
'   So for Jan we get ((1-1=0)/4=0)+1 = 1
'      for Feb we get ((5-1=4)/4=1)+1 = 2
'   etc.
'   for Dec we get ((45-1=44)/4=11)+1 = 12
'
Dim strPerNum ' Numeric value of period
Dim strAllPers ' All periods for use in index
strAllPers = "Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec"
strPerNum = ((InStr(strAllPers,strPer)-1)/4)+1

Problem

For one of our ERP systems we have to provide the dates for the start and end of the period to a SQL stored procedure.  The RES.PdtePerKey retrieves the end date for the period which is the Period field in the Periods Control Table as shown in the previous screen shot.  Then use the vbScript DateAdd function to calculate the start date of the period.

Dim dtePerKey ' Last date of the current POV period
dtePerKey = RES.PdtePerKey

Dim dteStart ' Start date of the current POV period
dteStart = DateAdd("d",1,DateAdd("m", -1, dtePerKey))

For Sep - 2016 we get 9/30/16 as dtePerKey.  We subtract one month to get the last day of August, then add one day to get the first day of September.  Note that the RES.PdtePerKey returns a date value, not a string, so use appropriate conversion functions as needed.

Our problem came when we converted from calendar year periods to 4-4-5 fiscal periods.  This means we can't guarantee that the period end date is the last day of the month or the period start date is the first of the month.  What's a mother to do?

First we configure the correct period end dates for our fiscal periods in the Periods control table.  Define the period end date and the prior period end date along with the target period and year.

In the Workbench Client there are a number of Accelerators.  Under the section Point-Of-View Information there is an accelerator named Get Period Prior Date Key.

Sweet.  In the FDQM Periods control table we define the correct end dates for the periods, use the accelerator to get the prior period end date, then add one day to get our period start date.  Double-clicking the accelerator adds the following code to your script.

'Declare Local Variables
Dim dtePriorPeriodKey

'Get prior period date key
dtePriorPeriodKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod, True).dteDateKey

Easy-peasy.  But when you run the script you get:

Error: An error occurred importing the file.  Detail: Object required: 'API'

Ain't that a kick in the knickers?  It turns out you can only use the calls to API objects in data pump scripts, not integration scripts.

Fortunately we have standard vbScript features available including ADODB which allows access to databases.  Any databases.  Even our FDQM database.  FDQM uses the tPOVPeriod table to store the period data.  We can use ADODB to make a connection to the FDQM database, query the tPOVPeriod table for the period in our POV, get the PriorPeriodKey value, then add one day.

The code I use which includes some error handling looks like this:

' Find the start date of the period.  We can't use the API in the import scripts
'   The tPOVPeriod table in the FDQM database has the period information which includes
'   the prior period end date.  So we make another ADODB connection to that database
'   and find the record for this period.
' Get the PriorPeriodKey field from the result set and add one day
'
Dim fdmSS    ' Connection to FDQM database
Dim fdmRs    ' Records returned from query
Set fdmSS = CreateObject("ADODB.Connection")
Set fdmRs = CreateObject("ADODB.Recordset")
fdmSS.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FDQM;Data Source=MySQLServer;"

' The query should return one record
'
Dim fdmQ
fdmQ = "SELECT * FROM tPOVPeriod where PeriodDesc = '" & RES.PstrPer & "'"
fdmRS.Open fdmQ, fdmSS

' If we get no records then something is broken
'
If fdmRS.bof And fdmRS.eof Then
RES.PstrActionValue = "No Start date found"
Import_Script=False
Exit Function
Else
Dim dtePriPer  ' Prior period end date as datetime value
dtePriPer = fdmRS.Fields("PriorPeriodKey").Value
dteStart = DateAdd("d",1,dtePriPer)   ' Add 1 day to get first day of current period
End If

While I used this technique to get to the prior period date we can get any fields we want from any FDQM tables.

• tPOVPeriod has the period control table data
• tPOVCategory has the Categories control table data
• tCtrlCurrency has the Currency control table data
• tPOVPartition has the partition/location data
• tDataMap has the mapping tables for all partitions and dimensions
• tLogActivity has the process and error logging data
• tSecUser lists all provisioned users and their security level
• tSecUserPartition lists all users, their provisioned partitions, and default partition

I can't think of a good reason why you would want to get to some of this data in an import script, but it is available if you need it.  

Library Access

Once upon a time several people were working on several different applications in our development environment.  We were adjusting shared dimensions, building and deleting HFM applications, and generally kicking sand around the playground.

http://aminoapps.com/page/cartoon/9847425/forget-the-internet-when-you-have-a-library-card-arthur-meme-contest

Then one day one of the developers couldn't get into the Application Library.  They got an error dialog with the title Requested Service not found and the helpful text:

----
Requested Service not found
----
Code: com.hyperion.awb.web.common.DimensionServiceException

Description: An error occurred processing the result from the server.

Actor: none

We also noticed we couldn't get to the Dimension Library.  And our scheduled taskflows had stopped working.  Since we could still login and open our HFM and Essbase applications we could rule out issues with foundation services, HFM, and Essbase.  Everything pointed to a problem with EPMA.

Analysis

While there were errors in the EPMA logs there wasn't an obvious smoking gun.  We engaged both Oracle support and our integration partner.  The odd thing about Oracle support support is that there is no option for the EPMA product.  All of the disciplines are cross-trained on EPMA but you have to really hunt around for someone who is an EPMA expert.

Between the three of us we noticed this set of errors in the DimensionServer.log:

[2017-08-26T23:57:18.764-04:00] [EPMADIM] [NOTIFICATION:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.LibraryManager] [tid: EPMA Server Startup] [ecid: disabled,0] Starting 11.1.2.2.00 FixInvalidDynamicPropertyReferences task
[2017-08-26T23:57:19.263-04:00] [EPMADIM] [INTERNAL_ERROR:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.LibraryManager] [tid: EPMA Server Startup] [ecid: disabled,0] Failed 11.1.2.2.00 FixInvalidDynamicPropertyReferences task    at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.FixInvalidDynamicPropReferencesForDimension(Int32 libraryID, Int32 applicationID, Int32 dimensionID, String dynamicProps, Int32 propertiesDimID, Int32 dynamicPropsPropID)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.<>c__DisplayClass64.<FixInvalidDynamicPropReferencesForLib>b__63(DALDatasetCommand command)
   at Hyperion.DataAccessLayerCore.DataAccessLayer.ExecLoadSQL(String SQL, Action`1 onSetParams, Action`1 onRead, DatabaseContext context)
   at Hyperion.DataAccessLayerCore.DataAccessLayer.ExecLoadSQL(String SQL, Action`1 onSetParams, Action`1 onRead)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.FixInvalidDynamicPropReferencesForLib(Int32 libraryID)
   at Hyperion.DimensionServer.DAO.LibraryManagerDAO.FixInvalidDynamicPropReferences()
   at Hyperion.DimensionServer.LibraryManager.FixInvalidDynamicPropertyReferences()
[2017-08-26T23:57:19.294-04:00] [EPMADIM] [ERROR:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.Global] [tid: EPMA Server Startup] [ecid: disabled,0] An error occurred during initialization of the Dimension Server Engine:  startIndex cannot be larger than length of string.
Parameter name: startIndex.    at Hyperion.DimensionServer.LibraryManager.FixInvalidDynamicPropertyReferences()
   at Hyperion.DimensionServer.Global.Initialize(ISessionManager sessionMgr, Guid systemSessionID, String sqlConnectionString)
[2017-08-26T23:57:19.294-04:00] [EPMADIM] [NOTIFICATION:32] [EPMADIM-1] [EPMADIM.Hyperion.DimensionServer.Utility.ChannelUtility] [tid: EPMA Server Startup] [ecid: disabled,0] Listening using IPv4

(I'm copying everything in the block above so it gets found by search engines.  But I highlighted the failed task that clued us into the solution.)  These errors were thrown each time EPMA services restarted.  And the errors started around the time the taskflows started failing.  Earlier in the log we just see the FixInvalidDynamicPropertyReferences task start and finish.  This looked like the culprit.

Solution

If you bingoogle “DimensionServer FixInvalidDynamicPropertyReferences” you find a couple articles that show the error above:

http://hyperion-epm-bi.blogspot.com/2016/01/epm-patch-11122500-breaks-epma.html
http://brovanture.com/news/blog-epm-patch-11-1-2-2-500-breaks-epma

Both articles note the cause as a specific Oracle patch.  It is an older patch that was superseded in our environments.  Since there were no recent patches applied we initially glossed over this.

But the fix is to run a SQL UPDATE statement to replace blank strings with NULL values:

UPDATE DS_Property_Dimension
   SET c_property_value = null
   FROM DS_Property_Dimension pd
      JOIN DS_Library lib
         ON lib.i_library_id = pd.i_library_id
      JOIN DS_Member prop
         ON prop.i_library_id = pd.i_library_id
            AND prop.i_dimension_id = pd.i_prop_def_dimension_id
            AND prop.i_member_id = pd.i_prop_def_member_id
      JOIN DS_Dimension d
         ON d.i_library_id = pd.i_library_id
            AND d.i_dimension_id = pd.i_dimension_id
  WHERE
      prop.c_member_name = 'DynamicProperties' AND
      pd.c_property_value IS NOT NULL AND pd.c_property_value = '';

Since we had exhausted all other avenues we replaced the UPDATE statement with a SELECT statement:

SELECT c_property_value
   FROM DS_Property_Dimension pd
      JOIN DS_Library lib
         ON lib.i_library_id = pd.i_library_id
      JOIN DS_Member prop
         ON prop.i_library_id = pd.i_library_id
            AND prop.i_dimension_id = pd.i_prop_def_dimension_id
            AND prop.i_member_id = pd.i_prop_def_member_id
      JOIN DS_Dimension d
         ON d.i_library_id = pd.i_library_id
            AND d.i_dimension_id = pd.i_dimension_id
  WHERE
      prop.c_member_name = 'DynamicProperties' AND
      pd.c_property_value IS NOT NULL AND pd.c_property_value = '';

The query returned a handful of records where the c_property_value was blank instead of NULL.  We ran the UPDATE query and after restarting the services the libraries, taskflows, and other EPMA features were available.

Conclusion

Prior to this we had issues with dimensions in one of the applications under development.  Part of fixing that required deleting and importing dimensions and adjusting attributes.  Our suspicion is that somewhere along the line blanks got imported instead of NULLs.

The first lesson is that EPMA issues can be tricky to resolve.  It seems odd that Oracle wouldn't have support staff focused on EPMA since that binds all of the other EPM featuers together along with foundation services.

The other lesson is that if you have an issue and find something that looks like it might help don't automatically discard it just because your situation is different.  Many times there is a way to check if the solution might apply to you.

HFM Data Audit (part 2)

In the previous post I detailed the mechanics of enabling and exporting data audits in HFM.  But what can you do with data once it's exported?

One option is to keep those .csv files for an arbitrary period of time.  If an issue arises you could search all those files for data of interest.  But you need some utility to search many files for text of interest and you could run into space issues which would require deleting deleting old data.

I prefer to import the data into a SQL database.  The data can be kept as long as needed and is easier to search.

With SQL server we can use the bcp utility to import the .csv files into a database table.  The table should match the fields we get in the .csv file.  The following is a sample create table statement to match the .csv file format.

CREATE TABLE [dbo].[AuditRecords](
[UserName]   [nvarchar](32) NULL,
[Activity]   [nvarchar](16) NULL,
[Timestamp]  [datetime] NULL,
[ServerName] [nvarchar](8) NULL,
[Scenario]   [nvarchar](16) NULL,
[Year]       [smallint] NULL,
[Period]     [nvarchar](3)  NULL,
[Entity]     [nvarchar](32) NULL,
[Value]      [nvarchar](16) NULL,
[Account]    [nvarchar](40) NULL,
[ICP]        [nvarchar](24) NULL,
[Custom1]    [nvarchar](32) NULL,
[Custom2]    [nvarchar](24) NULL,
[Custom3]    [nvarchar](8)  NULL,
[Custom4]    [nvarchar](32) NULL,
[DataValue]  [nvarchar](24) NULL
)

Now that we have a table to hold our data we need to import it.  The bcp utility uses an import format file to specify how to parse fields in the file being imported.  The specifications for the format file are kind of klunky which you can read about here.  But this is the format file I use:

10.0
16
1   SQLCHAR   0   64      ";"      1     UserName           SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0   32      ";"      2     Activity           SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0   24      ";"      3     Timestamp          ""
4   SQLCHAR   0   16      ";"      4     ServerName         SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR   0   32      ";"      5     Scenario           SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR   0   7       ";"      6     Year               ""
7   SQLCHAR   0   6       ";"      7     Period             SQL_Latin1_General_CP1_CI_AS
8   SQLCHAR   0   64      ";"      9     Entity             SQL_Latin1_General_CP1_CI_AS
9   SQLCHAR   0   32      ";"      10    Value              SQL_Latin1_General_CP1_CI_AS
10  SQLCHAR   0   80      ";"      11    Account            SQL_Latin1_General_CP1_CI_AS
11  SQLCHAR   0   48      ";"      12    ICP                SQL_Latin1_General_CP1_CI_AS
12  SQLCHAR   0   64      ";"      13    Custom1            SQL_Latin1_General_CP1_CI_AS
13  SQLCHAR   0   48      ";"      14    Custom2            SQL_Latin1_General_CP1_CI_AS
14  SQLCHAR   0   16      ";"      15    Custom3            SQL_Latin1_General_CP1_CI_AS
15  SQLCHAR   0   64      ";"      16    Custom4            SQL_Latin1_General_CP1_CI_AS
16  SQLCHAR   0   48      "\r\n"   17    DataValue          SQL_Latin1_General_CP1_CI_AS

Now we have a place to store our audit data and a way to import it.  The last step is to automate the whole process.  I run a PowerShell script daily that uses the AuditExport_cmd.exe to export yesterday's audit data to a .csv, and then import it into the database.  I also retain the .csv files in case something goes haywire with the bcp import.  I organize these retention folders by month and year in a parent folder named D:\LogArchives and periodically purge old retention folders.

The files created by the AuditExport utility have a maximum size of 17 Mb which may create more than one file per export.  The script needs the ability to import multiple files in a single batch.  The full script will export the data to a holding area, create a folder for retention if needed, then loop through the files in the holding area, import each with bcp into the database, then move file to the retaining folder.

One issue I ran into is that the data exported is in unicode format but bcp requires ASCII.  Also bcp runs on the SQL server.  I resolved this by importing the .csv file using Get-Content then exporting it using Out-File with the ASCII -encoding switch.  Since I have to run bcp on the SQL server the Out-File target is a share on the SQL server.  That share also has the bcp format file and a batch file to run the import.  This resolves the file format issue and keeps everything tidy on the SQL server.

The batch file on the SQL server is:

bcp DataAuditArchive.dbo.AuditRecords in T:\DataAudit\TempDataAudit.csv -U DataAudit_update -P Password123 -F 2 -f T:\DataAudit\DataAudit.fmt -e T:\DataAudit\DataAudit.err

Here my database is named DataAuditArchive and the table is AuditRecords.  The batch file, bcp format file, error file, and temporary copy of the .csv being imported are all in the T:\DataAudit folder.  This one line batch file gets called from a PowerShell script that runs on the HFM server.

I also want to save all of the output in case something goes wrong.  I do this by piping all of the results to a log file that gets saved in the monthly archive folder.  This gives an audit trail for the script file processing to allow troubleshooting.  If I find that one or more of the .csv files did not import I can import those manually from the retaining folder to ensure a complete audit record.

In the PowerShell script below the $Folder variable holds the name of the current months retaining folder which will house all of the .csv files for the month as well as the log file.  The $Working folder houses the initial export from the AuditExport utility.  The $BCPWorking folder is the share on the SQL Server that has the batch file, bcp import format, and the ASCII version of the .csv file currently being processed.

# Powershell script to archive and truncate data audit logs ending yesterday.
#

$Utility = 'D:\Oracle\Middleware\EPMSystem11R1\products\FinancialManagement\Utilities\HFMAuditExtractCmdLine_x64.exe'
$AppName = 'COMMA' # Name of the HFM Application
$Parms = ' -u D:\HFM.udl -a '+$AppName+' '  # UDL file and application name
$Data = ' -r '                              # flag to extract and truncate the data audit logs
#$Data = ' -x '                             # flag to extract and truncate the data audit logs
$Logs = 'D:\LogArchives\'                   # Parent folder for the log files
$TempFolder = 'Daily' # Store the dump from the HFMAuditExtract utility here
$BcpWorking = '\\SqlSvr\DataAudit'          # local folder on SQL server for BCP to use for import
$Start = ' -s 1/1/2013'                     # Arbitrary start date to make sure we get all previous data

$D = get-date

# New folder for each month that is named with the year and month
#
$Folder = $D.ToString('yyyy')+"_"+$D.ToString('MM')+$D.ToString('MMM')

# Capture everything through yesterday
#
$D = $D.AddDays(-1)
$End = ' -e '+$D.ToString('MM')+'/'+$D.ToString('dd')+'/'+$D.ToString('yyyy')

$Target = $Logs+$Folder                              # Destination for the logs
if (-not (test-path $Target)) { MkDir $Target }      # Create the destination folder if needed
$Working = $Logs+$TempFolder
$Output = ' -d '+$Working                            # Add the -d so this can be used as a parameter for the utility.

# Extract and truncate the task log
#   NOTE: the truncate task for data also does an extract
#
$CMD=$Utility+$Output+$Start+$End+$Parms+$Data       # Create the string to execute.  Path of utility and all parameters
echo $CMD                                            # Echo so we know what we did
invoke-expression $CMD                               # Execute the AuditExtract command line

# Process the file(s) that were created in the TempFolder
#
$TempFile = $BCPWorking + "\TempDataAudit_$AppName.csv"
$ScriptLog = $Target + '\BcpImport.log'
$BatchName = "T:\DataAudit\Import$AppName.cmd"

$AuditLogs = get-childitem $Working -filter "Data*$AppName*.csv"
foreach ($Log in $AuditLogs) {
    echo $Log.FullName

# Export the file be ANSI encoding because bcp has trouble with format files and unicode data files
#
    get-content $Log.FullName | out-file $TempFile -encoding ASCII

#   Run bcp to import the data
#
  $BcpResults = invoke-command -computername SqlSrv -scriptblock { Param($Bat) & cmd.exe /C $Bat } -ArgumentList $BatchName

# Move the file dated archive folder
#
  $MoveResults = Move-Item $Log.FullName $Target -passthru

# Delete the ASCII file we just imported
#
  if (test-path $TempFile) { del $TempFile }

# And save the results to the script log
#
  (' * * * ') | out-file $ScriptLog -append
  $Log.FullName | out-file $ScriptLog -append
  (' ') | out-file $ScriptLog -append
  $BcpResults | out-file $ScriptLog -append
  (' ') | out-file $ScriptLog -append
  $MoveResults | out-file $ScriptLog -append
}

This script is scheduled to run daily which keeps the DataAudit table in the HFM database lean and efficient.  The audit table becomes our source of truth unless the event happened today in which case we use the Data Audit tab in Workspace.

Truncating the Job Console logs

In the last post we talked about how to automate deleting the task flow logs.  Another place where log entries accumulate is the Library Job Console.

The Job Console log is more manageable than the Task Flow log.  In the Job Console tab you can display up to 200 entries per page and you can click the first record, scroll to the bottom of the list, shift+click to select all the entries, then right-click and delete.

This is better but can still be a bunch of work if you haven't cleared the logs in a while.  And wouldn't you rather have an automated solution that can be scheduled so you can work on other things?  Of course you would.

Analysis

The discovery was pretty easy.  In the EPMA database the table name [JM_Job] is a dead give away.  The contents show the same information we see in the Job Console tab.

There are three tables with the JM* prefix which hold the job log data: [JM_Job], [JM_Batch], and [JM_Attachment].  They are connected by the i_job_id key which has sequential values and is the primary key in the [JM_Job] table.  The [JM_Attachment] primary key combines the i_job_id and the i_attachment_id, where i_attachment_id is also a sequential value.  The primary key for the [JM_Batch] database combines the foreign keys i_job_id and i_attachment_id with the field i_batch_id.

The [JM_Job] database has the start and completion times for the various jobs.  So we can reference that to find our cut off date, get the i_job_id for the previous job, and delete from the three tables anything that is less than the cut off i_job_id.

Solution

The wrinkle is that because of foreign key constraints of i_job_id the [JM_Job] database has to be the last one truncated.  And because the i_attachment_id is a foreign key in [JM_Batch] that has to be the first table truncated.

Like with the task flow truncation we can either calculate or specify the cut off date.

DECLARE @CutOff datetime;
SET     @CutOff = DATEADD(M,-4,GetDate());
--DECLARE @CutOff char(10);
--SET     @CutOff = '2016-01-01'

There are several ways to tackle getting the i_job_id for our cut off date.  One approach is to assign the i_job_id to a variable and use that in the DELETE statement:

DECLARE @ID Int;
SET @ID = (SELECT Top 1 i_job_id FROM JM_Job WHERE d_started < @CutOff ORDER by i_job_id DESC)
DELETE FROM JM_Batch WHERE i_job_id <= @ID

But these are fairly small tables so it is easy to use a subselect in the DELETE:

DELETE from JM_Batch
where i_job_id in (
SELECT i_job_id
FROM JM_Job
WHERE d_started < @CutOff)

The last piece is to arrange the DELETEs in the correct order.  The complete TSQL script is:

use EPMA
go

-- Anything prior to cutoff date will be deleted
--   The DateAdd function can change depending on need
--
DECLARE @CutOff datetime;
SET     @CutOff = DATEADD(M,-4,GetDate());
--DECLARE @CutOff char(10);
--SET     @CutOff = '2015-01-01'
PRINT 'Cut off date = '+convert(VarChar(32),@CutOff)

-- Because of the key constraints the deletes need to happen in this order
--   JM_Batch has to be first because it has keys from JM_Attachment and JM_Job
--   JM_Attachment has key for JM_Job so deletes have to happen before
--     JM_Job but after JM_Batch
--   JM_Job has to be last because that has the date field we query against
--
-- For the first 2 deletes we get a list of i_job_id from JM_Job that are older
--   than the cutoff date, and compare the i_job_id in the table to that list
--
DELETE from JM_Batch
where i_job_id in (
SELECT i_job_id
FROM JM_Job
WHERE d_started < @CutOff)

DELETE from JM_Attachment
where i_job_id in (
SELECT i_job_id
FROM JM_Job
WHERE d_started < @CutOff)

DELETE from JM_Job
where d_started < @CutOff

This gives us a tidy bit of code that we can run on a regular basis to truncate the Library Job Console log to purge stale information.

This blog post is a bit short, so here's a picture of a kitty.

Automate Deleting Taskflow Log

We use HFM taskflows.  A lot.  When a taskflow runs it generates log information so you can check on the status of the execution and examine any errors to effect remediation.

But like the EPMA logs, these log entries stay around forever until you delete them.  On the Task Flow Status page you see each task flow status on a line with a check box.  To delete a log entry you click the check box to select the entry and click the Delete button.  Child's play.

Since these are check boxes you would think, as a standard GUI practice, you could check multiple boxes and click the delete button to delete multiple log records.  You would be wrong.  You have to select the log records one at a time to delete them.  Isn't that convenient?

So if you don't constantly do maintenance you wind up with a bunch of useless information in your log viewer.  Searches take longer and if you want to delete entries it takes a lot of time to complete.  What's a mother to do?

Analysis

We use the taskflows to monkey with our HFM applications.  Taskflows are found in the Consolidation Administration tab so you might think the information for taskflows is in the HFM table.  But we can also do EPMA related things like run data synchronizations and redploy applications.  So where do we find the tables that manage the taskflows and logs?  In the Shared Services database, of course.

It took me some digging to figure that out, but the first clue was finding a table named [ces_wf_instances].  SELECTing from that tables shows the field process_name with the names of my taskflows.  Other useful fields include starttime, a datetime field that we will use to find all the old log entries, and workflow_id and taskid which are used as references to other tables.  workflow_id is the taskid with the string "wf-" as a prefix.  workflow_id is also part of the primary key so it is unique across the tables.



The nice thing is that taskid values are always in ascending order.  I don't have any way to prove this but I suspect that taskid is based at least in part the datetime of the activity.  This means that when we process the other tables we can define a cutoff date, find the first taskid in the [ces_wf_instances] for that date, then delete anything that has a taskid less than that.  The not so nice thing is that in all the other tables the taskid field is named task_id.



What other tables will we process?  There are 13 tables with the ces_ prefix but some of them are empty and others are unrelated to logging.  The [ces_tasks] table lists tasks within the taskflow, [ces_messages] contains more detailed messages about the task.  Both tables contain a field named objectvalue which is an image blob.  This is a large hex string that gets processed to show its part of the workflow status.  Both of these tables are referenced by task_id which, like in [ces_wf_instances], is sequential, so we can still use the strategy of deleting records less than the target task_id.



There are also the [ces_participant] and [ces_participant_events] tables which have details on the steps in the taskflow.  Like the [ces_tasks] and [ces_messages] tables these tables have the objectvalue image blob.  The [ces_participant] is referenced by workflow_id which matches the field in the [ces_wf_instances] table.  There is also a field named participant_id which contains the workflow_id, the user, and the name of the step in the taskflow.  This participant_id is a foreign key in the [ces_participant_events] log.  Because there is a foreign key constraint between the tables when we delete records from the [ces_participant] table the records with the matching participant_id in the [ces_participant_events] table also get deleted.

Solution

So now that we know all the players, let's draw up a play to get us to the end zone.  We will build a SQL script that defines our cutoff date, find the last taskid associated with that date, then delete records from the other tables where the taskid is less than the one for the target date.

There are different tactics to define the cut off date.  If we are running this process manually maybe we just want to define the date with a static string:

DECLARE @PriorDate DateTime;
SET     @PriorDate = '2015-01-01';

But if we want to create a scheduled process we can calculate the desired cut off date.  For example, the following code will use the DATEADD() function to set the cut off date as 3 months prior to the current date:

DECLARE @PriorDate DateTime;
SET     @PriorDate = DATEADD(M,-3,GetDate());

Now that we have the date, we need to find the target taskid.  The taskid is sorted sequentially so we take the TOP 1 taskid where the starttime is greater than or equal the cut off date.  Note that we use greater than or equal to (>=) rather than just equal to (=) in case there was no taskflow execution on the cut off date.  This gives us a taskid where anything less will be prior to our cutoff date.  Also note that we make the @LastID variable nvarchar(100) which matches the data type used in the [ces_wf_instances] table.

DECLARE @LastID nvarchar(100);
SET     @LastID = (
  SELECT TOP 1 taskid
FROM [ces_wf_instances]
WHERE starttime >= @PriorDate
)

For the [ces_messages] and [ces_tasks] tables we can do a simple delete where task_id is less than our target taskid.  But for the [ces_participant] table we need to get the workflow_id for all of the records prior to our cut off date.  One way to do this is to use the IN clause and build a list of workflow_id from the [ces_wf_instances] table where the starttime is less than the cut off date.

DELETE FROM [ces_participant]
WHERE workflow_id IN (
select workflow_id
from [ces_wf_instances]
where starttime < @PriorDate)

Remember that because of the foreign key constraint deleting records from [ces_participant] will also delete the associated records from the [ces_participant_events] table.  Also note that because we need the [ces_wf_instances] table to do this delete we have to make that the last table from which we delete records.

The entire SQL script is:

-- Cut off date
DECLARE @PriorDate DateTime;
SET     @PriorDate = DATEADD(M,-3,GetDate());
--SET     @PriorDate = '2015-01-01'
PRINT 'Cut off date = '+convert(VarChar(32),@PriorDate)

-- Get the first datskid of the cutoff date
DECLARE @LastID nvarchar(100);
SET     @LastID = (
select top 1 taskid
from [ces_wf_instances]
where starttime >= @PriorDate
  )
PRINT 'Last TaskID = '+@LastID

-- These tables are simple deletes
DELETE from [ces_messages] where task_id < @LastID
DELETE from [ces_tasks]    where task_id < @LastID

-- [ces_participant] table has a foreign key constraint against [ces_participant_event]
-- table.  Deleting records from [ces_participant] deletes records with the same
-- particpant_id from the [ces_participant_event].  So to process these two tables we
-- get a list of workflow_ids where starttime is less than @PriorDate
DELETE FROM [ces_participant]
WHERE workflow_id IN (
select workflow_id
from [ces_wf_instances]
where starttime < @PriorDate
   )

-- We could also have deleted based on task_id
DELETE FROM [ces_wf_instances]
WHERE starttime < @PriorDate

This gives us a fairly simple SQL script we can schedule as a SQL agent job or any other means appropriate for the environment.  No manual steps, no muss, no fuss, and we keep the taskflow log and the underlying tables lean and manageable.