PBCS - FDMEE - Multi Period Load Question(s)... Frustrated beyond belief

elwayisgod

Hi,

When loading multi period load when data is in this format:

Account,CostCenter,Employee,Entity,Job,Location,Property,Version,BegBal-19,1/1/2019,2/1/2019,3/1/2019,4/1/2019,5/1/2019,6/1/2019,7/1/2019,8/1/2019,9/1/2019,10/1/2019,11/1/2019,12/1/2019,BegBal-20,1/1/2020,2/1/2020,3/1/2020

OWP_Basic Salary,96510,8672,1,HR Training & Development Dir,0,OWP_Value,OEP_Working,,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,,90738.34,90738.34,90738.34

Is there any 'magic' that needs to happen.  I have read JG's blog, but it's for a load where it's a single column of data and Period and Year are moved to the rows.  Is there any blog etc. out there that explains how to get this file format to load?

Currently right when we try the Import step it fails instantly:

2019-02-26 17:00:17,131 INFO  [AIF]: FDMEE Process: 802, Log Level: 4

2019-02-26 17:00:17,131 INFO  [AIF]: Location     : WFP (Partitionkey:32)

2019-02-26 17:00:17,131 INFO  [AIF]: Period Name  : Jan-18 (Period Key:1/31/18 12:00 AM)

2019-02-26 17:00:17,131 INFO  [AIF]: Category Name: OEP_Forecast (Category key:2)

2019-02-26 17:00:17,131 INFO  [AIF]: Rule Name    : OEP_Forecast (Rule ID:65)

2019-02-26 17:00:20,430 INFO  [AIF]: FDM Version: 11.1.2.4.230

2019-02-26 17:00:20,430 INFO  [AIF]: Log File Encoding: UTF-8

2019-02-26 17:00:21,685 INFO  [AIF]: -------START IMPORT STEP-------

2019-02-26 17:00:22,249 INFO  [AIF]: File Name: Test_Labor_Laod_2_26_2019Jan-18.csv

2019-02-26 17:00:22,561 INFO  [AIF]: EPMFDM-140274:Message - LOADID:PARTKEY:CATKEY:RULEID:CURRENCYKEY:FILEPATH:IMPGROUPFILETYPE::802;32:2:65:[NONE]:/u03/inbox/:MULTIPER

2019-02-26 17:00:22,573 INFO  [AIF]: EPMFDM-140274:Message - File CharEncoding as per settings: UTF-8

2019-02-26 17:00:22,573 INFO  [AIF]: EPMFDM-140274:Message - ImportTextData - Start

2019-02-26 17:00:22,722 ERROR [AIF]: Error opening file.

2019-02-26 17:00:22,798 INFO  [AIF]: EPMFDM-140274:Message - Initialize error maps

2019-02-26 17:00:22,798 INFO  [AIF]: EPMFDM-140274:Message - File Name Test_Labor_Laod_2_26_2019Jan-18.csv

periodKey2018-01-31

2019-02-26 17:00:22,804 ERROR [AIF]: EPMFDM-140274:Message - null

2019-02-26 17:00:22,812 ERROR [AIF]: Unexpected error: <type 'java.lang.RuntimeException'>

java.lang.RuntimeException

2019-02-26 17:00:22,813 FATAL [AIF]: Error in FileData.importData

Traceback (most recent call last):

  File "<string>", line 96, in importData

RuntimeException: java.lang.RuntimeException

2019-02-26 17:00:22,939 FATAL [AIF]: Error in File Import Data

2019-02-26 17:00:22,947 INFO  [AIF]: FDMEE Process End, Process ID: 802

On the 'Data Load Rule' setup.  The 'File Name Suffix Type' is grayed out but it's populated with 'Period Name' value.  Do I have to use that or should that be blank and I'm stuck and have to create a new Data Load Rule?

Anyways, appreciate any insight, the Oracle documentation is fricking terrible and can't find a real blog on how to do this.

Thanks!!!

JohnGoodwin

Yes it is possible to load Smart List and Dates using all data load types, more info - More to life...: Loading non-numeric data to planning using FDMEE

You can load data from the workbench but it will only load for the period that has been set in the POV bar.

JohnGoodwin

Here is an example,

Source file

Import format

File Type = Multi Column - Numeric Data

Period columns have been set in the expression for Amount.

Then running the rule from Jan-Dec will pick up the data from the columns

Cheers

