How to load several transformation files with a single action

[Former Member]

Hi everybody,

We are loading data from BI cube into BPC cube. We are working on SAP BPC 7.0 version and we have designed several transformation files in order to load each key figure we need.

Now, we want to load all the transformation files executing only one action. Which one is the best way to do it?

We thought that it would be possible to build a single process chain, where we would call the target cube and all the transformation files. In this way, the administrator only has to execute once a package that would execute the process chain. We don't want the administrator to execute several times a package looking for the different transformation files.

How can we do it? Is there any example or document related to it?

Any idea out there?

Kind regards

Albert Mas

[Former Member]

Thanks!

[Former Member]

Hi Albert,

Within one BPC Transformation file you can have multiple ROUNDS

Each ROUND is the combination of a *OPTIONS / *MAPPING / *CONVERSION block.

One Transformation file can have as many ROUNDS as you would like.

Try combining these various transformations into one and see if this gets you the behavior that you desire.

Cheers, Scott

[Former Member]

Hi Scott,

I think that your answer can be very useful. How can I combine two transformation files in one, in order to create two rounds?. I've tried it in different ways, but I've always a Validation error.

Can you help me?

These are my two Transformation files:

-> First Transformation File:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER =

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=NO

MAXREJECTCOUNT=

ROUNDAMOUNT=

SELECTION = Z_VERSION, 010; 0DIVISION, 01;0FISCVARNT, VE;0CURRENCY, EUR; 0FISCYEAR, 2010;0MATL_TYPE, CIAL;0MATL_TYPE, ARDA;0BUS_AREA, 19;0BUS_AREA, 60;0BUS_AREA, 61;0BUS_AREA, 62;Z_SDADPA, NCA;0FISCPER, 2010.001;0MATERIAL, V1ET01

*MAPPING

ACCOUNT_SIM=*NEWCOL(V005)

C_CATEGORY=Z_VERSION

ENTIDAD=*NEWCOL(ENT_NA)

MERCADO=IF(0DISTR_CHAN=STR(UE) THEN STR(UE_)&0COUNTRY;0DISTR_CHAN=STR(NC) THEN 0DISTR_CHAN;0DISTR_CHAN=*STR(2P) THEN *STR(2P_)&Z_KDKG1;0DISTR_CHAN)

PRODUCTO=0MATERIAL

P_DATASRC=IF(0BUS_AREA=STR(19) THEN STR(NCA);0BUS_AREA=STR(60) THEN STR(NCA);0BUS_AREA=STR(61) THEN STR(DAI);0BUS_AREA=STR(62) THEN *STR(DAI))

TIEMPO=0FISCPER

SIGNEDDATA=0G_AVV005

*CONVERSION

TIEMPO = TIEMPO.xls

ACCOUNT_SIM = ACCOUNT.xls

MERCADO=MERCADO.xls

-> Second Transformation File:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER =

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=NO

MAXREJECTCOUNT=

ROUNDAMOUNT=

SELECTION = Z_VERSION, 010; 0DIVISION, 01;0FISCVARNT, VE;0CURRENCY, EUR; 0FISCYEAR, 2010;0MATL_TYPE, CIAL;0MATL_TYPE, ARDA;0BUS_AREA, 19;0BUS_AREA, 60;0BUS_AREA, 61;0BUS_AREA, 62;Z_SDADPA, NCA;0FISCPER, 2010.001;0MATERIAL, V1ET01

*MAPPING

ACCOUNT_SIM=*NEWCOL(V007)

C_CATEGORY=Z_VERSION

ENTIDAD=*NEWCOL(ENT_NA)

MERCADO=IF(0DISTR_CHAN=STR(UE) THEN STR(UE_)&0COUNTRY;0DISTR_CHAN=STR(NC) THEN 0DISTR_CHAN;0DISTR_CHAN=*STR(2P) THEN *STR(2P_)&Z_KDKG1;0DISTR_CHAN)

PRODUCTO=0MATERIAL

P_DATASRC=IF(0BUS_AREA=STR(19) THEN STR(NCA);0BUS_AREA=STR(60) THEN STR(NCA);0BUS_AREA=STR(61) THEN STR(DAI);0BUS_AREA=STR(62) THEN *STR(DAI))

TIEMPO=0FISCPER

SIGNEDDATA=0G_AVV007

*CONVERSION

TIEMPO = TIEMPO.xls

ACCOUNT_SIM = ACCOUNT.xls

MERCADO=MERCADO.xls

Which one would be the final Transformation File

Thanks in advance,

