automate broadcasting in sap bi

1

Automate Broadcasting in SAP BI

using ABAP Program

24/02/2014

Johnson & Johnson

Relationship - JNJ

Satyam Bhardwaj (393410)

SAP BI [email protected]

2

Confidentiality Statement Include the confidentiality statement within the box provided. This has to be legally approved Confidentiality and Non-Disclosure Notice The information contained in this document is confidential and proprietary to TATA Consultancy Services. This information may not be disclosed, duplicated or used for any other purposes. The information contained in this document may not be released in whole or in part outside TCS for any purpose without the express written permission of TATA Consultancy Services.

Tata Code of Conduct We, in our dealings, are self-regulated by a Code of Conduct as enshrined in the Tata Code of Conduct. We request your support in helping us adhere to the Code in letter and spirit. We request that any violation or potential violation of the Code by any person be promptly brought to the notice of the Local Ethics Counselor or the Principal Ethics Counselor or the CEO of TCS. All communication received in this regard will be treated and kept as confidential.

3

Table of Content

1. Introduction .......................................................................................................................................... 4

2. BI Object Details .................................................................................................................................... 5

3. Cases captured in Broadcast Scheduling Program ................................................................................ 6

3.1 Case1-Explanation and Execution ............................................................................................... 6




4. Regret Mail sending. ........................................................................................................................... 10

5. Appendix ............................................................................................................................................. 11

5.1 Sample data for ZIBSET1 ............................................................................................................ 11

5.2 Sample data for ZIBSET2 ............................................................................................................ 11

5.3 Source Code ............................................................................................................................... 12

4

1. Introduction In SAP BI we have a standard functionality to set up and schedule the broadcasting. However, there can be numerous cases where the standard functionality cannot be used due to different business requirements. This document illustrates a way to schedule BI report broadcasting using an ABAP program. The Custom program(ZIB_SCHEDULE_1) is developed to automate theBroadcast scheduling. This program is scheduled to run multiple times in a day and broadcast different reports/queries based on conditions maintained. The Conditions for a broadcast is stored in a master data Info-object-> Information Broadcast Setting (ZIBSET1). This is the base table where all the Broadcast IDs with the broadcast settings have to be maintained. The Broadcast IDs stored in the info-object ZIBSET1, can be scheduled using the custom program. The Info-provider details for each Broadcast ids have been stored in a master data Info-object ZIBSET2 if we want to check whether data load happened into info provider on that particular day before broadcast id is sent out.

5

2. BI Object Details

Following are the objects which will be needed for the automation of broadcasting in SAP BI. These objects are referred in the program to read the broadcast schedule setting.

Information Broadcast Setting (ZIBSET1): This info-object has setting details of the broadcast IDs.

Attribute:

Week Day (ZDAY)- The day of a week for which broadcasting has to be scheduled.

Time (ZTIME)- Time at which the Broadcast has to be scheduled.

Customize Flag (ZFLAG)- The flag is set if the broadcast has to be scheduled for entire week which is a week before the month end week.

