S/4HANA CDS for SAP BW folk - Part I

It's been awhile since my last blog post here. Since then I have changed employer and a project and started working much more with S/4HANA which is a great piece of software.

Having worked more than 15 years with SAP BW, IP, BPC, BOBJ, HANA and classic analytics side of things I recently ended up on a massive S/4 programme with Embedded Analytics as the tool of choice for operational reporting. For SAP BI/BW person a lot of concepts used in S/4 are very familiar, but some are very new. Like CDS (Core Data Services) views, for instance. On BW side of things we infrequently (if ever) used CDS, but for S/4 CDS is the cornerstone of development, enhancements and data access controls. I won’t be going into the details of what is CDS and how it’s used (you can Google it yourself), I will just provide you with a few handy self-explanatory diagrams from SAP:

Those are the layers of so-called Virtual Data Model (VDM) used by S/4 extensively. As the rule of thumb, the pre-delivered interface views are prefixed with “I_” (and act pretty much as Cubes) and pre-delivered consumption views are prefixed with “C_” (and act pretty much as queries).

Most of the information online covers CDS from the very basics – how to create a view, what kinds of views are there and so on; they are either based on SFLIGHT data or on EPM model data and are very simple and far from practical. Practical information, as I define it, is "the real life example on the real life system from the real life project solving the real life problem". In my blog posts I usually try to work with the real life practical examples which you can use in your day to day professional work.

The question I’m answering is very simple – on S/4HANA 1809 on-premise edition – how can I quickly adjust an existing pre-delivered CDS view (consumption view, just to make it simpler). The version reference above is very important, because SAP makes new improvements constantly and the plethora of blog posts online fail to mention versions, so when you search for the information later you find posts from 2015, for example, explaining how to create your own OData service to expose CDS view data, which is completely outdated; or you find blog posts which explain how to create a custom tile for multi-dimensional reporting on 10 pages with a tonne of technicalities.

There is an option of using “Custom CDS Views” app in Fiori Launchpad “Extensibility” catalogue , but I find that it is more relevant for S/4HANA Cloud version which doesn’t allow backend access to NW AS, so the only option to develop custom CDS is Fiori app and not Eclipse for Cloud versions.

That’s why in this endeavour our best friend will be the “Query Browser” application from Fiori Launchpad:

It shows you all the available pre-delivered views which can be used for different purposes – from Overview Pages to KPI apps to Multi-Dimensional reports and should form the basis of any enhancement you wish to undertake (unless you just can’t find the suitable view and have to create everything from the scratch, then tough luck).

Another option of choice could be Analysis for Office add-in for Excel, which would show the available content split by areas of interest:

You can also open overview in RSRT and check the tech names. 

One of the ways from here to the definition of the Interface CDS will be to open SE11 and search for the view (let’s check the sales volume cube, drop the transient “2C” prefix):

Or, if you prefer to stick to Eclipse all the way (which I do), find the view in ADT perspective, right click on it and select "Open With"-> "Definition" or just double click on DDL Source if you have opened GUI session in Eclipse tab.

Okay. Now let’s search for I_BILLINGDOCUMENTITEMCUBE.

Found it! Open the DDL and see the definitions:

So now you get the idea how to identify some VDM components from what’s available at the very source of data.

Our task for today is to find a query C_SALESANALYTICSQRY_1 and adjust it slightly (that query is used for KPI tiles, but we want to just tweak it a bit and use it as a custom multi-dimensional report).

Search for the relevant view in Eclipse ADT perspective.

Show it in Project Explorer, right click and copy it to ZCDS_C_SALESANALYTICSQRY_2 to the package of your choosing. Change the @AbapCatalog.sqlViewName annotation to ZCDS_QRY2, remove @ClientHandling.algorithm: #SESSION_VARIABLE annotation and set @AccessControl.authorizationCheck to #NOT_ALLOWED (for the ease of testing), adjust the description of the view (if desired) and activate:

Now that the view is active let’s try and find it in Fiori’s Query Browser to make sure the custom views are visible there:

Select it and “Open for Analysis” – see that the prompt (parameters) is pre-set to populate ‘M’ in Exchange Rate Type, but the Display Currency isn’t prepopulated. That’s driven by @Consumption.defaultValue annotation:

define view ZCDS_C_SALESANALYTICSQRY_2

 with parameters

    @Consumption.defaultValue: 'M'
    P_ExchangeRateType : kurst,

    P_DisplayCurrency  : vdm_v_display_currency

