Skip to Content

Automating Master Data (Dimension) Load in BPC MS 10



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:
Destination Table:
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:


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
You must be Logged on to comment or reply to a post.
  • Hi, Badrish,

    Thank you for your post. It’s really very useful.

    I am currently using SAP BPC 10.1 MS. Are there any changes / enhancements in this area in the latest SAP BPC 10.1 MS version?

    Are there any other ways to automatically load Dimension Members into BPC dimensions (with member sheet update)?

    Can you please elaborate more about Import Dimension example package? What it does?

    Looking forward your feedback.

    Thank you very much.

    Best regards,


  • Hi, Badrish,

    Can you please describe how you did a Build BPC dimension task? Which SSIS task was used? How the parameters were filled?

    Thank you in advance.

    Best regards,


  • Hi Badrish,

    I am running into the “The Microsoft.Jet.OLEDB.4.0 provider is not registered” error.  I have the Access Database Engine 2010 installed.  Would I still need to install the Microsoft ACE 64 bit driver installed in side by side fashion?

    The package works if run from server but throws this error when run from EPM data manager.



    • Hi, Hitesh,

      Can you please check which ACE diver versions (64 bit or 32 bit) are installed on BPC server, SQL Server and in Development environment?

      Thank you.


  • Hi experts,

    Following the guide I receive an error in the step  “Excel Destination”. I generate correctly the “Excel connection manager”, but when I try to select the “Name of the Excel sheet”, it doesn’t show anything.

    I don`t have MS Excel installed in the server. I am usin SQL server 2012, VS 2012, BPC 10 MS.

    Any idea?



    • Hi, Alvaro,

      Please check if you are working with correct Access Database Engine drivers. SAP BPC server should have 64-BIT and the development environment 32-BIT drivers.



      • Hello Donatas,

        Thank you for your answer.

        The BPC server runs  in 64 bit. What do you mean with the development environment?

        Inside the “Excel Destination” I get that error: “The requested OLE DB provider Microsoft  ACE OLE DB 12.0 is not registed. If the 32 bit driver is not installed, run the package in 64 bit”.

        Any idea?



  • Hi experts,

    I finally solved my last issue but now I have another problem.

    When I execute the package, the “BPC Task Make Dim” fails without giving any error. Does anyone kwon how to solve this?

    Thank you.

    BPC Task Makedim fails.png

    BPC Task Makedim fails.png