Automating Master Data (Dimension) Load in BPC MS 10
Introduction
SSIS Package
- 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:
Excel Destination: |
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
I love the environment name BADSHELL ...lol 🙂
🙂
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
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
Please refer to the how to register @ SAP Help. You should find one for BPC 10. You should be able find more details there.
Registering Custom Tasks - SAP BusinessObjects Planning and Consolidation - SAP Library
Hello, Badrish,
Thank you for your feedback. Yes, the guide was very useful and I've successfully implemented custom task.
Best regards,
Donatas Budrys
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
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
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.
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
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.
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.