Building your own Monitoring Solution for Data Changes in HFM Phase Submission Loads - Part 2

In the last blog post Building your own Monitoring Solution for Data Changes in HFM Phase Submission Loads - Part 1, we described FDMEE automation requested by our customer. One of the requirements was to include a data change tracker to monitor the data changes along the different HFM submission phases.

The MUST-HAVE list for this custom functionality is:

• Get the HFM Process Management details (Review Level for the different phases)
• When Soft Close, be able to compare new data set against last data set loaded. That will monitor all data load changes
• Auto-map missing mappings so the workflow can be completed
• Auto-map for invalid HFM intersections and identify the CANNOT WRITE intersections (Soft Close)
• Keep audit in FDMEE so missing mappings and invalid intersections can be easily filtered in the Data Load Workbench
• Send email notification with detailed results 

Top-Down Solution Design, that's the key

There are multiple ways of addressing the design of a solution. If you ask me, my preferred approach is to start with drawing my solution at high-level. Why? Basically, with this Top-Down methodology, I can decompose the solution is smaller parts to have a better comprehension of its different components. I can then refine each part in greater detail.

Definitely, with this "Divide and Conquer" approach, I will have a better understanding of how my solution will look like and will reduce the impact of additional requirements.

Let's start drawing the solution!

Solution Diagram
As I usually say, "A picture paints a thousands words". There will be always a solution design diagram in every project I work:

Basically:

• We get the HFM Process Management details before data is loaded into HFM
• We perform the data monitoring after data is loaded into HFM

Let's use reverse engineering to add more explanation:

• After data has been loaded into HFM, the data monitoring is initiated for all HFM entities in Soft Close for any of the different phases. 
• As we first need to know the status for all phases, FDMEE will get this information from HFM and store it in the TDATASEG table. This action happens before data is loaded into HFM (BefLoad event script)
• Then, the data monitoring will compare the new data being loaded with data previously loaded. But, where is the data previously loaded? It's not in FDMEE because data is wiped out when the same POV (DLR and Period) is re-processed. Data must be accessible somewhere else. To workaround this, FDMEE data is copied to a custom table (Z_BALANCES_PRIOR) every time it is loaded into HFM (with no errors). This action happens after data is loaded into HFM (AftLoad event script)

In a nutshell, the first time data is loaded for a specific POV (DATA 0), there won't be data to be compare against. The second time data is loaded into HFM (DATA 1), it will be compared against DATA 0, and so on.

What do users get?

A very nice email notification with results :-)

The email above, shows that one intersection has been updated. Results are attached as a CSV file where they can see data differences for all phases. They can also see when previous data has not been loaded in the new load:

Taking the notifications to the Next Level

This solution for the automated process followed the pattern "This is very cool! Could we also have...?" I like challenges and delivering solutions to meet requirements. That makes everyone happy, doesn't it?

There are missing maps

We want to see which source values have not been mapped:

There are invalid intersections

We want to see the invalid intersections in the same way we did in FDM Classic!

There are invalid intersections, but why?

Most of the times, the Cannot Write intersections are due to process level, can we get process level for the different HFM process units with invalid intersections?

What's next?

Next post will cover details of implementation:

• How to get the HFM Process Management details
• SQL Query to get data differences (new, updated, or deleted data)

Cheers

Building your own Monitoring Solution for Data Changes in HFM Phase Submission Loads - Part 1

Back to blog!

From time to time I get some cool requirements from customers. That makes me think and do what I like to do: analyze, design, build and make everyone happy :-)

In this case, I got a challenging requirement for an automated integration between several source systems and HFM. Of course, using our favorite fishing tool, FDMEE. Yes, I know, everybody is now thinking about Cloud and more Cloud. However, FDMEE is still a very strong EPM integration tool supporting Hybrid and survives in 11.2.

I won't go into much detail so I can keep it simple.

The customer had already all automation built using open batch files and different direct data extracts. However, there was no email notification solution implemented. It was hard for them to get results without accessing FDMEE.

We started to gather the requirements and we came up with the following list:

• Bypass missing mappings and send summary by email
• Bypass invalid HFM intersections and send the Intersection Check report by email (html format)
• Identify the CANNOT WRITE intersections (the blue ones) and include them in the email body
• Show Process Management information about the HFM Process Units where data cannot be written. Basically, show Review Level so the users are aware of what is happening
• Build a data-tracking solution for HFM Phased Submission

