FDQM/EE Loading Metadata to on-premise and cloud EPM Applications – Part-2


Import Format

The import format will map the columns from the source file to the dimensions in the custom application, or set defaults for properties that are the same for every member. In other cases, some of the members are set to the text “BLANK” so that mapping will succeed, but then values for the BLANK members may or may not be set by the by the event script. The use of the text for BLANK is specifically required when using the output dat file to generate the metadata file. If pulling directly from TDATASEG the user may not need to use this method.

Please also note that the AMOUNT field should be set to any valid numeric amount so that the input file will be successfully processed. In addition, a SKIP row may be required if the input file includes a header record.
For this example, the Import Format is defined as follows:

Event Scripts
In this example, an AftExportToDat and BefLoad script were used to process the file and then push the metadata to HFM. It’s possible to perform everything needed in a single script if desired by the user, and alternate implementations are possible. The script details are as follows:

AftExportToDat – Generate the metadata file in the format required by HFM

import shutil
#Open the .dat file generated by FDMEE
datFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.dat”
datFile = open(datFileName,”r”)
#Open the .app file to be created for HFM
appFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.app”
appFile = open(appFileName,”w”)
#Create .app file Header lines
appFile.write(“!FILE_FORMAT=11.12\n!VERSION=11.1.5250\n!CUSTOM_ORDER=Movement;CostCenter;Location;Nature\n\n”)
appFile.write(“!MEMBERS=Entity\n’Label;DefCurrency;AllowAdjs;IsICP;AllowAdjFromChildren;SecurityClass;UserDefined1;UserDefined2;UserDefined3;HoldingCompany;SecurityAsPartner;Default Parent;Description(s)\n”)
#Include a counter to skip record in the .dat file generated by FDMEE
i = 0
#Parse the records in .dat file & replace BLANKs with empty string. (FDMEE puts BLANK for empty columns by default & might be rejected by target EPM application.)
for memberLine in datFile:
i = i + 1
if i > 1:
memberLine = memberLine.replace(“BLANK”,””)
memberLine = memberLine.split(“,”)
#Derive the Default Parent
if memberLine[15] != “”:
DefParent = memberLine[14]
elif memberLine[14] != “”:
DefParent = memberLine[13]
else:
DefParent = ‘#root’
#Write the .app file record for members using the below format.
appFile.write(“%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;%s;DefaultParent=%s;English=%s\n” % (memberLine[0],memberLine[1],memberLine[2],memberLine[3],memberLine[4],memberLine[5],memberLine[6],memberLine[7],memberLine[8],memberLine[9],memberLine[10],DefParent,memberLine[12]))
#Create another header record in the .app file for the hierarchy section.
appFile.write(“\n!HIERARCHIES=Entity\n”)
#Close the .dat file
datFile.close()
#Open the .dat file again for parsing the records for hierarchy section.
datFile = open(datFileName,”r”)
#Write a logic such that hierarchy sections are not repeated in the .app file.
j = 0
seen = set()
for hierarchyLine in datFile:
j = j + 1
if j > 1:
hierarchyLine = hierarchyLine.replace(“BLANK”,””)
hierarchyLine = hierarchyLine.split(“,”)
gen1 = “;%s\n” % (hierarchyLine[13])
gen2 = “%s;%s\n” % (hierarchyLine[13],hierarchyLine[14])
gen3 = “%s;%s\n” % (hierarchyLine[14],hierarchyLine[15])
if gen1 not in seen and hierarchyLine[13] != “”:
seen.add(gen1)
appFile.write(gen1)
if gen2 not in seen and hierarchyLine[14] != “”:
seen.add(gen2)
appFile.write(gen2)
if gen3 not in seen and hierarchyLine[15] != “”:
seen.add(gen3)
appFile.write(gen3)
#close the .app file & .dat file.
appFile.close()
datFile.close()

BefLoad – Load the file to HFM

#Import required HFM & Java libraries
from java.util import Locale
from java.io import File
import shutil
from oracle.epm.fm.hssservice import HSSUtilManager
from oracle.epm.fm.domainobject.application import SessionOM
from oracle.epm.fm.domainobject.loadextract import LoadExtractOM
from oracle.epm.fm.common.datatype import transport
#HSS Username / Password
UserName = “admin”
Password = “Password”
#Target HFM connection properties
clusterName = “HFMCluster”
application = “DEVHFM”
#Authenticate user & create HFM Session
ssoToken = HSSUtilManager.getSecurityManager().authenticateUser(UserName,Password)
hfmsession = SessionOM().createSession(ssoToken, Locale.ENGLISH,clusterName,application)
#Load members to HFM
loadOM = LoadExtractOM(hfmsession)
appFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.app”
metadata_file = File(appFileName)
memberLoadOptions = transport.MetadataLoadOptions()
memberLoadOptions.setUseReplaceMode(False)
memberLoadOptions.setEntities(True)
memberLoadOptions.setDelimiter(“;”)
setCustomDims = [False,False,False,False]
memberLoadOptions.setCustomDims(setCustomDims)
loadInfo = loadOM.loadMetadata(metadata_file, memberLoadOptions)
logFile = loadInfo.getLogFile()
logFileName = str(fdmContext[“OUTBOXDIR”].replace(“\\”,”/”)) + “/” + str(fdmContext[“TARGETAPPNAME”]) + “_” + str(fdmContext[“LOADID”]) + “.log”
shutil.copy(logFile.getPath().replace(“\\”,”/”),logFileName)
SessionOM().closeSession(hfmsession)
fdmAPI.showCustomMessage(“Metadata Loaded Successfully”)

Continue – Part-3

Advertisements