Creation of Transformation File

[Former Member]

Hi Experts,

I am new with BPC 10 NW and therefore needed your little guide to create transformation file :

I need to map (Account, Category, Currency, Time & Signed Data).

Account = PL310,PL320 & PL330

Category = Actual

Currency = LC

Time = 2014.01 to 2014.12

Signed Data = 10,000 for period one, 20,000 for period 2 and goes on till 120,000 for period 12.

Please guide me what to write below MAPPING and CONVERSION in order to map my imported data through Business Process Flows?

Thanks in Advance !!!!!

Regards,

Zain Bashir

[Former Member · Accepted solution]

Hi Vadim/Gajendra/Shrikant,

Thank you for your replies.

Actually i am trying to get used to of  Business Process Flow Tool

I have created one process template under BPF to Import Actual Data and under this i have defined two activities (Upload & Import of Flat File)

I have successfully uploaded a Flat File from my Desktop using (Account, Currency, Time, Category & Signed Data). While executing importing process, system requires import file (which i am fetching from my desktop) and in the next step, system requires transformation file (which i don't have it in my server and i need to create it).

System is giving me below error message while i am clicking CHECK STATUS of my import process :

CPMB/MODIFY completed in 0 seconds

/CPMB/CONVERT completed in 0 seconds

/CPMB/CLEAR completed in 0 seconds

[Selection]

--------------------------------------------------------------

FILE = \ROOT\WEBFOLDERS\Zain_Environment_BPC\Consolidation\DATAMANAGER\DATAFILES\EXAMPLES\UPLOAD TESTING.txt

TRANSFORMATION = \ROOT\WEBFOLDERS\Zain_Environment_BPC\Consolidation\DATAMANAGER\TRANSFORMATIONFILES\Import.xls

CLEARDATA = No

RUNLOGIC = No

CHECKLCK = No

[Message]

--------------------------------------------------------------

Task name CONVERT:

XML file (...IDATION\DATAMANAGER\TRANSFORMATIONFILES\IMPORT.TDM) is empty or is not found

Cannot find document/directory

model: Consolidation. Package status: ERROR

May be this is kind of basic question but i need your help in this please !!!

Regards,

Zain Bashir

[former_member186338 · Accepted solution]

Hi Zain,

For import from text file you need to create and validate transformation file. If your text file has correct columns - your transformation is very simple. Please, show your data file...

Vadim

[Former Member · Accepted solution]

Hi Vadim,

I am inserting an image of the data in text file for your reference, please guide me how to do the needful?

Thank & Regards,

Zain Bashir

[bishwajit_das · Accepted solution]

Hi,

As I see you text file, you probably just need to create a transformation file as below for the transaction data upload through a flat file:-

*OPTIONS

FORMAT  = DELIMITED

HEADER = YES

DELIMITER = ,

SKIP = 0

SKIPIF =

VALIDATERECORDS = YES

CREDITPOSITIVE = YES

MAXREJECTCOUNT =

ROUNDAMOUNT =

*MAPPING

ACCOUNT = *COL(1)

CATEGORY = *COL(2)

CURRENCY = *COL(3)

TIME = *COL(4)

SIGNEDDATA = *COL(5)

*CONVERSION

Wish this helps...

Regards,

Bishwajit

[Former Member · Accepted solution]

Hi Bishwajit,

Thank you for your valuable input but i am facing below error now :

/CPMB/MODIFY completed in 0 seconds

/CPMB/CONVERT completed in 0 seconds

/CPMB/CLEAR completed in 0 seconds

[Selection]

--------------------------------------------------------------

FILE = \ROOT\WEBFOLDERS\Zain_Environment_BPC\Consolidation\DATAMANAGER\DATAFILES\EXAMPLES\UPLOAD TESTING.txt

TRANSFORMATION = \ROOT\WEBFOLDERS\Zain_Environment_BPC\Consolidation\DATAMANAGER\TRANSFORMATIONFILES\EXAMPLES\TRANSFORMATION_UPLOAD_TESTING.xls

CLEARDATA = No

RUNLOGIC = No

CHECKLCK = No

[Message]

--------------------------------------------------------------

Task name CONVERT:

No 1 Round:

Cannot find column ACCOUNT in destination

model: Consolidation. Package status: ERROR

Can you please analyse what would be the mistake on destination now?

Thanks & Regards,

Zain Bashir

[Former Member · Accepted solution]

Hi Zain,

I think in your text file there are two delimiter that is "COMMA" and TAB.

Can try with one delimiter either TAB or COMMA.

Upload new txt with single delimiter then validate transformation file with below details.

If you are using Comma then

*OPTIONS

FORMAT  = DELIMITED

HEADER = YES

DELIMITER =

SKIP = 0

SKIPIF =

VALIDATERECORDS = YES

CREDITPOSITIVE = YES

MAXREJECTCOUNT =-1

ROUNDAMOUNT =*MAPPING

*MAPPING

ACCOUNT = *COL(1)

CATEGORY = *COL(2)

CURRENCY = *COL(3)

TIME = *COL(4)

SIGNEDDATA = *COL(5)

*CONVERSION

If you are using TAB delimiter then use delimiter =TAB

*OPTIONS

FORMAT  = DELIMITED

HEADER = YES

DELIMITER = TAB

SKIP = 0

SKIPIF =

VALIDATERECORDS = YES

CREDITPOSITIVE = YES

MAXREJECTCOUNT =-1

ROUNDAMOUNT =*MAPPING

*MAPPING

ACCOUNT = *COL(1)

CATEGORY = *COL(2)

CURRENCY = *COL(3)

TIME = *COL(4)

SIGNEDDATA = *COL(5)

*CONVERSION

hope this will help you.

Sushant

[Former Member · Accepted solution]

Hi Sushant,

I have uploaded new .txt file with only one delimiter (TAB) then followed your instruction but the error is still same

[Former Member · Accepted solution]

Hi Zain,

Did you validate new transformation file with new txt file?

\

Sushant

[Former Member · Accepted solution]

Hi Zain,

Account = PL310,PL320 & PL330

Category = Actual

Currency = LC

Time = 2014.01 to 2014.12

Signed Data = 10,000 for period one, 20,000 for period 2 and goes on till 120,000 for period 12.

your initial post

and your TXT has ACTUAL

Please confirm what you have maintained in category dimension and for currency dimension?

categery= ACTUAL  OR  category = Actual

Currency= LC OR Currency = Lc

Also confirm name of your dimension.

Cannot find column ACCOUNT in destination

please maintain same ID''s in your txt file as you have maintained in the your dimensions.

Sushant

[Former Member · Accepted solution]

Hi Zain ,

You can also refer below thread might be helpful.

Hope this will help you.

Regards

Sushant.

[Former Member · Accepted solution]

Hi Sushant,

Thank you for trying helping me out but still i am not able to make it

Let me make it little simple, lets say i have a scenario on which i have to upload and import below file:

Dropbox - TESTING.xlsx

and the dimensions used in Process Template are as follows:

Account_Zain

Category

Time

Signed_Data

RPTCurrency

and i need to map below data :

Account_Zain = PL310, PL320 & PL330

Category = ACTUAL

Time = 2014.01 TO 2014.12

Signed_Data = 10,000 TO 120,000

RPTCurrency = LC

Now what should i write in a transformation file to map this scenario?

Please guide !!!

[Former Member · Accepted solution]

Hi Zain,

where is your entity type dimension in your data?

In BPC Account Category Entity and Time these are mandatory dimension. But in your  have only account category and time dimesion.

how you can create a model without entity type dimension?

Regards

Sushant.

[Former Member · Accepted solution]

Hi Shushant,

Thanks for your prompt response.

Entity is maintained as driving dimension already, please refer screen shot and please guide me if i am missing anything anywhere?

.

[Former Member · Accepted solution]

Hi Zain,

Please share list of dimension name that you have maintained in your model.

You can save data only on BASE level members not on parent level.

so please create one txt tha twill have base members of each dimension. if you txt file don't have entity in the txt  then you have to map entity or other dimension manually that are available in your model.

Regards,

Sushant

[Former Member · Accepted solution]

Hi zain,

If your file don't have any dimension you have to map that dimension as show below.

Considering INDIA  base level member of entity dimension so all data will be loaded against India.

likewise you have to map you dimension members.

Sushant

[Former Member · Accepted solution]

Hi Sushant,

Thank you so much again for your untiring efforts to solve my problems but i regret its still not working

I am using Model named Consolidation, and following are the dimensions under this:

then i have changed my upload file as per your guidance like this:

https://www.dropbox.com/s/a9gpgfi39w9q3oi/TESTING.xlsx?dl=0

Then i have created Transformation file as per your guidance like this, but got error again :

This is the date preview of my file:

Please tell me do you think any thing is missing now?

Thanks & Regards,

Zain Bashir

[Former Member · Accepted solution]

Hi Sushant,

I think i have made it

It was Account description issue, it was not ACCOUNT not ACCOUNT_ZAIN but it was P_ACCT.

But now here i have new situation

Is it required to use all the dimensions of any model at the time of uploading ???

[Former Member · Accepted solution]

Hi Zain,

With the help of your dimension I have created one txt file please refer these file and try once again.

One thing that i want to highlight there is no need to create signeddata dimension this is system reserved keyword. when you create signeddata dimension system will through an error that why you created "SIGNED_DATA".

NOTE: members should be the same as you have maintained in each dimension.

1)     Create txt or csv file with the help of your dimension and dimension members but dimension member must be base level members.

