CDS View Metadata Tool (Base Table/Field Information)

[ccc_ccc]

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.