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:

/wp-content/uploads/2013/10/clip_image002_311927.jpg

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

Company Code

Profit Center

G/L Account

Relationship

Type

Base

Account

Debit/Credit

Amount

C100

P100

100000100

APS

GL1

100

C100

P100

100000200

KKR

GL2

150

C100

P101

100000100

APS

GL1

300

C100

P101

100000200

KKR

GL2

200

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

  • User selection of version to be executed during query run to the transaction data
  • Version data is not a part of transaction data & not a part of Base cube

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.

/wp-content/uploads/2013/10/clip_image002_311927.jpg

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.

/wp-content/uploads/2013/10/clip_image002_311927.jpg

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.

/wp-content/uploads/2013/10/clip_image002_311927.jpg

Step 3: Created a BEX Query as shown below, on Virtual Cube from Step 2. The Query contains a Filter for Version (YYVERSION).

/wp-content/uploads/2013/10/clip_image002_311927.jpg

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.

/wp-content/uploads/2013/10/clip_image002_311927.jpg

5. Mapping Data used for Testing

Below test mapping data is used.

/wp-content/uploads/2013/10/clip_image002_311927.jpg

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

/wp-content/uploads/2013/10/clip_image002_311927.jpg

Report Output: The Value for Version is 01/01/2001 and the Value for Relationship Type = FTP

untitled.PNG

    Test 2

Input: Version = 01/01/2010

/wp-content/uploads/2013/10/clip_image002_311927.jpg

Report Output: The Value for Version is 01/01/2010 and the Value for Relationship Type = FLT

untitled1.PNG

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 SYDATUM.

*Start

  1. TRY.

* 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_CSCHANM = 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_CSRT_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

  1. ENDTRY.

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 *-*

  1. ENDMETHOD.

  1. 8.  Appendix B

METHOD end_routine.

*=== Segments ===

FIELDSYMBOLS:

<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_PACKAGECHRT_ACCTS

AND GL_ACCOUNT = RESULT_PACKAGEGL_ACCOUNT

AND /BIC/YYVERSION IN GL_SX_SELECTION_CSRT_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.

  1. ENDLOOP. “<RESULT_FIELDS>

ENDMETHOD

To report this post you need to login first.

2 Comments

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

  1. Koen Hesters

    Hi,

    is there a way to limite the number of fields fetched from the souce.

    Meaning, if in my query only gl-account and amount exists.  I only want to fetch the according data, now in my sourcepackage I see all other objetcs getting data as well.

    Can you help me?

    Grtz

    Koen

    (0) 

Leave a Reply