2)     Upload the file to server.

3)     Create transformation file that will map each dimension members with your dimension. Use same dimension name that you have created for example you have created P_ACCT so in transformation file use P_ACCT to map the data.

4)     Then validate and save transformation file with uploaded data.

I have created .csv file except "SIGNED_DATA" dimension.You have to upload this file (similar file taking same dimension members) to server and create new transformation file as shown below screen and validate and run package.

For your case add one  more column Signed_data dimension use base level member in that column and also map that column. If you add column after signeddata (column 13 then) add one more row in mapping SIGED_DATA=*COL(13)

Take all dimension and its' base level members and upload file.

Transformation file

I hope this time you will complete your task successfully.

Sushant

[Former Member · Accepted solution]

Hi Zain,

Please read below document understand  it and then load data, but first understand the process how to create flat file and how to create transformation and conversion file.

Sapbpc nw 10.0 transactional data load guide

Sushant.

[Former Member · Accepted solution]

Hi Sushant,

Finally, i am getting new kind of error now, at least we are progressing :

Please guide  !!!!!!

[Former Member · Accepted solution]

Hi Sushant,

After clicking OK on the popup "Do you need Data Manager to update your conversion and transformation files for the rejected records?", i got below error:

after click Ok again:

Issue is still open to be resolved

