One of the most important steps during the creation of your application creation is determining how many years should exist in the application.  Specifying too many years could result in having wasted space and poor application performance while too few years could result in running out of room in your application when you hit the upper limit.

When HFM first shipped, you had no means of updating the Start Year or the End Year of your application; therefore, a mistake during the application creation process could lead to major headaches in the future.  As customer’s HFM applications matured and those upper year limits were getting close, Hyperion/Oracle rolled out the capability to add more years to the applications.  (with 11.1.2.4, there is a built in function to do this)

When it comes to removing years from the application, you can clear data from years; however, there is not a way to actually remove the years in the application.  Unfortunately, Oracle has never released guidance or functionality for altering the Start Year and will tell you that it isn’t possible to change this.  I’m here to tell you that, you can absolutely change this!  Before we get into how to make those updates, some quick words on why you’d want to do this and some considerations around it.

Reasons to remove years from your application:

• Database has grown significantly due to continued use of the same application over many years
• Metadata is cluttered due to the need to ensure historical data remains consistent
• Prior years are no longer used for reporting  and are not necessary
• Minimize the years displayed in the U/I to only valid years
• Reduce the number of database objects (data tables may exist for each year/period/scenario combination)

Considerations when removing years from your application:

• Beginning Balances for Balance Sheet accounts – (e.g. If our Start Year changes to 2015, we will still need prior year ending balances)
• HFM Rules/Calc Mgr – (e.g. ensure that rules are not impacted by beginning year change

Process to change the Start Year:

• “Easy Steps”
  • Make a backup.  (LCM / Database Backup / etc.)
  • Create an archive application – Consider creating an archive HFM application for the historical data.  Storing the information in a separate application gives you the ability to still view the data if needed without having to make metadata trade-offs in your day to day/primary application.  Use HFM Application Copy Utility or LCM/Application Snapshot to accomplish this.  (If you already have an archive app, you can export/import data, though Journals may be slightly annoying in this instance)
  • Update Beginning Year variables / values in the application rules / data for the new Start Year
  • Stop the HFM services
• “Not so Easy Steps”  (explained later in more detail, don’t panic yet)
  • Connect to your HFM database via your database tool of choice  (e.g. SQL Server Management Studio, SQLPlus, etc.)
  • Query the <APP_NAME>_BINARYFILES table by ENTRYIDX for Labels of type SharedAppData
  • Update bytes 74 & 75 of the first row to reflect your new Start Year
• Start HFM Services
• Verify application data / functionality
• “Post Validation Cleanup”
  • Stop HFM Services
  • Remove Year / Period specific database tables (e.g. <APP_NAME>_DCE_<PeriodNum>_<Year> )

HFM Binary Files Table

For each HFM application, you will find a Binary Files table in the format <APP_NAME>_BINARYFILES.  This table is used for multiple purposes, including storing application configuration information, application metadata, HFM rules, memberlists, etc.  The LABEL column defines the type of file the data is related to.  The BINARYFILE column contains the raw information from the corresponding file.  (e.g. Application Profile .PER file, Rules .RUL file, Metadata, etc.)  Since this column has a character limit that is smaller than most of these files, you will find multiple rows for each file type.  The ENTRYIDX column is used to order the rows in the proper order.  (Ascending order)

As the Start and End years are defined in the profile, we only want to view rows containing a LABEL of SharedAppData:

SELECT
 ENTRYIDX, LABEL, BINARYFILE
FROM
 <APP_NAME>_BINARYFILES
WHERE
 LABEL = 'SharedAppData'
ORDER BY
 ENTRYIDX ASC

 

SharedAppData 

As mentioned above, the SharedAppData rows correspond to the application profile (.PER) that defines the HFM application.  Analysis of this data indicates that both the Start and End years are stored in the first row of this information.  The Start Year is stored in bytes 74 and 75 while, the End Year is stored in bytes 78 and 79.  The data is stored in Least Significant Byte Order.  (e.g. Byte 75 is first half of year, 74 is the second)

In the Shared App Data above, the Start Year is 2006 while the End Year is 2015.

Start Year (Hexadecimal) = D6 07 = 07 D6 (flipped) = 2006 (decimal)
End Year (Hexadecimal) = DF 07 = 07 DF (flipped) = 2015 (decimal)

To change the Start Year, convert the decimal year to hexadecimal and then reverse the byte order.  For instance, if your new start year is 1999, you would end up with:  1999 (dec) = 07CF = CE 07

HINT:  You can use Windows built-in Calculator program in Programmer mode to make these conversions.

Update SharedAppData

After you’ve calculated your new Start Year value, create an Update query to alter the stored binary data

 

Verify Results

After you have updated your settings and restarted HFM, you can verify that you now have the appropriate years

Original Years

 

Updated Years

NOTE – While the screen shots are from an HFM 11.1.2.1 application, this has been tested through 11.1.2.4, though I won’t guarantee this works with every patch version, etc.  Always test in a non-production environment.