In this blog post we will use the LinkID established in the previous blog Part 1. Here we will establish a new model with a single version/period combination and use Data Bridge to load Responsibility Centres (RCs) and Line Items (LIs) from BW to the PCM model. Note that in SAP terminology an RC is a Cost Centre and a Line Item is a General Ledger Account or Cost Element. This load of master data in this blog will set the foundation for loading Line Item Values to PCM from BW in the next exercise.
Before we begin with the detailed steps, it is important to visualize the intended flow of data from BW to PCM and back out again and where BW data should ideally be placed in PCM. In summary, the BW Master Daya Keys should be loaded as Default Aliases in PCM (This is mainly because all the Result Views in PCM use Default Aliases, so this will make the mapping back to BW Info Providers easier later)
Step by Step
- In your BW system, identify a BW Query that reads from e.g. the Cost Element Balances Cube in BW and can be used as the basis for loading Dimension and Measure data to PCM. What you need to load to PCM will ultimately depend on the dimensionality of the Measure in PCM you are loading to. For example, if you want to load Line Item Values to PCM then you will need to find a BW Query that is dimensioned by (has the BW characteristics for) Period/Time, Version/ValueType, Responsibility Centre/Cost Centre, Line Item/Cost Element. Note that different queries can be used to load Master Data and Measure Data but if one query can be used to load both kinds of data in PCM then this simplifies the process. In the following steps we have used a custom BW query called ZPCMBASE_Q01.
- In PCM Model Builder, create a new model
- In the PCM Model, establish a single Version called „Actual“ and a single period called „P1“ (Note that this is just for simplicity here. You can read both of these values from your BW Query)
- From the PCM Model Builder, start Data Bridge via Tools>Launch>DataBridge
- At the popup, select to create a new CTF and SPE file. Click OK
- On the Wizard startup screen, click Next
- On the Specification File screen, click ... to create a new SPE file. Navigate to your chosen folder for storing these DataBridge SPE files and enter the name of a new SPE filed called e.g. „Load RC Structure XX.SPE
- Click Open and Click Next
- On the Select Data Source screen, select “Import from External Data Source”, select your Link ID (e.g.ETD) and accept the default to Import Structure
- Click Next
- On the Import OLAP structure screen, select Catalog e.g. ZPCMBASE>Cubes>ZPCMBASE/ZPCMBASE_Q01>Dimensions>PCOSTCTR>Hierarchies>PCMTCDEMO then select Total Company node. (Note this path will be different based on your BW query) Click Next
- On the Configure Specification File Screen, leave “Run the Specification Wizard” selected and click Next
- Select table PARENTCHILD to load to (note for higher volumes (e.g. above 5000 rows) the table PARENTCHILD_BULK) would make more sense to select). Click Next
- On the Field Mappings Screen, make the following selections from the dropdowns
- Click Preview to check the values to be loaded from BW. Click OK once finished
- Click Next (7 times) then Click Finish
- Click Execute Current Control File and save control file as e.g. “Load Structure XX” in your chosen folder for DataBridge CTF files. Click Save
- Ensure “Allow Creation of Structure” is selected and Click Run
- You should get a success message that XX lines have been processed. In your open PCM Model, confirm that the RC structure (based only on BW Master Data Keys) has been loaded
- We will now load Alias data to this RC structure. The Alias data can be provided by BW Master Data Texts
- In your PCM Model, create a new Alias called Description. Use menu path Tools>Manage Data Aliases
- Click Add, enter your new Alias called “Description” and click Close
- In DataBridge, ensure your “Load Structure XX.CTF” file is still open and click off the SPE file you just created so that it is not selected. Then click Insert new SPE
- In the DataBridge Wizard startup screen click Next
- In the Specification File screen, create a new SPE called e.g. “Load RC Alias XX.SPE”, Click Open and Click Next
- On the Select Data Source screen, select “Import from External Data Source”, select your Link ID (e.g. ETD) and accept the default to Import Structure
- On the Import OLAP structure screen, select Catalog e.g. ZPCMBASE>Cubes>ZPCMBASE/ZPCMBASE_Q01>Dimensions>PCOSTCTR>Hierarchies>PCMTCDEMO then select Total Company node. Click Next
- On the Configure Specification File Screen, leave “Run the Specification Wizard” selected and click Next
- Select table ALIAS to load to. Click Next
- On the Field Mappings Screen, make the following selections from the dropdowns
- Click Preview to check the values to be loaded from BW. Click OK once finished
- Click Next (7 times) then Click Finish
- Save your CTF and Click Execute Current Control File
- Click Run
- You should get a success message that 2x XX lines have been processed
- In your open PCM Model PCMBW_XX, confirm that you can now see Default Alias and Description Alias for your RC structure (HINT: to switch between Aliases in the PCM Dimension display click on Alias icon top right of Model Builder and select your Alias to display)
REPEAT THIS PROCESS FOR ALL OTHER DIMENSIONS YOU WILL NEED in YOUR PCM MODEL TO BE ABLE TO LOAD TRANSACTIONAL/MEASURE DATA TO PCM, i.e. Line Items, Versions, Periods, Cost Objects (Customers, Products, Channels etc)
PLEASE NOTE: The approach outlined in this exercise (Direct Integration from BW to PCM via BW Queries) has been tested to 400,000 rows. It is suggested that in excess of this figure, BW Open Hub and Data Loader would be the better approach. There will be a future blog on this topic.
PLEASE NOTE: For simple concatenations of the BW Query Data (e.g. adding a P before all (P)roduct BW Master Data Keys) then Data Bridge has a PRECEDE WITH function that can be used. It is not supported by the Wizard though so you have to manually adjust the SPE file created by the Wizard