Understanding how to Preserve Manually maintained Alternate Hierarchies with the new BPC Data Manager Package to Load from BW Hierarchies

With the release of SAP Business Objects Planning and Consolidations (BPC) 7.5, version for SAP NetWeaver into Ramp Up this past December one of the key improvements was the additional BW master data integration.  Specifically BPC 7.5, version for SAP NetWeaver has two new Data Manager Packages / BW Process chains that represent this new set of functionality.

For a detailed look into this functionality you can also reference the excellent How-To Guide authored by Jeffrey Holdeman.

• /CPMB/IMPORT_IOBJ_MASTER - BPC Import BW InfoObject Master Data
• /CPMB/IMPORT_IOBJ_HIER - BPC Import BW InfoObject Hierarchy Data

This blog is going to highlight and explain the behavior of the latter of the two new Data Manager Packages listed above. Specifically this blog will highlight the way that hierarchies are mapped from BW InfoObjects to BPC Dimensions and how to "work around" some potentially unfavorable features that are standard with this package.

When running the newly delivered Data Manager Package as the end user you are prompted to select the following fields; your InfoObject Source, The format (external or internal), as well as the name of the transformation file, this is all fairly straight forward as most data manager packages are.

Once the InfoObject Source is selected, the format as well as the transformation file, the next critical step in using this package is to click on the "Set Selection" button and choose the hierarchies you wish to load from your chosen InfoObject.

The end user will be prompted to choose the Hierarchies they want to load into BPC, the hierarchy version (if relevant), the Member ID (the top node in from the hierarchy you wish to load), and the Level (if relevant).

One thing that is not extremely clear that I wanted to make crystal is the way that the source and target hierarchies are mapped to one another when using this Data Manager Package. Within this Data Manager Package the first line in the "Set Selection" prompt will load PARENTH1 in the target dimension; the second line will load PARENTH2 and so on.  The standard behavior of this package is to completely blow away / delete all pre-existing hierarchies in your target dimension. There is currently no way to preserve pre-existing hierarchies that you have manually maintained in BPC directly (i.e. PARENTH3) using the standard functionality.

To make this package behavior very clear I will outline the behavior step by step:

1. The entire set of hierarchies of the target dimension is deleted
2. The hierarchies selected in the data manager prompt interface are matched to the target BPC dimension hierarchies based on their position in the prompt: (using the above screenshot as an example)

• STDHIER will map to and overwrite PARENTH1
• ALTHIER2 will map to and overwrite PARENTH2

NOTE: If for example, the end user wanted to reload PARENTH2 by itself from SAP BW this would not be possible as the target hierarchy that is mapped to is determined by the position of the entry on the prompt whereas the source by the contents of the prompt itself; without the first line in the prompt there can be no second.

Once the specifications for the source infoObject and hierarchy have been made, select the "Next" button in the data manager package prompt.

You will then be asked to choose the target dimension you wish to load into. Once you have chosen your target dimension you can simply click the "Finish" button to execute the package.

Multiple customers that I have recently spoken with have communicated a business requirement to preserve pre-existing hierarchies that are not being loaded into from BW. For example, if there is an alternate cost center hierarchy that is manually maintained in BPC NW as PARENTH3 the business wants this hierarchy preserved even when loading PARENTH1 and PARENTH2. To achieve this goal we can leverage another recently introduced feature within BPC 7.5 NW; the data manager package end routine.

The following part of this blog will explain how to preserve manually maintained hierarchies in your target dimension that are NOT BEING LOADED INTO from BW.

The ROUTINE BADI was introduced in BPC 7.5 NW which allows you to create an ABAP transformation prior to the execution of the transformation and conversion file (STARTROUTINE) or after the execution of the transformation and conversion file (ENDROUTINE).

To create a BADI of type routine, login to the backend NetWeaver BW 7.01 system that your BPC NW system is installed on top of. Once you have logged in to your backend system, navigate to transaction SE19.

Under Create Implementation choose the Enhancement Spot "UJD_ROUTINE" and click Create. Name your Enhancement Implementation.

Select the name of your BADI Implementation, the ABAP Class which will hold the code implementing the routine, and make sure the BADI definition name is BADI_UJD_ROUTINE. Once you have populated these fields click the Green Check Mark to continue.

Within the ABAP Class named above (Z_CL_BPC_HIER_UPDATE)  you will have the standard IF_UJD_ROUTINE~RUNROUTINE method which will contain the main portion of code your start or end routine will leverage. For the purposes of our END ROUTINE you will create two other methods as well.

• ADD_TO_HIERARCHIES_TO_BE_UPDAT
• CONVERT_TO_N_FORMAT

I will take you now step by step through the process of adding the code necessary to execute this workaround.

First navigate to transaction se24 and modify the newly created class we just created Z_CL_HIER_UPDATE.