The goal is to cover all requirements in future blog posts but today I will start with the key one, the data-tracking. Personally, I think this would be a great functionality for Cloud Data Management as well. It fills a gap in the data audit in FDMEE (you can see how cell data changes in HFM)

Requirement - Data Tracking for HFM Phased Submission

This is the requirement word by word:

To maintain control over the phased close process, a data tracking service is required to track data changes impacting Gross Margin and EBITDA from WD2 and WD4 respectively.  

We need the following key controls:

• The phased submission process should allow for a soft Gross Margin and EBITDA close whilst tracking changes to the underlying data impacting Gross Margin and EBITDA from WD2 and WD4 respectively
• The data load monitoring service must be relevant i.e. we DO NOT want to track all financial statement data changes and report them to users
• Appropriate stakeholders must be notified of the data changes once the deadlines have been passed
• This process must not disrupt the source trial balance data load to HFM i.e. the trial balance loaded must be complete and accurate
• Sufficient provision should be built into the design to switch over to a hard WD2/WD4 close if desired

Is it clear? I know it is not a conventional solution but it will help establish the control that is desired over the phased close process without "hard" locking components of the income statement. This is what the customer wants to achieve.

Let's give some shape to these words as we want to keep everything simple. HFM will be using phased submissions to manage the data flow with review levels acting as a trigger event for data tracking.

• Review Level 1 (RL1): Both the automated and manual FDMEE load processes are running and exporting data to HFM.
• Review Level 2 (RL2) Soft Close - the promotion to RL2 will now act as a trigger to activate the data monitoring script that will track data changes when data is exported to HFM.
• Review Level 3 (RL3) Hard Close – at RL3 the FDMEE Global user system account no longer has access to load data to HFM.

Phased Submissions at a glance

The term phased submissions in HFM is synonymous with managing data through stages of the close cycle. In a typical organization this would involve:

• Phase 1 – Promote inter-company data
• Phase 2 – Promote Trial Balance data
• Phase 3 – Promote Balance Sheet movement data/cash-flow
• Phase 4 – Promote disclosure notes 

The process encourages a phased hard close and is designed for locking data submissions defined by accounting processes (i.e when Phase 1 is promoted to Review Level 3, inter-company data cannot be changed by the trial balance loads in Phase 2)

Our Customer

The following tables show the different phases and details: 

WD2	WD4
Phase 1	Phase 2
Promote Gross Margin data	Promote EBITDA data
Soft close - RL2	Soft close - RL2
Enable data monitoring on accounts that impact Gross Margin	Enable data monitoring on accounts that impact EBITDA
Automated notifications informing users of changes to Gross Margin data	Automated notifications informing users of changes to EBITDA data
WD6
Phase 3	Phase 4
Promote Trial Balance data	Promote USGAAP/Disclosure Notes
Hard close – RL3	Hard close – RL3
Lock trial balance data	Lock USGAAP adjustments and changes to disclosure notes
Stop all email notifications	Journals/Offline (excel) submissions

Using Custom HFM dimension to Link Accounts and Phases

A conscious decision was made to try and avoid tagging submission groups on the account dimension as invariably accounts end up being incorrectly tagged (or Group Finance change their mind!) which results in the need for an application metadata update to correct the submission group assignment (used to profile phased submissions).

Instead a spare custom dimension was leveraged, in this case Custom6 (Custom5 is used to evaluate constant currency) with the intention of using FDMEE and SQL mapping scripts to load data to each of the Custom6 dimension members respectively. This means that we can now adjust the FDMEE mapping for phased submission purposes without the need to apply HFM metadata updates.

Please, note that this approach will only work dynamically where the chart of accounts adheres to a rational numbering sequence e.g. Revenue account are 1* series, Cost of Sales accounts are 2* series and OPEX accounts are 3* series etc. Other approaches are also valid.

Translating the Requirements for the Integration Guy

Although solution seems to be complex, design is the key. Basically, we need FDMEE to:

• Get the HFM Process Management details (Review Level for the different phases)
• When Soft Close, be able to compare new data set against last data set loaded. That will monitor all data load changes
• Auto-map missing mappings so the workflow can be completed
• Auto-map for invalid HFM intersections and identify the CANNOT WRITE intersections (Soft Close)
• Keep audit in FDMEE so missing mappings and invalid intersections can be easily filtered in the Data Load Workbench
• Send email notification with detailed results 

