Intersection Check Report for HFM - Custom XSL Solution

Hi all,

first of all I would like to wish you a HNY!

When loading data into HFM there is an option to validate HFM data intersections before load happens. This validation takes place during the Validate Step just after FDM validates all source dimension members have been mapped. The main objective is to capture errors before loading data into HFM, and therefore avoid partial loads

Checks performed
As a result of the validation we can get:

• Invalid Member: the mapped member (target member) does not exist in HFM or the user does not have access to the related security class.
• Invalid Intersection: the combination of Account, ICP, C1, C2, C3... is an invalid combination according to metadata definition. 
• Cannot Write: the data cell (intersection of dimensions) contains a "no input" rile, is on an aggregated level (parent) or data has been promoted to a process level with the read-only attribute.
• Locked Intersection: the period has not been started by the process level management or the period has been locked.

 

Differences between FDM and FDMEE

Main differences between Classic FDM and FDMEE are:

• Name: in FDM the report was named "Intersection Validation Report" while in FDMEE has been renamed to "Intersection Check Report"
• Format: in FDM the report was generated in XML format which was using XSL to format the report. The user could hover the mouse over errors in order to get details and instructions/tips for fixing them. He could also click on errors to navigate to valid members. In FDMEE, although the report is stored as a XML file, the report is generated PDF format which no hover capability. XSL transformation has been removed in FDMEE.
• Report: in FDM there was only type of report while in FDMEE we have two types. One report for typical HFM application with 4 custom dimensions and other for HFM applications which have more than 4 dimensions (Dynamic Report)
• Generation: in FDM the report was generated when the user clicked on Validate Action while in FDMEE the user can now generate the report also from button "Generate Intersection Check Report".

Analyzing Intersection Errors
I really like meetings/workshops where I'm sitting with both HFM administrators and responsible of source systems... when data is being loaded everybody is expectant to see who is going to be blamed when errors happen.
- HFM administrator will say to the source system administrator: Hey! you did not tell me this data was going to be extracted...
- Source system administrator will say to the HFM administrator: Hey! you did not specify which data you wanted...
- FMDEE administrator will blame both!!! :-)

Jokes apart, the Intersection Check Report is a really good starting point to fix issues related to HFM application design (top dimension members, intercompany accounts, etc.) and to source system extracts (data to be extracted, granularity, etc.) It's in this stage where the FDMEE administrator should be a bridge between both parties. At the end, we want everyone's life to be easier, and of course, that includes ours.

My suggestions to work on errors shown in the report are:

1. Check if HFM metadata is correctly configured
2. Check if mapping/import format is correct
3. Check if source data is correct

You can see how I prefer following reverse error finding but you are free to follow your own approach as long as you find the issue and fix it.

Template for Intersection Check Report
In FDMEE, report templates (RTF) are located in $Oracle_Middleware\EPMSystem11R1\products\FinancialDataQuality\templates.
You can find there two RTF files:
- HFM Fixed Columns Intersection Check Report.rtf

- HFM Dynamic Columns Intersection Check Report.rtf

If you take a look to the 2 RTF files you will notice that POV members (Scenario, Year, and Period) are displayed in a different way for each report. For "Fixed" report they are displayed on top of all columns (as Classic FDM). On the contrary, for "Dynamic" report they are displayed with all other dimension columns which are filled dynamically based on dimensions in the HFM application.

These reports can be adjusted by using BIP Word Desktop plug-in. Once you review the XML files generated from FDMEE to produce the Intersection Check Report then you will easily see the link between XML tags and RTF reports tags.

Query for Intersection Check Report
Don't try to take a look to the query associated to this report. I say that because this report is generated from API ($Oracle_Middleware\EPMSystem11R1\products\FinancialDataQuality\bin\HFM_VALIDATE.vbs) and cannot be launched from Report Execution link. Therefore it has a dummy query associated.

If you try to execute the report from Report Execution link it will be empty:

Enabling Intersection Check Report
To enable the Intersection Check Report you have to set target application option "Check Intersection" to Yes.

The default Intersection Check Report can be then specified at System or Application level: 

Running Intersection Check Report
As we stated before, the report is automatically generated at the Validate step when we either run the Import Step or the Validate Step.

The following example shows a report where ICP member is invalid:

If we scroll down we will see that [ICP None] is the only valid member for my current HFM configuration: 

We will have to review the issue and fix it before validate successes. It could be mapping issue, wrong source data, wrong HFM metadata... who knows?

If we want to re-generate it again we don't have to click on Validate link again (as Classic FDM). We can generate it by clicking on "Generate Intersection Check Report" button:

XML file for the Intersection Check Report
The action script HFM_VALIDATE.vbs generates a XML file which is then converted to PDF and prompted to the user. This XML file is very similar to the one generated in Classic FDM.
They have slightly different XML structure with different XML tags, surely to make the RTF template easier (the RTF report is filled from the XML and then converted to PDF)
So here you have another difference between Classic FDM and FDMEE: in FDMEE the Intersection Check Report has a report template associated (RTF) while in Classic FDM there was no Crystal report for it. The XML was just generated, transformed using XSL and displayed to the user in Internet Explorer.
The XML file can be found in the outbox folder of our target application:

If you take a look into it and you compare to a XML file generated from Classic FDM you will notice differences like for example XML tag <xmlrow> which identifies each line in the report, or the XML from Classic FDM having a reference to the XSL which transforms it:

Creating a custom XSL file to format the XML report in FDMEE (Dynamic Report)
So... why not trying to create a custom XSL to format the XML generated in FDMEE and display it as the same way it was done in Classic FDM? In that way I could easily navigate the XML file being able to see error details and tips.
The idea is to adjust the existing XLS file for the new XML tags and also make it dynamic as the number of dimensions can vary. I had to recall my old times in XML but at the end I got it working so I'm sure you can do it as well.

