BPC 10 MS: Load multiple time periods with MVAL - Transformation File

[Former Member]

Hello Experts,

Could you please help me?

My client wants to import data to a input schedule, through the transformation file in the followinf format:

ACCOUNT ENTITY KEY_FIGURE YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

AAAAA01  P15      QUANTITY      2013   10   20    30     40     50     60   70   80    90     100   120  300

H1: I tried to apply the MVAL function with the option (recommended by another user of SDN)

TIME=*MVAL (JAN | *IF(*STR(1) = *STR(1) THEN YEAR+*STR(.JAN)) || FEB | *IF(*STR(1) = *STR(1) THEN YEAR+*STR(.FEB)).....)

But this option, returns an error "Conversion from string "JAN | *IF*STR(1)" to an integer not valid".

H2: I have also tried to use the option:

TIME=*MVAL(6:17)

To use this option is mandatory to use the conversion file time.xls (according to the BPC manual).

Since I cannot read the column YEAR within my conversion file, how can I do this dinamically?

The first option worked in BPC 10 NW version, but in this MS version it doesn't.

Thank you for your help

Raquel

[Former Member]

Check your import file. I've run into this bug. It seems an import will fail for P12 if there is no delimiter after the 12 month, so if it's the last column that could be your issue. I opened my file in excel and put a single quote in column 18 and now the file saves with a delimiter after the last column and the import is successful. I was also able to move another dimension to the last column instead of it being DEC/P12 and that also eliminated the issue in case you have something that creates the flat file and can simply be reordered.

[Former Member]

Hi Raquel,

I have the same situation just like yours my flat file structure is like below:

Company -  CostCenter - Fiscal.Period - 01-02-03-04-05-06-07-08-09-10-11-12

xxxxx               yyyy               2015             5    6   7     6 .........................................

I have applied your solution but it throw me an error that says :

After this error I have changed the time as ;

MONTH_YEAR =*MVAL (*COL(4) |  *COL(3,1:4)+*STR(.01) || *COL(5) | *COL(3,1:4)+*STR(.02) || *COL(6) | *COL(3,1:4)+*STR(.03) || *COL(7) | *COL(3,1:4)+*STR(.04) || *COL(8) | *COL(3,1:4)+*STR(.05) || *COL(9) | *COL(3,1:4)+*STR(.06) || *COL(10) | *COL(3,1:4)+*STR(.07) || *COL(11) | *COL(3,1:4)+*STR(.08) || *COL(12) | *COL(3,1:4)+*STR(.09) || *COL(13) | *COL(3,1:4)+*STR(.10) || *COL(14) | *COL(3,1:4)+*STR(.11) || *COL(15) | *COL(3,1:4)+*STR(.12))

And I have added   "Fisc" to SKIPIF = in transformation.

This solution have worked for me.I hope it helps,

Onur Timur

[former_member186498]

Hi Raquel,

in your example the time inteval is MVAL(5:16)

"Since I cannot read the column YEAR within my conversion file, how can I do this dinamically?"

can you please explain better the issue?

Please share your transformation and conversion file.

Regards

     Roberto

[Former Member]

Hello Roberto,

Yes it is. You're right.

My transformation file is:

*OPTIONS

FORMAT = DELIMITED

HEADER = NO

DELIMITER = ;

SKIP = 1

SKIPIF =

VALIDATERECORDS=NO

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

Account=Account

Entity=Entity

Key_Figure=Key_Figure

Time=*MVAL(5:16)

*CONVERSION

When I say that the conversion file is not dinamic, I mean that I cannot do something like:

Internal                 External                       Formula

JAN              YEAR+*STR(.JAN)

Or can I? Someother way to do this?

Thank you

Best regards,

Raquel

[former_member186498]

Hi Raquel,

I'm not sure you can obtain a conversion for the time combined with a MVAL.

If you could have year+month together you could try using a conversion file where

EXTERNAL                  INTERNAL                            FORMULA

*.10                              *.JAN

*.20                              *.FEB

etc.

or you could try in transformation Time=COL(4) + *MVAL(5:16)

or you have to use a conversion file putting

EXTERNAL                  INTERNAL                            FORMULA

10                              2014.JAN

20                              2014.Feb

