loading smartlists into pbcs using fdmee
TRANSCRIPT
Loading Smart Lists using FDMEE
Ray FevrierData Integration Team
March 18, 2016
Confidential – Internal/Restricted/Highly Restricted Slide 2
Smart Lists in VisPlan Application
Account Member in VisPlan
Smart List Value Associated with the
Account
Confidential – Internal/Restricted/Highly Restricted
• Our sample has two data columns or Smart Lists:– Depreciation Method– Depreciation Convention
• Keep in mind, FDMEE can only handle one data column at a time• Smart List textual value have to be converted to a number prior to load (i.e.
SLN = 1)Slide 3
Sample Data
Confidential – Internal/Restricted/Highly Restricted
• In FDMEE, add a dimension for each Smart List values
• Under Data Table Column Name, assign a UD numberto each dimension
• Assign a sequence number to the SL dimensions to ensure they get process first by FDMEE
Slide 4
FDMEE – Target Application Settings
Confidential – Internal/Restricted/Highly Restricted
Under Expression, assign a dummy value to both the Account and Amount field to prevent FDMEE from
erroring out
Slide 5
FDMEE – Import Format Settings
Confidential – Internal/Restricted/Highly Restricted Slide 6
FDMEE - Location
Confidential – Internal/Restricted/Highly Restricted
• Under the Account dimension, create a separate SQL mapping for each SL value• Match each script to a Data Load
Rule (once you have created the DLR), that way each DLR will only run a specific script
Slide 7
FDMEE – Data Load Mapping Settings
Set AMOUNTX to the mapped/target value of the SL dimension (UD19X…)
Confidential – Internal/Restricted/Highly Restricted
Create an explicit mapping for each SL dimension with the SL Name in the source column and the SL ID in the target
Slide 8
FDMEE – Data Load Mapping Settings (Cont’d)
Confidential – Internal/Restricted/Highly Restricted
• Create a Data Load Rule (DLR) for each SL value• The DLRs are
exactly the same, their purpose is to allow FDMEE to process the file as many time as needed
Slide 9
FDMEE – Data Load Rule Settings
Confidential – Internal/Restricted/Highly Restricted Slide 10
Execute the Data Load Rule
Confidential – Internal/Restricted/Highly Restricted
• The SQL script hardcoded the Account member and converted SL from a textual to a numerical value
Slide 11
FDMEE – Data Load Workbench
Confidential – Internal/Restricted/Highly Restricted
• SmatView retrieve before and after the data load
Slide 12
Et Voila…