FDM: Loading Data to Multiple Databases Within the Same Application

Although FDMEE is the data management tool of the future for Workspace, it is still lacking some of the basic functionality that can be utilized in FDM classic. One of these issues arose recently on my current project: How can we have 2 separate load rules in FDMEE, but have them each pointing to a separate database in the same application? The answer, it seems, is that you can’t. To begin, let me describe the issue in FDMEE in a little more in depth…

First, in the locations tab, notice that there are 2 separate locations for our planning app (DFPLAN2). IFS_Plan should be pointed to the FinPln database, while RevCOGS should be pointed to the RevCOGS database. Notice that they are both tagged to point to the application DFPLAN2, with no differentiation of databases:

 

The same issue arises in the location details for each location, as there is nowhere to discern between the two databases:

 

This brings up the issue of bringing in the wrong dimensionality  for each of the locations. RevCOGS includes the BusinessUnit dimension, even though its Essbase cube does not have that dimension:

 

And IFS_Plan includes Customer & Product, even though those should be ignored for this database:

 

Upon importing the data to the data load workbench, the data does not validate. The only output given is a couple of blank columns (which doesn’t provide much intuition to go off of). That leads us back to our issue at hand…How can we distinguish between the 2 plan types, so that we have the correct dimensionality for our data loads? The simplest solution that we found is to create another Target System Adapter. This is done via the FDM Workbench on the FDM server.

 Log on to the server and open the workbench. Once in the workbench you will see your Target system adapters:

 

To copy an adapter, right click and select “Copy…”. Name the adapter (here we have named ours Essbase2)

 

Expand on the adapter and expand on the dimensions folder. The activated dimensions will be black, while the non-active dimensions are greyed out. Notice that each database has a different set of activated dimensions, and how the user-defined dimensions (UD1, UD2, etc) are customized for both.

 

Since each database has different dimensionality, each adapter will have a unique set of activated dimensions. To edit which dimensions are active, right click on the desired dimension and select “Properties…”

 

In the properties screen, the name and alias of the dimension are customizable. Make sure that these match up to each database’s dimensionality in Essbase. Down below are 2 checkboxes. One activates the dimension, the other notes whether or not the dimension is a required field. Leave a checkmark next to the “Active” box for all dimensions in the database.

 

Next, right-click on the adapter itself, and select “Adapter Options”. From the dropdown, select “Essbase DB Name”. Here is where to input the relevant database name, so that FDM will know which it is pointing at during the import process:

 

Notice that we identified a different database for both of the Target system adapters (RevCOGS & FinPln):

Now that we have made that change on the FDM server, we will see those changes take affect when we look at the import formats for both RevCOGS and IFS_Plan in FDM Classic. UD2 (Source Custom2) is Product and UD3 (Source Custom3) is Customer. They are being picked up from column 1 and column 2 (as noted by the field number column below) of our load file, respectively:

For IFS_Plan, BusinessUnit is UD2 (Source Custom2) and is grabbed from column 5 in our data file:

To conclude, we were successfully able to distinguish between 2 databases in 1 application. Remember, this was only necessary because the databases had different dimensionality.  We were not able to do this in FDMEE, rather in FDM Classic, which means we cannot load more than 1 period at a time. That is the downside to this solution, but until Oracle includes the functionality in FDMEE, it seems to be our best option.




Financial Reporting with Rolling Years and Periods (Step 4 of 4)

Step 4: Adding ‘Advanced Suppression’ to each of the Year & Period columns.

Step 4 in the development of this report contains a majority of the logic to be setup which will allow a range of periods to be displayed to users. The idea behind the logic in this section is to move the range of periods displayed to users based on the Period selected in the User POV. The “Range Matrix” below will shed some light on what should be displayed based on what is selected.

Just as Conditional Suppression was setup for the trigger columns, Conditional Suppression will need setup for these Year/Period columns. The difference between the “Trigger” section and the “Year/Period” section resides on how columns are chosen to be suppressed. As the name suggests, the “Trigger” section added in steps 1 & 2 will drive the conditional logic, and thus the range of Periods displayed to users. The examples below display a high-level subset of the column logic.

Example 1:

  • User selects “Jan” as the Period.
    • Which Periods will be displayed?
    • Sep (Prior Year)
    • Oct (Prior Year)
    • Nov (Prior Year)
    • Dec (Prior Year)
    • Jan (Current Year)
    • Which Periods will be hidden (suppressed)?
    • Feb-Dec (Current Year)

 

