Problems when loading a hierarchy BPC from SAP BW with a constant value

[Former Member]

Hello!,

Currently we are loading a dimension from BW, we have created a flow for loading data from BW, the InfoObjeto we use only has defined texts and attributes, but there is not a definite hierarchy. A hierarchy (PARENTH1) we have in our dimension of BPC and also need to be able to load it with a constant value. We have used the package (CPMB/IMPORT_IOBJ_MASTER) to load texts and attributes, with the following transformation file:

When does the load attributes and texts everything works fine. We have made a change in the transformation file and added a constant for the PARENTH1:

But executing the package load does not work. We have also used the package (CPMB/IMPORT_IOBJ_HIER) but as we don't have a hierarchy defined in BW to the infoObjeto does not work.

How do we load default the PARENTH with a constant value?

Of course, we want to automate the process to avoid having to manually enter the hierarchy in BPC.

Greetings and thanks!

[former_member186338 · Accepted solution]

Hi Emilio,

May be it's possible to define hierarchy in BW?

Alternative is to create some badi to update hierarchy in BPC. Other method like export to text file and import it back is not fully automatic...

Vadim

[former_member186338 · Accepted solution]

Ups, just tested:

Run export master data to file then import master data from the file exported on the previous step with fixed PARENTH1 assignment in transformation file. It works with something like (GLOBAL is a fixed parent):

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=2

SPECIFICMAPPING=YES

*MAPPING

ID=ID

PARENTH1=*IF(ID = *STR(GLOBAL) THEN *STR();  *STR(GLOBAL))

*CONVERSION

Vadim

[Former Member · Accepted solution]

Thanks Vadim, the solution you propose is valid. So the steps would be:

(1) run the load master data from BW.
(2) export package run and force PARENTH1 dimension as a constant.
(3) run Importancion package file generated in the previous step.

So we will have to run 3 packs for charging the dimension.

We will also look at the option of performing a Badi to simplify the process.

regards,

[former_member186338 · Accepted solution]

(2) and (3) can be run without user prompts! And the whole sequence can be run as a package link!

[former_member186338 · Accepted solution]

Samples:

DM script to export ID's of USER22 dimension without prompts:

INFO(%TEMPNO1%,%INCREASENO%)

INFO(%TEMPNO2%,%INCREASENO%)

INFO(%DIMENSIONMEMBERS_DATEFROM%,)

INFO(%DIMENSIONMEMBERS_KEYDATE%,)

INFO(%TRANSFORMATION%,\ROOT\WEBFOLDERS\SIM\ST123\DATAMANAGER\TRANSFORMATIONFILES\EXPORTU22.XLS)

INFO(%FILE%,\ROOT\WEBFOLDERS\SIM\ST123\DATAMANAGER\DATAFILES\expu22.txt)

INFO(%DIMENSIONMEMBERS%,@@@SAVE@@@@@@EXPAND@@@|DIMENSION:USER22|)

TASK(/CPMB/APPL_MD_SOURCE,SELECTION,%DIMENSIONMEMBERS%)

TASK(/CPMB/APPL_MD_SOURCE,OUTPUTNO,%TEMPNO1%)

TASK(/CPMB/APPL_MD_SOURCE,DATEFROM,%DIMENSIONMEMBERS_DATEFROM%)

TASK(/CPMB/APPL_MD_SOURCE,KEYDATE,%DIMENSIONMEMBERS_KEYDATE%)

TASK(/CPMB/EXPORT_MD_CONVERT,INPUTNO,%TEMPNO1%)

TASK(/CPMB/EXPORT_MD_CONVERT,TRANSFORMATIONFILEPATH,%TRANSFORMATION%)

TASK(/CPMB/EXPORT_MD_CONVERT,SUSER,%USER%)

TASK(/CPMB/EXPORT_MD_CONVERT,SAPPSET,%APPSET%)

TASK(/CPMB/EXPORT_MD_CONVERT,SAPP,%APP%)

TASK(/CPMB/EXPORT_MD_CONVERT,OUTPUTNO,%TEMPNO2%)

TASK(/CPMB/EXPORT_MD_CONVERT,KEYDATE,%DIMENSIONMEMBERS_KEYDATE%)