In the next chapter we will go through the solution design in FDMEE, and then we will continue with some examples where you can see how it works and the outcome of this data monitoring solution.

Merry Xmas!

BBT for FDMEE #1 - Target Accounts as Source in Mappings

Hola!

Working for customers, preparing training, conferences and the most important one, Francisco Jr running around, have been keeping me busy during the last months.

One of the presentations I've been working on is "Black Belt Techniques for FDMEE" (aka BBT for FDMEE). I thought it was interesting for people to know how to meet requirements of different complexity with some techniques which of course, aren't in the books :-)

Although I can't go into too much detail (I don't want to spoil the presentation), this is a foretaste of what you will enjoy if attending to Kscope17.

The Requirement

As you know, FDMEE can pull data from very heterogeneous source systems. Once data has been extracted, it has to be mapped into our target system (let's say HFM). Usually, people responsible of maintaining mappings (aka mappers) are more familiar with target system rather than source. 

This is not always the case but it's a common scenario when financial departments are split. How often do you hear "Not sure about this, we need to ask our ERP guy..."?

Another common scenario is that ICP/Custom dimensions mappings use source ERP account as a driver either importing source account into source ICP/Custom dimensions or using Multi-dim/Conditional maps. 

Have you have you ever asked to the mapper: Would it be easier for you if you could use the HFM account to define ICP/Custom dimension mappings rather than source account?

In my case, I always do. And what I found is that if they can define mapping using the target HFM account, maintenance tasks are much simpler and the number of mapping rules is highly reduced.

Of course, the immediate question is: Can we do that? Yes we can. How? 

Lookup dimensions as a Bridge, that's the answer

Lookup dimensions can be used in FDMEE for different purposes. How can they help us to meet our requirement?

• They don't have an impact on target application
• We can define a #SQL mapping to copy our target values into other source dimension values including the lookup dimension itself
• We can define the order in which the lookup dimension mapped

Have a look at this: Source Account > Target Account > Lookup > Source C1 > Target C1

Did you understand above flow? Let's put some lights on it.

Let's start defining our lookup dimension "HFM Account":

In this example, we are going to use the lookup dimension to copy the target HFM account into the source lookup. For that purpose, we need to make sure that the lookup dimension is mapped after Account dimension. As you can see above, sequence number for the lookup is 2 while Account has assigned 1.

Besides, column UD5 of TDATASEG(_T) has been assigned to HFM Account (I could have used any other like UD10 so I leave some UDx free in case we have new HFM custom dimensions). 

Copying the Target HFM Account into other/lookup dimensions

As for any other dimension, we can create maps for lookup dimensions. Our main goal is to copy a target value into other dimensions so why not using a SQL mapping?

The good thing of mapping scripts is that we can include multiple columns:

• Set target HFM Account to "Lookup"
• Set source HFM Account (UD5) to target Account (ACCOUNTX)

Done, our target account has been copied and it's now available as a source value in our lookup dimension.

A picture is worth than a thousand words

Let's create a multi-dimensional mapping to show how this works:

Mapping says: when source Product is "'001" and HFM Account is "Price" then target Product is "P7000_Phones"

Thanks to our lookup dimension we can use the HFM account as a source. Mapping rule is clear and easy to create. No need to change the SQL map we created, that's static.

What happens in Data Load Workbench?

1. Source ERP account "10100" has been mapped to "Price"
2. "Price" has been copied to source HFM Account
3. Product has been mapped using source Product and HFM Account

At some point, I expect Oracle enhancing multi-dim maps to support target dimensions also so let's see another example where this approach is quite useful as well.

Another example: write-back to SAP using SAP accounts as Source in Explicit maps

In this case, we are extracting data from HFM in order to generate report files for SAP (write-back)

The requirement is to map SAP IFRS using SAP Accounts. 

Following our approach:

1. Map HFM Account to SAP Account
2. Copy SAP Account to source SAP IFRS
3. Map SAP IFRS based on SAP Accounts

Let's see the workflow:

As you can see, we have now copied the SAP Account into another dimension rather than the lookup. That allows us to create our Explicit mappings using SAP accounts in a very easy way.

Cloud-Friendly

Once nice thing is that this solution is cloud-friendly. Data Management for the Cloud allows creating lookup dimensions and #SQL mapping scripts so you can implement it if not using my loved on-premises FDMEE.

I'm going to leave here for today. I hope you found this BBT useful.

More BTTs soon!