Remodeling process in BI
Author: Sourav Banerjee
Author Bio: Sourav is currently working as a SAP BI consultant in IBM India Pvt Ltd. He has worked in different BI support/enhancement projects throughout his career.
Why Remodeling ?
Remodeling is a concept using which new characters and/or key figures can be added to cube. This concept however is not valid for the DSO/master data. The reason which necessitates remodeling is the requirement of historical data change without a data reload. Info cubes are like set of data base tables (F/E table, dimension tables and SID tables) grouped together. Unlike DSO, cube stores a more summery level overview as there is no specific primary key. All characters in the dimension can be treated as key fields meaning that the records can be treated as unique entry as request ID becomes a key field (like change log table of a DSO). Presuming that a cube which stores millions of entries in a SAP landscape requires an addition of character. The normal way of doing it is to edit the cube and add the character to the dimension of your choice, this would however add the character with empty values for all historical data. If it is required to load that field’s value for all the historical data then we need to delete the entire content of the cube and then upload the data again assuming that the new data loading logic is already in place (inside transformation/update rule).
Question is why would someone load all the dimension tables (i.e., loading the entire cube) when there is change in only one dimension (where the character is added). This is where remodeling comes into picture which will convert only the dimension in question and all the dimension/fact tables will be remained as it was. While doing the remodeling the following operations are carried out by the system –
1. Deactivate the cube.
2. Deactivate all the dependent BI objects like transformation, multiprovider, DTP etc. This step is often referred as Impact.
3. Run the remodeling rule – Add/remove the character/key figure to the specific dimension or to the key figure part.
4. Run the rule to fill the dimension table (either direct or constant or exit). A database commit to update the field’s value to the dimension table i.e, altering the table.
5. Activate the cube.
Please note that the remodeling process will not activate the transformation, multiprovider and DTP. The reason is that, when we add a new character to the cube, the rule in the transformation remains empty unless manually added. This is why SAP gives us a chance to review and update the transformation ,MP etc rather than activating the dependent objects directly.
Whenever a new remodeling rule is created, it directly impacts the SAP report to update that cube. It is often found that while using remodeling if it fails, corrupts the backend program and therefore further remodeling on the same dimension becomes impossible. The only way left then to reimport the cube from some other environment.
Another common problem is that if the remodeling rule is created with a customer exit, there is no standard way to debug the code at runtime. Therefore we can only check the syntax but runtime analysis is not possible unless the actual remodeling rule is run. This is why very careful testing required prior to deploying the rule to the production environment. It is already recommended to test the rule in a copy cube prior to the execution of the rule in the actual cube.
Please note that while doing remodeling using customer exit, we can’t use the value of a field which is not inside the same dimension. Meaning that if you are planning to add posting year to dimension ‘X’, you can’t use 0CALMONTH value as an input (because this character is in the time dimension). You can however use the values of all the other characters from dimension ‘X’. Also note that once your code starts running, it updates the dimension table in loop; no mass update is possible.
For understanding purpose first let us take a look at the remodeling screen. It can be navigated from any cube. Please note that one remodeling rule is unique for one info cube which means that remodeling rule is not reusable.
Navigate to the cube where remodeling is needed. Right click and select Additional Function -> Remodeling.
Step2: Give Technical name of the remodeling rule and press create. The enter description and press transfer.
Step3: Click on the + sign to create a new rule.
Please find the explanations of all the above options –
1. Add Characteristic: This option is default in the remodeling rule. This will add a new character to a dimension of choice.
2. Delete Characteristics: Using this option we can delete any existing character from the cube. Please note that if we select this option, we don’t need to specify the rule or the dimension details. Only input needed will be the character technical name.
3. Replace Characteristics: Using this option, we can replace any existing character with a new character. In the option ‘Replace character’, F4 values would be the available characters in the cube whereas the option ‘With’ would include all the available characters in the system. In this case also the characters from the same dimensions can only be used as input.
4. Add Key figure: Using this option you could add a key figure to the cube. The following options are available : Constant or User exit. While using customer exit, only the F table or E table can be used as input.
5. Delete Key figure: Using this option, an existing key figure can be deleted from the cube. All you need to specify is the key figure’s technical name.
6. Replace Key figure: With this option, an existing key figure can be replaced by another key figure. Please note that the only rule available here is customer exit.
1. Constant: Constant values can be updated.
2. Attribute of Characteristics: With this option, the value of the new field can be mapped from any attributes from any character of the same dimension. Please note that the master data should be available in the same dimension.
3. 1:1 Mapping: With this option the mapping from another characteristic in the same dimension can be done.
4. Customer exit: If all the above options are not sufficient to achieve the requirement then one should go for customer exit.
Step4: Give the new character which needs to be added, the dimension name where the addition is required and the class name used for the customer exit (in this case ZCL_ADD_YEAR). Unlike other exits, in this case there is no standard place to write the customer exit code. There is one standard interface available called IF_RSCNV_EXIT which needs to be added to the class. Apparently the implementation of this interface to be done inside the custom class of your choice. The Method EXIT inside the interface can be used for this purpose.
Important parameters inside the interface method which are needed for the logic is given below –
I_R_OLD (Importing): Reference point to the entire row of the dimension table (old structure before remodeling)
C_R_NEWFIELD (Changing): Reference point to the new field in the dimension table (new structure)
Step5: Create a new class in SE24 name it as ZCL_ADD_YEAR. Add the interface IF_RSCNV_EXIT to the interface tab. Press Enter, then the method exit would be available in the Method tab.
Step6: Pseudo Logic to be written inside the method is given below –
- Get the actual row (data) from the dimension table from the reference I_R_OLD.
- Get the SID of End date (a field from the same dimension) from the above row. Convert it to value.
- Get year from date.
- Convert the date to corresponding SID.
- Assign to C_R_NEWFIELD.
Actual code is written below –
FIELD-SYMBOLS: <l_s_old> TYPE ANY,
<l_fillfield> TYPE ANY,
<l_newfield> TYPE ANY.
DATA: l_chavl TYPE rschavl,
l_chavl1 TYPE rschavl.
* Assign parameter I_R_OLD (Structure of the table before remodeling).
* Assign result/value of the new field to the paramter C_R_NEWFIELD.
* If you want to use the same customer exit for more than one remodeling rule
* make use of parmater I_CNVTABNM.
ASSIGN c_r_newfield->* TO <l_newfield>.
ASSIGN i_r_old->* TO <l_s_old>.
*Get the SID of End date for the dimension table row
ASSIGN COMPONENT ‘SID_GENDDA’ OF STRUCTURE <l_s_old> TO <l_fillfield>.
*Convert SID to value
CALL FUNCTION ‘RRSI_SID_VAL_SINGLE_CONVERT’
i_iobjnm = ‘GENDDA’
i_sid = <l_fillfield>
e_chavl = l_chavl
no_value_for_sid = 1.
*Get year from End date
l_chavl1 = l_chavl(4).
*Convert value to SID for Document year
CALL FUNCTION ‘RRSI_VAL_SID_SINGLE_CONVERT’
i_iobjnm = ‘ZYEAR1’
i_chavl = l_chavl1
e_sid = <l_newfield>
no_sid = 1
chavl_not_allowed = 2
chavl_not_figure = 3
chavl_not_plausible = 4
x_message = 5
interval_not_found = 6
foreign_lock = 7
inherited_error = 8
OTHERS = 9.
Step7: Schedule your remodeling rule and go to monitor.
Metadata: This step checks the consistency of the cube.
Impact: Identify the dependency of the cube and deactivate all necessary objects.
/BIC/DBWTEST2: This is the dimension table name and this step converts the table (i.e. runs the customer exit).
Activate: This step activates the cube after remodeling.
Cleanup: This step forces the remodeling job to stop and flag as successful.
Step8: Check the output of the cube now and see the content of the new field for the old data.
******************************** Thank You *******************************************