Skip to Content

Dynamically update Broadcast setting Variables for JAVA Stack

Jan, 2013

Applies to:  SAP BI 7.0

Target Readers:     SAP BI AND BW DEVELOPERS

Summary

There may be some BI reports which would take very long time to display the output and users are not ready to accept this type of response times. In this case there are different types of approaches to resolve it like Long term, medium term and Short time solutions. Short term solutions could be implemented in very short span of time. Such solution is filling OLAP CACHE with required data set daily just before user executes the report. Filling OLAP CACHE could be achieved through broadcasting. Report needs to be broadcasted with distribution type “Fill OLAP CACHE” and with required selections, for this we may have to change the Date, or any other variable values every time we broadcast it, even though the broadcast is scheduled automatically. This document explains how this updating of the variable value can be automated without having to change it every time manually.

Author:    Hari Prasad Balija

Company:   Infosys

Created on: 08th August, 2012

Keywords          :     SAP BI, REPORT PERFORMANCE, OLAP CACHE, ABAP CODE,

                                    BROADCASTING.

1.    Business Scenario.

There would be some instances where for some of the reports data should be retrieved from OLAP CACHE instead of database directly which would save major DB time so that report response time will be decreased drastically. Filling OLAP CACHE can be done by via Broad caster.

Generally in these cases we create a report with Customer Exit variables for TIME dimension Info objects which automatically populates values based on current calendar day. But there are cases in which we need to broadcast existing reports which has Manual Input variable for these objects and we face with the following issue when creating a broadcast Settings.

  1. 1. Since the Variable requires user to input the value we need to choose the Value in the Broadcast Settings.
  2. 2. Even though there is a way to create customer Exit (i_step = 1) associated with it, this will not be useful when the report is being broadcasted. The reason being the variable values are populated from broadcast setting table and not from customer Exit in case of Manual Input variables.

A better way of doing this will be to update the broadcast setting table with values required using a customized program as described below.

2.    Background Info

The parameter values entered in the Broadcast Settings are maintained in a standard table called RSRD_SETT_NODE_A, which has the following structure.

Here there is a unique entry created for every setting we create and the details of the variable being stored as sequence of strings under the value field.

Even though we select the values for the variables in the selection screen in the broadcaster, or it is being populated with a customer exit the values are hardcoded in this table and when the broadcast is being done by the pre calculation server it picks the variable value from this table and not via the i_step = 1 variable exit.

This happens only for manual entry variables. Hence the only possible option to update this variable values if the settings has to be broadcasted on periodic basis is to manually update the value in the tables.

For this we created an ABAP program that can do this job for SAP BI systems working on Java stack .This piece of Code has to be executed Via Process chain scheduled after the data is loaded daily into the concerned Info Provider and before the broadcast settings are scheduled. This way we make sure the settings run for the correct variable values.

In our particular case, users want to see the Customer Delivery Performance report daily for particular selection values. It might be for current calendar day, current calendar week or current calendar month. But report takes around fifteen minutes for one day itself then you could imagine the report execution time for one week and one month. The data load happens at daily frequency. Hence we fill the OLAP CACHE daily using Broadcaster before executes the report.

The report takes range of values for Calendar day and some other entries as input. We have identified most frequently used selections for the report execution by consulting end users. All other variables except for Calday would be provided with proper values in Broadcast Setting.  Calday variable is also provided with some default date range. Distribution type of Broadcast setting should be set to “FILL OLAP CACHE”. Then a unique entry would be created in standard Broadcast setting table RSRD_SETT_NODE_A for our newly created Broadcast setting. Our task is to update Calday variable value as per the requirement in this table so that OLAP CACHE is filled with relevant data to be used by users.

To achieve this, first a Z-table need to be created with following structure.

SETTING_NAME

Technical name of the Broadcast Setting

DATE_FROM

Variable Value Lower limit

DATE_TO

Variable Value Upper limit

One Z-Program also has to be created for one time activity.  The Z table should be updated with our newly created Broadcast setting Technical name, lower limit and upper limit of Date variable provided while creating broadcast setting using this Z program.

This Z-Program takes following entries as input and updates the Z-table:

Broadcasting Setting

Technical name of the Broadcast Setting

From Date

Variable Value Lower limit

To Date

Variable Value Upper limit

Now our original program to update standard Broadcast setting table comes in to picture.

This program takes following parameters as the input:

Broadcasting Setting

Technical name of the Broadcast Setting

Number of Days

Offset value from System Date

From Date

Variable Value Lower limit

To Date

Variable Value Upper limit

Either we can provided offset value from the current date so that upper value would be System date and Lowe value would be system date  – offset value or Upper and Lower values could be entered explicitly using From and To Dates. In the variant that would be used in the Process chain to fill the table daily we will use Number of days (Offset) to eliminate the manual dependency.

3.    How to use:       

Let us take an example of updating Calday variable with upper limit as System Date and lower limit as System Date-7 in a Broadcast setting ZZ_TEST_CDP_OLAP.

First an entry would be created in table RSRD_SETT_NODE_A for our setting ZZ_TEST_CDP_OLAP. And Calday variable would be having some initial values that we have entered while creating Broadcast Setting. Then the Z-table “ZCDP_BRDCAST_DAT” will be updated with same values as that are present in Standard table using Z-program “ZCDP_BRDCAST_DAT”

Then the Original program “Z_CDP_BROADCAST_CACHE2” need to be executed by providing following inputs

So now the Broadcast setting table will be updated with System date – 7 and System Date for lower and Upper limits of Calday variable respectively. And the OLAP CACHE is filled with Current Week data of the report when Broadcast setting is being executed. Also the Z-Table “ZCDP_BRDCAST_DAT” will be updated by this program with same date values as Standard table to use in next iteration of the program.

