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:
- Source data (from a table) is loaded into mbrEntity table,
- BPC’s build dimension task is used to process the dimension and
- 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