C+X (ZCPLUSX)- The field contains the particular day of a JNJ period on which broadcast is to be scheduled (Ex: if we want to sent out a report on C+1 then we have to fill this filed with a value 1.

Broadcast ID (ZBCID)- The Field contains the reference Broadcast id name to which the report is assigned, as a report can have multiple Broadcast Ids.

The sample data for info-object ZIBSET1 has been attached in Appendix.

Information Broadcast info-providers (ZIBSET2): This info-object has the link between the Information Broadcast ID and the Info-provider. The Information Broadcast ID has a link to a query and the query has a link to the Info-providers. This link between the Information Broadcast ID and the Info-provider is captured into this Info-object.

Compounding object: Info-provider (ZINFOPRO)

The sample data for info-object ZIBSET2 has been attached in Appendix.

6

3. Cases captured in Broadcast Scheduling Program There can be number of scenarios based on which the scheduling of Broadcast can be done. The program developed addresses four such cases. They are :

Case 1: Broadcast for all days without any condition at a particular system time of the day.

Case 2: Broadcast on a particular day of a week at a particular system time of the day. Case 3: Broadcast for the last week of the JNJ period at a particular system time of the

day. Case 4: Broadcast on a particular day of JNJ period at a particular system time of the

day. These cases are briefly explained in the following slides. 3.1 Case1-Explanation and Execution This is case when a Broadcast has to be done on all days at a particular system time of the day. For this case we need to maintain the entry in ZIBSET1 as shown in the screen-shot below:

Here: Information Broadcast = Broadcast id(ID_TEST1)

Time = Time of system at which the Broadcast has to be scheduled (12:30).

When the program is scheduled, it checks the system time with the time maintained in the Info-object. If a entry of info-provider is maintained in ZIBSET2 then the program checks the last load time of the info-providers. If the Info-provider has been loaded on the same day and a match is found for the time entry, particular broadcast id is broadcasted.

Note: If info-provider entry is not maintained in ZIBSET2, then the programs skips the check for loading time.

The programs checks the system time and the time maintained in the Info-object(For example: 12:30 in this case).if the time difference between system time and broadcast ID time that maintained in the table ZIBSET1 is less than 30 mints then the programs submits ID_TEST1 Broadcast id for Broadcast.

7

3.2 Case2-Explanation and Execution

This is case when a Broadcast has to be done on a particular day of a week at a particular system time of the day. For this case we need to maintain the entry in ZIBSET1 as shown in the screen-shot below:

Here: Information Broadcast = Broadcast id (ID_TEST2)

Time = Time of system at which the Broadcast has to be scheduled (12:30).

Week day = the day of week at which the Broadcast has to be scheduled (3) if we want to send out the report on Wednesday.

When the program is scheduled it checks the system time and the day of a week. Also if a entry is maintained for the info-providers in ZIBSET2, it checks the last loading time for the info-providers. If the info-provider has been loaded on the same day

Note: If info-provider entry is not maintained in ZIBSET2, then the program skips the check for loading time and perform the time and day validation and broadcast if match is found.

For example: In the screen-shot above, the programs check the day of a week, if it is the 3rd day of a week the program submits the broadcast id ID_TEST2 at 12:30 system time.

8


This is case when a Broadcast has to be done for last week of a JNJ period at a particular system time of the day. For this case we need to maintain the entry in ZIBSET1 as shown in the screen-shot below:

Here: Information Broadcast = Broadcast id (ID_TEST3).

Time = Time of system at which the Broadcast has to be scheduled (12:30)

Customise Flag = this flag indicates that this Broadcast ID has to be scheduled for thefirst week of the JNJ period (X).

Broadcast ID = this is the reference to the other Broadcast ID. If the same report has another broadcast ID associated with it then that broadcast id has to be maintained here (ID_TEST2).

When the program is scheduled it checks the system time and the date. Also if a entry is maintained for the info-providers in ZIBSET2, it checks the last loading time for the info-providers. If the date falls in the last week of the JNJ Period and If the info-provider has been loaded on the same day and the system time match is found the program Broadcast the Broadcast id.

Note: If info-provider entry is not maintained in ZIBSET2, then the program skips the check for loading time.

The Program is also designed to stop the multiple Broadcast of same report. As one report can bebroadcasted with multiple broadcast id, so it is quite possible that same report is scheduled tobroadcast using two different broadcast ids. For such dependencies, an extra field is added inZIBSE1 info-object (Broadcast IDZBCID). So the Program checks the scheduling of the referencebroadcast id also and if it conflicts with the primary broadcast id, the program skips the referencebroadcast id to prevent the multiple broadcast of same report.

For Example: In the screen-shot attached, the system checks the date. If the date falls in the lastweek of the JNJ Period, then the program broadcast the broadcast id ID_TEST3 at 12:30 systemtime. One thing to note here, is suppose the same Report is scheduled for Broadcast on aparticular day(3rd day as maintained in ID_TEST2) of a week using another broadcastid(ID_TEST2 as the case is here), the program

9

will skip the broadcast for ID_TEST3 for 3rd day ofthe week. This is done to prevent the multiple broadcast of the same report using differentBroadcast Ids.


This is case when a Broadcast has to be on a particular day of a JNJ period at a particular system time. For this case we need to maintain the entry in ZIBSET1 as shown in the screen-shot below:

Here: Information Broadcast = Broadcast id (ID_TEST4).

Time = Time of system at which the Broadcast has to be scheduled (12:30)

C+X = Day of the JNJ period on which broadcast has to be scheduled (2).

When the program is scheduled, it checks the date and time. If the date is found to be day maintained in the C+X field and the system time is validated, the program broadcast the particular broadcast id.

Note: In this case, the program does not check the load time of the info-provider. No matter it is maintained in ZIBSET2 or not, the program goes ahead broadcasting the Report. Hence the validation of info-provider load time is skipped in this case as this is a monthly case and the report will be broadcasted only once in a month.

For Example: In the screenshot, the Broadcast id ID_TEST4 has to be broadcasted on the 2nd day of the JNJ period at 12:30 system time. The program checks the system date and time and if it is 2nd day of JNJ period at 12:30, the program submits the broadcast id for broadcast.

10

4. Regret Mail sending.

The Program is designed to send a regret mail if the broadcast does not happen due to data load issue into info-providers.

If the info-providers are not loaded till the time of Program execution on a particular day , the program triggers an email to the email addresses maintained in ZIBEMAIL info-object and let the user know that the broadcast has been skipped due to data refresh issue. This feature is only applicable for Case 1, 2 and 3. Case 4 does not include this option of sending regret mails as there is no validation for the loading time of info-providers in case 4.

11

5. Appendix

5.1 Sample data for ZIBSET1

Information Broadcasting

Language Key

Week day Time

Customise Flag C+X

Broadcast ID

IB_TEST_01 EN 12:30:00 IB_TEST_02 EN 12:30:00 IB_TEST_03 EN 12:30:00 IB_TEST_04 EN 12:30:00 IB_TEST_05 EN 12:30:00

5.2 Sample data for ZIBSET2

Infoprovider Information broadcasting Language

ZCUBE1 IB_TEST_01 EN ZCUBE2 IB_TEST_02 EN ZCUBE3 IB_TEST_03 EN ZCUBE1 IB_TEST_04 EN ZCUBE2 IB_TEST_05 EN ZCUBE3 IB_TEST_06 EN ZCUBE1 IB_TEST_07 EN ZCUBE2 IB_TEST_08 EN ZCUBE3 IB_TEST_09 EN ZCUBE1 IB_TEST_10 EN

12

5.3 Source Code

*&---------------------------------------------------------------------*

*&Report ZIB_SCHEDULE

*&

*&---------------------------------------------------------------------*

*&

*&

*&---------------------------------------------------------------------*

REPORT zib_schedule_1.

***********************************************************************

*Data Declaration:

***********************************************************************

RANGES: r_pcadt FOR sy-datum.

TYPES:BEGIN OF x_brdcast,

/bic/zibset1 TYPE RSRD_SETTING_ID,

/bic/ztime TYPE /bic/oiztime,

/bic/zday TYPE /bic/oizday,

/bic/zflag TYPE /bic/oizflag,

/bic/zcplusx TYPE /bic/oizcplusx,

/bic/zbcid TYPE /bic/oizbcid,

END OF x_brdcast.

13

TYPES:BEGIN OF l_setting,

setting_id TYPE rsrd_setting_id,

object_id TYPE rsrd_object_id,

END OF l_setting.

TYPES:BEGIN OF l_setting2,

/bic/zinfopro TYPE rsinfocube,

/bic/zibset2 TYPE /bic/oizibset2,

flag TYPE c,

END OF l_setting2.

TYPES:BEGIN OF l_loading,

icube TYPE rsinfocube,

timestamp TYPE rstimestmp,

END OF l_loading.

DATA:lt_setting TYPE STANDARD TABLE OF l_setting,

lw_setting TYPE l_setting,

lt_setting2 TYPE STANDARD TABLE OF l_setting2,

lw_setting2 TYPE l_setting2,

l_loadingdate TYPE sy-datum,

l_temp(15) TYPE c,

lt_loading TYPE STANDARD TABLE OF l_loading,

lw_loading TYPE l_loading,

14

l_tabix TYPE sy-tabix,

l_date TYPE sy-datum,

v_firstday_x TYPE sy-datum.

FIELD-SYMBOLS: TYPE l_setting2.

DATA:lt_brdcast TYPE STANDARD TABLE OF x_brdcast,

lw_brdcast TYPE x_brdcast,

l_x TYPE c.

DATA v_date LIKE sy-datum.

DATA v_date1 LIKE sy-datum.

DATA: v_time LIKE sy-uzeit.

DATA: v_time2 LIKE syst-timlo.

DATA v_year(4) TYPE n.

DATA v_firstday LIKE sy-datum.

DATA v_firstday1 LIKE sy-datum.

DATA v_lastday LIKE sy-datum.

DATA: v_period LIKE t009b-poper.

DATA: day LIKE scal-indicator.

DATA:l_timestamp(15) TYPE c.

CONSTANTS : c_j1(2) TYPE c VALUE 'J1'.

15

FIELD-SYMBOLS: TYPE l_setting2.

TABLES: /bic/tzibset.

DATA :ibsetting LIKE /bic/tzibset.

DATA itab_ibsetting TYPE TABLE OF /bic/tzibset-/bic/zibset WITH HEADER LINE.

"Data Declaration for Regret mail

DATA: l_wa_document TYPE sodocchgi1,

l_t_content TYPE STANDARD TABLE OF solisti1,

l_wa_content TYPE solisti1,

l_t_reciever TYPE STANDARD TABLE OF somlreci1,

l_wa_reciever TYPE somlreci1,

l_v_subrc TYPE sy-subrc.

TYPES: BEGIN OF l_emailid,

email_id TYPE /BIC/OIZIBEMAIL,

END OF l_emailid.

DATA :lt_emailid TYPE STANDARD TABLE OF l_emailid.

FIELD-SYMBOLS: TYPE l_emailid.

***********************************************************************

SELECT /bic/zibset1

/bic/ztime

/bic/zday

16

/bic/zflag

/bic/zcplusx

/bic/zbcid

FROM /bic/pzibset1

INTO TABLE lt_brdcast

WHERE objvers = 'A'.

v_date = sy-datum.

v_time = syst-uzeit.

IF sy-subrc EQ 0.

SORT lt_brdcast[] BY /bic/zflag DESCENDING.

ENDIF.

v_date1 = sy-datum - 1.

CONCATENATE v_date1 '163000' INTO l_timestamp.

**Selecting the Report names for the broadcast ids.

IF lt_brdcast[] IS NOT INITIAL.

SELECT /bic/zinfopro

/bic/zibset2

FROM /bic/pzibset2 INTO TABLE lt_setting2

WHERE objvers = 'A'.

IF sy-subrc EQ 0.

SORT lt_setting2 BY /bic/zibset2 /bic/zinfopro.

ENDIF.

17

ENDIF.

*Selecting the loading times for the infocubes restricted in the report.

SELECT icube

timestamp

FROM rsiccont

INTO TABLE lt_loading

FOR ALL ENTRIES IN lt_setting2

WHERE icube = lt_setting2-/bic/zinfopro

AND timestamp >l_timestamp.

IF sy-subrc EQ 0.

SORT lt_loading BY icube.

ENDIF.

LOOP AT lt_setting2 ASSIGNING .

CLEAR lw_loading.

READ TABLE lt_loading INTO lw_loading WITH KEY icube = -/bic/zinfopro BINARY SEARCH.

IF sy-subrc EQ 0.

-flag = 'X'.

ENDIF.

ENDLOOP.

SORT lt_setting2 BY /bic/zibset2 flag.

*Get current period from system date

18

CALL FUNCTION 'DATE_TO_PERIOD_CONVERT'

EXPORTING

i_date = v_date

i_periv = c_j1

IMPORTING

e_buper = v_period

e_gjahr = v_year

EXCEPTIONS

input_false = 1

t009_notfound = 2

t009b_notfound = 3

OTHERS = 4.

*Get Next Period's first Day.

CALL FUNCTION 'LAST_DAY_IN_PERIOD_GET'

EXPORTING

i_gjahr = v_year

i_periv = c_j1

i_poper = v_period

IMPORTING

e_date = v_firstday

EXCEPTIONS

input_false = 1

19

t009_notfound = 2

t009b_notfound = 3

OTHERS = 4.

CALL FUNCTION 'FIRST_DAY_IN_PERIOD_GET'

EXPORTING

i_gjahr = v_year

i_periv = c_j1

i_poper = v_period

IMPORTING

e_date = v_firstday1

EXCEPTIONS

input_false = 1

t009_notfound = 2

t009b_notfound = 3

OTHERS = 4.

IF sy-subrc 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

* Retreive the day i.e. sunday, monday, etc

CALL FUNCTION 'DATE_COMPUTE_DAY'

EXPORTING

20

date = v_date

IMPORTING

day = day.

PERFORM get_date_range_for_pca USING v_firstday.

LOOP AT lt_brdcast INTO lw_brdcast.

l_tabix = sy-tabix.

IF lw_brdcast-/bic/ztime IS NOT INITIAL.

IF lw_brdcast-/bic/zcplusx IS NOT INITIAL. "Case C+X

l_date = v_firstday1 + ( lw_brdcast-/bic/zcplusx - 1 ).

IF v_date EQ l_date.

PERFORM check_time USING lw_brdcast-/bic/ztimev_time CHANGING l_x.

IF l_x = 'X'.

CLEAR lw_setting2.

READ TABLE lt_setting2 INTO lw_setting2 WITH KEY /bic/zibset2 = lw_brdcast-/bic/zibset1 BINARY SEARCH.

IF sy-subrc EQ 0.

IF lw_setting2-flag = 'X'.

SUBMIT rsrd_broadcast_starter AND RETURN WITH p_settng = lw_brdcast-/bic/zibset1.

ELSE.

PERFORM send_regret_mail USING lw_brdcast-/bic/zibset1. "Send Regret mail

ENDIF.

ENDIF.

21

ENDIF.

ENDIF.

ELSEIF lw_brdcast-/bic/zflag = ' '. "Case Flag is space

IF lw_brdcast-/bic/zday = ' '. "Day is initial


IF l_x = 'X'.

CLEAR lw_setting2.

READ TABLE lt_setting2 INTO lw_setting2 WITH KEY /bic/zibset2 = lw_brdcast-/bic/zibset1.

IF sy-subrc EQ 0.



ELSE.


ENDIF.

ELSE.


endif.

ENDIF.

ELSEIF lw_brdcast-/bic/zday = day. "Day is not initial


IF l_x = 'X'.

CLEAR lw_setting2.


IF sy-subrc EQ 0.

22



ELSE.


ENDIF.

ELSE.


endif.

ENDIF.

ENDIF.

ELSEIF v_date IN r_pcadt. "Case flag is X.


IF l_x = 'X'.

CLEAR lw_setting2.


IF sy-subrc EQ 0.



DELETE lt_brdcast WHERE /bic/zibset1 = lw_brdcast-/bic/zbcid.

ELSE.


ENDIF.

ELSE.

23


endif.

ENDIF.

ENDIF.

ENDIF.

ENDLOOP.

REFRESH:lt_setting,lt_brdcast,lt_setting2,lt_loading.

*&---------------------------------------------------------------------*

*&Form GET_DATE_RANGE_FOR_PCA

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* -->P_V_FIRSTDAY text

*----------------------------------------------------------------------*

FORM get_date_range_for_pca USING p_v_firstday.

REFRESH r_pcadt.

r_pcadt-sign = 'I'.

r_pcadt-option = 'BT'.

r_pcadt-low = p_v_firstday - 6.

r_pcadt-high = p_v_firstday - 2.

APPEND r_pcadt.

24

ENDFORM. "GET_DATE_RANGE_FOR_PCA

*&---------------------------------------------------------------------*

*&Form CHECK_TIME

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* -->P_LW_BRDCAST_/BIC/ZTIME text

* -->P_V_TIME text

* p_v_time.

diff = p_lw_brdcast - p_v_time.

ELSE.

diff = p_v_time - p_lw_brdcast.

ENDIF.

25

IF diff+0(2) = 0 AND diff+2(2)

26

l_wa_document-obj_langu = sy-langu.

l_wa_document-sensitivty = 'F'.

CONCATENATE P_LW_BRDCAST_/BIC/ZIBSET1 l_wa_document-obj_descrsy-datum

INTO l_wa_document-obj_descr SEPARATED BY '-'.

"Mail Body

l_wa_content-line = 'This is to inform you that the Load for the Broadcast did not complete at expected time.'.

APPEND l_wa_content TO l_t_content.

l_wa_content-line = 'Hence the Report is not available for Broadcast.'. APPEND l_wa_content TO l_t_content.

l_wa_content-line = ' '. APPEND l_wa_content TO l_t_content.




l_wa_content-line = 'Thanks & Regards '. APPEND l_wa_content TO l_t_content.

l_wa_content-line = 'BI S&M Team'. APPEND l_wa_content TO l_t_content.

" Receivers

SELECT /BIC/ZIBEMAIL from /BIC/PZIBEMAIL into TABLE lt_emailid

WHERE OBJVERS = 'A'.

LOOP AT lt_emailid ASSIGNING .

IF -email_id is NOT INITIAL.

l_wa_reciever-receiver = -email_id.

l_wa_reciever-rec_type = 'U'.

27

APPEND l_wa_reciever TO l_t_reciever.

ENDIF.

ENDLOOP.

" Send Regret Mail

CALL FUNCTION 'SO_NEW_DOCUMENT_SEND_API1'

EXPORTING

document_data = l_wa_document

document_type = 'RAW'

put_in_outbox = 'X'

commit_work = 'X'

* IMPORTING

* SENT_TO_ALL =

* NEW_OBJECT_ID =

TABLES

* OBJECT_HEADER =

object_content = l_t_content

* CONTENTS_HEX =

* OBJECT_PARA =

* OBJECT_PARB =

receivers = l_t_reciever

EXCEPTIONS

TOO_MANY_RECEIVERS = 1

28

DOCUMENT_NOT_SENT = 2

DOCUMENT_TYPE_NOT_EXIST = 3

OPERATION_NO_AUTHORIZATION = 4

PARAMETER_ERROR = 5

X_ERROR = 6

[email protected] Object DetailsCases captured in Broadcast Scheduling ProgramCase1-Explanation and ExecutionCase2-Explanation and ExecutionCase3-Explanation and ExecutionCase4-Explanation and Execution

Regret Mail sending.AppendixSample data for ZIBSET1Sample data for ZIBSET2Source Code

automate broadcasting in sap bi

Documents

tata code of conduct

sap bi

source code

bi report broadcasting

confidentiality statement

bi object details

approved confidentiality

standard functionality