Recently we tried to improve one of our BPC MS Environments integration, using Admin_MakeDim DTSX.

This SSIS allows you to update an existing BPC Dimension from a MS DB Table.

The only prerequisite concerning you have to fulfill is related to the structure of MS Table: is must to reproduce the mbr table structure.

The following sample is designed on a copy of Environment Shell, on Product Dimension (uou can easily reproduce this behaviour in your system).

Product Dimension has just 3 base members in a very simple Hierarchy

BPC_Admin_MakeDim_01.jpg

In MS SQL you have to create a “staging” table that has to reproduce mbr<dimension> table structure.

In our case we created mbrProductStaging, as a copy of mbrProduct

Then we copied data and did some simple changes:

a. added a new node (NEW_NODE)

b. added a new basemember (ProductD) connected to mentioned node (NEW_NODE)

c. changed parent of an existing base member (ProductA -> NEW_NODE)

BPC_Admin_MakeDim_02.jpg

From the EPM Add-In you have to run Admin_MakeDim.dtsx using the staging table (mbrProductStagin, in our case) as source

BPC_Admin_MakeDim_03.jpg

As a result Product Dimension will be correctly updated, both on Excel and MS SQL side, and reflecting all the changes done in mbrProductStaging table.

From the EPM Add-In Member Selector you can see the new ProductD, the changed ProductA under the NEW_NODE as well:

BPC_Admin_MakeDim_04.jpg

The only limitation is related to the BPC Admin Excel MemberSheet, that won’t be updated!

Unlucky the XLS Member Sheet won’t reflect such changes, even if MS DB Tables content have been changed!

BPC_Admin_MakeDim_05.jpg

This can lead to a loss of data, if you try to process mentioned dimension from MemberSheet.

In our case ProductD and NEW_NODE are going to be deleted:

BPC_Admin_MakeDim_06.jpg

In order to avoid member deletion and not to undo all the changes done by the Admin_MakeDim you have to execute “Export Dimensiontask command from BPC Admin, before making any changes. This will create a new XLS/XLSX file for the Dimension with exactly what is in the SQL DB table mbr<Dimension> (in our case mbrProduct MS SQL table)


BPC_Admin_MakeDim_07.jpg


We asked SAP whether “Export Dimension” command can be included in Admin_MakeDim Task (as a parameter) or created as BPC Task in SSIS.

Unlucky they said the current behaviour is “by product design” and suggested us to post this a new Idea.


So, if you think this is a product limitation to be overcome please add your vote!

Thanks


GFV



To report this post you need to login first.

Be the first to leave a comment

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

Leave a Reply