Wednesday, July 23, 2014

Invalid Records in HFM

Invalid Records

There are two types of invalid records in Oracle HFM.
  1. Invalid Metadata Members
  2. Invalid Intersections

Let us understand these records one by one.

1.     Invalid Metadata Members

These are members that existed at some point and were populated with data. Renaming or deleting these members orphans the data in the database.

E.g.  Suppose we have entity  called “FRANCE – 08980” in our HFM application SMBAPP.
When we create this entity , from HFM we can see Alias FRANCE and entity – 08980 either from HFM client, or webform or grid or smartview.
But behind HFM, in Oracle RDBMS, FRANCE is given an ID of 12 (Let’s assume) as it was 12th entity created. We can find the field when we look at the tables and locate the EntityID field. All the data loaded will be broken out by record and given an EntityID of 12
Suppose , we are not using this entity “FRANCE” anymore. When we delete the entity “FRANCE – 08980”, the only thing removed is the association between France and 10. The data records remain.

This is to make the speed of the metadata loads acceptable. Running all the possible validations could really slow the loads down. Over time, as metadata is changed, some of the existing records in the database will become invalid.

This type of invalid record can be deleted safely by using Delete Invalid Records.

2.     Invalid Intersections

This type is created when there was a relationship between the metadata members that was valid and is no longer. The members are still valid in the application, but the relationship where the data is populated is no longer valid.

Most commonly it is when there are changes to the CustomXTopMember relationship. This type of invalid data is not currently removed by any HFM functionality. The only way to resolve this issue is to rebuild the application.
Rebuilding the application as an option is not appealing. The best option is to be aware of this, and clear your data before you make the metadata changes.

We might wonder why Oracle did not put this validation of invalid records check during loading metadata.
 The reality is that validating every possible intersection is not reasonable. There is a big impact to metadata loading when the existing validations run.
We can see the impact when you try to load a file that has many errors or validation checks.
Because of this impact, one validation in particular is excluded when metadata is loaded into HFM. It is the validation to check for invalid records.

Oracle recommendation for deleting invalid records


It is recommended by Oracle that customers delete invalid records every couple of months, especially after significant changes to metadata. While doing this task is relatively safe for the database, if we have never done it, or it has been a while since you’ve done it, it can take a long time to complete. So be sure to do this during a down time for the application and never during the close.

Procedure to delete invalid records

1.       Open Hyerion Worksace
2.       Open the application from which to delete invalid records.
3.       Select Tasks -- > Data Management
4.       From the Manage Data page, expand Clear Invalid Records.
5.       Perform a task:
a.       Click Scan to scan for invalid records.
b.      Click Clear Invalid Records to clear the records.


Invalid records can also be deleted while copying application by selecting Advanced Options tab in copy utility.



1 comment:

  1. Hi Tejaswini, article was good. Could you throw some more light where this data will be stored, will they exist in the DCE,DCN,DCT tables or moved to some other table.

    ReplyDelete