[Former Member · Accepted solution]

Hi Zain,

As per your earlier post you have 11 dimension in your model.

But you just mapped 10+1(signeddata) dimension still there is one missing dimension that i's "SIGNED_DATA"'.

Again i am saying first read the documents and understand the process.

Please understand how we map the data in BPC.

P_ACCT=*col(1) means in first column we have account dimension member likewise you have to map the data similarly. You do your mapping.

You can see the column number in the 9th row.

Corrected file will be like

Transformation file

wish this time you will do it.

Sushant

[Shrikant_Jadhav · Accepted solution]

Hi Zain,

As suggested please go through provided links carefully you will defiantly solve your issue, these are very basic steps.

As suggested earlier please go throug this link  Sapbpc nw 10.0 transactional data load guide.

As shown in above screen shot you have not mapped all dimension in transformation file. If you 10 dimensions in model then you have provide mapping for all these dimensions and one SIGNEDDATA , follow the transformation file given by Sushant.

Shrikant

[Former Member · Accepted solution]

Hi Shrikant,

As suggested by you, i have followed all the steps of the link but i am getting below error:

please guide!

[bishwajit_das · Accepted solution]

Hi Zain,

Could you provide a screen-shot of the reject list of records?

Regards,

Bishwajit

[Former Member · Accepted solution]