As the first adjustment – let’s make currency to be pre-set to “GBP”:

define view ZCDS_C_SALESANALYTICSQRY_2

 with parameters

    @Consumption.defaultValue: 'M'
    P_ExchangeRateType : kurst,
    @Consumption.defaultValue: 'GBP'

    P_DisplayCurrency  : vdm_v_display_currency

Activate the view and open for analysis again (note: the default value wouldn’t be populated in “Data Preview” mode of Eclipse, because that way isn’t considered to be a consumption). The changes to prompts are immediately visible:

The resulting report is far from being usable:

Yes, we can drag and drop dimensions into rows and columns, hide or show measures, but that would mean that this report is pretty much ad-hoc and it’s initial state is far from the desired. Also, it might show too many measures, most of which we don’t require. As a BW/BI consultant, you are quite used to the flexibility of BW Query tool in Eclipse or to BEx Query Designer, so initially CDS could seem a bit cumbersome (and it somewhat is), but the reality of the situation is that the CDS view which we have copied is based on the real-time data in S/4 and shows the data immediately without having to model ETL processes, infoproviders, master data infoobjects, flows, queries, etc. That’s the big differences for the operational reporting – it is available right now, so in that respect Virtual Data Model of S/4 allows a lot of modelling flexibility without additional development overhead.

As the customisation starting point let’s add Customer to the rows so that our initial view will always have it. When I say starting point – I mean initial consumption CDS view. In Query Viewer’s analysis view end users or developers can adjust the view of the query and save as a tile, that would be enough for many users, but they would save it as a kind of variant, so that any changes to the underlying CDS output won’t be visible to them, unless they restart again from the initial view.

Anyway. To add any dimension to the row add the following annotation before it:

@AnalyticsDetails.query.axis: #ROWS

Add it before Customer select in CDS and that would mean that Customer will always be present in rows of that CDS:

@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.display: #KEY_TEXT

Customer,

There is a trick with annotations of the same type - rather than write them in two rows, we can use the curly brackets like this:

