How to Export Dimension Master data from BPC10.0 into flat File

[former_member394086]

Hi Experts,

I have created one Test environment for Back up Purpose into our BPC development system. No i wanted to export all the dimensions master data into a flat file from production system and the same i wanted to import it into test environment of development system.

1. How to create Transformation file for exporting of Dimension master data. Can you please give me an example? I am specially looking for Mapping section.

2. Similar question while importing the same data to development system?

3. Please give me any dimension example e.g. Product , Account or Organisation. Also While exporting master data , hierarchy also get exported into flat file?

I am trying to export master data using data manager package but it is asking for transformation file. Hence looking for an example format of transformation file. Kindly advise.

Regards,

Parag

[former_member186338 · Accepted solution]

Your issue is due to commas inside the properties:

APPROVER: [AP_w_FPA],[T3 - Business Supp]

and

OWNER: [AP_w_FPA],[T3 - Business Supp]

In the transformation file *OPTIONS use:

DELIMITER = TAB

instead of:

DELIMITER = ,

[former_member394086]

Thanks Vadim. I have followed as you instructed. Now no more Org member related error. But as i mentioned in my previous reply i am getting header related error and hence package is getting fail. Do i need to remove header from file?

ERROR : Invalid header name: "ID,ALLOC_PRE_OSS_EXCEPT,ALLOC_SP2_ALLOC,ALLOC_SP2_"

Parag

[former_member186338]

About invalid header:

You have to show the header from the file if you want to get help.

Also show transformation file.

[former_member394086]

Hi Vadim,

I am attaching snap shot of file. first line is header line.

header1.jpg header2.jpg

Also attaching transformation file details. mapping and conversion section is blank in transformation.

transformation.jpg

Thanks.

Parag

[former_member186338]

Sorry, but screenshots are useless to analyze! Post text!

[former_member186338]

And why are you trying to import comma delimited file with transformation DELIMITER=TAB???????????????????

[former_member394086]

okay. I have attached file and kept header and few member as it is. And below is transformation details.

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = TAB

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=-1

ROUNDAMOUNT=

organisation.txt

Header : File attached.

ID,ALLOC_PRE_OSS_EXCEPT,ALLOC_SP2_ALLOC,ALLOC_SP2_ALLOC_REG,ALLOC_SP2_EXCEPTION,

Parag

[former_member186338]

And why are you trying to import comma delimited file with transformation DELIMITER=TAB???????????????????

[former_member394086]

The problem is when i used DELIMITER=TAB and exported master data , then it again exported data with similar file format as like before (i.e. including ",").

[former_member186338]

When exporting in transformation file add line:

OUTPUTDELIMITER=TAB

[former_member394086]

Many Many Thanks Vadim. Yes your solution worked and without any issue i could able to import entire master data. This is not only help from your end but i got few important tips regarding transformation file too. Will proceed with Account dimension now and if found any issue first will research on it and then will trouble you again 🙂

Parag

[former_member186338]

"So as per your comment i need to export data first using below transformation option i.e. Delimiter = TAB. And once exported then import this file with the same option?"

Yes! I think I have explained it absolutely clear.

You can't use comma delimiter if you have commas inside property values.

[former_member186338]

Sorry, but it's absolutely basic question - why not to search a little or read help?

Put nothing in the mapping section 🙂 Everything will be exported! Just test yourself

[former_member394086]

Many Thanks Vadim. Let me try. 🙂

[former_member186338]

And what is the result :)?

[former_member394086]

Hi Vadim,

I have exported all the master data for below dimension successfully into flat file.

1. PRODUCT , ORGANISATION , ACCOUNT, CATEGORY, AUDITTRAIL.

When i have imported "PRODUCT" Dimension master data into development system, then i could able to successfully import it with it's hierarchy. But when i tried importing Organisation dimension data this package got error out with error "Member ADJRESTATEMENT of dimension ORGANIZATION has an invalid hierarchy parent '[T3 - Business Supp]'.

When i check data file which was exported from production then i came to know that it didn't exported Hierarchy and hence i am getting this error.

Can you please advise me how to export entire master data including hierarchy. Got surprise how it work for PRODUCT dimension but not the case for Organisation dimension.

To export i used Data Manager Package "Export Master Data to Flat File".

Please advise.

Regards,

Parag

[former_member186338]

You have to demonstrate export file samples!

"Member ADJRESTATEMENT of dimension ORGANIZATION has an invalid hierarchy parent '[T3 - Business Supp]" - can you show this line in the export file?

[former_member394086]

Hi Vadim,

Below is the line from export file.

Header :

ID,ALLOC_PRE_OSS_EXCEPT,ALLOC_SP2_ALLOC,ALLOC_SP2_ALLOC_REG,ALLOC_SP2_EXCEPTION,APPROVER,AVAIL_FCSTPLAN,BPC_MODELSPLIT,BPF_CORE_INCLUDE,BPF_CORE_STEP2,BPF_CORE_STEP3,BPF_CORE_STEP4,BPF_CORE_STEP5,CONTROL_LEVEL,CTRL_CURRENCY_NOT_LC,CURRENCY,EVDESCRIPTION,IS_DUMMY,L1,L2,L3,L4,L5,LEVEL,ORG_LEVEL,OWNER,PREPARER,PARENTH1

ORG Members :

ADJRESTATEMENT,,,,,[AP_w_FPA],[T3 - Business Supp],,,,,,,,,,,Restatement,,,,,,,,,[AP_w_FPA],[T3 - Business Supp]

AFRSCTGROWTH,,,,,[AP_w_FPA],[T3 - Business Supp],,,,,,,,,,,MEA SAV ZAA Others,,,,,,,,,[AP_w_FPA],[T3 - Business Supp]

Parag

[former_member186338]

Do you really have:

PARENTH1 [T3 - Business Supp]

in your original dimension master data???

Please show screenshot of the dimension administration!

P.S. May be you have comma in some property...

Then in transformation file use TAB as delimiter, not comma!

[former_member394086]

Hi Vadim,

Actually when i exported data into flat file that time system didnt picked up hierarchy i.e. PARENTH1. So while importing same file into dimension in development system , it picked up [T3 - Business Supp] as PARENTH1 (This was my understanding).organisation2.jpgorganisation1.jpg . So the question is why it is not showing PARENTH1 members in a file?

I am attaching screen shot from Organisation dimension.

So as per your comment i need to export data first using below transformation option i.e. Delimiter = TAB. And once exported then import this file with the same option?

*OPTIONS FORMAT = DELIMITED HEADER = YES DELIMITER = TAB AMOUNTDECIMALPOINT = . SKIP = 0 SKIPIF = VALIDATERECORDS=YES CREDITPOSITIVE=YES MAXREJECTCOUNT=-1 ROUNDAMOUNT=

One more question here. I did try with same exported file having "," but changes done in transformation file as suggested. This time Import Data manager Package again fail but error is different.

ERROR: Invalid header name: "ID,ALLOC_PRE_OSS_EXCEPT,ALLOC_SP2_ALLOC,ALLOC_SP2_".

Do i need to remove header from the file?

Many Thanks for your quick response and support.

Parag