Hi Bishwajit,

Below is the rejected list:

And this is the data preview:

Please guide?

[Former Member · Accepted solution]

Hi zain,

what is your data source? is it from flat file?

Regards,

Sushant

[Former Member · Accepted solution]

Yes sushant its flat file. I am referring below document for doing the needful :

  Sapbpc nw 10.0 transactional data load guide.

Thanks & Regards,

Zain Bashir

[Former Member · Accepted solution]

Hi Zain,

you just need to map your dimension name(not description) with the flat file field.

Don't use description of dimension for mapping, if you have maintained  D2_ZAIN1= audittrail then you have to use D2_ZAIN1 in the transformation file not audittrail.

you can refer my earlier post also.You need to map field column with dimension name. first create flat file,then map columns with your dimension name.

if your flat file don't have any dimension member suppose your flat file don't have category dimension member and currency dimension members then you have to map that dimension also like

CATEGORY=*Newcol(ACTUAL)

CURRENCY=*Newcol(USD)

All data will be loaded for the actual category and for USD currency.

First create flat file that will have base level members of all dimensions and map then accordingly. Validate your transformation file with your data.

when you running import package, specify that transformation file that you have already validated with your data.

Regards,

Sushant

[Former Member · Accepted solution]

Hi Sushant,

Thank you for your guidance again.

But this time system is giving me error on Conversion File. Do we need to create conversion sheets for each dimensions like we need to map all dimensions of a Module while creating transformation file?

[Former Member · Accepted solution]

Hi Sushant,

I think the rejected list i have attached on my last post indicates that there is some issue with members, according to message they are either invalid or calculated members.

I have checked, none of them are calculated member and all of them are base level members. Now i dont know what is the meaning of invalid member.

Would you be so kind to share your email id or mail me on my id so that i can show you my system?

Regards,

Zain Bashir

[Former Member · Accepted solution]

Hi Zain,

Sorry for late reply,

Please share your flat file and list of dimensions again. please share your file that you have uploaded to server.and some sample members from each dimension.

Regards,

Sushant

[Former Member · Accepted solution]

Hi Sushant,

Please refer below link screen shots and guide :

[Former Member · Accepted solution]

Hi Zain,

please refer below transformation file to load your data.Use column A only

For time dimension you can use conversion file so system can understand the conversion of member, or use same member in flat file that you have maintained in the dimension.

for time dimension:- If you have maintained time dimension ID's like 2014.01 for 2014.JAN, 2014.02 for 2014.FEB then use 2014.01, 2014.02. Use same ID's in flat file.

You can convert your flat as shown below. just for time dimension you use your members.

NOTE:  Considering 2014.01, 2014.02,2014.03,2014.04,2014.05,2014.06 are the ID's of time dimension.

when you are loading data first time from flat file try to use same member id's that you have maintained in each dimension.

Create conversion file save it and use it in the transformation file under conversion. if have created conversion file for time dimension then

DL_TIME=TIME.XLS (Name of conversion file)

Conversion files define the mapping (by dimensions) from external member names to internal member names

For conversion file you can refer below links.

Data Transformations - SAP Documentation

Data Conversions - SAP Documentation

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/9093eea1-0153-2e10-8294-a16350bac...

Regards,

Sushant

[Former Member · Accepted solution]

Thanks Bro !!!!!!!!

[Shrikant_Jadhav]

Hi,

Please go through the following links.

Sapbpc nw 10.0 transactional data load guide

Data Transformation Maintenance - SAP BusinessObjects Planning and Consolidation - SAP Library

What is your exact requirement ?

Shrikant

[gajendra_moond]

Hi Zain

What is your source?

You can map the field IDs from source here and restrict the selection in DMP (Data Manager Package).

[former_member186338]

Hi Zain,

But what for you need some transformation file? What do you want to do with data?

Vadim