VBA Macro to run DMP to import transaction data from BW Cube

[Former Member]

dear SAP Gurus

I'm trying to create a VBA Macro which user can run to import Transaction Data from BW Cube. The selections for the BW Cube fields are fixed and will not change.

I created a DMP called "Import TX BW" based on Process Chain /CPMB/LOAD_INFOPROV_UI.

I wrote a code which will run the package based on the ADMPackage values and also the prompt file.

Public Sub ExecuteDMPackageWithParameters()

    ' Get the DM Automation class instance

    Dim objDMautomation As EPMAddInDMAutomation

    Set objDMautomation = New EPMAddInDMAutomation

    ' Prompt file specified in Name Range PKG

    objDMautomation.RunPackage objPackageFromSheet("PKG"), strFilename("PKG")

End Sub

objPackageFromSheet is a ADMPackage function which will store the data of the package

strFilename is a String function which will store the path of the Prompt File.

Private Function objPackageFromSheet(strRange As String) As ADMPackage

    Dim i As Integer

    ' Get the range in which the DM package paramteres is set

    Dim rngPackageRange As Range

    Set rngPackageRange = Application.Names(strRange).RefersToRange

    ' Create the ADM Package object

    Set objPackageFromSheet = New ADMPackage

    ' Loop through the rows

    For i = 1 To rngPackageRange.Rows.Count

        Select Case rngPackageRange(i, 1).Value

            Case "Filename"

            objPackageFromSheet.Filename = rngPackageRange(i, 2).Value

            Case "GroupId"

            objPackageFromSheet.GroupId = rngPackageRange(i, 2).Value

            Case "PackageDesc"

            objPackageFromSheet.PackageDesc = rngPackageRange(i, 2).Value

            Case "PackageId"

            objPackageFromSheet.PackageId = rngPackageRange(i, 2).Value

            Case "PackageType"

            objPackageFromSheet.PackageType = rngPackageRange(i, 2).Value

            Case "TeamId"

            objPackageFromSheet.TeamId = rngPackageRange(i, 2).Value

            Case "UserGroup"

            objPackageFromSheet.UserGroup = rngPackageRange(i, 2).Value

        End Select

    Next

End Function

Private Function strFilename(strRange As String) As String

    Dim i As Integer

    ' Get the range in which the DM package paramteres is set

    Dim rngPackageRange As Range

    Set rngPackageRange = Application.Names(strRange).RefersToRange

    ' Loop through the rows

    For i = 1 To rngPackageRange.Rows.Count

        If rngPackageRange(i, 1).Value = "PromptFile" Then

            strFilename = rngPackageRange(i, 2).Value

            Exit Function

        End If

    Next

End Function

PKG as range in Excel

Parameter	Value
Filename	/CPMB/LOAD_INFOPROV_UI
GroupId	Data Management
PackageDesc	Import TX BW
PackageId	Import TX BW
PackageType	Process Chain
TeamId
UserGroup	0001
PromptFile	D:\BPCMDUPLOAD\MDPromptFileTest.txt

D:\BPCMDUPLOAD\MDPromptFileTest.txt contents:

Import TX BW{param_separator}<?xml version="1.0" encoding="utf-16"?>

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

<AnswerPromptPersistingFormat>

<_ap>

<Name>%InforProvide%</Name>

<Values> <string>ZCUPC01</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%SELECTION%</Name>

<Values>

<string><Selections><Selection Type="Selection"><Attribute><ID>0PROFIT_CTR</ID><Operator>1</Operator><LowValue>074000-002</LowValue><HighValue /></Attribute><Attribute><ID>ZCHGLACT</ID><Operator>3</Operator><LowValue>19510000</LowValue><HighValue>19520000</HighValue></Attribute><Attribute><ID>0FISCPER</ID><Operator>1</Operator><LowValue>2014001</LowValue><HighValue /></Attribute></Selection><Selection Type="FieldList"><FieldID>0COMP_CODE</FieldID><FieldID>0PROFIT_CTR</FieldID><FieldID>ZCHCOCDTR</FieldID><FieldID>ZCHFISPER</FieldID><FieldID>ZCHGLACT</FieldID></Selection></Selections></string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%TRANSFORMATION%</Name>

<Values> <string>\ROOT\WEBFOLDERS\XXX\XXX\DATAMANAGER\TRANSFORMATIONFILES\TRANSACTION\ACTUAL_IMPORT.xlsx</string> </Values> </_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%TARGETMODE%</Name>

<Values> <string>1</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%RUNLOGIC%</Name>

<Values> <string>1</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%CHECKLCK%</Name>

<Values> <string>0</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

</ArrayOfAnswerPromptPersistingFormat>

The Prompt file is created based on a successful DMP run which is done manually:

When I ran the macro, it gives an error message:

Run-time error '-1073479167 (c0040201)':

There is an error in XML document (13,22).

I assume it is referring to line 13,22 in the prompt file. I'm not sure what is wrong with the file, but it is referring to the part where the SELECTIONS on the BW InfoProvider is.

I did a test where I removed the "SELECTIONS" code (hightlighed in bold below) in order to remove the filter and take on all data in the BW Cube, and it works! However this does not give me the option to filter the data to import from BW to BPC...

Import TX BW{param_separator}<?xml version="1.0" encoding="utf-16"?>

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

<AnswerPromptPersistingFormat>

<_ap>

<Name>%InforProvide%</Name>

<Values> <string>ZCUPC01</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%SELECTION%</Name>

<Values> <string></string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%TRANSFORMATION%</Name>

<Values> <string>\ROOT\WEBFOLDERS\XXX\XXX\DATAMANAGER\TRANSFORMATIONFILES\TRANSACTION\ACTUAL_IMPORT.xlsx</string> </Values> </_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%TARGETMODE%</Name>

<Values> <string>1</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%RUNLOGIC%</Name>

<Values> <string>1</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

<AnswerPromptPersistingFormat>

<_ap>

<Name>%CHECKLCK%</Name>

<Values> <string>0</string> </Values>

</_ap>

</AnswerPromptPersistingFormat>

</ArrayOfAnswerPromptPersistingFormat>

Does anyone have any experience on this to share? Would be greatly appreciated.

[former_member186338 · Accepted solution]

OK, I will try to help you

Please explain what do you want to select?

I can see:

0PROFIT_CTR 074000-002 (single value)

ZCHGLACT 19510000 to 19520000 (range)

0FISCPER 2014001 (single value)

Is it a full list?

[Former Member · Accepted solution]

hi Vadim

Apreciate your help

Yes, that is for the Selection part:

For the dimension / field list part, it is for the following 5 fields:

<Selection Type="FieldList">

<FieldID>0COMP_CODE</FieldID>

<FieldID>0PROFIT_CTR</FieldID>

<FieldID>ZCHCOCDTR</FieldID>

<FieldID>ZCHFISPER</FieldID>

<FieldID>ZCHGLACT</FieldID>

</Selection>

[former_member186338 · Accepted solution]

Do you want to change this selection in VBA code?

[Former Member · Accepted solution]

Yes I would like to change this in the VBA Code

[former_member186338 · Accepted solution]

Then do the following:

1. Create New DM package for /CPMB/LOAD_INFOPROV_UI

2. In the advanced script use fixed selections for all variables based on standard package run.

2. Test that it's working.

3. Split SELECTION into number of info variables.

4. Use Prompt TEXT to get values for 0PROFIT_CTR,...

5. Combine SELECTION

6. Test DM with TEXT prompts

7. Use VBA automation to pass text prompts.

[former_member186338 · Accepted solution]

In your case the advanced DM script will be:

PROMPT(TEXT,%PROFIT%,"Profit Center")

PROMPT(TEXT,%ACCTLO%,"Account From")

PROMPT(TEXT,%ACCTHI%,"Account To")

PROMPT(TEXT,%PER%,"Period")

INFO(%S1%,<?xml version="1.0" encoding="utf-16"?><Selections xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><Selection Type="Selection"><Attribute><ID>0PROFIT_CTR</ID><Operator>1</Operator><LowValue>)

INFO(%S2%,</LowValue><HighValue /></Attribute><Attribute><ID>ZCHGLACT</ID><Operator>3</Operator><LowValue>)

INFO(%S3%,</LowValue><HighValue>)

INFO(%S4%,</HighValue></Attribute><Attribute><ID>0FISCPER</ID><Operator>1</Operator><LowValue>)

INFO(%S5%,</LowValue><HighValue /></Attribute></Selection><Selection Type="FieldList"><FieldID>0COMP_CODE</FieldID><FieldID>0PROFIT_CTR</FieldID><FieldID>ZCHCOCDTR</FieldID><FieldID>ZCHFISPER</FieldID><FieldID>ZCHGLACT</FieldID></Selection></Selections>)

INFO(%TEMPNO1%,%INCREASENO%)

INFO(%ACTNO%,%INCREASENO%)

INFO(%KEYDATE%,)

TASK(/CPMB/INFOPROVIDER_CONVERT,OUTPUTNO,%TEMPNO1%)

TASK(/CPMB/INFOPROVIDER_CONVERT,ACT_FILE_NO,%ACTNO%)

TASK(/CPMB/INFOPROVIDER_CONVERT,TRANSFORMATIONFILEPATH,\ROOT\WEBFOLDERS\XXX\XXX\DATAMANAGER\TRANSFORMATIONFILES\TRANSACTION\ACTUAL_IMPORT.xlsx)