Example 2:

  • User selects “Sep” as the Period.
    • Which Periods will be displayed?
    • May (Current Year)
    • Jun (Current Year)
    • Jul (Current Year)
    • Aug (Current Year)
    • Sep (Current Year)
    • Which Periods will be hidden (suppressed)?
    • Sep-Dec (Prior Year)
    • Jan-Apr (Current Year)
    • Oct-Dec (Current Year)

 

When adding columns to a report, each column will be tagged with an alphanumeric value that identifies the column number. Staying true to the rolling 5-month solution, columns “A” through “L” of your report identify the “Trigger” section (Jan equals “A”, Feb equals “B”… Dec equals “L”). The “Year & Period” section is identified by columns “M” through “AB” of your report (Sep of Prior Year equals “M”, Oct of prior year equals “N”… Dec of current year equals “AB”). When setting up the “Year & Period” Conditional Suppression, it is imperative that you know and understand which Periods correlate to which column numbers.

“Trigger” Section:

“Year & Period” Section:

The Conditional Suppression will need added to all “Year & Period” section columns (columns “M” through “AB” in the above images). Column “M” (which correlates to “Sep” of prior year) will need displayed to the user ONLY when the user selects “Jan” for the current POV of the Period dimension. By selecting “Jan”, the user is requesting to see data for Sep-Dec of the Prior Year, and Jan of the current year (as shown above in the “Range Matrix”). A subset of the Hyperion Reporting logic is shown in the image below. Similar logic is required for the remaining columns of the “Year & Period” section (columns “N” through “AB”) with the only difference being the suppressed “Trigger” columns selected.

Hyperion Reporting – Conditional Suppression Logic:

 

Year & Period Suppression Logic:

 

As stated before, the “Trigger” section of the report drives what is ultimately displayed to the user, and this is based on what the user selects in the User POV for Period. If a report requirement exists for something other than a 5-month rolling view, the number of “Year & Period” section columns would need adjusted, as would the Conditional Suppression logic, but the “Trigger” section will not need adjusted. The overall idea of how to implement this solution remains intact. Please feel free to contact me directly with any questions on implementing a solution such as this, I’m happy to assist when possible.

 




Financial Reporting with Rolling Years and Periods (Step 2 of 4)

Step 2: Adding ‘Advanced Suppression’ to each of the 12 Trigger columns.

The Conditional Suppression set on each of these columns (see Step 1) will suppress the column that correlates to the Period selected. If the end-user selects Jan, then the column representing Jan will be suppressed. This is used later in step 4 of the report development.

Keys:

  • The Advanced (Conditional) Suppression for each column relates to the 12 Periods added in Step 1.
  • The logic for Jan is as follows:
    • Suppress Column If:
      • “Member Name” “Period” “equals” “Jan”.
      • “Jan” is the actual member name.
  • The same logic in place for Jan will be required for the Feb-Dec columns, Thus…
    • Suppress Column If:
      • “Member Name” “Period” “equals” “Feb”.
      • “Feb” is the actual member name.
      • Etc…
  • Once steps 1 & 2 are complete, development of the trigger section has been finished.

 




Financial Reporting with Rolling Years and Periods (Step 1 of 4)

If you have worked with Hyperion Financial Reporting, odds are good that you’ve come across report requirements for Rolling Years and Periods, that is to say a report that spans periods from multiple years. This is an easy build if your data source includes a single “rolling” dimension for ‘Year’ and ‘Period’ – you utilize the “RelativeMember” function to move up or down the hierarchy. The difficulty arises when programming this same logic into a report with separate ‘Year’ and ‘Period’ dimensions. A solution utilizing “Trigger” columns is shown below.

The use of what I call trigger columns allows for Hyperion Financial Reporting to display rolling Years and Periods, whether your requirement is for a 2 or 12 month rolling report. The Trigger section of the report requires both the ‘Year’ and ‘Period’ dimension to be columns on the report, while any dimension; such as Account or Product, can be included on the rows. The steps below detail a rolling 5-month solution.

Step 1:  Add data columns for all 12 periods (Jan-Dec).

These columns will function as the Trigger section, essentially telling the other columns what the end-user selected for Period. This is important because later sections of the report require knowledge of the selected Period to ultimately decide what is displayed.

Keys:

  • These 12 columns MUST be Data columns.
  • These 12 columns MUST be hidden.
  • The Period member selection MUST be set to “Current Point of View for Period”.
  • The Year member can be any member – not relevant to the trigger.

  • Optional: Overwrite the Period POV cell with the 12 periods (Jan-Dec) as shown below.