Selection in data load from infoprovider

[Former Member]

Hi Guys,

In BPC NW 7.5 we have to load data from infoprovider allowing users to select data (like for dimension TIME) by BPC prompt. We found two solutions solving partially our problem. In fact, users have to modify manually the selections.

SOLUTION 1:

To load data we want use the process chain /CPMB/INFOPROVIDER, but we know that it is not possible insert selections in the /CPMB/INFOPROVIDER prompt (as describe in this post ).

To select data we can use an intermediate infocube as a BW workaround (as describe in this post ) to have a source with only the selected data.This could be done by a selection in the DTP between the source infocube and the intermediate infocube. This solution is not dynamic, in fact, in this case users have to modify manually the DTP selection.

How can we allow users to insert this selection in the DTP by a BPC prompt?

SOLUTION 2:

To select data we can use a transformation file inserting a selection like

SELECTION = <Dimension1_techname>,<Dimension1_value>.

It is not dynamic, in fact, also in this case users have to modify manually the file selection.

Do you know how to allow these selection by a BPC prompt to avoid these manual changes?

Do you know other solutions?

Thank you for your support.

[GFV]

Dear D-Mark

take a moment to read this recent Blog [Enhanced selections to load transactional data from any infoprovider|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/21962] [original link is broken] [original link is broken] [original link is broken]; by Pravin Datar.

Also chek out this Note [Note 1441872 - Enhance load from InfoProvider to add selection UI|https://service.sap.com/sap/support/notes/1441872]

A new BI Process Chain (/CPMB/LOAD_INFOPROV_UI) should help in managing BI Characterist Values selections, when loading form BI InfoProvider.

Hope it helps

GFV

[esjewett]

Hi D-Mark,

This is definitely a place where it would be nice to see some additional functionality added to BPC. Variable replacement in the transformation file based on the data manager prompt would probably be the best thing to have in the software.

In any case, getting back to your question, manually modifying the transformation file selection is the most common practice on BPC projects. The blog linked by Naresh is a fairly elegant way to do this, though it doesn't completely get around the fact that it's easy to forget to do and easy to get confused about what is going on in the transformation file.

A third option that no one has mentioned is to do a SELECTION statement in the transformation file based on navigational attributes in the source InfoProvider. This approach can make the selection statement dynamic based on the contents of BW InfoObjects. Still not very user-friendly, but if you can put an automatic process in place to update the BW navigational attributes this might meet your need without having to set up an extra BW staging InfoProvider.

The SELECTION syntax is documented here, though it doesn't mention that you can select on navigational attributes: [http://help.sap.com/saphelp_bpc75_nw/helpdata/en/5d/9a3fba600e4de29e2d165644d67bd1/frameset.htm]

With navigational attributes (the profit center attribute of cost center, for example) it would be something like:

SELECTION=0COST_CENTER___0PROFIT_CENTER,PC01

Ethan

[Former Member]

Hi Ethan,

We have just implemented the transformation file using the standard syntax available in help.sap.com (as you suggest), but it does not work. We insert the following selection in the OPTIONS:

SELECTION = 0ACCOUNT, AC0001;

All records are loaded! Not only records with account number AC0001.

Do you know if there are other errors in our code (in previous post)?

Another question:

Do you know how to call a transformation file directly from Business Process Flow?

[esjewett]

Hi,

I would try the following:

SELECTION=0ACCOUNT,AC0001

I usually try to avoid spaces as they can be misinterpreted by the system. If that doesn't work, can you share what support package you are on?

Regarding running a load using a specific transformation file from a BPF, the best way to do this is probably to set up a data manager package specifically hardcoded to use that transformation file and then call the DM package from the BPF.

Ethan

[Former Member]

Hi Ethan,

We found the error. The validation process displays the wrong records number. In fact, the output log display all records, instead in the target infoprovider are loaded ONLY the selected records. So, it works.

Maybe, there is something that doesnu2019t work correctly in the validation process output.

In order to save the transformation file with new selections, users have to use the u201CValidation & Process Transformation Fileu201D in eData.

How could we set the infoprovider technical name with a default value? This would allow users to avoid inputting each time the infoprovider technical name.

Regarging the BPF, how could we hardcode a data manager package to call transformation file from a BPF? Could you please explain better your solution?

Edited by: D-Mark on Sep 13, 2010 12:25 PM

Edited by: D-Mark on Sep 13, 2010 12:28 PM

[esjewett]

Hi,

Glad to hear that it's working. It does indeed sound like a bug with the validation output that could be fixed. You may want to open a quick OSS message with SAP to get the product team to look at it.

Regarding hardcoding the InfoProvider technical name or the transformation file in the data manager package, you will need to modify the package script of the data manager package you want to use (or create a new data manager package and possibly a new process chain and modify the script for that).

The process to view the script is outlined here: [http://help.sap.com/saphelp_bpc75_nw/helpdata/en/64/eb26e49af84e25b22b9a30c1cf70d8/frameset.htm]

The main page for the syntax of these script is here: [http://help.sap.com/saphelp_bpc75_nw/helpdata/en/81/29f9eaa9864bd38f62081e53770fa9/frameset.htm]

Basically you want to change the script to stop prompting the user for a value for the InfoProvider and Transformation files (just delete the prompt lines) and then hardcode the value in to the task instructions (in place of the %% variables that are currently populated by prompts).

Ethan

[Former Member]

Is there any condition for the selection statement where it does not work with conversion files?

I have entered the selection statement as SELECTION=0ACCOUNT,0050100100 but it does not seem to work. I also have a conversion file defined for account to remove the preceding 0's. Data gets loaded for all accounts instead of just the one i have specified.

[Former Member]

Hi Neeti,

   The SELECTION option available in transformation has been removed in the  EPM 10 version,as an alternative they have provided the selection GUI while loading the data using  Data Manager Package.

After clicking the validate & Process Transformation File and enter the info provider name.

Hope this helps.

[former_member186338]

Looks like not a perfect idea to answer one year old question about version 7.5...

Vadim

[Former Member]

Ok Vadim, Next time i will try to be perfect

[former_member186338]

It's just a question of some garbage posts...

[Former Member]

try below one.

/people/community.user/blog/2010/08/31/selection-screen-in-tranformation-file

[Former Member]

Hi Naresh,

Thank you for your interesting link. We implement this SELECTION option in the OPTION section of the transformation file.

*OPTIONS
FORMAT = DELIMITED
HEADER = YES
DELIMITER = ,
AMOUNTDECIMALPOINT = .
SELECTION = 0ACCOUNT, AC0001;
SKIPIF =
VALIDATERECORDS=YES
CREDITPOSITIVE=YES
MAXREJECTCOUNT=
ROUNDAMOUNT=


*MAPPING
ACCOUNT =  0ACCOUNT
DATASRC = *STR(INPUT)
ENTITY = 0COMP_CODE
RPTCURRENCY = *STR(LC)
VERSION = *STR(ACTUAL)
TIME = *STR(2010.JAN)
SIGNEDDATA = 0DEB_CRE_LC

The system validates the file, but all records are loaded: not only with Account=AC0001. It seems that the SELECTION option does not work.

Edited by: D-Mark on Sep 10, 2010 1:05 PM