Skip to Content
Technical Articles

Part#11.End to End Data Modeling and Reporting with CDS views

Let’s create a very simple data model on SAP’s demo data with FLIGHT module, just to showcase how an end to end business scenarios might look like.  To create any analytics data model we need DIMENSION Data which are the Master Data sets and FACT data which is a Transaction Data Set. Below are the related tables, 3 Dimension/Master tables and 1 Fact table.

Dimension Tables:

  1. SCUSTOM for Customer information.
  2. SCARR for Airline information
  3. SPFLI for Connection information.

Fact Table:

  1. SBOOK : Flight Bookings information

NOTE: For Time Dimension we can use SAP standard Basic Interface view I_CalendarDate and for Country and Country text we can use I_Country.

  1. Create the first view for Airline information : ZCDS_VIEW_AIRLN

CDS View:

Note these annotations;

@VDM:{viewType: #BASIC} : As already mentioned, this annotation tells that this is a Basic Interface view which delivers Master Data – Airline Information in this scenario.

@Semantics.currencyCode: true – This annotation tells system that field ‘currcode’ is treated as Currency field.

@Analytics: dataCategory: #DIMENSION – shows this is  Dimension data.

Output:

2. Create second view for Connection information: ZCDS_VIEW_CONN

Note: Check the keyword $Projection.  Instead of source table name or alias, we can use $Projection as well which will take care of Association.  The thing to note is with $Project, you can only use the fields which you have listed in the CDS view i.e. if the table has 20 fields but in your CDS view you want only 5 fields and you have listed them in the view, you can use only one of these 5 fields with $Projection.   You are free to use table name or alias, this is just what SAP is using for standard views and comes under best practices.

Output:  check the output and association;

 

3. Create a view for Customer information : ZCDS_VIEW_CUST

 

Output: check the Association jump to Country and Country Text via standard view I_Country.

 

 

4. Create a COMPOSITE view to combine all the Dimension views created above with Transaction or Fact data.

@AbapCatalog.sqlViewName: 'ZSQL_VIEW_FLBOOK'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Booking Information'

@VDM : {viewType: #COMPOSITE}
@Analytics: {dataCategory: #CUBE, dataExtraction: {enabled: true}}
define view ZCDS_VIEW_FLBOOK as select from sbook
association [1] to I_CalendarDate    as _CalendarDate on  $projection.FlightDate       = _CalendarDate.CalendarDate
association [1] to ZCDS_VIEW_AIRLINE as _Airline      on  $projection.Airline          = _Airline.Airline 
association [1] to zcds_view_conn    as _Connection   on  $projection.Airline          = _Connection.Airline
                                                      and $projection.FlightConnection = _Connection.FlConnectNumber
association [1] to ZCDS_VIEW_CUST    as _Customer     on  $projection.Customer         = _Customer.Customer
                                                                                                          
{
    
    /** DIMENSIONS **/

  key carrid                 as Airline,
  key connid                 as FlightConnection,
  key fldate                 as FlightDate,
  key bookid                 as BookNumber,
  key customid               as Customer,
  key agencynum              as TravelAgency,

  _CalendarDate.CalendarYear,

  _CalendarDate.CalendarMonth,

  _Customer.country      as CustomerCountry,

  _Customer.city         as CustomerCity,

 
  /** MEASURES **/
  @EndUserText.label: 'Booking Price'
  @DefaultAggregation: #SUM
  @Semantics.amount.currencyCode: 'Currency'
  forcuram               as BookingPrice,

  @EndUserText.label: 'Currency'
  @Semantics.currencyCode: true
  forcurkey              as Currency,
  
  @EndUserText.label: 'Luggage Weight'
  @DefaultAggregation: #SUM
  @Semantics.quantity.unitOfMeasure: 'WeightUOM'
  luggweight             as WeightOfLuggage,

  @EndUserText.label: 'Weight Unit'
  @Semantics.unitOfMeasure: true
  wunit                  as WeightUOM,


  // Associations
  _Airline,
  _CalendarDate,
  _CalendarDate._CalendarMonth,
  _CalendarDate._CalendarYear,
  _Connection,
  _Customer,
  
  _Customer._Country     as _CustomerCountry
}

 

Look at the few annotations used above;

@VDM : {viewType: #COMPOSITE}

This annotation describes that this is a COMPOSITE view which is an association of Master data and Transaction data. This is an SAP Best Practice to configure this annotation to follow the development standards.

@Analytics: dataCategory: #CUBE

In the above CDS view the dataCategory annotation is set to #CUBE which would define this CDS view as a data Cube.  All other Dimension table are associated with this cube to built the final Reports.  On thing to note is once a CDS view is created as #CUBE, no Dimension view can consume it.  Only views which can consume a Cube view should be of type cube themselves.  Only CDS view of type CUBE can be consumed in S/4HANA Analytics layer.  It is mandatory to define at least one measures in the CDS view of type CUBE, else the view will not get activated and throw an error.

@DefaultAggregation: #SUM

This annotation is very important in the design of a CUBE like structure which we would see shortly.  The system will be able to know these are actually Key Figures only when we use this annotation.  As a result system will put them into the right bucket ‘Key Figures’ in the cube.

@EndUserText.label: ‘Weight of Luggage’

This annotation can be used to give a required business labels to the fields instead of using standard table field names.

 

The above view is like a CUBE where combined Dimension data with Fact data.  Now we need a final view which will be ready for the consumption by UI tools and finally by business users in the from of Reports/Dashboards.  This view will be like a Query on top of the previous CUBE view.

 

Let’s create the final view as below;

@AbapCatalog.sqlViewName: 'ZSQL_VIEW_FRPRT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Details Report'
@OData.publish: true

@VDM: {viewType: #CONSUMPTION}
@Analytics.query: true
define view ZCDS_VIEW_FREPORT as select from ZCDS_VIEW_FLBOOK

{
    //ZCDS_VIEW_FLBOOK
    
    key Airline,
    key FlightConnection,
    key FlightDate,
    key BookNumber,
    key Customer,
    --key TravelAgency,
    
    CalendarYear,
    CalendarMonth,
    CustomerCountry,
    CustomerCity,
    WeightOfLuggage,
    WeightUOM,
    BookingPrice,
    Currency,
    /* Associations */
    //ZCDS_VIEW_FLBOOK
    _Airline,
    _CalendarDate,
    _CalendarMonth,
    _CalendarYear,
    _Connection,
    _Customer,
    _CustomerCountry
}

Note the below annotations;

@VDM: {viewType: #CONSUMPTION}

This annotation describes that this view is the final view ready to Consumed.  No Joins or Associations are performed here and this is made to behave like a query via below annotation.

@Analytics.query: true

This annotation actually SET this view to be consumption ready by different UI tools.It is mandatory to use this annotation if our intend is to create final analytical reports on this view via SAP front end tools – Bex, Webi, Lumira, SAC – SAP Analytics Cloud etc.

 

 

CONSUMING CDS VIEW INTO SAP UI TOOLS:

 

Now we will see how to consume the final View which also has a VDM ViewType CONSUMPTION means it is ready to be consumed.

  • There are 2 tools in SAP S4HANA where this view can be consumed.
    • RSRTS_ODP_DIS
    • RSRT

 

RSRTS_ODP_DIS:

This tcode display the structure of the Cube we created with CDS views.  If you remember, we created a CDS view of VDM type CUBE : ZCDS_VIEW_FLBOOK and the DDIC SQL view name is : ZSQL_VIEW_FLBOOK.

->Run T-code RSRTS_ODP_DIS

-Choose ODP Context as ‘ABAP Core Data Services

-For ODP Name, enter the DDIC SQL View name followed by ‘2C

-Execute

What you will see now is a ‘Transient Provider’ system created.  Note the structure, it is exactly like a BW cube.  If you are a BW consultant, you can relate well.  If not, just look at the nodes ‘KEY’, ‘DATA’, ‘UNIT’, and ‘KEY FIGURE’ and the fields underneath.  This happens due to the different annotations we used while creating the CDS view, especially datacategory: #CUBE.  If you don’t put this annotation, this will NOT work at all.

 

 

-Click on button ‘Standard Query’

-Now you can actually place data elements into rows and columns like a report.

 

-Since it is kind of a virtual cube now, you can also pull it into Bex Query Designer to create a Bex Query which can be configured with more advanced features and can be consumed into SAP Front end tools like Lumira, Webi, Analysis for MS Excel.

-> Open Bex Query Designer;

-Hit ‘Create’ and search for the view 2CZSQL_VIEW_FLBOOK and open

Now within Bex designer, you can create a regular report with all the advanced Bex features like Rows, Columns, Restricted/Calculated Key figures, Cell definitions, Exceptions, Conditions etc. and can be consumed into all Analytics tools like Webi, Lumira, Analysis for Office etc.

 

RSRT:

For simple CDS view with few Mathematical expression which we have already configured in the CDS view itself, we need not to really configure them into Bex Query designer for user consumption, rather we can directly have business users to access those via t-code RSRT.  To enable the business user to access a CDS view as type of an analytical report, we need to create the final view which is ready for user consumption.  This view will read the COMPOSITE view we created above.  This view needs to be created of type CONSUMPTION and we need to add another annotation which will make this view as a query:  @Analytics.query: true

Note the annotations :

@VDM: {viewType: #CONSUMPTION}

@Analytics.query: true

@AbapCatalog.sqlViewName: 'ZSQL_VIEW_FRPRT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Details Report'

@VDM: {viewType: #CONSUMPTION}
@Analytics.query: true
define view ZCDS_VIEW_FREPORT as select from ZCDS_VIEW_FLBOOK

{
    //ZCDS_VIEW_FLBOOK
    
    key Airline,
    key FlightConnection,
    key FlightDate,
    key BookNumber,
    key Customer,
    --key TravelAgency,
    
    
    CalendarYear,
    CalendarMonth,
    CustomerCountry,
    CustomerCity,  
    WeightOfLuggage,
    WeightUOM,  
    BookingPrice,
    Currency,
    /* Associations */
    //ZCDS_VIEW_FLBOOK
    _Airline,
    _CalendarDate,
    _CalendarMonth,
    _CalendarYear,
    _Connection,
    _Customer,
    _CustomerCountry
}

Now run T-code: RSRT and enter the name of the view followed by 2C and hit ‘Execute’

You can see the Key Figures already added as columns.  Business users can now add Rows based on the analytics requirement they have.

 

In case a business user would want to have a default view with some Rows/Columns already added into the report when he/she execute it via RSRT.  That can also be enabled via another annotations.  Lets try that as well;

Check the annotation: @AnalyticsDetails.query.axis: #ROWS

You have different options here like Bex Designer;

 

Look at the whole code with ROWS and COLUMNS config.

@AbapCatalog.sqlViewName: 'ZSQL_VIEW_FRPRT'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Flight Details Report'

@VDM: {viewType: #CONSUMPTION}
@Analytics.query: true
define view ZCDS_VIEW_FREPORT as select from ZCDS_VIEW_FLBOOK

{
    //ZCDS_VIEW_FLBOOK
    
    key Airline,
    key FlightConnection,
    key FlightDate,
    key BookNumber,
    key Customer,
    --key TravelAgency,
    
    @AnalyticsDetails.query.axis: #ROWS
    CalendarYear,
    @AnalyticsDetails.query.axis: #ROWS
    CalendarMonth,
    @AnalyticsDetails.query.axis: #ROWS
    CustomerCountry,
    @AnalyticsDetails.query.axis: #ROWS
    CustomerCity,
    @AnalyticsDetails.query.axis: #COLUMNS
    WeightOfLuggage,
    WeightUOM,
    @AnalyticsDetails.query.axis: #COLUMNS
    BookingPrice,
    Currency,
    /* Associations */
    //ZCDS_VIEW_FLBOOK
    _Airline,
    _CalendarDate,
    _CalendarMonth,
    _CalendarYear,
    _Connection,
    _Customer,
    _CustomerCountry
}

Now check this query again in RSRT, see how awesome it is 😊.. check the default rows & columns added.

 

Now if you are an ABAP expert, you might be thinking how to add a Selection Screen to this report and if you are a BW guy, you might be thinking how to add ‘Characteristic Restriction’ to filter the data.  This is absolutely possible in a CDS view with use of one another annotation:  @Consumption.filter.selectionType:  There are different options to choose from, see below.  Let’s add a selection parameter for Country with a ‘Single Selection’

Try running this query again with tcode: RSRT.  Check the selection box shows up on the top.  Enter US and execute.

 

Parameter as Mandatory Selection:

If you want to make the parameter as a mandatory selection; you can add another clause in the annotation :

 

Now if you try to run without passing any value, you can see an error message;

All above we saw how to consume CDS view into SAP Analytics tools. Now we will see how to consume the same view into FIORI—

 

 

CONSUME CDS VIEW INTO ANALYSIS FOR OFFICE:

As I mentioned, a CDS view can be consumed into Analysis for Office to enable ad-hoc analysis by the business users.  Let’s look how we can do it;

  1. Open Analysis for office and connect to the S4HANA system;
  2. Search for the CDS view of VDM type ‘COMPOSITE’ and dataCategory ‘CUBE’ we created above;

Data is ready now to do ad-hoc analysis;

 

CONSUME CDS VIEW INTO FIORI APPLICATION:

As we already saw in one of the previous blog that to consume a CDS view we need an OData service.  So let’s first configure an OData service in the CDS view.

  1. Configure OData Service – This can be done by adding an annotation. Activate the view;

 

2. Copy the service name by hovering over mouse pointer on the yellow icon that show up after            adding OData annotation;

3. Register the Service.

Goto code /n/IWFND/MAINT_SERVICE, click on ‘Add Service’ button;

 

4. Choose System Alias – ‘LOCAL’ and the external Service name which we just created by   

       OData.  Hit Enter.

 

 

5. Select the service and hit ‘Add Selected Service’ button to register the service. This is only One Time Activity.

 

6. Choose LOCAL for Package Selection and execute;

7. Service is registered.

8. Start Web IDE to create a FIORI app on top of the CDS

9. New-Project from Template;

 

10. Choose ‘List Report Application’;

 

11. Add the required details and click ‘Next;

 

12. Connect to Server;

 

13. Choose the registered service name;

 

14. Select all the annotations;

 

15. For Data Binding, select the CDS view name and hit ‘Finish’

 

16. Now execute the FIORI application;

 

 

17. You can see a FIORI tile created;

 

18. Double click on FIORI tile and enter your credentials;

 

19. Enter ‘US’ in the mandatory parameter for Country Selection and hot ‘Go’. Since we did not yet selected columns to be displayed, we will see this message;

 

20. Click on ‘Setting’ button to add columns;

 

21. Look at the wonderful report generated. This is the new reporting methodology followed in S4 environment.  There are many great features as well.

22. Users can enable more filters by themselves, I like this feature too much. No code required.  Click on Adapt Filter->More Filters.  Select the ones you need;

 

23. Check these filters are now available to filter out data;

 

In summary, we looked at the complete end to end cycle of an analytics requirements.  In your projects, you would find a similar development but can be very complex based on what business users would want to see as the final reports.

That concludes the end to end CDS to UI tools and Fiori scenario and the blog post series.  Hope you have enjoyed it!

Feel free to ask questions you may have and stay tuned for more advanced topics on CDS views.

 

Follow for upcoming blog posts: Sanjeev Kumar

Keep learning…..

SK.

2 Comments
You must be Logged on to comment or reply to a post.
  • This blog post series is very helpful because you have explained ABAP CDS technology in easy to understand terms. Should this scenario work in Suite on HANA, Netweaver 7.50 SPS 8?

     

    I am a BW and ABAP consultant and have found the information very useful. We just updated our ECC on DB2 system to Suite on HANA with Netweaver 7.5 and EHP8. I am exploring the ability of our system to use BW and Business Objects front end tools to report on ABAP CDS Analytic views in ECC, very similar to what you have explained in this blog. This blog refers to S/4 HANA system.  I am able to create all the CDS views but testing them through RSRT or RSRT_ODP_DIS results in a short dump and error when trying to execute the query in our system.