PaPM Writer: Delete and Insert

[former_member650362]

Perhaps one of the function building blocks in SAP Profitability and Performance Management (PaPM) which needs no further introduction in terms of its functionality is the Writer function. As straightforward as its given name, this processing function enables the user to basically write to various data sources such as Temporary PaPM Table (source: Environment), External sources such as Data Dictionary Table, SAP HANA Table, BW InfoProvider and Model RDL – which are the possible output functions that the user can plug together with a Writer along with the input function in performing its writing process.

For those who might not be too familiar and would want to have a bit of an overview of what the Writer function is, refer to the latest section in the Application Help document - Writer.

Depending on which of the possible output functions given above is chosen, the Writer function intuitively recognizes the type of function and as such, it is able to offer variable options to the user in terms of the following:

First, how the user would want to control the writing of data thru what is called the Model Writer Type wherein the user can choose from Insert, or Delete and Insert for Temporary PaPM Tables, Data Dictionary Table, SAP HANA Table and an additional Model Writer Type Modify is available if the output function is Model BW.

Second, whether Planning Engine or BW Data Transfer Process will be utilized in case the output function is a Model BW. These two corresponds to the BW Write Types: Planning and Loading respectively. Learn more about Planning Engine or BW Data Transfer Process via this URL.

To illustrate this behavior based on the type of output function mentioned above, let me show you the available options in the Writer function dependent on the output functions chosen in the examples below:

1. Model Table

When a Model Table with Source: Environment, Data Dictionary, HANA, SDA is used as an output function, Model Writer Type provides the user options (1) Insert & (2) Delete and Insert

Sources: Environment, Data Dictionary, HANA and SDA

2. Model BW

When a Model BW with Source: Business Warehouse or Environment is used as an output function, BW Write Type provides the user options (1) Planning & (2) Loading. And as I have mentioned earlier, there is an additional Model Writer Type “Modify” which is enabled only with setting the BW Write Type to “Planning”.

Sources: Business Warehouse and Environment

Now that we are aware of these options, let me now proceed further to discuss the Model Writer Types. However, this blog will be focusing more on the Model Writer Type: Delete and Insert which offers more flexibility. In contrast, Model Writer Type: Insert will be also presented in the following section in order for us to have a better appreciation when it comes to their distinctions from one another.

Delete and Insert

Earlier as it was presented in our introduction, the Writer function grants the user to have control as to the manner on how data will be written into the output function depending on what is set in the Model Writer Type.

Model Writer Type: Insert

This inserts new data on top of an existing one. So if you have entry A as the input and B as the existing output data, the writer will simply insert A to the output therefore producing A and B records as the result.

Model Writer Type: Delete and Insert

This first deletes an existing data then inserts the new data. So given the same input and output above, the writer will first be deleting B from the output and will insert A, producing A as the result. This is known as the original behavior for Delete and Insert.

Between these two types, notice how Delete and Insert is more dynamic. It provides another flavor in writing as it clears everything first before writing data to the output.

This behavior will be taken one step higher with what is called Selective Delete and Insert. Imagine the added control of being able to define and select only the entries needed in the input and insert it to the output, replacing only those entries having the same characteristic/s and retaining the rest. Powerful isn’t it? Harnessing this functionality will be shown in the succeeding section.

Selective Delete and Insert

In order for the Selective Delete and Insert to take effect, we consider 2 things:

1. Defining a Process Template selection


2. Selecting the created Process during Runtime

These 2 items will play a vital role in the execution of our writing process for the Selective Delete and Insert scenario.

Let us take as an example the Input and Output below to help us explore and observe in detail how this Selective Delete and Insert work for Temporary Table, Data Dictionary Table, SAP HANA Table and SAP BW ADSO. For the Input, the same Model Table with source: Environment is used. For the Output, the source will vary.

Writer Input – Model Table with Source: Environment

Writer Output - Model Table with Source: Environment

Given this set of data, what if the modeler would want to select only those entries with Customer CN001 and CN003 from the Input, to be written to the Output.

A Process Template Selection should be defined first to ensure that only these items will be selected. To set this up, here are the steps:

1. Maintain the CUSTOMER field in the Selection Fields Tab in the Calculation Unit of the environment. This will be relevant in declaring our selection as we proceed, in the Process which we will be creating.

2. In the Processes Tab, create a Process Template.

3. Below in the Selections Tab - Selection Conditions section, add the CUSTOMER field and maintain the selections CN001 and CN003. This selection will be considered by the system both in deleting and inserting of data to the output.

