Automating Master Data (Dimension) Load in BPC MS 10

[former_member204026]

Introduction

 

The following is a simple example of how to load the master data into SAP BPC MS 10.  SQL server integration services package has been used to automate the master data load (dimension) in BPC. The example assumes that Microsoft excel will not be available on the server.

SSIS Package

SSIS (SQL Server Integration Servcies) Package is used to automate the dimension load. As part of this example:

1. Source data (from a table) is loaded into mbrEntity table,


2. BPC’s build dimension task is used to process the dimension and


3. Finally the dimension file (excel) is loaded so that the dimension members can be maintained from the BPC admin console

Task	Details
	This task loads the data from the source to the mbrEntity table. Make sure that the source query eliminates the entities already available in the mbrEntity Source Query: SELECT SRC.ID,SRC.NEWID, SRC.EVDESCRIPTION, SRC.PARENTH1, SRC.CURRENCY, SRC.ELIM, SRC.FX_TYPE, SRC.INTCO, SRC.OWNER, SRC.REVIEWER FROM SRCDB.DBO.SRCENTITY SRC WHERE SRC.ID IS NOT IN (SELECT ID FROM MBRENTITY) Destination Table: mbrEntity
	BPC's Make Dimension task to process the dimension from the mbrEntity table.
	This task uses an empty dimension file (identical to member file but without members) to replace the dimension file. This will allow the next task to load the data available in mbrEntity table to the dimension file.   Replacing the dimension file with an empty dimension template allows us to sync up the mbrEntity table and dimension file(excel). This is a work around to avoid deleting or updating the excel based dimension file on the server.   Empty Dimension File (template):
	Load the dimension file (which is empty now) with data available in mbrEntity table.   Source Table: mbrEntity   Excel Destination: Dimension file  \\Webfolders\<Environment>\AdminApp\Entity.xlsx

Data Manager

 

Copy the package (.dtsx) file to \\Webfolders\<Environment>\<Model>\DataManager\PackageFiles folder.  Create the new data manager package using Data Manager >> Organize Package List >> Add Package.



Choose .dtsx package,name for the data manager package  and save the packages.



The package is ready for execution. The package can be executed using the data manger >> run package option.

Additional Note

 

• Make sure you have Microsoft ACE 64 bit driver is installed on the server to avoid “The Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine” error


• The example doesn’t cover the following aspects, please pay special attention during implementation
  
  
  
  • Include appropriate data manager prompts
  
  
  • Include appropriate logging steps
  
  
  • Include roll back steps
  
  
  
  


• Please refer to SAP guide for further help