BPC MS Admin Excel Member Sheet not updated (when running Admin_MakeDim DTSX)

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

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)

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

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:

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!

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:

In order to avoid member deletion and not to undo all the changes done by the Admin_MakeDim you have to execute “Export Dimension” task 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)

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