Task Audit Extract alternative

In some of my earlier articles I have talked about managing the Task Audit information in HFM. The main point of these articles is that the Audit Extract Utility (AEU) no longer meets the requirements for a robust and complete task audit. The AEU is still fine for the extract of data audit information just not the task audit. To recap, the two main problems with the AEU is that the detailed logs of application changes (i.e. metadata, member list and rules loads) are not extracted and the last logon information is deleted. In this article I will go through an alternative to the AEU for managing the Task Audit data. It is customisable so that any application changes and logon information are preserved and you can ignore some events such as Logoff.

Process Overview

The key to this new Task Audit extract process is a SQL statement which selects and formats the data. This SQL statement is then automated by calling from a batch file or from a trigger/job in the relational database. The automation process collects the output from the SQL statement and writes it to a file.

Note: I have only tested this on SQL Server. For Oracle databases, the REPLACE function should work. You will need to use something like the to_date function instead of MONTH in the WHERE clause.

SQL Statement

This is a basic SQL statement to extract the data. If you look in the log file that the AEU creates, you will see something similar to this:

SELECT U.sUserName, a.ActivityCode, a.ServerName, a.AppName,
a.StartTime, a.EndTime, a.strModuleName
FROM @APPL@_TASK_AUDIT AS a INNER JOIN
HSV_ACTIVITY_USERS AS U WITH (NOLOCK) ON a.ActivityUserID = U.lUserID

Note: you need to change @APPL@ to the name of your HFM application.

This gives you data something like this:

While this has extracted the data, we need to make a few changes to make it more readable:

1. change the ActivityCode to the activity description (e.g. Logon, Logoff)
2. format the StartTime and/or EndTime to an actual date
3. change the strModuleName to the module description.

There is a link at the bottom of the page to download the SQL statement.

StartTime/EndTime

The format of StartTime and EndTime is standard SQL so it depends on your database. For SQL Server you can use the DATEADD function:

DATEADD(day, -2, a.StartTime)

For some reason the display of dates in SQL Server needs to be adjusted by 2 days. This might not be the case for your server. For Oracle you use to_date. This will convert the numbers 42907.877962963 etc to something like 2017-07-10 15:10:43.000.

You can now play around with the display of the StartTime and EndTime to get the format you want. I prefer to split out the date and time. This will display the start time as YYYYMMDD (zero-padded):

CONVERT(VARCHAR(4), DATEPART(yyyy, DATEADD(dd, -2, a.StartTime)))
+ RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(month, DATEADD(dd, -2, a.StartTime))), 2)
+ RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(dd, DATEADD(dd, -2, a.StartTime))), 2)

And this displays the time element of the start time as HHMM:

RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(hour, DATEADD(dd, -2, a.StartTime))), 2)
+ RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(mi, DATEADD(dd, -2, a.StartTime))), 2)

ActivityCode

There is no table in the HFM database that contains the descriptions for the ActivityCode column so we will have to use a CASE function. You could create a table in your database to hold the activity codes and their descriptions if you preferred. So the CASE syntax is like this:

CASE a.ActivityCode
WHEN  0 THEN 'Idle'
WHEN  1 THEN 'Rules Load'
WHEN  2 THEN 'Rules Scan'
WHEN  3 THEN 'Rules Extract'
……
END AS Activity,

ModuleName

The module name is similar to ActivityCode in that there is no table containing the descriptions but has an extra complication. All of the records have #@RID@# at the beginning. We could keep that piece of text in the CASE statement but I prefer to remove it:

CASE REPLACE(a.strModuleName, '#@RID@#', '')
WHEN  0 THEN 'ADMINISTRATION'
WHEN  1 THEN 'CELL_HISTORY'
WHEN  2 THEN 'CLOSE_APPLICATION'
WHEN  3 THEN 'CONSOLIDATION'
……
ELSE a.strModuleName
END AS Module

WHERE clause

We don’t want to extract all the Task Audit data every month so we need a WHERE clause to restrict what data is extracted. This piece of code will only include data that has a start time within the month four calendar months ago. So if we are in November now, it will only include records for July.

WHERE DATEDIFF(MONTH, DATEADD(day, -2, a.StartTime), GETDATE()) = 4

You could also include exclusions on the ActivityCode (e.g. Logoff is 31) if you wanted.

Automation

Save your SQL statement to a file and call it using the sqlcmd statement e.g.:

sqlcmd -S %Server% -E -d %DB% -i %Script% -o "%Output%" -s "|" –W