Albert Mas

[Former Member]

Hi Albert,

Put them into one transformation file, put all the content you put below into ONE SINGLE transformation file.

*OPTIONS
FORMAT = DELIMITED
HEADER = YES
DELIMITER =
AMOUNTDECIMALPOINT = .
SKIP = 0
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=NO
MAXREJECTCOUNT=
ROUNDAMOUNT=
SELECTION = Z_VERSION, 010; 0DIVISION, 01;0FISCVARNT, VE;0CURRENCY, EUR; 0FISCYEAR, 2010;0MATL_TYPE, CIAL;0MATL_TYPE, ARDA;0BUS_AREA, 19;0BUS_AREA, 60;0BUS_AREA, 61;0BUS_AREA, 62;Z_SDADPA, NCA;0FISCPER, 2010.001;0MATERIAL, V1ET01
*MAPPING
ACCOUNT_SIM=*NEWCOL(V005)
C_CATEGORY=Z_VERSION
ENTIDAD=*NEWCOL(ENT_NA)
MERCADO=IF(0DISTR_CHAN=STR(UE) THEN STR(UE_)&0COUNTRY;0DISTR_CHAN=STR(NC) THEN 0DISTR_CHAN;0DISTR_CHAN=*STR(2P) THEN *STR(2P_)&Z_KDKG1;0DISTR_CHAN)
PRODUCTO=0MATERIAL
P_DATASRC=IF(0BUS_AREA=STR(19) THEN STR(NCA);0BUS_AREA=STR(60) THEN STR(NCA);0BUS_AREA=STR(61) THEN STR(DAI);0BUS_AREA=STR(62) THEN *STR(DAI))
TIEMPO=0FISCPER
SIGNEDDATA=0G_AVV005
*CONVERSION
TIEMPO = TIEMPO.xls
ACCOUNT_SIM = ACCOUNT.xls
MERCADO=MERCADO.xls
*OPTIONS
FORMAT = DELIMITED
HEADER = YES
DELIMITER =
AMOUNTDECIMALPOINT = .
SKIP = 0
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=NO
MAXREJECTCOUNT=
ROUNDAMOUNT=
SELECTION = Z_VERSION, 010; 0DIVISION, 01;0FISCVARNT, VE;0CURRENCY, EUR; 0FISCYEAR, 2010;0MATL_TYPE, CIAL;0MATL_TYPE, ARDA;0BUS_AREA, 19;0BUS_AREA, 60;0BUS_AREA, 61;0BUS_AREA, 62;Z_SDADPA, NCA;0FISCPER, 2010.001;0MATERIAL, V1ET01
*MAPPING
ACCOUNT_SIM=*NEWCOL(V007)
C_CATEGORY=Z_VERSION
ENTIDAD=*NEWCOL(ENT_NA)
MERCADO=IF(0DISTR_CHAN=STR(UE) THEN STR(UE_)&0COUNTRY;0DISTR_CHAN=STR(NC) THEN 0DISTR_CHAN;0DISTR_CHAN=*STR(2P) THEN *STR(2P_)&Z_KDKG1;0DISTR_CHAN)
PRODUCTO=0MATERIAL
P_DATASRC=IF(0BUS_AREA=STR(19) THEN STR(NCA);0BUS_AREA=STR(60) THEN STR(NCA);0BUS_AREA=STR(61) THEN STR(DAI);0BUS_AREA=STR(62) THEN *STR(DAI))
TIEMPO=0FISCPER
SIGNEDDATA=0G_AVV007
*CONVERSION
TIEMPO = TIEMPO.xls
ACCOUNT_SIM = ACCOUNT.xls
MERCADO=MERCADO.xls

Each combination of OPTIONS / MAPPING / CONVERSION in a transformation file is equivalent to a round.

Cheers, Scott

[Former Member]

Hi Scott,

I've put several transformation files into one transformation file as you've showed me, and it's true that it makes as many rounds as the number of combinations of OPTIONS / MAPPING / CONVERSION appear, but the following problem happens:

- when I look at the log after validating the transformation file, it only shows you values for the last combination of OPTIONS / MAPPING / CONVERSION

- when I execute the package for loading dat into the cube, it ONLY loads data for the last combination of OPTIONS / MAPPING / CONVERSION that appears in the transformation file

I show you the execution's package log:

---

/CPMB/MODIFY : Completed in 0 seconds

/CPMB/INFOPROVIDER_CONVERT : Completed in 15 seconds

/CPMB/CLEAR_DATA : Completed in 1 seconds

