Real Time BEx Report in Optimized way

We all come across the situation where we need real time data in BEx report and expecting in optimized way where we have millions of records in source. For example, we might create status report where user can lock or unlock or submit each data region separately and user wants to check the status in BEx report. Problem is, status of data region will change frequently which will increase the volume of data and real status is necessary to make sense of the process.

Required BW Objects:

1. Delta enabled Data source
2. Data source for full load
3. Standard Cube or DSO
4. Virtual provider with Function Module
5. Multi Provider
6. Process Chain
7. Date type info object
8. Time type info object
9. BEx Query with Date and Time in Row or Column

Delta enabled Data source:

A DataSource is a set of fields that provide the data for a business unit for data transfer into the BW. From a technical perspective, the DataSource is a set of logically-related fields that are provided to transfer data into the BW in a flat structure (the extraction structure) or in multiple flat structures (for hierarchies).

Standard DSO:

A DataStore object serves as a storage location for consolidated and cleansed transaction data or master data on a document (atomic) level.

Standard Cube:

An InfoCube is a set of relational tables arranged according to the star schema: A large fact table in the middle surrounded by several dimension tables.

Virtual Provider with Function Module:

A VirtualProvider with a user-defined function module that reads the data in the VirtualProvider for analysis and reporting purposes.

InfoObject:

Business evaluation objects are known in BI as InfoObjects. They are divide into characteristics (for example, customers), key figures (for example, revenue), units (for example, currency, amount unit), time characteristics (for example, fiscal year) and technical characteristics (for example, request number).

Multiprovider:

A MultiProvider is a type of InfoProvider that combines data from a number of InfoProviders and makes it available for analysis purposes. The MultiProvider itself does not contain any data. Its data comes entirely from the InfoProviders on which it is based. These InfoProviders are connected to one another by a union operation.

BEx Query:

You analyze the dataset of the BI system by defining queries for InfoProviders using the BEx Query Designer. By selecting and combining InfoObjects (characteristics and key figures) or reusable query elements (such as structures) in a query, you determine the way in which you evaluate the data in the selected InfoProvider.

Process Chain:

A process chain is a sequence of processes that are scheduled to wait in the background for an event. Some of these processes trigger a separate event that can, in turn, start other processes.

Steps involved:

1. Create Delta enabled Data source using Source table or View or Function module. Sometimes we may not have date and time field in source but we can add those fields using View or Function module and replicate it in BW side.

2. Create Standard Info Cube or DSO based on your requirement or key fields with necessary info Objects and schedule process chain once a day or hour based on data volume. Please note that Date and Time type Info objects are mandatory for this requirement.

3. Create a function module to access the same data source for Virtual cube with condition to retrieve last one day data or an hour data based on your process chain schedule time. See below for function module sample code.

4. Create Virtual info provider using previously created function module. Please note that Standard provider and Virtual provider should have same key fields or Info providers for mapping in Multi provider.

5. Create Multi provider by combining both Standard and Virtual provider and Identify/Assign the key info objects. Now we can see the latest data as well as historic data in Multi provider but sometimes there might be duplicate records or both old and new data with same key which we should only show new data to Users in BEx Report.

6. Create BEx Query with necessary Characteristics in Row and column but Date and Time or Timestamp characteristics in Row or Column is mandatory to identify the latest data. Please note that we can hide these fields using Display property.

7. Create key figures using formula and assign the date and time field using variable. Please note that the variable type should be Number.

 

 

8. Create Condition for previously created Key figures with Top N equal 1 or Bottom N equal 1 to show only one data in reports.

Virtual Provider based Function Module Sample Code:

 

FUNCTION <Function Module Name>.
*”———————————————————————-
*”*”Local Interface:
*” IMPORTING
*” REFERENCE(I_INFOPROV) TYPE RSINFOPROV
*” REFERENCE(I_TH_SFC) TYPE RSDRI_TH_SFC
*” REFERENCE(I_TH_SFK) TYPE RSDRI_TH_SFK
*” REFERENCE(I_T_RANGE) TYPE RSDRI_T_RANGE
*” REFERENCE(I_TX_RANGETAB) TYPE RSDRI_TX_RANGETAB
*” REFERENCE(I_FIRST_CALL) TYPE RS_BOOL
*” REFERENCE(I_PACKAGESIZE) TYPE I DEFAULT 100
*” EXPORTING
*” REFERENCE(E_T_DATA) TYPE STANDARD TABLE
*” REFERENCE(E_END_OF_DATA) TYPE RS_BOOL
*” REFERENCE(E_T_MSG) TYPE RS_T_MSG
*” EXCEPTIONS
*” WRONG_INPUT
*” READ_ACCESSS_ERROR
*”———————————————————————-
* initialize
FIELD-SYMBOLS: <LS_TABLE> TYPE <STRUCTURE>,
<LS_DATA> TYPE ANY.
DATA: L_T_COMPONENT TYPE ABAP_COMPDESCR_TAB,
LT_TABLE TYPE TABLE OF <STRUCTURE>,
LV_TIM TYPE SY-TIMLO.
* this is specific to infoprovider VIRTCUBE1
DATA : LV_LOGSYS TYPE TBDLS-LOGSYS,
LV_PSA_TABLE TYPE RSODSTECH,
LV_USEROBJ TYPE SOBJ_NAME,
BEGIN OF LS_TAB1,
REQUEST TYPE RSREQUID,
RNR TYPE RSSID,
PARENT_RNR TYPE RSSID,
END OF LS_TAB1,
LT_TAB1 LIKE TABLE OF LS_TAB1,
LV_STRING TYPE STRING,
LS_RANGE TYPE RSDRI_S_RANGE.
* LT_VIEW TYPE TABLE OF ZBPC_AUDIT.