Now you are going to create the two new methods that were just mentioned above.

Type in the text names for both methods and give them descriptions accordingly. Once you have completed this you can select the "CONVERT_TO_N_FORMAT" method and click on the Parameters button.

Parameter

Type

Associated Type

I_HIE_MBR_NODES

Importing

UJA_T_MBR_NODE

R_HIE_N_NODES

Returning

UJN_T_HIER_NODE

Populate the parameter selections with the values outlined in the table above.

Once you have populated the parameter fields accordingly click on the button to take you into the actual code of the CONVERT_TO_N_FORMAT class method.

method convert_to_n_format. data: ls_hier_nodes type uja_s_mbr_node, lt_hier_nodes type uja_t_mbr_node, lsn_hier_nodes type ujn_s_hier_node, ltn_hier_nodes type ujn_t_hier_node, counter type i. counter = 1. refresh r_hie_n_nodes. lt_hier_nodes = i_hie_mbr_nodes. sort lt_hier_nodes by parent member. loop at lt_hier_nodes into ls_hier_nodes. at new parent. counter = 1. endat. lsn_hier_nodes-nodename = ls_hier_nodes-member. lsn_hier_nodes-parent = ls_hier_nodes-parent. lsn_hier_nodes-hier_name = ls_hier_nodes-hier_name. lsn_hier_nodes-order = counter. insert lsn_hier_nodes into table r_hie_n_nodes. counter = counter + 1. endloop. endmethod.

Cut and paste the above code into the CONVERT_TO_N_FORMAT method and save your work.Back out to the screen listing the methods select the “ADD_TO_HIERARCHIES_TO_BE_UPDAT” method and click on the Parameters button.

Parameter

Type
Associated Type

IT_HIER_NODES
Importing
UJN_T_HIER_NODE

CT_HIER_NODES
Changing
ANY TABLE

Populate the parameters according to the table above and enter the Implementation of the method ADD_TO_HIERARCHIES_TO_BE_UPDAT as we did previously for the CONVERT_TO_N_FORMAT method.  

method add_to_hierarchies_to_be_updat. data: l_lines type i, lr_data type ref to data, ls_hier_nodes type ujn_s_hier_node, counter type i. field-symbols: <ls_line> type any, <rowindex> type any, <lt_data> type standard table. create data lr_data like line of ct_hier_nodes. assign lr_data->* to <ls_line>. create data lr_data like table of <ls_line>. assign lr_data->* to <lt_data>. <lt_data> = ct_hier_nodes. sort <lt_data> by ('ROWINDEX') descending. read table <lt_data> index 1 into <ls_line>. assign component 'ROWINDEX' of structure <ls_line> to <rowindex>. counter = <rowindex>. sort <lt_data> by ('ROWINDEX'). clear <ls_line>. loop at it_hier_nodes into ls_hier_nodes. counter = counter + 1. <rowindex> = counter. move-corresponding ls_hier_nodes to <ls_line>. insert <ls_line> into table <lt_data>. endloop. refresh ct_hier_nodes. ct_hier_nodes = <lt_data>. * break-point. endmethod.

Cut and paste the above code into your method and save.We now need to create the Attributes of our class.

Hit the back button until you see list of methods that you have been working with.

Click on the Attributes Tab and create the class attributes detailed in the below matrix:

Attribute

Level
Visibility
Typing
Associated Type

P_APPSET_ID
Instance Attribute
Private
Type
UJ_APPSET_ID

P_APPL_ID
Instance Attribute
Private
Type
UJ_APPL_ID

PO_MODEL
Instance Attribute
Private
Type Ref To
IF_UJ_MODEL

P_DIM_TARG
Instance Attribute
Private
Type
UJ_DIM_NAME

Save your work. Finally, navigate once again to your Methods tab and enter the implementation for the IF_UJD_ROUTINE~RUNROUTINE method where our END ROUTINE will be executed from.