TASK(/CPMB/INFOPROVIDER_CONVERT,SUSER,%USER%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPPSET,%APPSET%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPP,%APP%)

TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,ZCUPC01)

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%S1%%PROFIT%%S2%%ACCTLO%%S3%%ACCTHI%%S4%%PER%%S5%)

TASK(/CPMB/INFOPROVIDER_CONVERT,KEYDATE,%KEYDATE%)

TASK(/CPMB/LOAD_IP,PREPROCESSMODE,0)

TASK(/CPMB/LOAD_IP,TARGETMODE,1)

TASK(/CPMB/LOAD_IP,INPUTNO,%TEMPNO1%)

TASK(/CPMB/LOAD_IP,ACT_FILE_NO,%ACTNO%)

TASK(/CPMB/LOAD_IP,RUNLOGIC,1)

TASK(/CPMB/LOAD_IP,CHECKLCK,0)

TASK(/CPMB/LOAD_IP,KEYDATE,%KEYDATE%)

Please check that it's working!

Then use VBA code to pass 4 variables like here:

[Former Member · Accepted solution]

Thanks Vadim, it works now Thank you for your help once again!

[Former Member]

Hi Edmund,

If your BW selections will be fixed, then what is the reason to use Process Chain /CPMB/LOAD_INFOPROV_UI???? This process chain is used if you are going to give users the ability to change the selections. Are they going to change?

In your case I feel you can use the normal process chain called /CPMB/LOAD_INFOPROV, and give the BW selections while validating the Transformation file! It doesn't change. To run the DMP from macro use below simple code....

Option Explicit

Dim EPM As New FPMXLClient.EPMAddInAutomation

Sub (Button Name)

EPM.DataManagerRunPackage "(Package name)", "(Group ID)", ""

‘ To view status of the DM Package

EPM.DataManagerOpenViewStatusDialog

  End Sub

Hope this helps.....

Regards,

JP

[former_member186338]

"and give the BW selections while validating the Transformation file" - how ? No selections possible with /CPMB/LOAD_INFOPROV

[Former Member]

Vadim...When you Validate and Process the Transformation file, while selecting the Infocube from the list below there is a button named "Set Selections". In this we can give the selections we want to filter the data. Right? Not in the DMP.

Regards,

JP

[former_member186338]

Sorry, but absolutely not related to /CPMB/LOAD_INFOPROV

No selections possible.

"Validate and Process the Transformation file" - just to validate file that can be used with different chains!

[Former Member]

How can the same file be used for different chains??

Vadim many people have been using this approach for the past many years to successfully load the transaction data from BW cube into BPC with filters. Those selections given in the transformation file are kept with that transformation file even while running the DMP.  You can test!

I wont suggest it if I hadn't tested and used it....

For me, macro would be complex!

Regards,

JP

[former_member186338]

Sorry, but you are absolutely incorrect!

When you validate transformation file first you select the chain to be used for validation:

Selection here assumes that the chain will be /CPMB/LOAD_INFOPROV_UI

But it's not stored in any place! Selection is provided only to test the transformation file logic!

After validation you can use this transformation file with

/CPMB/LOAD_INFOPROV_UI

or with

/CPMB/LOAD_INFOPROVIDER

But /CPMB/LOAD_INFOPROVIDER do not support any filter!

"I wont suggest it if I hadn't tested and used it...." - test again

And if you read answers you will see that topic author don't have fixed selections

[Former Member]

But /CPMB/LOAD_INFOPROVIDER do not support any filter! - correct I agree. But it definitely takes the filter given in the transformation file for loading!

While validating I agree we select the Data type as " Transaction data from netweaver BW infoprovider" which can be used for any of these 2 process chains /CPMB/LOAD_INFOPROV_UI OR /CPMB/LOAD_INFOPROVIDER.

Topic author had earlier said in his original post that BW selections are fixed...so at that time I suggested this.

I'm trying to create a VBA Macro which user can run to import Transaction Data from BW Cube. The selections for the BW Cube fields are fixed and will not change.

Regards,

JP

[former_member186338]

"But it definitely takes the filter given in the transformation file for loading!" who told you this fairy tale ???????

The only way to filter something for /CPMB/LOAD_INFOPROVIDER is to use SELECTION statement in transformation file directly: Data Transformation Maintenance - SAP BusinessObjects Planning and Consolidation - SAP Library

But it's limited to single member for dimension

And no relation to validation process!!!

[former_member186338]

"Topic author had earlier said in his original post that BW selections are fixed...so at that time I suggested this." - after I asked another question:

"Do you want to change this selection in VBA code?"

Answer:

"Yes I would like to change this in the VBA Code"

Please read full discussion