Executing Data Manager Package Silently (No Prompts)

[gajendra_moond]

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:

<USERPROFILE>\Documents\PC_NW\<DOMAIN>\<user name>\AppInfo\<Environment>\<Model>\DataManager\<user name>\

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 "<Package" and "</Package" as highlighted above. This xml will contain the latest selections for that DMP on that machine. That is the reason that next time when you execute the DMP, you see your selections automatically filled based on the selections made last time. In this case, the package has been executed for TIME = 2017.01

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 "<Answer>" and "</Answer>" of the highlighted xml code in a notepad or wordpad or Excel

- Replace all "&lt;" with "<" and all "&gt;" with ">"

Here is how the code will look like:

Copy to remaining Periods{param_separator}<?xml version="1.0" encoding="utf-16"?>

<ArrayOfAnswerPromptPersistingFormat xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <AnswerPromptPersistingFormat>

    <_ap>

      <Name>%SELECTION%</Name>

      <Values />

    </_ap>

    <_apc>

      <StringListPair>

        <str>TIME</str>

        <lst>

          <string>2017.01</string>

        </lst>

      </StringListPair>

    </_apc>

  </AnswerPromptPersistingFormat>

</ArrayOfAnswerPromptPersistingFormat>

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}<?xml version=" & vQuote & "1.0" & vQuote & " encoding=" & vQuote & "utf-16" & vQuote & "?>"

Print #1, "<ArrayOfAnswerPromptPersistingFormat xmlns:xsi=" & vQuote & "http://www.w3.org/2001/XMLSchema-instance" & vQuote & " xmlns:xsd=" & vQuote & "http://www.w3.org/2001/XMLSchema" & vQuote & ">"

Print #1, "  <AnswerPromptPersistingFormat>"

Print #1, "    <_ap>"

Print #1, "      <Name>%SELECTION%</Name>"

Print #1, "      <Values />"

Print #1, "    </_ap>"

Print #1, "    <_apc>"

Print #1, "      <StringListPair>"

Print #1, "        <str>TIME</str>"

Print #1, "        <lst>"

Print #1, "          <string>" & iTimeId & " </string>"

Print #1, "        </lst>"

Print #1, "      </StringListPair>"

Print #1, "    </_apc>"

Print #1, "  </AnswerPromptPersistingFormat>"

Print #1, "</ArrayOfAnswerPromptPersistingFormat>"

'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 "USERPROFILE\Documents\". 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") & "\Documents\LocalResponse.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.