The items between the % symbols are batch variables e.g.

set Script=getActivity.sql

That will extract the Task Audit data to a file. You will then probably add a statement to delete the records from Task Audit for the same month. And for the delete statement you will add some exceptions such as

WHERE ActivityCode NOT IN (1, 21, 23, 26, 29, 75)

This will keep all the application changes and logon information.

You can download the full SQL statement here. It has the extension .key and is a text file:Download GetActivity SQL statement

Conclusion

With this SQL statement, you can have an automated process to extract and manage the Task Audit data. The data that is extracted and deleted is customisable. This means that you can keep your Task Audit data to a minimum while keeping a full year’s data for the important audit records. Information on all application changes and are kept as well as the logon information for all users.

Managing maps in FDMEE (or how brilliant are Multi-Dim maps)

In this blog I want to look at how to manage FDMEE maps. By that I mean a reliable and repeatable process for importing and exporting that is simple for the users and covers all the different maps. The process must take an export from FDMEE and create an import file in the correct format. Currently in FDMEE you can’t do this – there are problems with scripts, Data Load Rules, what is exported and what can be imported.

From an FDM point of view, it is very common to manage maps in Excel. It gives you a backup of the maps outside FDM and allows the maps to be reviewed, signed off and audited with a minimum of fuss and training. Using Excel for FDMEE maps is not the same. In FDM there were scripts to extract all the maps by location which could be scheduled to run once a month. This gives you a backup and an audit of the maps. You can do the same in FDMEE but it is a little more difficult because you have to write the scripts from scratch. In FDM you can import the maps through the Excel interface. You can do the same in FDMEE but it does not import all the map information and some of the data is not supported.

Scripts
The FDMEE Admin guide refers to scripts in maps as Conditional Mapping. These are the scripts that you write in Jython or SQL not the Multi-Dimensional maps. Exporting and importing these Jython/SQL scripts is problematic. In FDM, scripts in maps can be exported and imported through the Excel interface. That’s FDM, what about FDMEE? The Admin Guide says: “FDMEE does not support #SQL and #SCRIPT for Excel mappings”. OK so that just about kills that one dead then. The FDMEE Excel template is set up to include the VBScript column in the upload named range (and yes it is called VBScript even though the language is Jython/SQL). I have used the template for importing Conditional maps and it works. The problem is that the Admin guide says that it’s not supported. So is it that there is a problem with importing these scripts that I haven’t come across yet or have I strayed into a “consultants only” area? I don’t know but I am certain of one thing: if the manual says don’t do it then you need to have a very good reason for ignoring that.

Here is an overview of the functionality for exporting and importing:

Export – Excel

• Scripts are not exported from Data Load Mapping. The #SCRIPT/#SQL header record is exported but the actual script that is executed is not.
• In 11.1.2.4 all map information can be exported through the Excel Interface. In 11.1.2.3 you have to write a custom script to export all the map information or use LCM.
• Current dimension only.
• I have found that the Export to Excel sometimes produces a file with just the column headings.

Export – Text (Current Dimension and All Dimensions)

• Scripts are not exported from Data Load Mapping. The #SCRIPT/#SQL header record is exported but the actual script that is executed is not.
• Current dimension or All dimensions can be exported.

Import – Excel