method if_ujd_routine~runroutine. data: l_success type abap_bool, l_tabname type tabname, l_log_msg type string, l_hier_name type uj_hier_name, ls_dimension type uja_dimension, lo_dim type ref to if_uja_dim_data, lo_hier type ref to if_uja_hier, ls_dim type uja_s_dim, lr_data type ref to data, lt_hier_nodes type uja_t_mbr_node, ls_hier_nodes type uja_s_mbr_node, lsn_hier_nodes type ujn_s_hier_node, ltn_hier_nodes type ujn_t_hier_node, lt_hier_info type uja_t_hier, lt_hiers type uja_t_hier, ls_hier_info type uja_s_hier, lt_package_paras type ujd_th_value, ls_package_paras type ujd_s_value. field-symbols: <lt_data> type standard table, <lt_final> type standard table, <l_hienm> type any, <l_account> type any, <l_newaccount> type any, <l_signeddata> type any, <l_newsigndta> type any, <ls_line> type any, <ls_newline> type any. " PUT IMPORT VARIABLES INTO MEMORY p_appset_id = i_source_info-appset_id. p_appl_id = i_source_info-app_id. read table it_package_paras with key fieldname = 'DIMNAME' into ls_package_paras. if sy-subrc eq 0. p_dim_targ = ls_package_paras-value. else. " If the dimension being loaded into isn't found something is wrong so exit the BADI er_data = ir_data. exit. endif. " CREATE MODEL REFERENCE FOR APPLICATION SET " Create a reference for the Application Set Model. cl_uj_model=>get_model( exporting i_appset_id = p_appset_id receiving ro_model = po_model ). if po_model is initial. raise exception type cx_ujp_process_exception exporting textid = cx_ujp_process_exception=>ex_no_appset_found d_appset = p_appset_id. exit. endif. ** "Read Target Dimension Data lo_dim = po_model->get_dim_data( i_dim_name = p_dim_targ i_appl_id = p_appl_id ). lo_dim->get_hier_list( importing et_hier_info = lt_hier_info ). assign ir_data->* to <lt_data>. create data lr_data like line of <lt_data>. assign lr_data->* to <ls_line>. create data lr_data like table of <ls_line>. assign lr_data->* to <lt_final>. assign component 'HIER_NAME' of structure <ls_line> to <l_hienm>. loop at <lt_data> into <ls_line>. read table lt_hier_info into ls_hier_info with key hier_name = <l_hienm>. if sy-subrc eq 0. delete lt_hier_info where hier_name eq <l_hienm>. endif. endloop. loop at lt_hier_info into ls_hier_info. l_hier_name = ls_hier_info-hier_name. lo_hier = lo_dim->get_hier_ref( i_hier_name = l_hier_name ). lo_hier->load( ). lo_hier->get_all_member( importing et_mbr_node = lt_hier_nodes ). ltn_hier_nodes = convert_to_n_format( lt_hier_nodes ). add_to_hierarchies_to_be_updat( exporting it_hier_nodes = ltn_hier_nodes changing ct_hier_nodes = <lt_data> ). endloop. <lt_final> = <lt_data>. get reference of <lt_final> into lr_data. er_data = lr_data. endmethod.

Copy and paste the above code into your class method and save and activate the entire class.

Navigate back to SE19. Display the BADI Implementation and you should now see the Methods that were just created. Double click on the Filter Val. Button.

Add a name to your end routine which you will later use in order to reference the routine from your transformation file. For the purposes of this blog we will use BPCHIEUPD however the name is arbitrary.

Activate your Enhancement Implementation and we are now ready to test our workaround.

Login to your BPC Administration Console and navigate to the dimension member sheet for the dimension whose hierarchies you wish to load from BW.

Notice that the dimension member sheet has three hierarchies and only two that are to be loaded. The standard behavior of this data manager package will be to delete the third manually maintained hierarchy. By employing our newly created End Routine we will be able to preserve the third hierarchy.

Navigate to the BPC for Excel Client and the transformation file associated with your BW hierarchy load package.

Within the transformation file specify the name of your endroutine as illustrated in the screenshot above.

Run your data manager package and specify the hierarchies that you are going to load into PARENTH1 and PARENTH2.

·         STDHIER = PARENTH1

·         ALTHIER2 = PARENTH2

Run your data manager package and you will see inside of the Admin Console that your target dimension still has all three hierarchies.

With BPC 7.5 NW there are quite a few new BADI’s that can help consultants and customers meet different business requirements. I recommend that you familiarize yourselves with these new BADI’s and the various business cases in which it makes sense to use them.

Scott Cairncross   is a Senior Director within the EPM RIG team and is responsible for the EPM RIG Americas.

---

I am very interested to hear the communities comments and constructive feedback. Thanks for your comments!
Comment on this weblog

Showing messages 1 through 6 of 6.

---

Titles Only
Main Topics
Oldest First

• Does it also work for manually created members?
  2010-09-08 01:07:39 Dries Paesmans Business Card [Reply]
  Hi Scott,
  good thing there are workarounds being developped ... One question, we have the problem that for manually created members in BPC, which do not exist in the BW hierarchy, its PARENTH1 column is always deleted when running the load hierachy package. Can we use this workaround to solve this problem as well?

  • Does it also work for manually created members?
    2010-09-08 15:22:23 Scott Cairncross Business Card [Reply]
    Hi Dries, I am not 100% certain but I believe this methodology could work for your scenario. Cheers, Scott