etc. without using the Year field (COL4) of the input file

or you have to split the input file in 12 records and just changing the transformation with

TIME=COL(4) + COL(5)

the first and the last two choises work, for the second option you have to try.

Regards

     Roberto

[Former Member]

Hello Roberto,

Could you please explain your conversion file?

EXTERNAL                  INTERNAL                            FORMULA

*.10                              *.JAN

*.20                              *.FEB

I didn't understand quite well.

In the meantime I tried the second option, and it didn't work out. The error was:

"Index was outside the bounds of array "

Thank you

Best regards,

Raquel

[former_member186498]

Hi Raquel,

sorry for the delay but today the mail-feedback doesn't work and so I missed your request until now

Could you please explain your conversion file?

EXTERNAL                  INTERNAL                            FORMULA
*.10                              *.JAN
*.20                              *.FEB

I didn't understand quite well.

If you could have year+month together you could try using a conversion file where

if the input file has this form, e.g. 2014.10, 2014.30 this conversion leave the year and substitue 10 with Jan, etc.

EXTERNAL                  INTERNAL                            FORMULA

*.10                              *.JAN

*.20                              *.FEB

etc.

For the error in the second option I don't know the sp of your bpc 10 version, so see please

1646100 - MVAL and other specific mapping combinations

1939997 - BPC MS: ERROR: "Index was outside the bounds of the array" when running the import package...same as 1945804 - Transaction data import failed with MVAL function

Regards

     Roberto

[Former Member]

Hello Roberto,

Thank you so much for your replies

Our system components are:

- BPC release 10.0.12.0

- EPM Add-in 10 SP 15 Patch 2 .NET 3.5

Yesterday I opened a incident with SAP, because I have indeed tried your option, with the column headers equal to the members of dimension (TIME 2014.JAN...2014.DEC) but without conversion file.

I read your notes and unfortunately in this SP12 all should be fixed. Lets hope that this is not another a bug.

The weirdest thing happened when all records are accepted, except the last record because the time value that the system assumes is P12 (for example if the last column is 2014.DEC).

Thank you.

Best regards,

Raquel

[former_member186498]

Hello Raquel,

please share your last transformation and input files.

Regards

     Roberto

[Former Member]

Hello Roberto,

SAP hasn't respond to my message yet.

Please find the document that I've attach to the post. The files are the same. In this document you can see the log errors and the result of the rejected records.

Thank you.

Best regards,

Raquel Oliveira

[former_member186498]

Hi Raquel,

sorry but your file contains only partial screenshot, so the files are incomplete.

As I see this seems not an issue, it seems only that one records contains one or more dimension values not present in the master data or in the conversion file and/or transformation file, because you use HEADER=YES.

If you click on yes in this screenshot bpc should open the conversion file that has the rejected value and you can complete the line with the internal value or add it in the master data if necessary.
I see also some strange things, you wrote HEADER=YES and you have an input file but in the portion of transformation file I see you're using constants in the mapping region so the input file is unseful.

with HEADER=YES you can skip the name if the same or write

CATEGORY=CATEGORY,

CURRENCY=CURRENCY etc.

Regards

     Roberto

[Former Member]

Hello Roberto,

Maybe I haven't been clear. In my previous posts, I mencioned that I don't have a conversion file. I don't need a convesion file because my columns are the members of the TIME dimension, so the value that is in the header is the dimension member.

The reason why I have some transformation lines with *STR() is because I know that this dimension members are constants, and my key user doesn't need to fill all dimension in the source file, only the dimensions that can have multiple values.

As obvious, the month DEC exists in the TIME dimension, and the error occurs because the transformation is assuming P12 as a value in this month.

As I also mentioned in my previous posts, the error appears for every columns that are in last, if I remove the column DEC, the error appears with NOV. And NOV also exists in the TIME dimension.

Hope I've been clear. A soon as I can I will post the transformation and input files. I don't have .

Thank you.

Raquel

[Former Member]

Hello Roberto,

I have been talking with SAP on the phone.

Did you ever implemented a requisite like the one I'm posting here?

Is it possible to use the *MVAL option without the conversion file (in the BPC 10 NW I know its possible I have already implemented it).

Thank you

Best regards,

Raquel Oliveira