How to get and set Package Links prompt values from EPM Add-in VBA API?

[Former Member]

Hey Guys,

It’s my first post here and I would like to introduce myself. My name is Piotr, I’m 29 years old, I live in Warsaw, Poland and work as Junor Consultant.

I was asked by my colleague to help him build cross systems (SAP BPC and Oracle EPM) data load process.

He is the designer of the build and I help him by finding solutions to new problems that occur.

At some point in the data load process we want to:

• 1)See prompt values assigned to given Package Links
• 2)Run Package Links with these promts

I have already developed working solution that runs Packages with prompt values stored in Excel sheets, but I would also like to develop similar solution for Package Links. The reason I want to run Package Links is that client has a lot of already defined Package Links.

To achieve it we would like to use Excel EPM Add-in API.

The problem is I don’t know how to see and change Package Links Prompt values in VBA using API.

The Sub DataManagerRunPackageLink (packageLinkId As String, packageLinkName As String) from EPMAddInAutomation only receive package Link ID and package Link name.

I can find package Link ID and prompt values in SAP BPC Database through SAP Client by SE16 -> UJD_LINK -> <ANSWERPROMPT>

but I don’t know how to access BPC Database from EPM Add-in VBA API.

Is it possible?

I also don’t like my current idea. I think it’s over-complicated to extract Package Links prompt values from Database.

Is there any other way to access/change Package Links prompt values from EPM Addin VBA API?

Thanks,

Piotr

[former_member186338 · Accepted solution]

After some analysis I can see that package link can't be replaced by multiple DM package launch using VBA - packages in package link are executed one after one depending on results of previous package.

I can see only one solution - create a new custom logic badi based on the code of program UJD_TEST_PACKAGE_LINK. This badi has to accept answer prompt as a text parameter and will execute package link with this answer prompt.

Then you can create DM package with script logic running custom logic badi. And you can pass answer prompt for this package using VBA.

[former_member186338]

P.P.S. Just published the blog with the ExecuteDM procedure: https://blogs.sap.com/2017/06/16/simple-vba-function-to-pass-parameters-to-dm-packages/

[Former Member]

Vadim,

Thank you very much 🙂

[former_member186338]

Unfortunately there is no way to set package link prompts using VBA EPM. And the idea to directly modify saved prompts in database is bad in general.

Instead of package links use VBA to set individual prompts for DM packages using modified code of:

https://archive.sap.com/documents/docs/DOC-32636

"client has a lot of already defined Package Links" - extract package links from table and write code to run multiple DM packages.

[former_member186338]

P.S. I can publish the code that can do the following:

Public Sub TestRunDM()
    Dim strAnsw() As String
    ReDim strAnsw(0 To 5)
    strAnsw(0) = "%DIMENSIONMEMBERS%D|DIMENSION:TRADING_PARTNER|TP_NONE,TP_000001,TP_999999,TP_XX0098,TP_XX0099,TP_1,TP_CH00,TP_CH02,TP_CZ00,TP_CZ02,TP_DE00,TP_DK00,TP_ES00,TP_FR00,TP_GB00,TP_HK00,TP_HU00,TP_HQ00,TP_IT00,TP_MX00"
    strAnsw(1) = "%DIMENSIONMEMBERS_KEYDATE%V-1"
    strAnsw(2) = "%DIMENSIONMEMBERS_DATEFROM%V"
    strAnsw(3) = "%TRANSFORMATION%V\ROOT\WEBFOLDERS\XXX_Copy_20160620\XXXYTD\DATAMANAGER\TRANSFORMATIONFILES\IMPORT.XLS"
    strAnsw(4) = "%FILE%V\ROOT\WEBFOLDERS\GMC_Copy_20160620\XXXYTD\DATAMANAGER\DATAFILES\EXPORT\testtp.txt"
    strAnsw(5) = "%ADDITIONINFO%V0"
    
    ExeceuteDM strAnsw, "", "Data Management", "Export Master Data to File", "/CPMB/EXPORT_MD_TO_FILE", "0001"

End Sub

Public Sub ExeceuteDM(ByRef strAnswerArr() As String, strTeam As String, strPackageCroup As String, _
    strPackage As String, strChain As String, strUserGroup As String)
' strAnswerArr() - each line define a single prompt variable
' Line format: %VARIABLENAME%xVALUE
' x can be "V" for simple sting value or "P"/"D" for complex string containing dimension names and dimension members
' "P" is used for SELECTINPUT, SELECT, COPYMOVE, COPYMOVEINPUT, MEMBERFROMTOINPUT
' "D" is used for DIMENSIONMEMBER
' Single empty line - no answer prompt
' strTeam - Team, "" for Company
' strPackageCroup - Package Group like "Data Management"
' strPackage - Package name like "Clear"
' strChain - Process Chain name like "/CPMB/CLEAR"
' strUserGroup - User "0001", Admin "0000"