How to deal with BPC Data Manager packages programmatically

If you work with BPC you might noticed that interface of Data Manager is very complicated for end-users.

In this post I will show you how you can skip routine actions and allow your users to start a package in a few clicks.

1. What is Local Response File

BPC EPM add-in v.10 automatically stores history of your prompt values in one Local Response File (named DMUserSelection.xml) in addition, you can use custom Local Response Files to store predefined prompt values and apply them when needed.

Thanks to this and ability to create/change these files dynamically you can develop more flexible and user friendly interfaces.

FYI: Actually, BPC v. 7.5 also had functionally similar file (DMMUserSelection.ini) but it has ini-format.

By default,  DMUserSelection.xml  file is located here:

  <USERPROFILE>\Documents\PC_{0}\<DOMAIN>\<user name>\AppInfo\<Environment>\<Model>\DataManager\<user name>\

In my inviroment:

c:\Users\snelyapenko\Documents\PC_{0}\MY-DOMAIN\snelyapenko\AppInfo\BPC_BUDGET\MY_MODEL\DataManager\snelyapenko\

But as I said before you can create your own versions of this Local Response File and place them where ever you need.

2. Get package properties

There are several ways how to get package properties, here is one of them.

First of all you need to know the name of your package and its group:

Data Manager  – > Organise – > Organise Package list

In my case:

Package Name = “Test Package”

Package Group = “Admin”

In my example this information is enough to find it and work with this package. Code below shows how you can do it.

'Declare all the variables
Dim pkge() As FPMXLClient.ADMPackage
Dim autoDM As New FPMXLClient.EPMAddInDMAutomation
Dim auto As New FPMXLClient.EPMAddInAutomation
Dim tm As New FPMXLClient.ADMTeam
Dim gr As New FPMXLClient.ADMPackageGroup
Dim serchFlg As Boolean
Dim pkgeName, pkgeGroup, pkgeProperties As String
Dim pkgIndex As Integer
'Here is our custom Local Response File
inFileName = Environ$("USERPROFILE") & "\Documents\test.xml"
pkgeName = "Test Package"    '<-- Place here your package name
pkgeGroup = "Admin"               '<-- Place here your package group name
With tm
    .Description = ""
    .ID = ""
End With
With gr
    .GroupId = pkgeGroup
    .TeamId = ""
End With

 'Get all the packages in the group
pkge = autoDM.GetPackageList(tm, gr)

'Search your package within the group
 serchFlg = False
 For i = LBound(pkge) To UBound(pkge)
     If pkge(i).PackageId = pkgeName Then
        pkgeProperties = "Filename:" + pkge(i).Filename _
        + Chr(13) + "GroupId:" + pkge(i).GroupId _
        + Chr(13) + "PackageDesc:" + pkge(i).PackageDesc _
        + Chr(13) + "PackageId:" + pkge(i).PackageId _
        + Chr(13) + "PackageType:" + pkge(i).PackageType _
        + Chr(13) + "TeamId:" + pkge(i).TeamId _
        + Chr(13) + "UserGroup:" + pkge(i).UserGroup _
        + Chr(13) + "Array Index:" + Str(i)
        MsgBox pkgeProperties, , "It's good to know all your package properties"
        'Mark flag
        serchFlg = True
        'Store index
        pkgIndex = i
      End If
 Next i
If serchFlg = False Then
MsgBox "Your package " + pkgeName + " is not found within the group " + pkgeGroup
End If 

1. Create Custom Local Response File

Following code opens regular Run Package wizard and creates custom local response file (xml) with defined prompt values.

I create it once for each package just to have it structure during the development.

'Create your Local Response File
Call autoDM.CreateLocalResponseFile(pkge(pkgIndex), inFileName)

Before using this file you can change any parameter in it according to the context or other logic, or just create it entirely, e.g. I did it using simple Print Method, but you can use MSXML or something else but all this is outside the scope of this post.

4. Run package

Following code runs package with predefined custom Local Response File.

It runs the package in “silent mode”, so I recommend you add here massage box that informs user about what he is going to run.

'Run package with predefined custom Local Response File
Call autoDM.RunPackage(pkge(pkgIndex), inFileName)

5. Other features

Following code opens Run Package Wizard of specified package with default Local Response File (DMUserSelection.xml)

'Open Run Package Wizard with default Local Response File (DMUserSelection.xml)
Call auto.DataManagerRunPackage("Test Package", "Admin", "")

If you know all your package properties you can specify them explicitly in the following structure and then use this variable to refer your package.

'Declare package variable
With pkge1
    .Filename = "MY FILES/Admin/Test Package.dtsx"
    .GroupId = "Admin"
    .PackageDesc = "Test Package"
    .PackageId = "Test Package"
    .PackageType = "File"
    '.TeamId = "Company (Public)"
    .TeamId = ""
    .UserGroup = "0001"
End With
'Run this package
Call autoDM.RunPackage(pkge1, inFileName)

This post is just an example of how you can work with Data Manager API and you should not consider it as a complete solution.

Best Regards,
  Sergey