Selection Screen in Tranformation file

To import the transaction data from BI cube, best option is make use of transformation file. In transformation file if we want to filter the records based on some conditions then we have to make use of option SELECTION where we will give the parameters as there is no option to pass the data from LoadInfoProvider Package to transformation file.

Now the problem comes, if we want to update BPC cube frequently. E.g. if we are uploading the Actuals from BI cube then this activity needs to be done monthly. And if, user is doing actual upload activity then there are chances of error as he might can remove some semi-colon or delete some other parameters. Also he might not have any interest in data which is visible in INSTRUCTION sheet of transformation file.

So best solution is we can provide the selection screen in transformation file itself with all the necessary parameters by using excel functionality. Following is the procedure:

1.       In the Transformation file, add another sheet e.g. INPUT.

2.       Give the selection parameters in the added sheet.

E.g.

3.       Now use the excel functionality (& or concatenate) in SELECTION option in the Instruction sheet of transformation file which contains all the details like options, mapping and conversion.

=”SELECTION= 0COMP_CODE,”&INPUT!F6&”;0FISCYEAR,”&INPUT!F7&”;0LOC_CURRCY,INR;0FISCPER3,”&INPUT!F8&”;”

So I am referencing the INPUT sheet data into INSTRUCTION sheet. So whenever user selects the Financial Year & Period, values will get changed automatically in INSTRUCTION sheet.

Now we can hide INSTRUCTION sheet and only INPUT sheet will be visible to user. So he just selects the required data and validates and uploads.