@AnalyticsDetails.query: {axis: #ROWS, display: #KEY_TEXT}

Activate and preview the query (you can just go back and return in browser):

You can see Customer in rows now. Let’s remove Customer and add Profit Centre to rows and refresh the view again.

  @AnalyticsDetails.query.axis: #ROWS

  ProfitCenter,

Lovely. We have changed the profit centre, but there is no description. Let’s right click on the dimension member and check “Display”.

Nope. No attributes or texts. Not great! Let’s see how we can change it. The annotation that drives key and text is:

@AnalyticsDetails.query.display: #KEY_TEXT    

Insert it above Profit Center select and activate the view.

@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.display: #KEY_TEXT

ProfitCenter,

Don’t forget that by pressing Ctrl+Space shortcut you can see the code completion popup with options

Refresh the query and try activating the text and key again.

Still not working! Argggh. What’s wrong? Well, what’s wrong is that using just the @AnalyticsDetails.query.display won’t work because the system doesn’t really know where to find text of Profit Center dimension. Unlike BW modelling when the system knows that any hierarchies, texts and attributes are located within the Text/Hierarchy/Attribute tables of the InfoObject, CDS doesn’t know that and has to be pointed to the right location via association. SAP pre-deliveres interface views for most of the master data objects, so it’s quite easy to enhance the pre-delivered CDS to pick up necessary attributes and texts of the desired dimension. You may spot that other dimensions don’t really have the relevant associations as well, but the texts are available in Query View anyway. That is because all the relevant associations for those dimensions are already in place in the underlying interface views, so the consumption view already knows where to find the texts and other relevant attributes.

Now, what we need to do to do it properly is to copy the interface view I_SalesAnalyticsCube_1 into ZCDS_I_SALESANALYTICSCUBE_1 and adjust it accordingly by adding the relevant association to the Profit Center MD Interface View. :

Without knowing what is the view name I can search for it with wild cards “I*ProfitCenter*”

Success! Now we know where to find the details of Profit Centres. Open the CDS view and see what’s going on there. You will see that it returns all the relevant attributes as well as texts. This is the great and easy way for BW/BI consultants to hit the ground running, because usually they aren’t familiar with S/4 or ECC data models directly and don’t really know (or care) where to locate data for Profit Centres, Cost Centres, GL Accounts, etc. in ERP systems. For us it was always the simpler way – find 0PROFIT_CTR_ATTR and 0PROFIT_CTR_TEXT data sources, activate them and load into 0PROFIT_CTR infoobject. Job’s done. Don’t care about any ECC tables. So with VDM SAP decided to make everyone’s life a bit easier as well!

Anyway, let’s carry on. Copy the interface view and create association to the I_PROFITCENTER view the following way:

association [0..1] to   I_ProfitCenter as _ProfitCenter  on  $projection.ProfitCenter       = _ProfitCenter.ProfitCenter

@ObjectModel.foreignKey annotation should make sure ProfitCenter is actually aware that it’s being associated with another view. Place it above ProfitCenter select:

@ObjectModel.foreignKey.association: '_ProfitCenter'

ProfitCenter,

Add _ProfitCenter association to the list of exposed associations below:

Don’t forget to replace the Interface view reference in the Consumption view:

as select from ZCDS_I_SalesAnalyticsCube_1(P_ExchangeRateType:$parameters.P_ExchangeRateType, P_DisplayCurrency: $parameters.P_DisplayCurrency) as SAC

Now activate the Consumption view and refresh the Query. The description is there immediately as well as all the attributes of Profit Centre:

Okay. Now let’s implement another annotation to hide all the unnecessary key figures. We are only interested in Net Sales, Profit Margin and Costs, the last three measures in CDS. So for the rest of the measures we should use the @AnalyticsDetails.query.hidden annotation. Paste it above all other measures except the last three, activate the view and refresh the query. Also, check that all the measures we are interested in are actually formulas (calculated key figures, in other words):

The query now initially contains only three measures and Profit Centre in rows. All other measures are still available in filter and can be shown:

Now check the Open Billing Requests key figure on the screenshot above. Let’s say we don’t want to expose that key figure to the consumption at all, in that case we will use @Consumption.hidden: true annotation on that key figure, instead of @AnalyticsDetails.query.hidden. Let’s try that and see what would be the end result:

It’s not there anymore!

Last thing we want to do in part 1 is to add additional parameter to the prompt – Sales Organisation and make sure it looks fine. Sales organisation is a Company Code, which is data element BUKRS and should be used as such in the parameter definition:

define view ZCDS_C_SALESANALYTICSQRY_2
 with parameters
@Consumption.defaultValue: 'M'
P_ExchangeRateType : kurst,
@Consumption.defaultValue: 'GBP'
P_DisplayCurrency  : vdm_v_display_currency,

P_SalesOrg :  bukrs

I won’t be adding default value, because you got the point already, so activate the view and check the prompt. Whoa. Again. Nothing happened! But there is a warning on the parameter which we have ignored:

The parameter itself doesn’t do anything and will be ignored. The parameter has to be used for something. Well, like a WHERE clause. Let’s add it to our code:

This time the warning message is gone and Sales Organisation parameter value is used in WHERE clause now, so the field is now in prompt:

But it’s called “Company Code” while we actually mean “Sales Organisation” this time. It inherited the label from BUKRS data element. Very useful tool to check the annotations is "Active Annotations" view in ADT:

It shows the list of all available annotations in the selected view. So here we can see why P_SalesOrg parameter is having that label:

It's quite easy to trace everything to it's sources in simpler views, but the more complex the views get the harder it gets to trace labels, dependencies, and so on.

Let's go ahead and override the inherited "Company Code" labelusing another annotation @EndUserText.label: 'Sales Organization' just above the parameter definition:

define view ZCDS_C_SALESANALYTICSQRY_2 

 with parameters

@Consumption.defaultValue: 'M'
P_ExchangeRateType : kurst,
@Consumption.defaultValue: 'GBP'
P_DisplayCurrency  : vdm_v_display_currency,
@EndUserText.label: 'Sales Organization'

P_SalesOrg :  bukrs

Activate and refresh the view:

Also, active annotations now changed from inherited to defined value:

I hope you enjoyed this relatively simple intro into S/4 CDS Views and VDM, which is actually quite a powerful tool but requires a bit more work than BEx Queries. I haven’t worked with it much yet, but I quite like it because it takes data modelling to the lowest coding level, almost like a raw DB development, which isn’t for everyone, but after you’ve got the hang of it it – it’s quite enjoyable!

In the following parts I will show how to work with time and expand on in-built functions and how can they be used and tested, as well as how to work with CDS as OData source and expose the queries as Fiori tiles.