/CPMB/APPEND_LOAD : Completed in 1 seconds

/CPMB/CLEAR : Completed in 0 seconds

-

-

---

InforProvide=AACPA_01

TRANSFORMATION= DATAMANAGERTRANSFORMATIONFILESPrueba_PrecioTarifa_NCA.xls

CLEARDATA= No

RUNLOGIC= No

CHECKLCK= Sí

-

-

---

Task name CONVERT:

No 1 Round:

Record count: 62

Accept count: 62

Reject count: 0

Skip count: 0

No 2 Round:

Record count: 62

Accept count: 62

Reject count: 0

Skip count: 0

No 3 Round:

Record count: 4063

Accept count: 4063

Reject count: 0

Skip count: 0

Task name CLEAR CUBE DATA:

Reject count: 0

Submit count: 172

Task name APPEND LOAD:

Reject count: 0

Submit count: 4063

Application : SIMULCOST. Package status: SUCCESS

-

-

---

The number of 'Submit record count' is always the same as the number of accepted records of the last round. And if I create a view from the target cube, it only has data for the last combination of OPTIONS / MAPPING / CONVERSION.

Do I must change any parameter?

Can you help me?

Thanks in advance,

Albert Mas

[Former Member]

Thank you for your solutions but I have problems yet.

Only I want to load in a CSV file from 2 Key Figures (REVENUE and COST):

CECO1,TIME1,Category1,P_ACCT1,K_REVENUE,K_COST

M1,2008.JAN,ACTUAL,1000000000,25,50

M2,2008.JAN,ACTUAL,1000000001,30,89

M3,2008.JAN,ACTUAL,1000000003,40,69

My Transformation FIle is:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=NO

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

CECO=CECO1

TIME=TIME1

CATEGORY=Category1

P_ACCT=P_ACCT1

ENTIDAD_ANALITIC=*NEWCOL(REVENUE)

SIGNEDDATA = K_REVENUE

*CONVERSION

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=NO

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

CECO=CECO1

TIME=TIME1

CATEGORY=Category1

P_ACCT=P_ACCT1

ENTIDAD_ANALITIC=*NEWCOL(COST)

SIGNEDDATA = K_COST

*CONVERSION

I run a package and I got the next error: "Cannot find dimension K_COST in curret aplication"

Why?

Thanks for yor help.

[Former Member]

Hi ,

At the end, the transformation file in 7NW supports only one round and hence only the last round is considered.

Greetings!

[srinivas_thota5]

Hi Pacojmartin, Experts

are you able to load two keyfigures together,

I have similar requirement.

GL Account Cost Center DataSrc Category Time LC USD EUR

GLACC1 CC1 INPUT Actual 2010.JAN 100 150 120

GLACC2 CC2 INPUT Actual 2010.JAN 200 300 240

GLACC3 CC3 INPUT Actual 2010.JAN 300 450 360

from the source system I am getting the Keyfigures in LC, USD and EUR, so I want to upload the Keyfigures in three different currencies, to skip currency translation in BPC.

Is it possible in NW 7.0?

Thanks,

Sri

[Former Member]

Hi Sri,

Unfortunately, it's not possible in NW 7.0 to load more than a key figure in a transformation file: if you want to load X key figures, you will have to work with X transformation files.

Cheers,

Albert Mas

[srinivas_thota5]

Hi Albert,

I understand from your previous messege that we can only have one round of transformation in NW 7.0, Is it right?

I have a situation where I have multiple key figures, and i can maintain multiple transformation files and can upload multiple times but the source flat file is having multiple key figures; when I am uploading the system is checking for other columns of keyfigures ( S_USD, P_SAL, P_S_USD) and showing an error no S_USD, P_SAL, P_S_USD Dimensions are found.

How can skip other keyfigures columns in the transformation file or any other way to skip the other Keyfigures.

Source flat file:

EMPLOYEE;COSTCENTER;HCDATASRC;GEOGRAPHY;HCACCOUNT;S_PC; S_USD; P_SAL; P_S_USD

E_00000ABC;1002; FT_ACTIVE G_US01; 1001; 5; 5; 5; 5

E_00000123; 2037; FT_ACTIVE G_US01; 1001; 3; 3; 3; 3

E_00000999; 5294; FT_ACTIVE G_US01; 1001; 3; 3; 3; 3

E_00000XYZ;5117; FT_ACTIVE G_US01; 1001; 2; 2 ; 2; 1

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

CostCenter=COSTCENTER

Employee=EMPLOYEE

Geography=GEOGRAPHY