CLEAR: E_T_DATA, E_T_MSG.
* Check InfoProivder Name
CHECK I_INFOPROV = ‘<Virtual Provider Name>’.

RANGES : FIELD1 FOR <VIEW-FIELD1>,
FIELD2 FOR <VIEW-FIELD2>.
* Get Logical System Name
CALL FUNCTION ‘OWN_LOGICAL_SYSTEM_GET’
IMPORTING
OWN_LOGICAL_SYSTEM = LV_LOGSYS
EXCEPTIONS
OWN_LOGICAL_SYSTEM_NOT_DEFINED = 1
OTHERS = 2.
IF SY-SUBRC <> 0.
* Implement suitable error handling here
ENDIF.
* Combine Datasource and Logical system name
CONCATENATE <DATA SOURCE NAME> LV_LOGSYS INTO LV_USEROBJ.

* Select Latest Request number successfully loaded to Cube from PSA
SELECT PARENT_RNR RNR_SID PARENT_RNR_SID
FROM RSSTATMANREQMAP
INTO TABLE LT_TAB1
WHERE DTA_DEST EQ <STANDARD INFO PROVIDER NAME> AND
DTA_DEST_TYPE EQ ‘INFO PROVIDER TYPE’ AND
DTA_SOURCE LIKE LV_USEROBJ ESCAPE ‘#’ AND
PROCESS EQ ‘DTP_LOAD’.

SORT LT_TAB1 BY PARENT_RNR DESCENDING.
READ TABLE LT_TAB1 INTO LS_TAB1 INDEX 1.

* Get Timestamp of the particualr request
SELECT SINGLE TIMESTAMPBEGIN FROM RSREQDONE INTO LV_TIME WHERE RNR EQ LS_TAB1-REQUEST.
IF SY-SUBRC EQ 0.
CONVERT TIME STAMP LV_TIME TIME ZONE ‘UTC’ INTO DATE LV_DATE TIME LV_TIM.
LV_TIM = LV_TIM – 360.
CONCATENATE LV_DATE LV_TIM INTO LV_TIMESTAMP.
LV_TIME = LV_TIMESTAMP.
ENDIF.
CONVERT TIME STAMP LV_TIME TIME ZONE ‘UTC’ INTO DATE LV_DATE TIME LV_TIM.

LOOP AT I_T_RANGE INTO LS_RANGE.
CASE LS_RANGE-CHANM.
WHEN ‘FIELD1’.
LR_FIELD1-OPTION = LS_RANGE-COMPOP.
LR_FIELD1-LOW = LS_RANGE-LOW.
LR_FIELD1-HIGH = LS_RANGE-HIGH.
LR_ FIELD1-SIGN = LS_RANGE-SIGN.
APPEND LR_ FIELD1.
WHEN ‘FIELD2’.
LR_FIELD2-OPTION = LS_RANGE-COMPOP.
LR_FIELD2-LOW = LS_RANGE-LOW.
LR_FIELD2-HIGH = LS_RANGE-HIGH.
LR_FIELD2-SIGN = LS_RANGE-SIGN.
APPEND LR_ FIELD2.
WHEN OTHERS.
ENDCASE.
ENDLOOP.

IF LR_TIMESTAMP IS NOT INITIAL.
* Retrieve the Audit data from that Timestamp
SELECT FIELD1 FIELD2 FROM <TABLE_NAME>
INTO TABLE LT_TABLE
WHERE FIELD1 IN LR_FIELD1 AND FIELD2 IN LR_FIELD2

