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.
Caution:
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.
Remodeling Steps:
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.
Step1:
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.
RULES:
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 –
METHOD if_rscnv_exit~exit.
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’
EXPORTING
i_iobjnm = ‘GENDDA’
i_sid = <l_fillfield>
IMPORTING
e_chavl = l_chavl
EXCEPTIONS
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’
EXPORTING
i_iobjnm = ‘ZYEAR1’
i_chavl = l_chavl1
IMPORTING
e_sid = <l_newfield>
EXCEPTIONS
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.
ENDMETHOD.
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 *******************************************
Informative and Good Document 🙂
Thanks,
Purushotham.
Dear Sourav Banerjee,
I have few questions,
1. Will the code in the method "exit" will execute for every single historical record present in the cube. For example, if the cube contains 1000 rows before remodeling, after remodeling will the code in the "exit" method, will be executed 1000 times?
2. We are dealing with the SID's only in the code present in the "exit" methos, what about the dimension ID's. Because in the dimension table only Dimension Id's are present not the SID's(except in the dimension is line item enabled).
Hi Lakshmi,
Please find the answers below -
1. Exit will be valid for all the historical records available in the dimension table (on which the remodeling is performed). If the cube is having 1000 lines that does not mean that the dimension table will also have 1000 entries, if you see what I mean.
2 I afraid you might have to revisit the dimension table structure. Each dimension table (irrespective of whether it is a line item dimension or not) should have Dim id as key field and SIDs of all the characters inside that dimension.
If you have more question, please mail me at snitwipo@gmail.com and I would be happy to clarify your doubts.
Regards
Sourav
Nice work..
Very useful and helpful article. Thanks for making and sharing Sourav.
Thanks
Very Nice & Easy to Learn even for those who are novice to BI. Thanks for sharing bro..
Thanks to all 🙂
Hi Sourav,
thanks a lot for this wanderful explanation on Remodelling Process. I have a question about it. I did a remodeling of a cube, where i added 1 characteristic and load it with data from another infoobject. it works perfectly. all this on our D-System. Now my problem is ,i want to import the change in the Q & P-system. I'm a bit affraid since i'm doing it for the 1. time. can you please give some advices how to do it in a proper way?
Thanks in advance
Valerie
Hi Sourav,
Nice and detailed info on remodelling which I was searching for.
Thanks!
Mohammed.
Nice work on the details of the customer exit
thanks
Good work Sourav. Detailed explanation with screen shots. Thanks for sharing.
Srikanth.
Hi,
Is it possible to do a lookup from DSO to populate the added fields?
Rgds,
Nash
Hi Nash,
Yes it is indeed possible provided you have all the required fields for the lookup in the dimension in question
Regards
Sourav
Helpful one . thanks for sharing 🙂
Thanks for the detailed doc!
However I'm having an hard time understanding why SAP didn't provide out of the box conversion capabilities for date objects (date -> calmonth, date -> calmonth2, date -> year, calmonth -> calmonth2, calmonth -> year at least)
SAP has already provided those conversion that can be easily achieved at BEx level.
http://help.sap.com/saphelp_nw73ehp1/helpdata/en/1f/83813ec8d3fe3ee10000000a114084/frameset.htm
I was about to write the blog on it but due to some system issues i couldn't do that.
Regards,
AL
Well, they are not the same thing, or do you mean that this doc is useless? 😉
Hi,
You said about the conversions related to TIME so i gave you the link..
As far as document is concerned i feel all the docs and blogs has its own importance and i really appreciate the efforts each one of them put to make it.
Regards,
AL
Not to make the story too long, but given the fact that this page is about remodeling my comment was about the ability to do this basic conversions in remodeling out of the box, instead of developing the code that the author has kindly shared.
Good article..!
Hi Sourav,
Thanks, It worked.
Below is the code for reference. Hope this helps!
METHOD if_rscnv_exit~exit.
TYPES: BEGIN OF ty_t_field_data,
doc_number TYPE /bi0/oidoc_number,
/bic/zzspins1 TYPE /bic/oizzspins1,
END OF ty_t_field_data,
BEGIN OF ty_t_old_data,
doc_number TYPE /bi0/oidoc_number,
END OF ty_t_old_data.
DATA: l_chavl TYPE rschavl,
l_chavl1 TYPE rschavl,
l_attr TYPE string,
lv_docnum TYPE /bi0/oidoc_number.
DATA: lt_data_for_new_field TYPE STANDARD TABLE OF ty_t_field_data,
lt_old_data TYPE STANDARD TABLE OF ty_t_old_data,
ls_data_for_new_field TYPE ty_t_field_data.
FIELD-SYMBOLS: <l_s_old> TYPE any,
<l_fillfield> TYPE any,
<l_newfield> TYPE any.
*****************************************************************************
* 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>.
REFRESH lt_old_data.
SELECT doc_number
FROM /bic/adsd_1700
INTO TABLE lt_old_data.
IF sy-subrc EQ 0.
REFRESH lt_data_for_new_field.
SELECT doc_number /bic/zzspins1
FROM /bic/adsd_5000
INTO TABLE lt_data_for_new_field
FOR ALL ENTRIES IN lt_old_data
WHERE doc_number EQ lt_old_data-doc_number.
IF sy-subrc EQ 0.
ENDIF.
ENDIF.
*Get the SID for the dimension table row
ASSIGN COMPONENT 'SID_0DOC_NUMBER' OF STRUCTURE <l_s_old> TO <l_fillfield>.
*Convert SID to value
CALL FUNCTION 'RRSI_SID_VAL_SINGLE_CONVERT'
EXPORTING
i_iobjnm = '0DOC_NUMBER'
i_sid = <l_fillfield>
IMPORTING
e_chavl = l_chavl
EXCEPTIONS
no_value_for_sid = 1
x_message = 2
OTHERS = 3.
IF sy-subrc <> 0.
ENDIF.
CLEAR lv_docnum.
lv_docnum = l_chavl.
CLEAR ls_data_for_new_field.
READ TABLE lt_data_for_new_field INTO ls_data_for_new_field
WITH KEY doc_number = lv_docnum.
IF sy-subrc EQ 0.
CLEAR l_chavl1.
l_chavl1 = ls_data_for_new_field-/bic/zzspins1.
*Convert value to SID
CALL FUNCTION 'RRSI_VAL_SID_SINGLE_CONVERT'
EXPORTING
i_iobjnm = 'ZZSPINS1'
i_chavl = l_chavl1
IMPORTING
e_sid = <l_newfield>
EXCEPTIONS
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.
IF sy-subrc <> 0.
ENDIF.
ENDIF.
ENDMETHOD.
Regards,
Nash
Hi Sourav,
Nice doc...
This was great. detailed explanation on remodelling.
If we have to load historic data how can we do it...we have to reload again?
Yes we have to reload again and it will give only new records for specific fields.
Thanks,
Phani.
nice dock .
very informative and excellent document .
Thanks
Abhinav
Hi All,
I am trying to remove a field from SPO (semantic partitioned objects) in SAP BW on HANA system using remodeling option.
This method is successful on DSO and Cube, but it is not working on SPO.
The scheduled job is completing successfully without any errors but the field is not deleting from the SPO
Kindly advise on the same
thanks in advance.