Export Transformation File

[Former Member]

I am building an export file to load data into ECC and I need to provide the data in a specific format.

How can I do the following:

1.  Cost Center to ECC needs to be the combination of the BPC Cost Center and Company code.  For example, costcenter-companycode(6000-1000)

2. Conversion of Account and company codes to ECC equivalent.

Any assistance is greatly appreciated.

[Former Member]

In doing the data manager package: Export from Fact table, I have taken the Cost Center "*Str" language out of my transformation file and was able to get the file to process without an issue when I did not use a conversion file for the Entity and Intercompany dimensions.  Once I added the conversions I received the Error in the previous post: .  My transformation file looks like: Index was outside the bounds of the array. I have never come across this error.

My transformation file looks like:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = TAB

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

Account=Account

AccountDet=AccountDet

AuditTrail=AuditTrail

Category=Category

CostCenter=CostCenter

Entity=Entity

Groups=Groups

Intercompany=Intercompany

Market=Market

Product=Product

ProfitCenter=ProfitCenter

Project_Trncurr=Project_Trncurr

Time=Time

SIGNEDDATA=SIGNEDDATA

*CONVERSION

Entity=NEW_TEST_BACK_ECC.xls!ENTITY

Intercompany=NEW_TEST_BACK_ECC.xls!INTERCOMPANY

My conversion file for Entity and Intercompany(ECC-Trading Partner) is (External is the output and Internal is BPC):

EXTERNAL	INTERNAL	FORMULA
2000	101
3000	102
2002	103
2001	104
2003	108
4000	105
1000	106
1010	107

All assistance and ideas are welcome.

[former_member186498]

Hi Sean,

your script seems ok, look at this note 1939997 - BPC MS: ERROR: "Index was outside the bounds of the array" when running the import package... if you have a sp between 0 and 8 then it could be your case and you have to upgrade your sp to solve it.

Regards

     Roberto

[Former Member]

Roberto,

Thank you for your reply. My client is on SP11.  This particular issue is related to exporting from BPC and not importing into BPC.

Do you have any other suggestions?

Thank you

[former_member186498]

Hi Sean,

please add

OUTPUTHEADER=

OUTPUTDELIMITER=

to your transformation file, think also to start from default export file

*OPTIONS
FORMAT=DELIMITED
HEADER=YES
DELIMITER=
VALIDATERECORDS=NO
ROUNDAMOUNT=7
OUTPUTHEADER=
OUTPUTDELIMITER=

*MAPPING
*CONVERSION

this should work, after add instructions one by one and see if works, same with conversion files, probably the issue is in your conversion files maybe a value in the fact tables not included in conversion.

Regards

     Roberto

[Former Member]

Roberto,

This was a great help!  There is one last item.  My transformation file is below.  All of the lines work except the costcenter line.  I do get the results with the "-" however the entity does not get converted.  I have posted an example of the result below as well.  For example, in the ENTITY column you will see "9000" where as in the COSTCENTER column it will still show the BPC dimension name "ELIM01".  Is there a way to have the entity converted when the file combines the costcenter and entity as stated in the costcenter line instructions?

I do not have a conversion in the conversion file for the cost center.

Thank you

*OPTIONS

FORMAT=DELIMITED

HEADER=YES

DELIMITER=

VALIDATERECORDS=NO

ROUNDAMOUNT=7

OUTPUTHEADER=

OUTPUTDELIMITER=

*MAPPING

Account=Account

AccountDet=AccountDet

AuditTrail=AuditTrail

Category=Category

Entity=Entity

CostCenter=CostCenter+*STR(-)+Entity

Groups=Groups

Intercompany=Intercompany

Market=Market

Product=Product

ProfitCenter=ProfitCenter

Project_Trncurr=Project_Trncurr

Time=Time

SIGNEDDATA=SIGNEDDATA

*CONVERSION

Entity=NEW_TEST_BACK_ECC.xls!ENTITY

Intercompany=NEW_TEST_BACK_ECC.xls!INTERCOMPANY