HCAccount=HCACCOUNT

HCDataSrc=HCDATASRC

RptCurrency=*NEWCOL(PC)

Time=*NEWCOL(XXXX.NOYEAR)

Version=*NEWCOL(WORKING_FORECAST)

Amount=S_PC

*CONVERSION

HCAccount=Hcaccount.xls!HCAccount

Thanks,

Sri

[Former Member]

HI SCOTT,

I TRIED  TO LOAD MULTIPLE TRANSFORMATION IN A SINGLE TRANSFORMATION.

BUT I GOT ERROR .SECOND DOESNT LOADING IN THAT FILE ONLY LOAD ONE TRANSFORMATION.CAN U SHOW ME HOW TO DO THAT PROCESS.IS NEED TO MAP ONE TO ONE MAPIN THAT TRANSFORMATION

[former_member671850]

HI SCOTT,

I AM FACING A PROBLEM WHEN I RUN 2 ROUNDS IN ONE TRANSFORMATION FILE...

I need to distribute a source field in to BPC through making 2 conversion files... following is the data

Transformation file

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

CONVERTAMOUNTWDIM=ZOUTPUT

*MAPPING

CATEGORY=*NEWCOL(ACT)

PAO=0COSTCENTER

TIME=0FISCYEAR

ZOUTPUT=0FUNDS_CTR

SIGNEDDATA=0DEB_CRE_LC

*CONVERSION

PAO=PAO_CONVER.XLS

ZOUTPUT=ZOUTPUT_CONVER.xls

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

CONVERTAMOUNTWDIM=ZOUTPUT

*MAPPING

CATEGORY=*NEWCOL(ACT)

PAO=0COSTCENTER

TIME=0FISCYEAR

ZOUTPUT=0FUNDS_CTR

SIGNEDDATA=0DEB_CRE_LC

*CONVERSION

PAO=PAO_CONVER.XLS

ZOUTPUT=AMOUNT_CONVER.XLS Conversion file 1 (PAO=PAO_CONVER.XLS) EXTERNAL INTERNAL ID0001 F08001 ID0002 F08001 ID0003 F08001 DG0001 F08001 DG0002 F08001

Conversion file 2 (ZOUTPUT=ZOUTPUT_CONVER.xls)

ID0001	FX01	VALUE*1
ID0002	FX01	VALUE*1
ID0003	FX01	VALUE*.40
DG0001	FX02	VALUE*1
DG0002	FX02	VALUE*1

Conversion file 3 (ZOUTPUT=AMOUNT_CONVER.XLS)

EXTERNAL	INTERNAL	FORMULA
ID0003	FX02	VALUE*.60

I am getting the following error

	[Start validating transformation file]
	Validating transformation file format
	Start validation transformation 1/2
	Validating options...
	Validation of options was successful.
	Validating mappings...
	Validation of mappings was successful.
	Validating conversions...
	Validation of the conversion was successful
	Start validation transformation 2/2
	Validating options...
	Validation of options was successful.
	Validating mappings...
	Validation of mappings was successful.
	Validating conversions...
	Validation of the conversion was successful
	Creating the transformation xml file. Please wait...
	Transformation xml file has been saved successfully.
	Begin validate transformation file with data file...
	[Start test transformation file]
	Validate has successfully completed
	ValidateRecords = YES
	Reject count: 0
	Record count: 6
	Skip count: 0
	Accept count: 6
	0COSTCENTER is not a valid command or column 0COSTCENTER does not exist in source
	Validation with data file failed

[Former Member]

Dear Albert.

I have the same problem. We are trying to load key-figure model but we didn´t it still.

I can see an interested issue:

I tried to do the indications but only I could input 3 transformation file including 3 PROMPT in the Process Chain using DM -> advanced:

PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,Import.xls)

PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,Import.xls)

PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,Import.xls)

But when I run a package, I get an error:

FILE= DATAMANAGERDATAFILESpruebas_CAMPOVALOR3.csv

TRANSFORMATION= DATAMANAGERTRANSFORMATIONFILESTF_REVENUE.xls

TRANSFORMATION= DATAMANAGERTRANSFORMATIONFILESTF_COST.xls

TRANSFORMATION= DATAMANAGERTRANSFORMATIONFILESTF_OVERHEAD.xls

CLEARDATA= Sí

RUNLOGIC= No

CHECKLCK= Sí

-

-

---

Task name CONVERT:

No 1 Round:

Cannot find dimension K_REVENUE in current application

Application : CONTROLLING. Package status: ERROR