By Patrick Kiernan
December 22nd, 2017
What do you do when one line item provides more information than only one destination Point of View?
For instance, assume that all of the trial balance accounts from an extract will load to a custom dimension member of “END”. However further assume that the trial balance has multiple members, typically in a custom dimension, for the balance sheet accounts that record movement.
Custom Dimension Hierarchy:
Note on Hierarchy unrelated to FDM: Where “END” as a child of Total_Movement has a Node attribute of 1. Move_Check has a Node Attribute of 0, Move_Dtl through ICO_TRNS have attributes of 1 and END of -1: so that a check of BEG + MOVE – END = 0.
For instance, let’s take a look where the following data points the Equipment account which records both an ending balance as the sum of these data points, as well as providing the movement details.
Furthermore, the beginning balance in HFM is calculated from the prior year end value so this is not to be loaded to BEG, but is required as a part of the END balance. This type of load may seem like a dilemma, as the sum of the data points must load the “END” member and also provide the movement details to the members ADD and RDX.
The solution is in the same in FDM as with FDMEE – Logic Accounts!
First, assume that all the Custom source members for the UnitFlows dimension (Custom 4) are mapped to “END”.
Or using the Like map:
The results of the mapping without a Logic Account setup would result in only targeting the END member, as per the following:
Let’s set up Logic Accounts to create replicated data points that can then also be mapped. Recall we only need the ADD (Additions) and RDX (Reductions).
In the Setup tab, navigate to the Data Load Setup and then select Logic Group. In the Logic Group Summary, select Add.
In the Details section add the definition for the Logic Group. I like to use an “L_NAME” where “L_” indicates a Logic Group. This will be seen later. This will use the Logic Type of “Complex Logic”. Not to worry, it is not complex.
Then in Logic Items, select Add and enter the parameters, and ensure to check the Export box. The Export box is a Yes-No switch that determines whether a logic account is considered an export account and therefore is subjected to the conversion table validation process. If the switch is set to Yes, then you must map the logic account. Click Save.
Once saved, select the pencil icon in the Criteria Value to enter the parameters. It will no longer be gray.
Click on the Add icon to add rows in the Dimension/Criteria. Here since we want the UD4 members for all accounts the selection can be limited to only the UD4 dimension. If this would be limited to specific accounts, then those accounts could also be listed in a Criteria Type of “In” and then in the Criteria Value listed. Such as “Land,Computer,Equipment”. There would then be two lines, Account and then UD4. The following is limited to UD4, since this example file is limited to Equipment and no other accounts.
Click OK and then Save.
Next, this Logic Account Group needs to be assigned to a Location. Navigate to Locations, select the Location and then use the magnifying glass to assign the Logic Account Group to the Location. Save the changes.
Next when going to the Workbench to reprocess the load file, it give a validation error as there are three new records, where the original three have been duplicated, but now with an “L_” prefix as in the UD4 field as defined in the Logic Group.
Opening the file shows these:
So now the “L_ADD” needs to be mapped to “ADD”, “L_BEG” to IGNORE (the beginning balance is a roll-forward rule) and “L_RDX” to RDX”.
And now there are both the Ending balances, accumulated from the BEG, ADD, and RDX, as well as the movement, all without having to do any supplemental data processes. This could also be performed on other types of accounts, for instance if A/R has the 30/60/90/120 balances identified in the G/L extract in combination as the total A/R balance.
Now, one data source can be loaded via one extract file to multiple destinations.