Upload Solution with BO Analysis for Office

[Former Member]

Whenever working with Integrated Planning ususally there is a requirement for uploading values. The requirement seems simple at a first glance, but many challenges are coming with it. Some of the most important are:

• Restrictions in planning queries should also be considered in the upload solution
• When changing a planning query or the data model, the upload solution should be synchronized
• Performance
• Delta Upload and Full Upload should be possible (Adding or overwriting values)

The presented solution is based on the same query used for planning. The user can decide by changing a variable in the variable screen if he wants to plan, upload in delta or upload in full (overwrite). After uploading the values, the user can change to normal mode and check the result by just changing a variable in the variable screen.

Here the solution in detail. The solution was implemented in BW7.3. As a frontend BO Analysis for Office is used. This is the first version of the solution description, screeshots and further details will follow when asked.

1. Introduce a characteristic with 3 possible values:
   1. ‘#’ is used for normal mode
   2. ‘1’ is used for upload with overwrite
   3. ‘2’ is used for upload with delta
2. Add the characteristic to your planning data model (InfoProvider, MultiProvider, Aggregation Level)
3. Create an input ready variable for the characteristic, so the user can decide which mode he wants to enter when opening the query/sheet
4. Add the characteristic to your planning query under Restrictions and assign the variable
5. Create an own planning function type which considers 0-values. This is necessary for the upload with overwrite, otherwise these values would not be considered. The standard type will ignore 0-values.
6. Create a planning function of this user defined type, a filter and a planning sequence. The planning function must be executed each time a user is pressing the save button and will look similar to the following coding:

FOREACH UPLOAD_KENNZEICHEN.

  IF UPLOAD_KENNZEICHEN = 1.

      {0AMOUNT, #} = {0AMOUNT, 1}.

   {0AMOUNT, 1} = 0.

  ENDIF.

  IF UPLOAD_KENNZEICHEN = 2.

    {0AMOUNT, #} = {0AMOUNT, #} + {0AMOUNT, 2}.

    {0AMOUNT, 2} = 0.

 ENDIF.

      ENDFOR.

          This procedure copies the values from the upload modes to the normal mode.

1. The query will always be empty when accessing it with upload mode, as the preceding planning function will copy the values to the normal mode. A precondition for this is to activate the null elimination for lines in the query.
2. Integrated the planning sequence in BO Analysis for Office. Use the option “Only use changed values” for the planning sequence. This option will enhance your performance.
3. Use the callback function in BO Analysis for Office to execute the planning sequence each time the Save Button is pressed.
4. Change the number of input ready lines in BO Analysis for Office to a suitable one, probably around 100 lines.

With this solution the user now can enter the query in upload mode, paste his offline planned values and save the values.

Note that each time you change the query or data model, there is no maintenance in the upload solution necessary!