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:
What's next?
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