• Importing scripts in maps (#SCRIPT/#SQL) through the Excel interface is not supported. Possible (?) just not supported.
• You can use LCM but that contains maps for the whole application and not just the maps for a single location.
• You cannot use the Excel Interface in 11.1.2.4 to import maps – it does not increment the DataKey column correctly and it will corrupt the maps.
• You can write a custom script that reads a text file and imports these Conditional maps.

Import – Text (Current Dimension and All Dimensions)

• Importing scripts in maps (#SCRIPT/#SQL) through text files is not possible.

In summary, exporting and importing scripts in maps is difficult. I keep scripts in maps down to absolute minimum. I always have an external backup of the scripts because I use Notepad++ not the internal editor.

Data Load Rules
A map can be restricted to a Data Load Rule and this information can be exported and imported. The problem here is in the Excel interface because it uses the Data Rule Name id not the name. The default Excel template that can be downloaded from FDMEE does not include the Data Rule Name column in the named range. You can change the named range to include the column but you must make sure you use the id not the name.

Suggested strategy
Above all, we want a reliable method of exporting and importing all the relevant map information. That means it must be text format – the Excel route is one dimension at a time for export, does not always work and the Data Load Rule is tricky. There are two problems we need to overcome: Scripts and Data Load Rules. If we use text format the Data Load Rule problem is fixed; you can just add the name of the Data Load Rule. That leaves the maps with scripts and a simple way of getting around this problem is not to use them. It’s not enough to say ‘don’t use them’ because there are times when you don’t have a choice. So we will look at a great alternative to scripts in maps: Multi-Dimensional maps.

Multi-Dimensional maps
Scripts in maps are used to do what is not possible in standard maps such as check the target values in other dimensions or map based on the data amount. We know we can use Multi-Dimensional maps to reference the source values in other dimensions. What the manual does not tell you is that with Multi-Dimensional maps you can reference target dimensions, the amount, specify ‘not’ conditions etc. The workspace won’t let you do any of this but the text file will. NOTE: this has been tested on a SQL Server database; things might be different on Oracle.

Here is an example of a multi-dimensional map in a text file:
UD2,#MULTIDIM UD1=[OtherInterest],INTOTH,mINTOTH,,

This is saying if the source Custom1 member is OtherInterest, then map Custom2 to INTOTH. The rule name is mINTOTH. You can reference the Target dimension by adding X to the end of the dimension name. So if I change UD1 to UD1X it will look at the Target Custom1 member:
UD2,#MULTIDIM UD1X=[OTHINT],INTOTH,mINTOTH,,

That means if the Target Custom1 member is OTHINT, then map to INTOTH. You can’t edit these maps in the workspace – they have to be managed in the text files.

You can also reference the data amount by using the AMOUNT or AMOUNTX column so, as an example, map to one account if it’s a credit and another if it’s a debit. The Multi-Dim map only works if there is an equals sign (=) so if you want to map based on amounts you must use <= or >=:

UD2,#MULTIDIM AMOUNT >=[0],POSITIVE,mTEST_P,,
UD2,#MULTIDIM AMOUNT <=[0],NEGATIVE,mTEST_N,,

There have been many occasions when it would have saved a lot of time and mapping if I could just have said if the source does NOT begin with 8 (for example) then map here. Well you can do that too using Multi-Dims. Again the key here is that the Multi-Dim part in the text file must contain an equals sign so we use != instead of <>:
UD2,#MULTIDIM Account !=[1100],OHDETOTH,MOHDIRECT,,

So if the Account is not equal to 1100, then map the Custom2 member to OHDETOTH. And this is how it looks in the workspace:

On the downside though there is a laughable bug in Multi-Dim maps. I say laughable because when I first saw it I thought it was funny. It’s not so funny when you try and find a way around it however. I have only tested this on 11.1.2.3 so it might be fixed in 11.1.2.4. Anyway, the bug is if the source value contains OR in capitals, then FDMEE thinks it is a keyword. So CORE becomes C”OR”E:


The workaround is to use lower case but if your database is Oracle, then you will probably need to think of something else.

Conclusion
Using Multi-Dimensional maps is a great alternative to Jython or SQL scripts. It means that you can have a safe and reliable way of importing and exporting maps. There are some things Multi-Dim maps can’t do but they have greatly reduced the need for scripts. Now use Excel as a front end to these text files (import and export) and you have a robust and easy to use way of managing and backing up the maps.

Creating an Excel spreadsheet from FDM without loading Excel

I have written many scripts for FDM that generate files which are ultimately destined for Excel. I know how to create Excel spreadsheets through a script but you don’t want to run scripts that call Excel on a server. Excel needs to be installed for one thing and secondly you run the risk of slowing everything down because there are so many instances of Excel running. So I looked at different ways of creating an Excel spreadsheet and I decided that ADO (Active Data Objects) was probably the simplest method.

The idea behind this is to allow you to create a backup of your maps for all dimensions in a single file. You will have a separate file per location and period. Each file can then be loaded back into FDM.

There are two parts to this: the Script and the template spreadsheet. The script takes a copy of the template spreadsheet and uses it as a source for an ADO recordset. The template spreadsheet has the header information for the maps which is used by the script to populate the correct columns. The script then queries FDM to get the maps for the current location and adds them to the spreadsheet. ADO does not allow any control over the spreadsheet and I wanted the name range to be automatically updated. So there is a macro in the spreadsheet that runs whenever you open the spreadsheet. The macro reads through all the sheets and will create a name range for any maps it finds.

The script follows. Copy this to a file called webExportMapsToExcel.uss and save it in the folder Data\Scripts\Custom.

Option Explicit

Sub webExportMapsToExcel()
'------------------------------------------------------------------
'Oracle Hyperion FDM CUSTOM Script:
'
'Created By: Marcus Hemy. Marcus@HandL.me.uk
'Date Created: November 2013
'
'Purpose: Create an Excel spreadsheet with the maps for the current location without loading Excel.
'
'Dependencies: The script needs a starting Excel spreadsheet.
'
'------------------------------------------------------------------
Dim objFSO
Dim strFileTemplate
Dim strFileExtension
Dim strFileCreated
Dim strConnection, conn, strSQL
Dim field
Dim strDoubleQuote
Dim rs, rsMap
Dim sDimensionList, sDimension
Dim arrDimensions
Dim strLoc
Dim strPer
Dim strMessage
Dim strScriptName
Dim strSourceFolder
Dim strTargetFolder

strScriptName = "webExportMapsToExcel"

 'Check Excel Template file exists in the Templates folder.
 'Get the Outbox folder
 strSourceFolder = DW.Connection.PstrDirOutbox
 'Add a backslash if it needs one
 If Right(strSourceFolder, 1) <> "\" Then strSourceFolder = strSourceFolder & "\"
 'Add the Templates folder
 strSourceFolder = strSourceFolder & "Templates\"

 'Set the folder where the file will be created to the Inbox.
 strTargetFolder = DW.Connection.PstrDirInbox
 If Right(strTargetFolder, 1) <> "\" Then strTargetFolder = strTargetFolder & "\"
 'Name of the template file.
 strFileTemplate = "MapTemplate"
 'File extension. XLS works but XLSM does not.
 strFileExtension = ".XLS"

'Dimension list. Just add another dimension label here.
 sDimensionList = "Account;ICP;Entity;UD1;UD2;UD3;UD4"
 arrDimensions = Split(sDimensionList, ";")

'The output file will have the current location and period added to the file name.
 strLoc = RES.PstrLoc
 'Remove any spaces from the perod
 strPer = Replace(RES.PstrPer, " ", "")

 'Create the file system object
 Set objFSO = CreateObject("Scripting.FileSystemObject")

 'Make sure the template file exists
 If objFSO.FileExists(strSourceFolder & strFileTemplate & strFileExtension) Then

'Make a copy of the Template
     strFileCreated = strTargetFolder & strFileTemplate & "-" & strLoc & "-" & strPer & strFileExtension
     objFSO.CopyFile strSourceFolder & strFileTemplate & strFileExtension, strFileCreated, True

 Else
     strMessage = "Cannot find the source template file: " & strSourceFolder & strFileTemplate & strFileExtension

     If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
         'Win32 Client (Use Msgbox)
         MsgBox strMessage, vbOKOnly, strScriptName
     Else
         'Web Client (Set API action properties)
         RES.PlngActionType = 2 '(See Enumeration: ScriptActionTypes)
         RES.PstrActionValue = strScriptName & vbCrLf & strMessage
     End If
     Exit Sub
 End If

 'Destroy the file system object
 Set objFSO = Nothing

'Need to pass a double quote to a string so save it to a variable
 strDoubleQuote = Chr(34)

 'Set up the connecion string based on the file extension
 Select Case UCase(strFileExtension)
 Case ".XLS"
     strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDoubleQuote & strFileCreated & strDoubleQuote & ";Extended Properties=" & strDoubleQuote & "Excel 8.0;HDR=Yes;" & strDoubleQuote & ";"
 Case ".XLSX", ".XLSM"
     strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDoubleQuote & strFileCreated & strDoubleQuote & ";Extended Properties=" & strDoubleQuote & "Excel 12.0 Xml;HDR=Yes;" & strDoubleQuote & ";"
 End Select

 'Create an ADO connection
 Set conn = CreateObject("ADODB.Connection")

 'Connect ie open the file
 conn.Open strConnection

 'Create a recordset
 Set rs = CreateObject("ADODB.recordset")

 'Specify the cell range where the current header info is.
 strSQL = "SELECT * FROM A1:J2"
 'Now open those cells as a recordset
 rs.Open strSQL, conn, 3, 3

 'Read the maps for each dimension
 For Each sDimension In arrDimensions

     'Get the map records for the Location and Dimension
     Set rsMap = API.SQLMgr.fMapItems(CStr(strLoc), sDimension)

     'Make sure the function returned records
     If Not rsMap.BOF And Not rsMap.EOF Then
         'Now loop through the map records
         Do While Not rsMap.EOF
             'Write a new row to Excel
             rs.AddNew
             'Go through each column. OriginalValue holds the column name.
             For Each field In rs.Fields
                 field.Value = rsMap.Fields(field.OriginalValue).Value
             Next
             rs.Update

             'Get the next map record
             rsMap.MoveNext
         Loop
     End If

     Set rsMap = Nothing
 Next

'Tidy up
 rs.Close
 Set rs = Nothing

conn.Close
 Set conn = Nothing

strMessage = "Maps exported to: " & strFileCreated

If LCase(API.DataWindow.Connection.PstrClientType) = "workbench" Then
     'Win32 Client (Use Msgbox)
     MsgBox strMessage, vbOKOnly, strScriptName
 Else
     'Web Client (Set API action properties)
     RES.PlngActionType = 2 '(See Enumeration: ScriptActionTypes)
     RES.PstrActionValue = strScriptName & vbCrLf & strMessage
 End If

End Sub

For the template, open Excel and in cell A1 add tDataMap. In A2 to J2 add PartitionKey, DimName, SrcKey, SrcDesc, TargKey, ChangeSign, WhereClauseType, WhereClauseValue, Sequence, VBScript

Go to the VBE in Excel (Alt+F11) and add the following code to ThisWorkbook:

Option Explicit

Private Sub Workbook_Open()
Dim sSheet As String
Dim nCounter As Integer
Dim sCurrentLocation As String
Dim sStartCellAddress As String
Dim sEndCellAddress As String
Dim MaxRows As Long
Dim sNamedRange As String
Dim sSourceFileName As String

'ActiveCell.Offset(cMOVEDOWN, cSAMECOLUMN).Select
Const cMOVERIGHT As Integer = 1
Const cMOVELEFT As Integer = -1
Const cMOVEUP As Integer = -1
Const cMOVEDOWN As Integer = 1
Const cSAMEROW As Integer = 0
Const cSAMECOLUMN As Integer = 0

On Error GoTo PROC_ERR

 sSourceFileName = "MAPTEMPLATE.XLS"
 If Right$(UCase$(ActiveWorkbook.Name), Len(sSourceFileName)) = sSourceFileName Then
     'Don't do this for the source file
 Else
     'Go through each sheet
     SaveLocation False
     With ActiveWorkbook.Sheets
         For nCounter = 1 To .Count
             If .Item(nCounter).Type = xlWorksheet Then
                 sSheet = ActiveWorkbook.Sheets(nCounter).Name
                 Application.Sheets(sSheet).Select
                 'Search for tDataMap - produces an error if it doesn't find it
                 On Error Resume Next
                 Cells.Find(What:="tdatamap", After:=ActiveCell, LookIn:=xlFormulas, _
                                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                 MatchCase:=False, SearchFormat:=False).Activate
                 On Error GoTo PROC_ERR
                 If UCase$(ActiveCell.Text) = "TDATAMAP" Then
                     'Create the name range
                     sNamedRange = "ups" & sSheet
                     sStartCellAddress = ActiveCell.Address
                     sEndCellAddress = ColLetter(ActiveCell.Offset(cSAMEROW, cMOVERIGHT * 9).Column)
                     MaxRows = Range(sStartCellAddress).CurrentRegion.Rows.Count
                     sEndCellAddress = "$" & sEndCellAddress & "$" & MaxRows
                     ActiveWorkbook.Names.Add Name:=sNamedRange, RefersTo:="='" & sSheet & "'!" & sStartCellAddress & ":" & sEndCellAddress, Visible:=True
                 End If
             End If
         Next
     End With
     SaveLocation True
 End If
PROC_EXIT:
     Exit Sub

PROC_ERR:
     MsgBox "Error: " & Err.Number & " " & Err.Description & vbCrLf & "In procedure Workbook.Open", vbOKOnly + vbCritical, "CreateNameRange"
     Resume PROC_EXIT

End Sub
Private Function ColLetter(ByVal ColNumber As Integer) As String
     ColLetter = Left$(Cells(1, ColNumber).Address(False, False), 1 - (ColNumber > 26))
End Function
Public Sub SaveLocation(ReturnToLoc As Boolean)
Static Wb As Workbook
Static WS As Worksheet
Static R As Range

On Error Resume Next
If ReturnToLoc = False Then
Set Wb = ActiveWorkbook
Set WS = ActiveSheet
Set R = Selection
Else
Wb.Activate
WS.Activate
R.Select
End If

End Sub

Call this file MapTemplate and save it as an XLS. Put it in the Templates folder under the FDM application Outbox.

MapTemplate webExportMapsToExcelUSS