4.    Process Chain

Process chain need to be created as follows.

It includes the variant of the program “Z_CDP_BROADCAST_CACHE2” as first step in it.

Variant of “Z_CDP_BROADCAST_CACHE2” Program details:

And the next step of Process Chain would be the variant of Standard Program “RSRD_BROADCAST_STARTER” to execute the Broadcast setting in background.

Variant of “RSRD_BROADCAST_STARTER” program details

  1. 6. Appendix:
  2. 1. Source Code of Main Program: i.e. Z_CDP_BROADCAST_CACHE2

REPORT  Z_CDP_BROADCAST_CACHE2.

DATA : lv_input_str TYPE STRING,

       lv_output_str TYPE STRING,

       lv_ind like sy-tabix,

       count(2) type c,

       lv_indx like sy-tabix,

       lv_indmax like sy-tabix,

       lv_var1 TYPE STRING,

       lv_brdcst type string,

       flag type i value 0,

      datevar type sy-datum,

      datefrom(8) type c,

      dateto(8)   type c.

DATA : t_rsrd TYPE STANDARD TABLE OF RSRD_SETT_NODE_A,

       wa_rsrd TYPE RSRD_SETT_NODE_A.

DATA : WA_ZCDP_BRDCAST_DAT TYPE ZCDP_BRDCAST_DAT.

parameters : p_set(30) type c.

parameters : p_days type i default 0.

parameters : p_frmdat type dats visible length 10 default ‘YYYYMMDD’.

parameters : p_todat type dats visible length 10 default ‘YYYYMMDD’.

      if p_days <> 0.

        dateto = sy-datum.

        datevar = sy-datum – p_days.

        datefrom   = datevar.

      endif.

       lv_brdcst = p_set.

       select * from rsrd_sett_node_a into table t_rsrd

       where setting_id = lv_brdcst

       and id = ‘PR_VARIABLE_STRING’

       and objvers = ‘A’.

       if sy-subrc <> 0.

         write: ‘Failed’, ‘: Broadcast setting is not found’ .

         exit.

       endif.

       sort t_rsrd by RSR_INDEX. “Sorting the internal table by index

       select single * from ZCDP_BRDCAST_DAT into wa_ZCDP_BRDCAST_DAT

                       where SETTING_NAME = lv_brdcst.

LOOP AT t_rsrd into wa_rsrd.

  IF WA_RSRD-VALUE CS wa_ZCDP_BRDCAST_DAT-DATE_FROM.

     if p_days <> 0.

       REPLACE  wa_ZCDP_BRDCAST_DAT-DATE_FROM WITH datefrom INTO WA_RSRD-VALUE.

       wa_ZCDP_BRDCAST_DAT-DATE_FROM = datefrom.

     else.

       REPLACE  wa_ZCDP_BRDCAST_DAT-DATE_FROM WITH p_frmdat INTO WA_RSRD-VALUE.

       wa_ZCDP_BRDCAST_DAT-DATE_FROM = p_frmdat.

     endif.

     MODIFY T_RSRD FROM WA_RSRD.

     flag = +1.

  ENDIF.

  IF WA_RSRD-VALUE CS wa_ZCDP_BRDCAST_DAT-DATE_TO.

     if p_days <> 0.

         REPLACE wa_ZCDP_BRDCAST_DAT-DATE_TO WITH dateto INTO WA_RSRD-VALUE.

         wa_ZCDP_BRDCAST_DAT-DATE_TO = dateto.

     else.

         REPLACE wa_ZCDP_BRDCAST_DAT-DATE_TO WITH p_todat INTO WA_RSRD-VALUE.

         wa_ZCDP_BRDCAST_DAT-DATE_TO = p_todat.

     endif.

     MODIFY T_RSRD FROM WA_RSRD.

     flag = +1.

  ENDIF.

  if flag = 2.

    exit.

  endif.

  1. ENDLOOP.

clear flag.

MODIFY RSRD_SETT_NODE_A FROM table t_rsrd.

MODIFY ZCDP_BRDCAST_DAT FROM wa_ZCDP_BRDCAST_DAT.

WRITE ‘SUCCESS’.

  1. 2. Source Code of the program “ZCDP_BRDCAST_DAT”: (It is used only once at the beginning)

REPORT  ZCDP_BRDCAST_DAT.

DATA : wa_ZCDP_BRDCAST_DAT type ZCDP_BRDCAST_DAT.

parameters : p_set(30) type c.

parameters : p_datfrm type dats visible length 10 default ‘YYYYMMDD’.

parameters : p_datto  type dats visible length 10 default ‘YYYYMMDD’.

wa_ZCDP_BRDCAST_DAT-SETTING_NAME = p_set. “Broadcast Setting Name

wa_ZCDP_BRDCAST_DAT-DATE_FROM = p_datfrm.

wa_ZCDP_BRDCAST_DAT-DATE_TO = p_datto.

modify ZCDP_BRDCAST_DAT from wa_ZCDP_BRDCAST_DAT.

if sy-subrc = 0.

write : ‘Table is successfully updated’.

  1. else.

write : ‘Table updation is failed’.

  1. endif.

  1. References:
  2. http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/20d671c6-b377-2d10-7488-b75841c389a3?quicklink=index&overridelayout=true
  3. http://help.sap.com/saphelp_nw70ehp2/helpdata/en/23/1b9341d38aa209e10000000a155106/content.htm
  4. http://help.sap.com/saphelp_nw70/helpdata/en/00/241da12ef84f40bb6ecd3ae73d8b58/content.htm
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply