After longtime, I’m writing blog in SAP , hope it will be useful who are working with CDS Views.
About Author: I Nanda Anantha working in SAP BW/ABAP and HANA for last 18 years and written few blogs in past and my interest is exploring and learning new things in SAP.
Why I’m writing this blog?
Recently, I was participated in S/4HANA migration project and from SAP BW side I’m responsible to migrate not whitelisted standard extractor to CDS views and during this time I faced an issue (discussed later in this blog) and did not find solution anywhere, after my long research found a solution hence thought of share with you all and help you if you stuck in similar issue.
What’s is the issue?
As mentioned earlier, I was participated in S/4HANA migration project and from SAP BW side I’m responsible to migrate not whitelisted standard extractors to CDS views, in another words not whitelisted means, the extractors which are listed in not whitelisted (for example 2LIS_17_* PM Notification extractors) are not going to work anymore in S/4HANA and you should replace not whitelisted extractors with respective SAP provided CDS Views to continue data extraction process into BW system. For more information on this topic, you can search in google and you can find amazing blogs from our friends. Back to the point so when you look at CDS View definition, SAP changed field naming convention strategy for CDS Views and given more meaningful field names (like MAINTENANCENOTIFICATION from INOTIFCAUSEDATA CDS view) rather than old traditional naming format (QMNUM from QMUR table). Here is the roadblock since CDS view field names are changed we cannot copy existing transformation, for example 2LIS_17_I0CAUSE–>DSO to INOTIFCAUSEDATA–>DSO because field names are different, so system does not allow you to copy transformation. (Please refer to the below image)
Solution and options.
Option 1:
Manual mapping: We need to create custom CDS view on top of standard CDS view and rename CDS View field names to sync with extractor field names by looking at Description, Data type and length from CDS View (see exmaple below) , so that we can perform transformation copy in BW system, however this is tough and time-consuming process.
Rename/mapping Example à MAINTENANCENOTIFICATION as “QMNUM.”
Option 2:
Finding base table and field information for CDS View since SAP given replacement of extractor it should (90 %) bring data from the same tables and fields, if we get this info then our job will become easier and this information can used for multiple purposes (like lineage, while validating data finding base table, converting existing ABAP program to CDS Views etc.)
So, I choose option 2 and thought of create a CDS View Metadata Tool where it will give base table information for CDS view and partially pre-generated query.
CDS View Metadata Tool Selection Screen.
You must provide CDS view and respective data source extract structure (you can find extract structure in RSA2 tcode for any data source)
Why extract structure required: CDS View not all columns directly coming from Table/field, few columns are calculated columns which means value can be derived based on logic , for calculated columns we are filling Table field column based on description comparing between extract structure field and CDS view field.
Here is the output of CDS View Metadata Tool
Let talk about this screenshot.
Top of Page: Gives you more header level information like CDS view Name, respective DD name, Module and Module Text
Blue : View Field Name, View Field Length, View Field Datatype and View Field Text
Yellow : Base Table, Base Table Field Name, Length , Data Type and Field Text
V.Field & T.Field : Pre-generated CDS View field name to extractor field name
Duplicate : Flagged repeated field names, you have to rename/adjust as per your needs in column V.Field & T.Field
Calculated Column Ind: This column tells us whether column is calculated or direct.
I tried my level best to explain about this tool, if you have questions, please let me know, I will try to explain.
Here is the ABAP code, you should run this code in S/4HANA system.
Text element names.
P_EXTST Datasource Extract Structure
P_VNAME CDS View Name
ABAP Code.
report zcvmdtool.
parameters: p_vname type ddlname default 'INOTIFCAUSEDATA',
p_extst type tabname default 'MC17I00CSE'.
types: begin of ty_s_base_field,
entity_name type string,
element_name type string,
base_object type string,
base_field type string,
is_calculated type abap_bool,
tabclass type tabclass,
cdsview type objectname,
elefield type string,
vleng type ddleng,
vdatatype type datatype_d,
vscrtext_l type scrtext_l,
tleng type ddleng,
tdatatype type datatype_d,
tscrtext_l type scrtext_l,
dup_cnt type i,
field_dup type string,
appcmp type uffctr,
appcmpname type ufps_posid,
appcmptext(60) type c,
end of ty_s_base_field,
begin of ty_extstfl,
tabname type tabname,
fieldname type fieldname,
leng type ddleng,
datatype type datatype_d,
scrtext_l type scrtext_l,
end of ty_extstfl,
begin of ty_vldt,
tabname type tabname,
leng type ddleng,
datatype type datatype_d,
scrtext_l type scrtext_l,
end of ty_vldt,
begin of ty_tldt,
tabname type tabname,
leng type ddleng,
datatype type datatype_d,
scrtext_l type scrtext_l,
end of ty_tldt.
data: rt_base_fields type if_dd_ddl_utilities=>ty_t_base_fields,
t_base_field type standard table of ty_s_base_field,
t_base_field_d type standard table of ty_s_base_field, "find duplicate fields.
w_base_field type ty_s_base_field,
w_base_field_d type ty_s_base_field, "find duplicate fields.
ls_base_field_info type if_dd_ddl_utilities=>ty_s_base_field,
lo_finder type ref to cl_dd_ddl_field_tracker,
lo_ex type ref to cx_dd_ddl_read,
t_extstfl type standard table of ty_extstfl,
w_extstfl type ty_extstfl,
t_vldt type standard table of ty_vldt,
w_vldt type ty_vldt,
t_tldt type standard table of ty_tldt,
w_tldt type ty_tldt,
it_fieldcat type slis_t_fieldcat_alv,
wa_fieldcat type slis_fieldcat_alv,
wa_fieldlayout type slis_layout_alv,
it_listheader type slis_t_listheader,
wa_listheader type slis_listheader,
lv_repid type sy-repid,
lv_cdsddl type ddlname,
lv_cdstext type ddtext,
lv_appcmp type uffctr,
lv_appcmpname type ufps_posid,
lv_appcmptext(60) type c,
lv_cdsermsg(100) type c.
field-symbols <fs_basefield> like line of t_base_field.
start-of-selection.
lv_repid = sy-repid.
if p_vname is initial.
return.
else.
perform validate_cds_view.
if sy-subrc eq 0 and sy-dbcnt > 0.
perform base_table_extract.
perform build_header_data.
perform disp_catalog.
perform final_output_disp.
else.
concatenate p_vname ' :: CDS View not found' into lv_cdsermsg.
message lv_cdsermsg type 'I'.
endif.
endif.
*&---------------------------------------------------------------------*
*& Form base_table_extract
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
form base_table_extract .
*Datasource extract structure fields, length, data type and text
select tabname,fieldname,leng ,datatype, scrtext_l into table @t_extstfl from dd03vt where
tabname = @p_extst and ddlanguage = 'E'.
*Extract DD name for given CDS View
select single cds_ddl from ddl_object_names into @lv_cdsddl where cds_db_view = @p_vname.
* Extract business module for given CDS view.
select single a~applicationcomponent, a~applicationcomponentname, b~applicationcomponenttext into ( @lv_appcmp,@lv_appcmpname,@lv_appcmptext ) from cds_views_pkg_appcomp as a inner join application_component_text as b on
a~applicationcomponent = b~applicationcomponent
where a~ddlsourcename = @lv_cdsddl and
b~language = 'E'.
*Extract CDS view field name, data type,length and text
* select tabname leng datatype scrtext_l into t_vldt from dd03vt where
* tabname = p_vname
lo_finder = new cl_dd_ddl_field_tracker( iv_ddlname = lv_cdsddl ).
try.
data(lt_field_infos) = lo_finder->get_base_field_information( ).
catch cx_dd_ddl_read into lo_ex.
endtry.
move-corresponding lt_field_infos to t_base_field.
sort t_base_field by base_field.
loop at t_base_field assigning <fs_basefield> where base_field is not initial.
read table t_base_field_d into w_base_field_d with key
base_field = <fs_basefield>-base_field.
if sy-subrc eq 0.
w_base_field_d-base_field = <fs_basefield>-base_field.
w_base_field_d-dup_cnt = w_base_field_d-dup_cnt + 1.
append w_base_field_d to t_base_field_d.
else.
w_base_field_d-base_field = <fs_basefield>-base_field.
w_base_field_d-dup_cnt = 1.
append w_base_field_d to t_base_field_d.
endif.
endloop.
if t_base_field_d is not initial.
delete t_base_field_d where dup_cnt le 1.
endif.
sort t_base_field by is_calculated.
loop at t_base_field assigning <fs_basefield>. "into w_base_field.
read table t_base_field_d into w_base_field_d with key
base_field = <fs_basefield>-base_field.
if sy-subrc eq 0.
<fs_basefield>-field_dup = 'X'.
else.
<fs_basefield>-field_dup = ''.
endif.
<fs_basefield>-cdsview = p_vname.
select single leng datatype scrtext_l into ( <fs_basefield>-vleng, <fs_basefield>-vdatatype, <fs_basefield>-vscrtext_l ) from dd03vt where
tabname = p_vname and fieldname = <fs_basefield>-element_name and ddlanguage = 'E'.
select single leng datatype scrtext_l into ( <fs_basefield>-tleng, <fs_basefield>-tdatatype, <fs_basefield>-tscrtext_l ) from dd03vt where
tabname = <fs_basefield>-base_object and fieldname = <fs_basefield>-base_field and ddlanguage = 'E'.
if <fs_basefield>-is_calculated = 'X'.
read table t_extstfl into w_extstfl with key scrtext_l = <fs_basefield>-vscrtext_l.
if sy-subrc eq 0.
<fs_basefield>-base_field = w_extstfl-fieldname.
<fs_basefield>-tleng = w_extstfl-leng.
<fs_basefield>-tdatatype = w_extstfl-datatype.
<fs_basefield>-tscrtext_l = w_extstfl-scrtext_l.
else.
<fs_basefield>-base_field = <fs_basefield>-base_field.
<fs_basefield>-tleng = <fs_basefield>-tleng.
<fs_basefield>-tdatatype = <fs_basefield>-tdatatype.
<fs_basefield>-tscrtext_l = <fs_basefield>-tscrtext_l.
endif.
endif.
if <fs_basefield>-is_calculated = '' or <fs_basefield>-base_field <> ''.
concatenate <fs_basefield>-element_name ' ' 'As' ' ' '"' <fs_basefield>-base_field '"' ',' '"' <fs_basefield>-tscrtext_l into <fs_basefield>-elefield.
else.
concatenate <fs_basefield>-element_name ',' '"' <fs_basefield>-vscrtext_l into <fs_basefield>-elefield separated by space.
endif.
endloop.
endform.
*&---------------------------------------------------------------------*
*& Form disp_catalog
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
form disp_catalog .
* wa_fieldcat-fieldname = 'ENTITY_NAME'.
* wa_fieldcat-seltext_l = 'CDS View DDL Name'.
* wa_fieldcat-emphasize = 'C000'.
* append wa_fieldcat to it_fieldcat.
*
* wa_fieldcat-fieldname = 'CDSVIEW'.
* wa_fieldcat-seltext_l = 'CDS View Name'.
* wa_fieldcat-emphasize = 'C100'.
* append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'ELEMENT_NAME'.
wa_fieldcat-seltext_l = 'View Field Name'.
wa_fieldcat-emphasize = 'C100'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'VLENG'.
wa_fieldcat-seltext_l = 'View Field Length'.
wa_fieldcat-emphasize = 'C100'.
wa_fieldcat-just = 'C'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'VDATATYPE'.
wa_fieldcat-seltext_l = 'View Field Datatype'.
wa_fieldcat-just = 'C'.
wa_fieldcat-emphasize = 'C100'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'VSCRTEXT_L'.
wa_fieldcat-seltext_l = 'View Field Text'.
wa_fieldcat-just = 'L'.
wa_fieldcat-emphasize = 'C100'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'BASE_OBJECT'.
wa_fieldcat-seltext_l = 'Table/DDL'.
wa_fieldcat-emphasize = 'C300'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'BASE_FIELD'.
wa_fieldcat-seltext_l = 'Table Field'.
wa_fieldcat-emphasize = 'C300'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'TLENG'.
wa_fieldcat-seltext_l = 'Table Field Length'.
wa_fieldcat-just = 'C'.
wa_fieldcat-emphasize = 'C300'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'TDATATYPE'.
wa_fieldcat-seltext_l = 'Table Field Datatype'.
wa_fieldcat-just = 'C'.
wa_fieldcat-emphasize = 'C300'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'TSCRTEXT_L'.
wa_fieldcat-seltext_l = 'Table Field Text'.
wa_fieldcat-just = 'L'.
wa_fieldcat-emphasize = 'C300'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'FIELD_DUP'.
wa_fieldcat-seltext_l = 'Duplicate Field Ind'.
wa_fieldcat-emphasize = 'C300'.
wa_fieldcat-just = 'C'.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'ELEFIELD'.
wa_fieldcat-seltext_l = 'V.Field & T.Field'.
wa_fieldcat-just = 'L'.
wa_fieldcat-emphasize = ''.
append wa_fieldcat to it_fieldcat.
wa_fieldcat-fieldname = 'IS_CALCULATED'.
wa_fieldcat-seltext_l = 'Calculated Column Ind'.
wa_fieldcat-just = 'C'.
wa_fieldcat-emphasize = ''.
append wa_fieldcat to it_fieldcat.
endform.
*&---------------------------------------------------------------------*
*& Form final_output_disp
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
form final_output_disp .
wa_fieldlayout-colwidth_optimize = 'X'.
* sort t_base_field by base_object.
if t_base_field is not initial.
call function 'REUSE_ALV_GRID_DISPLAY'
exporting
it_fieldcat = it_fieldcat
is_layout = wa_fieldlayout
i_callback_program = lv_repid
i_callback_top_of_page = 'TOP_OF_PAGE'
tables
t_outtab = t_base_field
exceptions
program_error = 1
others = 2.
else.
message 'Columns not found for given CDS View. ' type 'S'.
endif.
endform.
form top_of_page.
call function 'REUSE_ALV_COMMENTARY_WRITE'
exporting
it_list_commentary = it_listheader.
endform.
form build_header_data .
wa_listheader-typ = 'H'.
concatenate 'CDS DD Name = ' lv_cdsddl into wa_listheader-info separated by space.
append wa_listheader to it_listheader.
clear:wa_listheader.
wa_listheader-typ = 'H'.
concatenate 'CDS View Name = ' p_vname into wa_listheader-info separated by space.
append wa_listheader to it_listheader.
clear:wa_listheader.
wa_listheader-typ = 'H'.
clear: lv_cdstext.
select single ddtext from dd25t into lv_cdstext where viewname = p_vname and ddlanguage = 'E'.
concatenate 'CDS View Text = ' lv_cdstext into wa_listheader-info separated by space.
append wa_listheader to it_listheader.
clear:wa_listheader.
wa_listheader-typ = 'H'.
clear: lv_cdstext.
concatenate 'Module Name = ' lv_appcmpname into wa_listheader-info separated by space.
append wa_listheader to it_listheader.
clear:wa_listheader.
wa_listheader-typ = 'H'.
clear: lv_cdstext.
concatenate 'Module Text = ' lv_appcmptext into wa_listheader-info separated by space.
append wa_listheader to it_listheader.
clear:wa_listheader.
endform.
*&---------------------------------------------------------------------*
*& Form validate_cds_view
*&---------------------------------------------------------------------*
*& text
*&---------------------------------------------------------------------*
*& --> p1 text
*& <-- p2 text
*&---------------------------------------------------------------------*
form validate_cds_view .
*Extract DD name for given CDS View
select single cds_ddl from ddl_object_names into @lv_cdsddl where cds_db_view = @p_vname.
endform.
Thank you for reading.
Nanda Anantha.
Original Article:
https://blogs.sap.com/2023/05/24/cds-view-metadata-tool/