* On Succcessful Query, Append the records to Virtual Cube
IF SY-SUBRC EQ 0 AND LT_TABLE IS NOT INITIAL.
*create a working area
ASSIGN LOCAL COPY OF INITIAL LINE OF E_T_DATA TO <LS_DATA>.
* get description of components of <ls_data>
PERFORM GET_TYPE_COMPONENTS(SAPLRSDRC_SERVICES)
USING <LS_DATA>
CHANGING L_T_COMPONENT.
LOOP AT LT_AUDIT ASSIGNING <LS_AUDIT>.
PERFORM MOVE_E070_TO_DATA
USING <LS_AUDIT>
I_TH_SFC
I_TH_SFK
L_T_COMPONENT
CHANGING <LS_DATA>.

APPEND <LS_DATA> TO E_T_DATA.
ENDLOOP.
*all data are selected
E_END_OF_DATA = ‘X’.
ELSEIF LT_AUDIT IS INITIAL.
E_END_OF_DATA = ‘X’.
ENDIF.
ELSE.
E_END_OF_DATA = ‘X’.
ENDIF.
ENDFUNCTION.

* Forms to Fill the data field by field
FORM MOVE_E070_TO_DATA USING LS_AUDIT_FORM TYPE <STRUCTURE TYPE>
I_TH_SFC TYPE RSDRI_TH_SFC
I_TH_SFK TYPE RSDRI_TH_SFK
I_T_COMPONENT TYPE ABAP_COMPDESCR_TAB
CHANGING E_S_DATA TYPE ANY.
* define FIELD-SYMBOLS
FIELD-SYMBOLS: <L_S_COMPONENT> TYPE ABAP_COMPDESCR,
<L_COMP_DATA> TYPE ANY,
<L_S_SFC> TYPE RSDRI_S_SFC,
<L_S_SFK> TYPE RSDRI_S_SFK.
* Datadeclaration
DATA: L_COMPNO TYPE I,
L_T_COMPONENT TYPE ABAP_COMPDESCR_TAB,
LS_AUDIT TYPE<STRUCTURE TYPE>,
LV_TIMESTAMP TYPE C LENGTH 15.
* initialize
CLEAR E_S_DATA.
LS_TABLE = LS_AUDIT_FORM.
*——————————————————————–*
* Post the data for ZRECORD Key Figure * Constant ‘1’
READ TABLE I_TH_SFK ASSIGNING <L_S_SFK> WITH TABLE KEY KYFNM = ‘<KEY FIGURE>’.
IF SY-SUBRC = 0.
* get number of target column
READ TABLE I_T_COMPONENT ASSIGNING <L_S_COMPONENT> WITH KEY NAME = <L_S_SFK>-KYFALIAS.
IF SY-SUBRC = 0.
* number of target data component
L_COMPNO = SY-TABIX.
* get target data component
ASSIGN COMPONENT L_COMPNO OF STRUCTURE E_S_DATA TO <L_COMP_DATA>.
* MOVE 1 TO <L_COMP_DATA>.
<L_COMP_DATA> = <KEY FIGURE>.
ENDIF.
ENDIF.
*——————————————————————–*
* Post Application ID
IF NOT LS_AUDIT_FORM-FIELD1 IS INITIAL.
READ TABLE I_TH_SFC ASSIGNING <L_S_SFC> WITH TABLE KEY CHANM = ‘FIELD1’.
IF SY-SUBRC = 0.
* get number of target column
READ TABLE I_T_COMPONENT ASSIGNING <L_S_COMPONENT> WITH KEY NAME = <L_S_SFC>-CHAALIAS.
IF SY-SUBRC = 0.
* number of target data component
L_COMPNO = SY-TABIX.
* get target data component
ASSIGN COMPONENT L_COMPNO OF STRUCTURE E_S_DATA TO <L_COMP_DATA>.
MOVE LS_AUDIT_FORM-FIELD1 TO <L_COMP_DATA>.
ENDIF.
ENDIF.
ENDIF.
*——————————————————————–*
* Post Sequence Number
IF NOT LS_AUDIT_FORM-FIELD2 IS INITIAL.
READ TABLE I_TH_SFC ASSIGNING <L_S_SFC> WITH TABLE KEY CHANM = ‘FIELD2’.
IF SY-SUBRC = 0.
* get number of target column
READ TABLE I_T_COMPONENT ASSIGNING <L_S_COMPONENT> WITH KEY NAME = <L_S_SFC>-CHAALIAS.
IF SY-SUBRC = 0.
* number of target data component
L_COMPNO = SY-TABIX.
* get target data component
ASSIGN COMPONENT L_COMPNO OF STRUCTURE E_S_DATA TO <L_COMP_DATA>.
MOVE LS_AUDIT_FORM-FIELD2 TO <L_COMP_DATA>.
ENDIF.
ENDIF.
ENDIF.
*——————————————————————–*
ENDMETHOD.