• Error in importing hierarchy from BW infobject into BPC
  2010-08-19 20:10:05 Dalil Khan Business Card [Reply]
  Thanks for the very useful blog. I was wondering if you may be able to help me with my situtation.
  I am importing text hierarchy from BW infoobject into BPC. In my datamanager I specify the top level of the hierarchy = TCTB, Empty & Member ID = TB(the top level). It is rejecting all the records. Following is the error log. All records rejected. Is there any way I can import these records. Thanks

  /CPMB/MODIFY completed in 0 seconds
  /CPMB/BW_IOBJ_HIER_SOURCE completed in 0 seconds
  /CPMB/IOBJ_SOURCE_HD_CONVERT completed in 3 seconds
  /CPMB/BPC_HIER_DATA_TARGET completed in 4 seconds
  /CPMB/CLEAR completed in 0 seconds
  [Selection]
  ------------------------------------------------------------
  INFOOBJECT=0CS_ITEM
  SELECTION=<Selections><Selection Type="Hierarchy" ImportText="1"><Hierarchy><ID>D8IGJYZMI5W3TJ2UBMZGCL6QF</ID><MemberID>TB</MemberID><Level></Level></Hierarchy></Selection><KeyDate>20100820</KeyDate></Selections>
  FORMAT= No
  TRANSFORMATION= DATAMANAGER\TRANSFORMATIONFILES\PILOT\forHier_Account.xls
  DIMNAME=P_ACCT
  [Messages]
  ------------------------------------------------------------
  Task name HIERARCHY DATA SOURCE:
  Info: Hierarchy node includes text node or external characteristics
  Record count: 2724
  Task name CONVERT:
  No 1 Round:
  Record count: 2724
  Accept count: 2724
  Reject count: 0
  Skip count: 0
  Task name HIERARCHY DATA TARGET:
  Hierarchy nodes include dimension members that do not exist
  Submit count: 0
  Application: PLANNING Package status: ERROR

  • Error in importing hierarchy from BW infobject into BPC
    2010-09-08 15:24:54 Scott Cairncross Business Card [Reply]
    Hi Dhalil, I would need to see what the actual error was to be able to help more. I am not certain. It seems as if the text nodes in your hierarchy are not being created as real nodes in the BPC hierarchy. That would cause this error, however I am uncertain of the root cause without doing a deeper analysis.
    Cheers, Scott

• Nice Workaround!
  2010-07-30 12:59:07 Jim Link Business Card [Reply]
  Scott,

  Nice work - this appears to be a pretty straightforward work around for these particular issues.

  We're running the BPC 7.0M version now, with plans to move to NW in the future. After reading this and Jeff's How-To Guide, I'm excited with the direction things are moving with regards to master data.

  Questions:
  1) Our organizational structure allows for a set up where both Cost Center and Profit Center reside in a single dimension called Entity (Cost Center as a child of Profit Center). I presume that if we set up the same way in 7.5NW that there is no way to take advantage of the master data and/or hierarchy DM packages since there are two infoobjects pointed to a single dimension?

  2) Forgive me as I'm not terribly technical (and it wasn't obvious to me), but would I need to implement the above steps for each dimension that I'd like to load a hierarchy for? Or is it done once and then it applies to all dimensions?

  2a) Depending on the response to #2, are there any plans to make the above work around part of standard configuration in future releases? It would seem to me that if it's set one time for all dimensions that it would be good to just have it in place from the get-go.

  3) I assume that if I left out the "ENDROUTINE=BPCHIEUPD" out of the transformation file that it would remove the manually maintained hierarchy, correct?

  4) Any plans for shared infoobjects in future releases so that we can just skip loading master data into BPC from BW altogether? That's probably far-fetched given the challenges of separate name spaces, but a guy can dream.

  5) Your last sentence mentions a number of new BADIs and that you recommend becoming familiar with them. Any particular place you'd recommend to learn more about them?

  Thanks,
  Jim Link

  • Nice Workaround!
    2010-09-08 15:33:57 Scott Cairncross Business Card [Reply]
    Hi Jim,
    Trying to answer the best I can here...

    1) You are correct you would not be able to use the standard however you COULD build a nice workaround in the ROUTINE badi (similar to this workaround) to merge the hierarchies based on a set of rules (attribute relationships, potentially)

    2) You do this once and then you include the ENDROUTINE in each of the Transformation files for the individual Dimensions. The workaround is generic and should work for all cases.

    2a) Agreed -> this is not my call though, I think this is best taken up in the EPM Customer Council ASUG meetings.

    3) Yes

    4) Good dream, but I believe that is a bit farther off

    5) Sure - the Articles/How To Guides section of the EPM workspace is a good area, also with SP3 of BPC 7.5 NW there are now "SAMPLES" that are delivered. When you create a new BADI implementation it will ask you if you want to inherit from the SAMPLE. Say yes and you have a nice set of code to tinker with and get familiar with.