The purpose of this document is to provide guidance for setting up a VBA button that creates a dimension member on the fly and seeing the results after the execution with one click from an input form.
Here is what a sample input form and the magic button would look like after it is set up:
Abdullah Baydas is a Senior SAP BI-BPC Consultant with SAP project experience in various industries since 2007. He played a very active role in many full cycle BPC projects and full cycle BW/BI projects. He designed and implemented BW-BI solutions for many use-cases and ETL scenarios. He developed reports in numerous SAP reporting platforms including BW-BEX, BPC, SAP Business Objects BI Products, and ABAP.
He is currently working as a Senior Technical Consultant in BPC and BW-BI projects for Performance Analytics Corporation, Canada.
I encountered several occasions where business users keep asking for a magic button that will create a dimension member conveniently from an input form. One example of this would be an input form where business users need to plan new employee hires in an HR model.
There is a How-to guide for creating Master Data on the fly for BPC 10 NW and it addresses the requirement really well but from the UI perspective it requires a few steps before an end user actually sees the new member on an input form/report. They need to run a package and pass through several prompts and windows, and after the package is completed must “Log Off & Log On” or select “Refresh Metadata for Current Connection.” Lastly they need to refresh the input form so that the new member shows up on the reports or input forms. End users are required to pass through quite a few windows and buttons to complete the task. I am not saying it is hard but any developer who has dealt with business users will tell you that they would like a magic button option. One button that rules them all… so to speak.
- This document applies to BPC 10 version for NW and EPM Add-in version must be as of Support Package 13 Patch 02 (RefreshConnectionMetadata API is available as of SP13-p2).
- “Master Data on the Fly in SAP BusinessObjects Planning and Consolidation 10.0, version for NetWeaver” how to guide must be implemented.
- The End user who logged on to EPM Add-in needs to have proper rights to run a DM Package and create dimension members.
Step by Step Procedure:
Note: I am using a TEST environment which is a new copy of ENVIRONMENTSHELL.
Step 1: Note down the Package Name and Package Group for the package you created with the How-to Guide.
First and foremost, test the MODF package and make sure it is working properly.
The package is created based on the HTG but we need to note down two important parameters. The first parameter is the Package Name which is “Create Product MDOF” in my case and the second parameter is the Package Group which is “Data Management” for my package.
Step 2: Copy the VBA Macro to the Workbook.
Go to the Developer ribbon in Excel and select Visual Basic. If the Developer tab is not active you need to add the tab from “Excel Options –> Customize Ribbon”.
Right Click anywhere in the Project explorer window and select “Insert –> Module”
Double click Module1 and copy & paste the VBA Macro from attached text file “VBA Macro for Magic Button to create a dimension member from an Input Form.txt” to the Module1.
Change the Constant values according to your Scenario.
i) cPackageGroupId (Package Group): Specify the Package Group of MDOF package that you noted down in step 1
ii) cPackageId (Package) : Specify the Package ID of MDOF package that you noted down in step 1
iii) cDimName : Specify the Dimension Name that you’d like to create a new member for
iv) cParentMbrId : Specify a Parent ID for the new member
v) cTemplateMbrId : Specify the template member ID ( I am using ProductA as a template)
vi) cNewMemberIDRange : Specify the excel Input cell for ID of the new member (You might have to re-define it after you create the input form in Step 3)
vii) cNewMemberDescRange : Specify the excel Input cell for description of the new member (You might have to re-define it after you create the input form in Step 3)
Explanation of Macro: The macro first creates a Local Response XML file for the prompts of the MDOF package. Secondly, it runs the package in silent mode with the created Local Response XML file, then refreshes the metadata for current connection. Finally, it refreshes the worksheet and clears the input cells.
Close the VBA Editor and go back to Excel.
Step 3: Create a simple Input form with the previously named dimension placed on the row axis
If you are using a Planning model of a fresh copy of EnvironmentShell like I am you can create an input form similar to the one below.
Of course, the Input Form might be different for your scenario.
I’ll only point out a few important notes for the input form.
Important Note 1: The Row Axis members should contain the children of the Parent ID that you’d like to select in the new member creation. This is to ensure that the new member will be expanded in the rows when the report is refreshed. In my case, The parent for the new Product members will be “TotalProduct” member.
Important Note 2: Leave a few blank rows between the end of page axis and beginning column headings.
In my example, The Page Axis members ends at “C7” and Columns headings start at row 12 so there are a few blank rows (Rows; 8,9,10,11) in between which will be used for the new member ID and Description input cells and a VBA Button.
Step 4: Input Cells and Magic Button
Go to the cell “B9” and type “New Member Id”, This will be a heading so make it Bold.
Go to the cell “C9” and type “New Member Description”, This will be a heading so make it Bold.
Select range “B9:C10” and create borders so that it’ll look like an input box.
Important Note: In my example “B10” will be the New Member ID input cell and “C10” will be the New Member Description input cell. If your input cells are different you need to change the cNewMemberIDRange and cNewMemberDescRange constant values in the VBA Macro you just copied in step 2.
Select Developer Ribbon, select “Insert” , “Button”
Select the place for the Button.
Assign Macro “CreateNewMember” and press OK.
Right Click the Button, Select Edit Text and rename it something like “Create New <DimensionName> Member”
Here is the last state of the sample Input Form:
Step 5: Test the Magic Button.
Enter a new member ID and Description, press the magic button and count to 10.
You’ll see the new member in your report right away after the execution of macro is finished. The macro will create the new member, refresh connection metadata and refresh the input form for you.
Note: The macro waits for ten seconds until the package ends. If runtime of the package in your system is more than ten seconds you might have to increase waiting time. Search for the comment “‘Wait for 10 seconds until the package ends” in the macro and change the waiting time in the line “Application.Wait (Now + TimeValue(“0:00:10″)).”
You can check the package log from “DataManager –> View Status” in case you need to troubleshoot.
Troubleshooting on the Local Response XML file:
In my scenario the there are four prompts asked to the end users in the “Create Product MDOF” package. Those are “New Member ID”, “Description”, “Parent for the new Member” and “Template Member”.
Here is my package script to compare the prompts with yours;
PROMPT(TEXT,%PROP_ID%,”New Member ID”,)
PROMPT(SELECT,%SELECTION%,,”Select the Parent for the new member, Click Next”,”PRODUCT”)
PROMPT(SELECT,%TEMPLATE_MBR%,,”Select the Template Member (default property values will be copied from this member), Click Next”, “PRODUCT”)
If there are more prompts on your MDOF package, for example mandatory ACCTYPE property prompt for any Account type dimension, or if you need to define any other additional properties for the dimension you need to slightly change the VBA macro to create the XML Local response file correctly.
Scroll down on the CreateNewMember macro and search for the comment starts with ” ‘Uncomment the line below…”.
You need to uncomment the line that starts with ” SampleFileCreated = ” and comment out the line that starts with “Call CreateLocalResponseXMLFile(“.
Now enter a new member ID to the input cell and press the button to run the macro.
It’ll display the UI screen of the package. When you finish the selections and press run it’ll first create the XML file with the selections you just made and then run the package.
Note: This was a one time exercise, after it runs, don’t forget change the macro back to it’s original state. You need to comment out the line that starts with ” SampleFileCreated = ” and uncomment the line that starts with “Call CreateLocalResponseXMLFile(“.
Go to Documents folder under your user folder. “C:\Users\<WindowsUser>\Documents” in my case.
After this moment you need to scroll down in the VBA code and find “Private Sub CreateLocalResponseXMLFile”.
You need to change the code that populates the XML according to the sample Local Response XML file you just opened with Notepad. You might need to add some lines or delete some lines to populate the correct Local Response XML with this sub procedure.
After completing this task test the button again and make sure the new dimension member is created correctly.
- Master Data on the Fly in SAP BusinessObjects Planning and Consolidation 10.0, version for NetWeaver
- How to deal with BPC Data Manager packages rogrammatically