1. Business Scenario:
Report based on G/L account data, uses the mapping values that are stored in a DSO. Storing the data by applying the mappings in the Standard Cube load is not feasible, as the user requirement is to see the current version mappings applied to historical transaction data or historical version mappings applied to current transactional data.
Data dependency:
The mappings in the DSO can change frequently based on the business need. Huge volume of G/L Data
In order to meet the business requirement, considering the data dependency, the report is built on a
Virtual Cube.
Hence, the mapping from the ‘mapping DSO’ needs to be applied to the transaction data before the report is displayed using a virtual Cube. The Virtual Cube uses the Base Cube as a source and a transformation created between Base Cube and Virtual Cube. When the user executes the BEx report, the business logic implemented in the Transformations is applied to the resultant dataset before it is displayed in the report to the end user.
The below picture gives the Scenario overview:
2. Sample Report Output
In order to understand the business scenario better, the below example is discussed with some sample data and expected report output.
Note: Sample data provided is only for requirement understanding; it does not contain any data from the client (Actual Data).
Sample Transaction Data in Base Cube:
Company Code | Profit Center | G/L Account | Debit/Credit Amount |
C100 | P100 | 100000100 | 100 |
C100 | P100 | 100000200 | 150 |
C100 | P101 | 100000100 | 300 |
C100 | P101 | 100000200 | 200 |
Sample Mappings Data in Mappings DSO:
G/L Account | Version | Relationship Type | Base Account |
100000100 | 10/01/2012 | APS | GL1 |
100000200 | 10/01/2012 | KKR | GL2 |
100000100 | 11/01/2012 | ABC | GL3 |
100000200 | 11/01/2012 | XYZ | GL4 |
Expected Report Output
Case1: User enters Verison = 10/01/2012
|
Case 2: User enters Version = 11/01/2012
Company Code | Profit Center | G/L Account | Relationship Type | Base Account | Debit/Credit Amount |
C100 | P100 | 100000100 | ABC | GL3 | 100 |
C100 | P100 | 100000200 | XYZ | GL4 | 150 |
C100 | P101 | 100000100 | ABC | GL3 | 300 |
C100 | P101 | 100000200 | XYZ | GL4 | 200 |
3. Technical Challenge
The user requires ability to select the Version of the mappings which has to be applied to the transaction data before it is displayed in the BEx report
To address this, a Manual Input variable is created on the Version info object which is ready for input. Having a ready for input manual input variable in the BEx report leads to below two issues,
1. Passing the Version value entered in the selection prompt of the BEx Query to backend for using in the transformation, between Base Cube and Virtual Cube, for doing a lookup from mapping DSO is not straight forward.
2. As the value entered at the variable selection prompt of the BEx is used as filter for fetching the records from the Base Cubes, the report output does not display any data because Version is not available in the Base Cube.
In order to address the above technical challenges, below step by step approach is followed.
4. Approach to overcome the Challenge
In order to overcome the above challenge following steps have been implemented,
Note: For the approach, we have used a multi cube instead of a base cube. But the approach will still be same when only a base cube is used.
Step 1: A DSO is created as shown below. This DSO contains the G/L Account to Base Account & Relationship Type mappings. YYVERSION, which is a date in our case, is used to determine the mappings that have to be applied to the report output.
Step 2: A Virtual Cube is created that has the Multi Cube as its source. A transformation is created between the Multi Cube and the Virtual Cube as shown below.
The structure of the Virtual Cube is as shown below. Highlighted in RED are the characteristics for
Version, Relationship Type and Base Account.
Version is added to the Virtual Cube, so that a User Entry Variable can be created in the BEx Query, enabling user for entering the Value for Version of the mappings which have to be applied to the report output.
Base Account and Relationship Type are added to the Virtual Cubes, so that they can be derived from the Mapping DSO, based on the Version entered by the user.
Step 3: Created a BEX Query as shown below, on Virtual Cube from Step 2. The Query contains a Filter for Version (YYVERSION).
Step 4: The ABAP code mentioned in the Appendix A and Appendix B has been written in the Inverse
End Routine and End Routine respectively, so that the value entered by user for Version is stored in to a
Global Variable. The value thus stored into the Global Variable is used in the End Routine for doing the look up from the Mapping DSO.
Step 5: In the transformation from Multi Cube to the Virtual Cube, the Field Level routine for YYVERSION
is enabled and in the “METHOD invert_YYVERSION.”, a line of code as below has been added.
This step is very important for our scenario as we do not have YYVERSION in the Multi Cube, which is source for the Virtual Cube. Hence the value entered by user for YYVERSION is passed as a selection to the Multi Cube, and since there is no YYVERSION available in the Multi Cube, the report would always display “No Applicable Data Found”.
By enabling the Inverse Routine, by adding the dummy line of code, for the YYVERSION, we were able to influence the transfer of selection conditions, in which the system does not transfer any selection conditions to the source and therefore we were able to retrieve the dataset from the source.
5. Mapping Data used for Testing
Below test mapping data is used.
The mapping data contains 2 records with different Version, one for 01/01/2001 and other for
01/01/2010, for the transaction level G/L account 1000100262. Both the records have the Base
Account as 1123001038. The Relationship type for Version 01/01/2001 is FTP and the relationship type for Version 01/01/2010 is FLT.
6. Test Results
Test 1
Input: Version = 01/01/2001
Report Output: The Value for Version is 01/01/2001 and the Value for Relationship Type = FTP
Test 2
Input: Version = 01/01/2010
Report Output: The Value for Version is 01/01/2010 and the Value for Relationship Type = FLT
7. Appendix A
Global Declaration.
*$*$ begin of global - insert your declaration only below this line *-*
DATA: GI_THX_SELECTION_CS TYPE RSARC_THX_SELCS, GL_SX_SELECTION_CS LIKE LINE OF GI_THX_SELECTION_CS, GL_SX_SELECTION_CS2 LIKE LINE OF GI_THX_SELECTION_CS.
... "insert your code here
*$*$ end of global - insert your declaration only before this line *-*
Code in the Inverse End Routine
METHOD inverse_end_routine.
TYPE-POOLS : SBIWA.
DATA:
I_RT_CHAVL_CS TYPE RSARC_RT_CHAVL,
I_THX_SELECTION_CS TYPE RSARC_THX_SELCS,
C_T_SELECTION TYPE SBIWA_T_SELECT, E_EXACT TYPE RS_BOOL.
DATA:
L_TR_DIMENSIONS TYPE RSMDS_TR_DIMENSIONS,
"table of dimension references
L_R_DIMENSION LIKE LINE OF L_TR_DIMENSIONS,
"dimension reference
L_DIMNAME TYPE RSMDS_DIMNAME,
"dimension name
L_SX_SELECTION_CS LIKE LINE OF I_THX_SELECTION_CS,
"work area for single characteristc RANGE table L_R_UNIVERSE TYPE REF TO CL_RS_INFOOBJECT_UNIVERSE. "reference for InfoObject universe
DATA:
L_S_SELECTION LIKE LINE OF C_T_SELECTION,
L_S_CHAVL_CS LIKE LINE OF I_RT_CHAVL_CS, DATE TYPE SY-DATUM.
*Start
* Transform selection set for outbound
CALL METHOD I_R_SELSET_OUTBOUND->TO_RANGES CHANGING
C_T_RANGES = I_RT_CHAVL_CS.
* Get reference to InfoObject universe
L_R_UNIVERSE = CL_RS_INFOOBJECT_UNIVERSE=>GET_INSTANCE( ).
*Get all dimensions (i.e. fields) from outbound selection which are
*restricted
L_TR_DIMENSIONS = I_R_SELSET_OUTBOUND->GET_DIMENSIONS( ).
LOOP AT L_TR_DIMENSIONS INTO L_R_DIMENSION.
CLEAR L_SX_SELECTION_CS.
* Get dimension name (= field name)
L_DIMNAME = L_R_DIMENSION->GET_NAME( ).
* Transform dimension name to InfoObject name
L_SX_SELECTION_CS-CHANM = L_R_UNIVERSE->DIMNAME_TO_IOBJNM(
L_DIMNAME ).
*Project complete outbound selection set to current dimension and
*and convert to RANGE table representation
CALL METHOD I_R_SELSET_OUTBOUND->TO_RANGES
EXPORTING
I_R_DIMENSION = L_R_DIMENSION
CHANGING
C_T_RANGES = L_SX_SELECTION_CS-RT_CHAVL.
APPEND L_SX_SELECTION_CS TO I_THX_SELECTION_CS. ENDLOOP.
READ TABLE I_THX_SELECTION_CS INTO GL_SX_SELECTION_CS
WITH KEY CHANM = 'YYVERSION'. CATCH CX_RSMDS_INPUT_INVALID CX_RSMDS_INPUT_INVALID_TYPE.
* Should not occur
* If the exception occurs request all values from source
* for this routine to be on the save side
*c_r_selset_inbound = cl_rsmds_set=>get_universal_set( ).
*c_exact = rs_c_false. "Inversion is no longer exact
C_TH_FIELDS_INBOUND = I_TH_FIELDS_OUTBOUND. C_R_SELSET_INBOUND = I_R_SELSET_OUTBOUND. C_EXACT = RS_C_TRUE.
*$*$ end of inverse routine - insert your code only before this line *-*
METHOD end_routine.
*=== Segments ===
FIELD-SYMBOLS:
<RESULT_FIELDS> TYPE _ty_s_TG_1.
DATA:
MONITOR_REC TYPE rstmonitor.
TYPES: TY_D_MAP TYPE /BIC/AYGL_DSO00.
DATA: IT_D_MAP TYPE STANDARD TABLE OF TY_D_MAP , ST_D_MAP TYPE TY_D_MAP.
* Get base account and Rel.Type
SELECT * INTO TABLE IT_D_MAP FROM /BIC/AYGL_DSO00
FOR ALL ENTRIES IN RESULT_PACKAGE
WHERE CHRT_ACCTS = RESULT_PACKAGE-CHRT_ACCTS
AND GL_ACCOUNT = RESULT_PACKAGE-GL_ACCOUNT
AND /BIC/YYVERSION IN GL_SX_SELECTION_CS-RT_CHAVL.
LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>.
* Get base account and Rel.Type
CLEAR ST_D_MAP.
READ TABLE IT_D_MAP INTO ST_D_MAP
WITH KEY CHRT_ACCTS = <RESULT_FIELDS>-CHRT_ACCTS
GL_ACCOUNT = <RESULT_FIELDS>-GL_ACCOUNT.
*
<RESULT_FIELDS>-/BIC/ZACC_BASE = ST_D_MAP-/BIC/ZACC_BASE.
<RESULT_FIELDS>-/BIC/ZRELTYP = ST_D_MAP-/BIC/ZRELTYP.
<RESULT_FIELDS>-/BIC/YYVERSION = ST_D_MAP-/BIC/YYVERSION.
ENDMETHOD
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
11 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 |