Loading Budget Data

Oracle/Hyperion Financial Data Quality Management (FDM) is best recognized for its elegant, streamlined & easy to use interface which is often used to load a trial balance (generally representing a single period of data).  For this reason, the period & year dimensions are mapped “globally” from the point of view.  This application design leads many to believe that multiple periods of data need to be loaded as multiple files with individual periods of data.

This urban legend couldn’t be more false!  FDM has native functionality that allows multiple periods to be loaded from a single file using a single workflow process.  This functionality is known as Multiload.  Multiload can be used to load multiple periods, categories or locations while providing almost all the same functionality that is available with the standard workflow (Import -> Check) process.  The most common use is to load multiple periods of data for budget or when loading historical data when first implementing a system like HFM or Planning.

The Multiload feature requires a specific layout and format.  Microsoft Excel & Text files are the only file types that can be processed through the multiload function.

Microsoft Excel

A Microsoft Excel workbook can be used to load multiple periods, categories as well as locations through FDM using the multiload functionality.  The data sheet must contain  5 header definition rows:

  1. The FDM Location name through which the data column will be processed
  2. The FDM Category name through which the data column will be processed
  3. The FDM Period (or Period Key) through which the data will be processed
  4. The Load tags used to tell FDM how to process the data – i.e., merge when importing, replace when loading to the target application
  5. The Dimension/Value tag – each data column will have a ‘V’ while the dimensionality should be assigned in fields without headers 1-4

The excel method requires an Excel named range to be included in the workbook that begins with ‘ups’ and includes each of the rows (including header definition rows) and columns of data that need to be processed.

Text

A text files can be used to load up to 12 periods of data for a single location and category.  As with the Microsoft Excel format, the text file format requires a number of header rows followed by the data.  The 6 header definition rows are:

  1. The FDM Location name through which the data column will be processed
  2. The FDM Category name through which the data column will be processed
  3. The Period key of the first FDM period in the data set
  4. The number of periods in the file
  5. The Load tags used to tell FDM how to process the data – i.e., merge when importing, replace when loading to the target application
  6. The Dimension/Value tag – each data column will have a ‘V’

The below table highlights the pros and cons of the supported file types.

Pros Cons
Excel
  • Supports loading more than 12 periods
  • Can load multiple periods, categories, locations at one time
  • Performance; an individual file is created for each column of data
  • Excel row limits the amount of data that can be processed
Text
  • Performance; a single file is created by FDM for all periods in the file
  • No limit on number of rows
  • Limited to 12 periods of data per file
  • Limited to a single location & category of data

The decision on when to use which file type is going to be driven by the individual needs of each organization. As with much of FDM, the product is flexible as Oracle recognizes that data loading is not a one size fits all process. In my implementation experience, text files are preferred.  The #1 reason is that I highly value performance.

Let’s explore the Excel option for a moment and assume that you haven’t seen the light that is known as Hyperion Planning.  A specific worksheet layout is required.  The chances that any Excel model aligns to the required format is virtually zero (unless you are an existing user of the multiload functionality).  Well that’s ok, you could always create an upload tab and link back to the input sheet.  Yes you could but what happens when Joe user decides he wants to insert a new row/column?

Additionally, Excel 2003 and below only has 65,536 rows (I know that number off the top of my head from my VBA coding days) per sheet.  That means if you have data that exceeds this number of rows, you will need multiple sheets.  “Come on Tony, when am I going to have more than 65k rows of data in Excel?”  Certainly it’s unrealistic that a user would create a budget with that many rows but what if you have a home-grown system for detailed revenue planning and that data needs to make its way into your EPM application via FDM?

Beside the watch-outs & limitations noted above, the biggest reason to favor text formats over Excel is in how FDM handles the data when processing an Excel multiload file.  Each data column gets created and loaded as a single file and, if consolidation is enabled, a single time period is consolidated.  This process repeats for each data column – FDM creating a text file, loading it & running a consolidation.  With the text file option, FDM will generate a single file with all the periods of data, load it and run one consolidation that encompasses all of the time periods.  Text by design is far more efficient.

Regardless of which method you chose, knowing that FDM can support the need to process multiple periods of data in a single process is surely valuable information.  I hope that you explore this functionality and find it to be a benefit in your organization.  As with all of my posts, please feel free to post questions or comments!

This entry was posted in Multiload, Out of the Box Functionality. Bookmark the permalink.

6 Responses to Loading Budget Data

  1. katiemiller48 says:

    Tony,

    I stumbled across this blog after Kscope in June and it’s been a great help! I do have a quick question about multiloads if you could assist. We are working on the budget and utilizing multiload functionality via Excel. Is it possible to do multiloads on multiple tabs? I have successfully loaded data from 2 different tabs, using RZ on the first and AZ on the second. Everything seemed fine. Are there any system limitations on this? Is it recommended to only do 1 tab with 12 months? I appreciate any advice. Thanks!

    • Tony Scalese says:

      Katie,

      I’m glad you have found the blog useful and that shameless plugs at KScope weren’t over the top :)

      Technically what you are doing is supported. You have to make sure that the named ranges are ordered properly to ensure your RZ vs AZ tags get processed properly.

      Personally I shy aware from excel multiload due to the performance limitations but if it is working for you, run with it.

  2. Tony Scalese says:

    You are correct, certain workflow functionality when using multiload is not available. The most important, in my humble opinion, is the intersection validation report which does not execute when processing data via multiload. If there is one thing I want to stress about FDM, if you can verbalize it, there is a good chance you can make FDM do it. Should you is a whole separate question that is outside the scope of this limited comment window.

    As for other functionality, cell text & line item detailed is also limited when using multiload. I find that those are rarely used in FDM so it’s not a real show stopper. The good news is that there is an enhancement request for the Intersection Validation report to display during multiloads. Until then, we need to devise creative ways to address this limitation.

  3. Curro says:

    Congratulations for this post. That’s true that Text-based are preferred but it so difficult to get end-users divorced from Excel…

    Would you mind adding some details about FDM functionalities that don’t apply to Multiload, or apply to Excel and not to Text?. Sometimes is a bit confusing. For example, Cell text Loading, or Intersection Validation Reports, Loading Line Item Details etc. It would be interesting to confirm what can be done with what and in case you need scripts, which would be the best way of enabling the functionality.

    Thanks again!

    • Tony Scalese says:

      Think outside the box. It’s not that you have to divorce them from excel. The mutliload process can be independent of the actual budgeting process. Maybe a macro that takes their Excel input and creates a text file??

      I am not sure I understand what you are requesting. How the application can address needs – for detailed functionality you mention – is generally something that is going to be addressed based on client specific need. I am not sure what type of posting you are hoping to see. Care to elaborate?

      • Curro says:

        As far as I know, when working with HFM, there are some FDM functionalities that are avaialable only for single period loads and not for multiload files (e.g: Intersection Validation Reports).
        I would like to have some feedback from you about this and how this could be addressed (if it’s possible).

        Thanks!

Leave a Reply