Is transaction data upload possible without use of BW in BPC NW 7.5?

[Former Member]

Hi,

Is it possible to upload flat files (transaction data) into BPC NW 7.5 without using BW? If we use BW, we map the external members with the internal members. But how do we map members when we don't want to use BW?

For e.g., if the transaction data contains Amount (numeric values), how do we map this?

Thanks,

Harini

[krishna_priya1 · Accepted solution]

"Import Transaction Data" package is used to import transaction data from flat-file . Except loading CSV file to the server all other steps are same as data load from BW . Following are the steps to load transaction data from flat-file.

-> Upload CSV file to the server by eData-> Data Upload

-> create transformation file which maps columns of CSV file with Dimensions

-> create conversion file ,if required, for External To Internal mapping

-> Run Import Transaction Data

To map Amount , in tranformation file below mapping is used.AMOUNT is header of column in CSV file which has numeric values

SIGNEDDATA = AMOUNT

Hope this helps.

[Former Member · Accepted solution]

Thanks for the prompt reply. I had been unsure, but I had already executed those very steps that you've mentioned. I had mapped SIGNEDDATA=Amount.

Now, when I don't use a conversion file, I get a parse transformation file error, and validation fails.

But when I use a conversion file, I get an error stating that "Amount" does not exist in the source data, and then validation fails.

So what am I doing wrong?

[krishna_priya1 · Accepted solution]

Can you try changing Amount to all caps both in CSV file & transformation file . can you post log ?

[Former Member · Accepted solution]

BPC is case sensitive. Please check the help document for Data Loading scenario's from Flat File to BPC. Did you uploaded the file to Server first? Is there any conversions exists from your external to internal ?. and formula's, skipping of columns,?

Regards,

Sonti.

[Former Member · Accepted solution]

I had mapped the dimensions and the transformation file is as follows:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=

*MAPPING

RB_ENTITY=Entity

RB_ACCT=Account

Time=Year

RB_PDT_1=Product

RB_CUS_3=Customer

RptCurrency=LC

SIGNEDDATA=AMOUNT

RB_Category=Category

RB_DATASRC=Src

*CONVERSION

RB_ENTITY=RB_Entity.xls

RB_ACCT=RB_AccountConv.xls

Time=Time.xls

RB_PDT_1=RB_Pdt.xls

RB_CUS_3=RB_Cus.xls

RptCurrency=RptCurrency.xls

SIGNEDDATA=SignedData.xls

RB_Category=RB_Category.xls

RB_DATASRC=RB_DataSrc.xls

(all the conversion files have been mapped as * (external) to other (internal))

The log is as follows:

[Start validating transformation file]

Validating format of transformation file

Validating optionsu2026

Validation on options was successful

Validating mappingsu2026

Validation on mappings was successful

Validating conversionsu2026

Validation on conversions was successful

Creating the transformation xml file; wait a moment

Transformation xml file saved successfully

Connecting to server...

Begin validate transformation file with data fileu2026

[Start test transformation file]

Validate has successfully completed

ValidateRecords = YES

[List of conversion file]

Conversion file: DataManager\ConversionFiles\RB_ENTITY.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\RB_ACCOUNTCONV.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\TIME.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\RB_PDT.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\RB_CUS.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\RPTCURRENCY.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\SIGNEDDATA.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\RB_CATEGORY.XLS!CONVERSION

Conversion file: DataManager\ConversionFiles\RB_DATASRC.XLS!CONVERSION

AMOUNT is not a valid command or column AMOUNT does not exist in source

Error: Validate with data file failed

Changing Amount to AMOUNT in both .csv file and transformation file hasn't helped.

[Former Member · Accepted solution]

Hi,

I had already uploaded the file to the server first. I had verified the case for all the dimensions also. The conversions are * (external) to other (internal) for all.

[Former Member · Accepted solution]

Hi,

My comments:

1. Conversion file is not mandatory. If you have some member in the CSV file, which is not in the BPC format, then only you need to use the conversion file to convert the external format to internal.

If you have * (in external) and other (in internal), in all the conversion files, then all the members will be loaded to member - OTHER for all the dimensions. Is this what you want?

Moreover, OTHER should be a valid member of the dimension.

2. The error says - "AMOUNT is not a valid command or column AMOUNT does not exist in source"

Do you have this column in the CSV file?

[Former Member · Accepted solution]

Hi Nilanjan,

You are correct that conversion is not required. I tried validation after removing the conversion section, but the same error prevails. I have verified that the column AMOUNT does exist in the CSV file.

The log is as follows:

[Start validating transformation file]

Validating format of transformation file

Validating optionsu2026

Validation on options was successful

Validating mappingsu2026

Validation on mappings was successful

Validating conversionsu2026

Validation on conversions was successful

Creating the transformation xml file; wait a moment

Transformation xml file saved successfully

Connecting to server...

Begin validate transformation file with data fileu2026

[Start test transformation file]

Validate has successfully completed

ValidateRecords = YES

AMOUNT is not a valid command or column AMOUNT does not exist in source

Error: Validate with data file failed

There are no rejected records.

Is AMOUNT being wrongly mapped somewhere?

Thanks,

Harini.

[Former Member · Accepted solution]

Hi,

Can you change the keyword AMOUNT in your CSV file to something else. Then upload the file once again. Make the necessary changes to the mapping section of the transformation file, and then try again to validate the transformation file.

[Former Member · Accepted solution]

There is problem in your transofrmation file for AMOUNT.

Transformation file should be;

AMOUNT = *col(your column number).

[krishna_priya1 · Accepted solution]

Hi Harini,

What is LC in you transformation file . Is it hardcoded value? then you need to specify as *NEWCOL(LC) .

[Former Member · Accepted solution]

Hi Nilanjan,

Yes, changing the keyword from AMOUNT to PRICE worked. The file had validated now. Thank you so much.

Regards,

Harini.

[Former Member · Accepted solution]

Hi Nilanjan,

Yes, changing the keyword from AMOUNT to PRICE worked. The file had validated now. Thank you so much.

Regards,

Harini.

[Former Member]

Hi,

Check a sample transformation file to understand its components from help.sa.com/epm

Save your file as csv and select the delimited and other options if you flat file has a header.

In Mapping section, it is important you have mapping mentioned for all your BPC dimensions. Order of the dimensions not important and does not have to be in the same order as columns in your file.

If you do not have data in your flat file for a particular dimension eg: CATEGORY = *NEWCOL(ACTUAL) which would fill the default value 'ACTUAL'.

If you do not have the header you could have the following as mapping:

ENTITY= *col(1)

ACCOUNT*col(2)

CATEGORY=*NEWCOL(ACTUAL)

TIME=*col(3)

AMOUNT=*col(4)

Conversion section is required only if you think the master data in the source file is different from what you have in BPC dimension members. Example, you can create on conversion file and maintain the required conversions for each dimension in separate tabs and refer that in the transformation accordingly.

Thanks,

Sreeni

[Former Member]

Hi,

The flat file has headers and I have checked the case there as well. Since I'm not using BW, I have maintained * (external) to other (internal) in conversion files.