John

elwayisgod

File Name Suffix field is not NULL and is grayed out.  How in the world can I clear that out.  I have EXACTLY what your screenshots show BTW.

JohnGoodwin

I don't see that.

Maybe you originally set it and then change the import format type, as with multi column types it is disabled.

You can always create a new rule.

elwayisgod

I did start to create a new rule and that field is not populated and is blank.  I believe that is good news.  I will find out shortly.

Can I load SmartList and Date values in same file?  Or do I have to break those out into separate source files?  Or is it not possible to even load SmartList and Dates?

Another thing.  On multi period load should I be able to manually click on the 'Import' fish and have it populate below so I can see if the mappings etc are working correctly?

TY

JohnGoodwin

Yes it is possible to load Smart List and Dates using all data load types, more info - More to life...: Loading non-numeric data to planning using FDMEE

You can load data from the workbench but it will only load for the period that has been set in the POV bar.

elwayisgod

Good to know. We were getting some cryptic error about Workbench was disabled.   Good lord this tool makes my head hurt. 

We did change our source data file to put Periods and Years in the row and only have a single column of data.  I think that's the way to go, but not sure if I'm missing some key feature of loading multi periods in multiple columns of data.  Any insight?

On the SmartList and Text load, do you recommend two different source files?  One for numeric data and one for non numeric?

JohnGoodwin

I have provided an example of how to load with period in the columns.

It is possible to load non-numeric data and numeric data together using all data type, though with the all data type if you have multiple records against the same POV it will not sum them and take the max value, also it is quicker to load numeric data through a numeric load as it is loading directly to Essbase using a load rule, all data type uses the planning OLU, so it can make more sense to keep them separate but it is up to you.

elwayisgod

If I run via the Data Load Rule and only upto the Import step.  I get:

[TC] - [Amount=NN] Account,CostCenter,Employee,Entity,Job,Location,Property,Version,BegBal-19,1/1/2019,2/1/2019,3/1/2019,4/1/2019,5/1/2019,6/1/2019,7/1/2019,8/1/2019,9/1/2019,10/1/2019,11/1/2019,12/1/2019,BegBal-20,1/1/2020,2/1/2020,3/1/2020

Rows Loaded: 45

Rows Rejected: 1

If I click the Import fish to try and bring the data into the preview screen with Jan-19 as my period which is my 2nd data column, I get:

[TC] - [Amount=NN] Account,CostCenter,Employee,Entity,Job,Location,Property,Version,BegBal-19,1/1/2019,2/1/2019,3/1/2019,4/1/2019,5/1/2019,6/1/2019,7/1/2019,8/1/2019,9/1/2019,10/1/2019,11/1/2019,12/1/2019,BegBal-20,1/1/2020,2/1/2020,3/1/2020

[TC] - [Amount=NN] OWP_Basic Salary,96510,8672,1,HR Training & Development Dir,0,OWP_Value,OEP_Working,,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,90738.34,,90738.34,90738.34,90738.34

[TC] - [Amount=NN] OWP_Basic Salary,80105,4462,1,SD Sr Systems Analyst,0,OWP_Value,OEP_Working,,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,90634.88,,90634.88,90634.88,90634.88

[TC] - [Amount=NN] OWP_Basic Salary,80510,8632,1,CO Infrastructure Lead,0,OWP_Value,OEP_Working,,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,88628.8,,88628.8,88628.8,88628.8

Rows Loaded: 0

Rows Rejected: 4

That's what's throwing me off, I want/need to see it in the preview screen after the import step.  Maybe I'm chasing ghost here.....

JohnGoodwin

If you load from the workbench then it needs to be the first period column in the POV, because that is the first in the column list defined in the import format and that is where it will always start from. Though in theory if the import format has been defined correctly you shouldn't get errors, it will just load data from the first column.

Why not just run an import from the data load rule and then check the workbench, it does not take much.

There are other options using the driver expression which is explained with examples in Loading Multi-Column Numeric Data

Robert Angel

The workbench is very useful for getting to the bottom of your final issues, it provides a view of before / after column states as a result of your mappings and you can utilise it to just show you the error lines, then jump to correct mappings, try a step over and iterate until you have a wholly good load.

So in your case by going to workbench and changing the view to see errors only you will see your four lines and you will be able to fix them.

It is a good deal less painful than trying to find the errors in the often massive log files where the noise to truth ratio is considerable.