Executing Data Manager Package Silently (No Prompts)

Introduction

This blog post does not contain a new approach but an attempt to explain the process of running Data Manager Packages without any prompts. It delves a little deeper to understand the concept of local response file and how to leverage it for various purposes.

Scope

The scope of this blog is to provide an ability to run a Data Manager Package without any prompts. This is applicable especially in the cases where a user refreshes on a predefined context and then wish to trigger any post processing – say via a Script Logic.

Software Components

– SAP BW7.31

– SAP BPC 10.0 Version for SAP NW

– EPM Excel Addin – Version 10 SP21 .NET4

– MS Excel 2013 (32-bit) on Windows 7 machine

Additional Details

Referenced link:

How to deal with BPC Data Manager packages programmatically

Case

Let us say we have an input form where a user enters value for a month of the year and then triggers a script logic to copy the same value across all other months of that year. We will be referring a Data Manager Package named – Copy to remaining Periods which contains the script logic to perform the copy. The input for this DMP is value for a month(base level here) for the TIME dimension.

Explanation

Every time you run a DMP in a dialog mode, selections are to be entered as requested before it is to be run. When you execute a DMP manually for the first time, an XML file will be created named – DMUserSelection.xml and the default location of the file is:

DocumentsPC_NWAppInfoDataManager

This xml contains details of all the DMPs and their selections for a given model in the Environment. Now let us take a look at the content of this xml file:

Every package’s response is captured between “

Now if we could somehow pass this xml response to DMP, we can avoid the user prompts and can execute it silently. VBA is capable of generating text and xml files and we will be leveraging it for our purpose. Perform the following steps:

– Copy the xml code between “” and “” of the highlighted xml code in a notepad or wordpad or Excel

– Replace all “<” with “<” and all “>” with “>”

Here is how the code will look like:

 Copy to remaining Periods{param_separator}         <_ap>       %SELECTION%                 <_apc>                TIME                    2017.01                          

Now notice that the top two lines contain quote marks and VBA will not be able to read quotes within quotes, so we will replace it with a variable called vQuote. We will be creating a function to generate a local response xml based on the DMP ID, Dimension and selection. This is how the VBA function will look like:

 Private Sub CreateLocalResponseXMLFile(iFileName As String, iPackageId As String, iDimName As String, iTimeId As String) 'Local Declerations Dim vQuote As String vQuote = Chr$(34) 'Close any open files Close 'Open XML File Open iFileName For Output As #1 'Populate XML File line by line Print #1, iPackageId & "{param_separator}" Print #1, "" Print #1, "  " Print #1, "    <_ap>" Print #1, "      %SELECTION%" Print #1, "      " Print #1, "    " Print #1, "    <_apc>" Print #1, "      " Print #1, "        TIME" Print #1, "        " Print #1, "          " & iTimeId & " " Print #1, "        " Print #1, "      " Print #1, "    " Print #1, "  " Print #1, "" 'Close XML File Close #1 End Sub 

Notice that “2017.01” is replaced by iTimeId so that desired variable can be passed. Let us say that we want to pass selection as the first month of the next year every time we execute it. Now we will have a DMP to trigger a script logic to copy value from one period to all other periods. We will generate and save the local response file under “USERPROFILEDocuments”. The macro will check for old response file and deletes it before creating the new one.

Here is how the macro would look like:

 Sub Copy_to_Periods() Dim cPackageId As String Dim cDimName As String Dim cTimeId As String Dim myFolderName, myFileName As String Dim vFileName As String 'General type declerations Dim oAutomation As Object Set oAutomation = CreateObject("FPMXLClient.EPMAddInAutomation") Dim oAutomationDM As Object Set oAutomationDM = CreateObject("FPMXLClient.EPMAddInDMAutomation") Dim oPackage As Object Set oPackage = CreateObject("FPMXLClient.ADMPackage") cTimeId = Year(Now()) +1 & ".01" cDimName = "TIME" cPackageGroupId = "Your_Package_Group_ID" cPackageId = "Copy to remaining Periods" 'Declare package With oPackage .Filename = "/CPMB/DEFAULT_FORMULAS" '<----Enter the process chain ID .GroupID = cPackageGroupId .PackageDesc = "" .PackageId = cPackageId .PackageType = "Process Chain" .TeamID = "Your_team_ID" .UserGroup = "Your_user_Group" End With 'Path of Local Response XML File vFileName = Environ$("USERPROFILE") & "DocumentsLocalResponse.xml" 'Locate and delete old local response file 'Enter file path to delete files from myFolderName = Environ$("USERPROFILE") & "Documents" myFileName = Dir(myFolderName & "*.xml") 'Delete all files without an Excel extension Do While myFileName <> "LocalResponse.xlsx"   If myFileName = "" Then Exit Do   Kill myFolderName & myFileName   myFileName = Dir Loop 'Create Local Response XML File Call CreateLocalResponseXMLFile(vFileName, cPackageId, cDimName, cTimeId) 'Run Package Call oAutomationDM.RunPackage(oPackage, vFileName) End Sub 

Once this macro is executed, you can look for the generated xml file for local response under predefined folder. On opening that file, you will see the desired value that we wanted to pass.

This is a very simple case but hope it helps to understand the basics of Local Response xml and how to use it.