Now that this has been setup, it is imperative to call this Process Selection in the Writer function during runtime. Simply select it from the Process droplist in the Process Tab of the Run Manager before finally clicking the Run button in the Run Manager window.

Temporary Table, Data Dictionary Table and SDA

As expected, only matching entries for CUSTOMER CN001 and CN003 will be replaced in the Output during the writing process.

Notice how the entries for CUSTOMER CN002 and CN004 were retained. In addition, an added row for CUSTOMER CN003 was added to the Output having a Quantity 250 and an Amount of 300.  This is how it goes for Temporary Table, Data Dictionary Table and SDA.

Considering the same Input, let us observe how data was written for the other Output Tables of different sources.

SAP HANA and SAP BW ADSO

As expected, the entries for CUSTOMER CN002 and CN004 were untouched. As a new observation for SAP HANA and SAP BW ADSO, no new entry for CUSTOMER CN003 was created since the system aggregated all entries for CUSTOMER CN003 therefore, combining 300 + 250 = 550 for the field Quantity and 2500 + 300 = 2800 for the field Amount. Note that for SAP BW ADSO, this is the expected result as it is shown during Analyze or DB aggregation is used in transaction code RSA1.

At this point, we now recognize the significance of a Process Template Selection in establishing added control in writing the data thru Selective Data and Insert and how it works for Temporary Table, Dictionary Table, SDA, SAP HANA Table and SAP BW ADSO.

Now that we are aware of these, let us explore another scenario which is equally important – providing us extra leverage in Modeling.

Selective Delete and Insert using Input Tab selection

Earlier, we highlighted the Process Template selection as an integral part in making our Selective Delete and Insert work. In addition, we can have the same control for Selective Delete and Insert by making use of the Writer’s Input Tab (instead of a Process Template Selection), however with limitations. Unlike the Process Template selection, this only works when the output function is a Model BW.

Let us again consider the same Input and Output we used earlier.

Writer Input – Model Table with Source: Environment

Writer Output – Model BW with Source: Environment

As we would want to insert only those entries for Customer CN001 and CN003 like we previously did using Process Template selection, we would have to declare the field Customer in the Input Tab of the Writer function.

Afterwards, maintain the corresponding selections for Customer CN001 and CN003.

Again, only matching entries for CUSTOMER CN001 and CN003 will be replaced in the Output during the writing process.

As expected, the entries for CUSTOMER CN002 and CN004 were untouched. Since the output function is a Model BW, entries for CUSTOMER CN003 is aggregated, combining 300 + 250 = 550 for the field Quantity and 2500 + 300 = 2800 for the field Amount.

Our discussion so far has led us to recognize this variety in Selective Delete and Insert – now making it two different ways to choose from on how to go about your selections, depending on your preference and of course, your scenarios that you currently have.

In addition to these Selective Delete and Insert scenarios which may come in handy is yet another interesting thing which you may consider as a simple trick and also since quite a number of modelers are already doing this is the fact that a Join function may be used to execute your Writer function. It might be the case that there are particular scenarios that a modeler needs to trigger multiple writers at the same time - for instance, one writing in Target 1 and another writing in Target 2. To enable this process as a sort of  “one time single execution” of multiple Writers, we can integrate a Join function in this Input-Writer-Output scenario.

To illustrate how this is done, we create a Join function, put both Writers in the Rules Tab and “union” them.

To avoid the Join function from buffering the result (storing it in Y table), it is also possible to select data like Customer = NOTHING in the Join logic so the Join function can just act solely as a main trigger for the Writers. Once the “Run” button of the Join function gets chosen, both Writers will be called and will proceed in deleting and inserting data to their respective Target tables. The Join function will show no result which is as expected but both Target tables will be filled up with entries coming from their respective Writers.

For the Delete and Insert perspective in this scenario, ensure that the Writer’s Processing Type is switched to “Executable” to trigger the Delete mechanism. Otherwise, having a Writer as a “Sub-Function” will enable it to only deliver half of the expectation – it will not Delete but will proceed with Insert only.

In order to switch the Processing Type, navigate to the Function Attributes by choosing the corresponding button above the environment left-tree and switch the Processing Type in the General Tab from “Sub-Function” to “Executable”. Afterwards, for this change to kick-in, ensure to reactivate the Writer and the succeeding function that will use it – in this case, the Join function.

For more info, you may refer to the general note stated in our Application Help document - Writer.

I hope this blog post was able to provide you a better understanding of the Writer function in terms of its flexibility in different data and external sources, the relevance and basics of Selective Delete and Insert and additional insights from other scenarios I have presented.

If you find this post helpful click on the LIKE button and share it with a friend or colleague.

Thanks a lot for your time!