Skip to Content

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
/wp-content/uploads/2013/10/image002_297819.jpg
Task Details
/wp-content/uploads/2013/10/image003_297820.jpg
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

/wp-content/uploads/2013/10/image005_297830.jpg

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
/wp-content/uploads/2013/10/image006_297831.jpg
BPC’s Make Dimension task to process the dimension from the mbrEntity table.
/wp-content/uploads/2013/10/image008_297832.jpg
/wp-content/uploads/2013/10/image009_297833.jpg

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.

 

/wp-content/uploads/2013/10/image011_297834.jpg

Empty Dimension File (template):

/wp-content/uploads/2013/10/image013_297835.jpg

/wp-content/uploads/2013/10/image014_297836.jpg

Load the dimension file (which is empty now) with data available in mbrEntity table.

/wp-content/uploads/2013/10/image016_297837.jpg

 

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.

/wp-content/uploads/2013/10/image018_297838.jpg

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

/wp-content/uploads/2013/10/image022_297839.jpg

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

/wp-content/uploads/2013/10/image024_297840.jpg

/wp-content/uploads/2013/10/image026_297841.jpg

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
To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Donatas Budrys

    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,

    Donatas

    (0) 
  2. Donatas Budrys

    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,

    Donatas

    (0) 
      1. Donatas Budrys

        Hello, Badrish,

        Thank you for your feedback. Yes, the guide was very useful and I’ve successfully implemented custom task.

        Best regards,

        Donatas Budrys

        (0) 
  3. Hitesh Patel

    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.

    Thanks,

    Hitesh

    (0) 
    1. Donatas Budrys

      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.

      Donatas

      (0) 
  4. Alvaro Jover

    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?

    Regards,

    Álvaro.

    (0) 
    1. Donatas Budrys

      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.

      Regards,

      Donatas

      (0) 
      1. Alvaro Jover

        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?

        Regards,

        Álvaro.

        (0) 
  5. Alvaro Jover

    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

    (0) 

Leave a Reply