cancel
Showing results for 
Search instead for 
Did you mean: 

Flat file Import with multiple years of budget data

srinivas_thota5
Participant
0 Kudos

Hello,

We are planning to use flat file to upload budget values into BPC cube using standard Import package and user wants to budget for more than one years.

User propsed a file format which is handy for them while entering budget values. In his format all 12 months are in columns and year as row. I know we can use MVAL funtion to handle multiple key figures to upload but is there any function or workaround in transformation file to handle to upload this kind of file without using BADI or excel macros.

PROJECTVersionYEARJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTotal
PRJ01Budget201312356788899894556409
PRJ01Budget2014234678989100904657421

Thanks,

Srinivas Thota

Accepted Solutions (0)

Answers (6)

Answers (6)

Former Member
0 Kudos

Hi Guys,

What about the BLANK months? Did anyone of you experience differences loading 0 or blanks? In my case, if i have a blank for any month, system is loading a 0 instead of rejecting that missing number.

In BPC7, we used that rejections to avoid modifications on certain months. But now, in EPM10, if we load blanks, they turn into 0.

Thanks in advance

Luisma

Former Member
0 Kudos

Hi all,

I have found a workaround to this issue that works. You can try something like:

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

Using a dummy *IF allows to concatenate year ant the string for the month. Hope we can have a offcial solution from SAP to this issue.

Thanks,

CarlosE

Former Member
0 Kudos

Hi,

Please try

TIME=*MVAL(*COL(4)|*STR(*COL(3))+*STR(.JAN)......

Regards,

Kalyan.

Former Member
0 Kudos

Hi Kalyan:

I ve tried that but this is the result:

[line 23] Unexpected  '*COL' at position 23 in the mapping formula [*MVAL(*COL(10) | *STR(*COL(9)) + *STR(.01) || *COL(11) | *STR(*COL(9)) + *STR(.02) )]

It looks like it s a program error.

regards

mah

Former Member
0 Kudos

Hi Srinivas,

I spent a lot of time on this today, but could not find solution, as per document year+*STR(.01) should work, but it does not. SAP has to look into it and provide solution.

But, in the meantime u may implement workaround like BADI or VBA to process the file and make it ready for load. 

Regards,

Kalyan.

Former Member
0 Kudos

Srinivas,

There are several ways to do this but I think the most straight forward is as follows...

  1. Set the transformation header option to no (HEADER=NO)
  2. Set the transformation skip option to one to skip the header record (SKIP=1)
  3. Map your project and version fields by using *COL(1) and *COL(2) respectively
  4. For the time dimension use *MVAL like this...
    • TIME=*MVAL(*COL(4)|*COL(3)+*STR(.JAN)||*COL(4)|*COL(3)+*STR(.FEB)...)   "..." means continue function for the rest of the months
    • This function will concatenate the year and the hardcoded month
  5. Map the rest of your fields, which I assume are hardcoded.

Below is an example of the complete transformation file.

*OPTIONS
FORMAT = DELIMITED
HEADER = NO
DELIMITER = ,
AMOUNTDECIMALPOINT = .
SKIP = 1
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=YES
MAXREJECTCOUNT=0
ROUNDAMOUNT=
*MAPPING
PROJECT=*COL(1)
VERSION=*COL(2)
TIME=*MVAL(*COL(4)|*COL(3)+*STR(.JAN)||*COL(5)|*COL(3)+*STR(.FEB)||*COL(6)|*COL(3)+*STR(.MAR)||*COL(7)|*COL(3)+*STR(.APR)||*COL(8)|*COL(3)+*STR(.MAY)||*COL(9)|*COL(3)+*STR(.JUN)||*COL(10)|*COL(3)+*STR(.JUL)||*COL(11)|*COL(3)+*STR(.AUG)||*COL(12)|*COL(3)+*STR(.SEP)||*COL(13)|*COL(3)+*STR(.OCT)||*COL(14)|*COL(3)+*STR(.NOV)||*COL(15)|*COL(3)+*STR(.DEC))
ACCOUNT=*NEWCOL(A_56790873)
DATASRC=*NEWCOL(UPLOAD)
ENTITY=*NEWCOL(CC_22221)
*CONVERSION

This is a working example based on SAP BPC 7.5 NW SP11

Documentation used to created *MVAL function

http://help.sap.com/saphelp_bpc75_nw/helpdata/en/b8/a76a1ca9ac4ca698259a8ff397bb61/frameset.htm

srinivas_thota5
Participant
0 Kudos

Hi Jonathan,

Thanks for the detailed information,

I tried this option but i am not able to validate transformation file. I am getting below error when I am validating without transaction data.

Its not able to validate the "+" in the syntax.

[line 16] Unexpected '+' at position 23 in the mapping formula [*MVAL(*COL(4)|*COL(3) + *STR(.JAN)||*COL(4)|*COL(3) +*STR(.FEB))]: '')'' is expected

Any inputs?

Former Member
0 Kudos

Hi Srinivas, I m facing the same issue on validating "+". I was wondering if you were able to solve it.

regards

jav

Former Member
0 Kudos

What version and platform of BPC?

Former Member
0 Kudos

BPC NW 10 (SAPK-80102INCPMBPC)

regards

jav

Former Member
0 Kudos

Hi,

Seems its issue with BPC 10, right now I am on BPC 10 NW and SAP prodcut team is looking into this issue. I am waiting for their reply.

In mean time we used worked with start routine BADI.

Thanks,

Srinivas THota

Former Member
0 Kudos

Please post what you find out from SAP.  My code above is a working example in NW 7.5 SP11. 

Former Member
0 Kudos

Hi Srinivas,

I am having the same issue you had about using '+' inside MVAL, did SAP Support found any solution for this issue? I would really appreciate if you could give me any advice.

Thanks,

CarlosE

Former Member
0 Kudos

Hi Srinivas,

BPC have a function called MVAL where you can use to catch values reading the time in the header. So instead of you write, ig. 12 lines (considering a year) you can write only a one line, putting the period in the header.

In your case, you can try using *MVAL for 24 periods.

Your file will have the following format:



















































PROJECTVersion2013.JAN2013.FEB2013.MAR2013.APR2013.MAY2013.JUN2013.JUL2013.AUG2013.SEP2013.OCT2013.NOV2013.DEC2014.JAN2014.FEB2014.MAR2014.APR2014.MAY2014.JUN2014.JUL2014.AUG2014.SEP2014.OCT2014.NOV2014.DEC
PRJ01Budget12356788899894556234678989

So in your transformation file you mapp:

Time = *MVAL(3:26)

Please refer:

http://help.sap.com/saphelp_bpc70/helpdata/en/5d/9a3fba600e4de29e2d165644d67bd1/content.htm

Best Regards,

Lucas

former_member192509
Participant
0 Kudos

Hi Shinivas.

The user can wish upload the file in that format, but is recomended that the user upload the file in the traditional way, because  the transformation file is easier to maintain, configure and enlarge, so in this case, you can advise it to use dynamics tables to transform this format:

PROJECTVersionYEARJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDECTotal
PRJ01Budget201312356788899894556409
PRJ01Budget2014234678989

In this Format:

PROJECTVERSIONYEARMONTHVALUE
PRJ01Budget2013JAN1
PRJ01Budget2013FEB2
PRJ01Budget2013MAR3
PRJ01Budget2013APR4

Kind Regards.

Alejandro