TASK(/CPMB/FILE_TARGET,INPUTNO,%TEMPNO2%)

TASK(/CPMB/FILE_TARGET,FULLFILENAME,%FILE%))

TASK(/CPMB/FILE_TARGET,ADDITIONALINFO,0))

Transformation file:

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=2

SPECIFICMAPPING=YES

*MAPPING

ID=ID

*CONVERSION

DM script to import ID's of USER22 dimension with hierarchy fixed to GLOBAL without prompts (update mode):

INFO(%TEMPNO1%,%INCREASENO%)

INFO(%TEMPNO2%,%INCREASENO%)

INFO(%DIMNAME_DATEFROM%,)

INFO(%DIMNAME_KEYDATE%,)

INFO(%TRANSFORMATION%,\ROOT\WEBFOLDERS\SIM\ST123\DATAMANAGER\TRANSFORMATIONFILES\IMPORTU22.XLS)

INFO(%FILE%,\ROOT\WEBFOLDERS\SIM\ST123\DATAMANAGER\DATAFILES\expu22.txt)

INFO(%DIMNAME%,USER22)

TASK(/CPMB/MASTER_CONVERT,OUTPUTNO,%TEMPNO1%)

TASK(/CPMB/MASTER_CONVERT,FORMULA_FILE_NO,%TEMPNO2%)

TASK(/CPMB/MASTER_CONVERT,TRANSFORMATIONFILEPATH,%TRANSFORMATION%)

TASK(/CPMB/MASTER_CONVERT,SUSER,%USER%)

TASK(/CPMB/MASTER_CONVERT,SAPPSET,%APPSET%)

TASK(/CPMB/MASTER_CONVERT,SAPP,%APP%)

TASK(/CPMB/MASTER_CONVERT,FILE,%FILE%)

TASK(/CPMB/MASTER_CONVERT,DIMNAME,%DIMNAME%)

TASK(/CPMB/MASTER_CONVERT,KEYDATE,%DIMNAME_KEYDATE%)

TASK(/CPMB/MASTER_LOAD,INPUTNO,%TEMPNO1%)

TASK(/CPMB/MASTER_LOAD,FORMULA_FILE_NO,%TEMPNO2%)

TASK(/CPMB/MASTER_LOAD,DIMNAME,%DIMNAME%)

TASK(/CPMB/MASTER_LOAD,WRITEMODE,2)

TASK(/CPMB/MASTER_LOAD,DATEFROM,%DIMNAME_DATEFROM%)

TASK(/CPMB/MASTER_LOAD,KEYDATE,%DIMNAME_KEYDATE%)

Transformation file (same as in prev post):

*OPTIONS

FORMAT = DELIMITED

HEADER = YES

DELIMITER = ,

AMOUNTDECIMALPOINT = .

SKIP = 0

SKIPIF =

VALIDATERECORDS=YES

CREDITPOSITIVE=YES

MAXREJECTCOUNT=

ROUNDAMOUNT=2

SPECIFICMAPPING=YES

*MAPPING

ID=ID

PARENTH1=*IF(ID = *STR(GLOBAL) THEN *STR();  *STR(GLOBAL))

*CONVERSION

Vadim

[former_member186338 · Accepted solution]

Package link:

No prompts...

[Former Member · Accepted solution]

Hey Vadim,

Your solution works perfect but i wanted to add small tip.

When you use "/CPMB/EXPORT_MD_TO_FILE" , the output file's ID order is different then both your ID and Parenth1 order. After you import this output file to your dimension, your hierarchy order will be corrupted.

In order to avoid this, you have to put 2 little commands to advanced script of "/CPMB/EXPORT_MD_TO_FILE" package which are;

INFO(%SORTEDBY%,[Hierarchy Name You want to sort by])

TASK(/CPMB/APPL_MD_SOURCE,SORTEDBY,%SORTEDBY%)

An example;

INFO(%SORTEDBY%,PARENTH1)

TASK(/CPMB/APPL_MD_SOURCE,SORTEDBY,%SORTEDBY%)

But this only starts to work with;

CPMBPC 800 SP13

CPMBPC 801 SP06

CPMBPC 810 SP02

You can see details in OSS Note : 1986887