Conversion for Entity:

EXTERNAL	INTERNAL
101	2000
102	3000
103	2002
104	2001
108	2003
105	4000
106	1000
107	1010
ELIM01	9000
ELIM02	9001
ELIM03	9002
ELIM04	9003

Results:

ACCOUNT

ACCOUNTDET

AUDITTRAIL

CATEGORY

ENTITY

COSTCENTER

GROUPS

INTERCOMPANY

MARKET

PRODUCT

PROFITCENTER

PROJECT_TRNCURR

TIME

CURRENCY

SIGNEDDATA

1151000

FL99

ELIM_AUTO

ACTUAL

9000

NO_CC-ELIM01

NON_GROUP

2000

NO_MKT

NO_PROD

NO_PC

NO_PT

2015.01

USD

-2840661.76

1151000

FL99

ELIM_AUTO

ACTUAL

9000

NO_CC-ELIM01

NON_GROUP

3000

NO_MKT

NO_PROD

NO_PC

NO_PT

2015.01

USD

-5430886.2

[former_member186498]

Hi Sean,

using "-" as concatenator could cause several issues in BPC because bpc try to think it as minus if entity numeric (9000) then the risk is higher, better to use "_" to concatenate entity and cost center.

CostCenter=COSTCENTER+*STR(_)+ENTITY should work.

Regards

     Roberto

[Former Member]

Roberto,

I made the change.  However, the Entity in the CostCenter column is still not converting.  It stays in the BPC format.  Should I be doing something in the conversion file for the CostCenter? If so, then I don't know what it will be since the Entity needs to be converted to the external equivalent.

Thanks

[former_member186498]

Hi Sean,

can you try renaming the entity as string, e.g "E9000" to see if the numeric value generate the issue?

try also using COL(x) instead name of dimension, e.g. CostCenter=*COL(3)+*STR(_)+*COL(5) and see if works.

Regards

     Roberto

[Former Member]

Do I have to ues the *COL(X) for each of the dimensions if I change one?

[Former Member]

Roberto,

I figured out what you meant and made the changes that you suggested.  The costcenter line still did not convert the entity portion of the string.

Any other suggestions?

Thank you for your time with this.

[former_member186498]

Hi Sean,

you never close the threads, remember that is your duty to close these correctly after solved assigning a "correct answer", read please How to close a discussion and why and Are you a responsible person in SCN?

Regards

     Roberto

[Shrikant_Jadhav]

Hi Sean,

Kindly provide sample data structure of source and target.

Shrikant 

[Former Member]

Shrikant,

Thank you for your reply.  I have attached the Transformation and Conversion files that I am working with for the conversion back to ECC.  I need to write the transformation file so that the data from BPC goes back into the format that isin te Conversion file for: ENTITY, COST CENTER,TIME, INTERCOMPANY.

The cost center needs to be a combination of the Cost Center and Entity dimensions in BPC. (See the Cost Center image)

Not all GL accounts will need to be converted, only specific ones will need to be.  I am not sure how to do this when exporting data.

Thank you

ECC_BPC_EXPORT_DEV1.txt.zip

[Former Member]

Hi Sean,

Use D.M package export from fact table and do some changes in transformation file. If you want concatenate the company code and cost center then use below.

use Keyword  "*STR(string)"

CATEGORY=*COL(1)

ACCOUNT=*COL(2)

AUDITTRAIL=*COL(3)

ENTITY=*COL(4)

COSTCENTER=*COL(5)+*STR( - ) +*COL(4)

PROFITCENTER=*COL(6)

CURRENCY=*COL(7)

TIME=*COL(8)

SIGNEDDATA=*COL(9)

For more detail please go through below link.

Data Transformations - SAP BusinessObjects Planning and Consolidation - SAP Library

Hope this will solve your problem.

[Former Member]

Thank you for your reply.  When I do this I receive the error:

In addition, I have to convert the entity to another format using a conversion file. I did not put this into the file yet. I wanted to see if this worked first.

Any ideas?

Thanks