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.
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.
- 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
Referenced link:
How to deal with BPC Data Manager packages programmatically
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.
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 "<" with "<" and all ">" 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.