Let's say that we have the following XML generated in FDMEE:

You can see how this is opened in IE as a normal XML.
After creating our custom XSL we have to reference it from the XML file as follows:

Then if we try to open it with IE we will see the XML formatted as Classic FDM (click on links, hover on errors, etc.):

How did I create the XSL file? I adjusted the XLS from Classic FDM including code like:

 

I suggest you to review the XSL file for Classic FDM to see how it transforms XML files and then adjust it to new XML tags. Also, think that the number of dimensions may vary so some of your code should perform transformations dynamically as well.

Where did I place the XSL file? It's interesting that the XSL file is accessible from anywhere and not only locally so I chose htdocs folder of OHS to make it available when the OHS service is up and running:

The XSL reference can be the prepended to tag <?xml version="1.0"?> as follows:

<?xml-stylesheet type="text/xsl" href="http://epm11123:19000/FDMEE-XSL/EPMA7DIM.xsl"?>



Creating a Custom Script to display the XML transformed by the custom XSL file
We cannot alter FDMEE to display the transformed XML file instead of the PDF but we can create a custom script that:
1. Prepend XSL reference to the XML file
2. Display the XML file
You can think in many different ways of implementing this solution. I'm so lazy so I have created a custom script with two parameters:
- Parameter 1: Location
- Parameter 2: Process Id

The first parameter is useless but it is good to show how parameters work. The second parameter has associated a query that returns all process Ids for my target HFM application (Id = 17)

The following code prepends the XSL reference to the XML file and shows the formatted XML file:

#---------------------------------------------------------------------------
# Adds to the Intersection Validation Report (XML) the first line with
# the XSL reference
# XSL File = TARGETAPPLICATION.xsl
# XML File = TARGETAPPLICATION_ProcessId_VALIDATE.xml
# The process ID and Application Name are passed as input parameter
# For example, for application EPMA7DIM, the line to be added is:
# <?xml-stylesheet type="text/xsl" href=".\IVR_XLS\EPMA7DIM.xsl"?>
# Parameters
# Param 1: Location
# Param 2: Process ID
#---------------------------------------------------------------------------
# Get value for Parameter CUSTOM_PROCESSID (Process Id)
sProcessId = fdmAPI.getCustomScriptParameterValue("CUSTOM_PROCESSID")

# Custom Scripts are registered at Target Application Level so we can get the
# target HFM application from the FDMEE context
sHFMApp = fdmContext["TARGETAPPNAME"]

# Get XML and XSL Files
# XML Tag for XML-Stylesheet reference
sXSLRef = "<?xml-stylesheet type=\"text/xsl\" href=\"http://epm11123:19000/FDMEE-XSL/" + sHFMApp + ".xsl\"?>"
# XML File for the Intersection Validation Report
sXMLFileName = sHFMApp + "_" + sProcessId + "_VALIDATE.xml"
# Full path for the XML File
sXMLFilePath = fdmContext["OUTBOXDIR"] + "/" + sXMLFileName

# Prepend line with XSL reference (only for first execution)
# Open XML file and read first line
inputXMLfile =  open(sXMLFilePath)
sFirstLine = inputXMLfile.read(1<<15)
# Close XML file
inputXMLfile.close()
if sFirstLine[:16] != "<?xml-stylesheet":
    prepend(sXMLFilePath,sXSLRef + '\n' )

# Show the Intersection Validation Report (XML)
fdmAPI.showCustomFile(sXMLFilePath)

You have to define as well the prepend function at the beginning of the script:

#---------------------------------------------------------------------------
# Function prepend: prepend string to file
# Buffer is initialized to 2^15 = 32678
# 1. Backup Original File (Original file -> Add extension .bak)
# 2. Prepend sdata line to sfilename
# 3. Close files
# 4. Remove Backup file
#---------------------------------------------------------------------------
def prepend(sfilename, sdata, ibufsize=1<<15):
    import os
    # Backup file name (use os file extension)
    sbackupname = sfilename + os.extsep + "bak"
    # Remove previous backup file if it exists (equivalent to remove())
    try: os.unlink(sbackupname)
    except OSError: pass # do nothing if backup file does not exist (continue)
    # Rename original file name to backup file
    os.rename(sfilename,sbackupname)
   
    # Open input/output files (outputfile's permissions lost)
    inputfile =  open(sbackupname)
    outputfile = open(sfilename, 'w')
  
    # Prepend sdata line to file only first time we run the report   
    outputfile.write(sdata)
         
    # Copy the rest of original lines
    sbuffer = inputfile.read(ibufsize)
    while sbuffer:
        outputfile.write(sbuffer)
        sbuffer = inputfile.read(ibufsize)
   
    # Close files
    inputfile.close()
    outputfile.close()
       
    # Remove backup on success
    try: os.unlink(sbackupname)
    except OSError: pass

Running the Custom Script
We can now run the custom script from "Script Execution" link:

We have to select the Process Id from the list of processes returned by the Query (entering it manually won't work) and execute the script in "Online Mode" in order the report to be displayed. If we execute the script in "Offline Mode" the report will not be displayed.

After clicking OK we will be asked to open the XML file:

I found an issue here but I did not have time to research on it. If you open the XML file directly from the prompt window, the XML file will be opened in the same window as Workspace... so you can save it locally and open it. 

 

That's all my folks. With this post I tried to show you what is the Intersection Check Report, how is generated in FDMEE, how useful it is for error solving, and finally how you can create a custom solution to display the report as it was done in Classic FDM.
I have also shown some Jython coding: open/delete a file, write into a file, work with exceptions (try - except), get input parameters, etc.

I hope you enjoy it!