Skip to Content

Purpose and target audience:

 This blog explains the scenario to create BW Query on top of CDS View (Transient Provider).  includes how it can be created and the values for doing it. By using this scenario, sophisiticated calculations are possible, which are impossible or difficult to implement in Analytic Query of CDS view.

 Main target audience is BW experts who have BW Query knowledge. Yes, this is for you, who have already have enough experiences of realizing sophisticated or complex business requirements of customers’ KPIs with BW Query so have already known BW Query has powerful functions to make it possible.

 Another target is the ones who want to realize sophisiticated calculations in S/4HANA Analytics including BPC for S/4HANA. With BW Query function and the help of BW experts, it might be able to be realized much easier. One of the key or challenge for S/4HANA App development is to realize the sophisiticated calculations on CDS View.

It is for S/4HANA On Premise.

 

Highlights:

  • BW Query can be the source of manay analytical apps in S/4HANA including Fiori KPI tile and Fiori Elements app (by exposing it as OData Service), as well as Fiori Multidimensional Reporting app, BusinessObjects, SAP Analytics Cloud (SAC).
  • BW Query can be created from CDS View in which Analytics Data Category is set to #DIMENSION or #CUBE ( called Transient Provider), although Analytic Query should be used as much as possible.
  • Customers can enjoy the values of BW Query as below.
    • Many BW OLAP functions are available which are impossible or difficult in Analytical Queries of CDS View.
      • The Second Structure, Sort by an attribute desc, Condition, Constant Selection etc..
    • Some of those BW functions can be used when exposed to OData Service, and can be used for Fiori Elements apps and KPI/Report Tiles like Restricted/Calculated Measures.
    • For BPC for S/4HANA data, it is possible to leverage those functions to create reports and also possible to create KPI Tile or Fiori Elements apps from BPC for S/4HANA Data even when using Realtime Infocube.

 

Scenarios to use BW Query in S/4HANA:

BW Query can be created from CDS View (called Transient Provider), as well as BW Providers, e.g. Composite Provider. (In addition, it can be created from HANA Calculation View.)

BW Query can be the source of

  • Fiori Multidimensional Reporting (Transient Provider)
  • Fiori KPI Tile / Report Tile (OData)
  • Fiori Element apps (OData with UI Annotation)
  • SAP Analytics Cloud (Transient Provider/OData)
  • BusinessObjects (Transient Provier)

BW Query has already been used in BPC for S/4HANA and embedded BW in S/4HANA. There are some Predefined Fiori apps using BW Query, e.g. Cost Centers – Actuals.

 

For creating Multidimensional Reporting app using BW Query, see How to create custom Fiori Multidimensional Reporting Application in S/4HANA on-premise. But BW Query is not listed in Fiori Query Browser.

 

Fiori Multidimensional Reporting app:

 

BW Query can be exposed as OData Service and it can be used as the source for Fiori Elements app (List Report, Analytical List Page, Overview Page, Object Page, etc..) and Fiori KPI Tile and Report Tile. See Steps to Create an ODATA service for a BW Query and BW OData Queries to expose BW Query as OData Service. This means you can create them for creating Fiori analytical app using BPC for S/4HANA data. This also means Restricrted and Calculated Key Figure in BW Query can be used for KPI tile or Fiori Elements apps. You may be able to go without using “Semantic Tag”.

 

BW Query can be set as OData Service in “General” tab of BW Query Definition in BW-MT.

For creating KPI Tile, see Create Your First Smart Business KPI and Tiles in 10 Minutes. But Please note that it can be created only if the BW Query is in S/4HANA, as Evaluation of KPI Tile cannot be saved in Standalone BW system in which S4CORE or S4FND is not installed. See SAP Note 2547185 in detail.

To understand Fiori Elements, there are many sites like Introduction to SAP Fiori Elements. There are also many blogs about Fiori Elements apps like SAP FIORI ELEMENT: List Report For Beginners. For creating Fiori Elements app from the OData Service based on BW Query, UI annotations have to be added with e.g. Annotation Modeler. See Fiori Elements – How to Develop a List Report – Using Local Annotations.

  • When used in Overview Page, the result set is aggregated by attributes in the BW Query, not by the attribute selected in the card at the moment.

 

KPI Tile:

 

Fiori Elements apps (Overview Page):

 

Analysis Office in BusinessObjects:

Analysis Office can be used for Analytic Query of CDS View as well as BW Query.

 

SAP Analytics Cloud (SAC):

BW Query and Analytic Query of CDS View can be the source of SAC. Live scenario (access directly to S/4HANA) as well as Offline scenario (Data is imported to SAC from S/4HANA), and Analytic Query or BW Query is the main option for Live scenario. See Data Connections in detail.

 

Note: whether Analytic Query or BW Query?

If functionality is available, a CDS query (means CDS view with @Analaytics.query:true) should be preferred. Lifecycle (transport, activaten, where used) of BW query is different from CDS View. Also changes of CDS view might cause changes of BW-InfoObject names, such that the BW query has to be adjusted. In short BW-queries on top of CDS transient Providers are possible, but might cause issues regarding the lifecycle.

 

Create BW Query with BW-MT:

BW-Modeling Tools (BW-MT) has to be prepared beforehand to create BW Query. It is the add-in tool in HANA Studio. (See the blog “BW Modeling Tools – Installation and configuration hints”)

Login the BW system with BW-MT and start creating a BW Query from a InfoProvider (anyAny InfoProviders are okay to select).

 

Check “Search for TransientProvider”, and push “Browse” of InfoProvider.

 

Search the CDS View as the InfoProvider of the BW Query.

The name of the InfoProvider should be “2C<SQL View name of the CDS View>”, e.g. “2CIFIGLBALCUBE“ for the CDS View “I_GLACCTBALANCECUBE”.

 

  • CDS Views in which Analytics Data Category is set as “CUBE” or “DIMENSION” can be selected (@Analytics.dataCategory: #CUBE or #DIMENSION) as only those CDS Views works as InfoProviders (called Transient Provider).

Then the InfoProvider name is set in the original screen.

 

Set Query technical name and description of the Query, and push “Finish” in button-right.

Query Name:            Q0010

Description:               BW Query from CDS View

 

(Set query definition)

Query definition is opened. The query is set as below. I will skip the detail of how to create BW Query as is the same as normal BW Query creation. Please read the blog “New Query Designer in Eclipse with SAP BW 7.4 powered by SAP HANA” and watch the movie in it in detail about BW Query creation with BW-MT.

 

“General” tab:

 

“Filter” tab:

In “Fixed Values” in the “Filter: Fixed Values”, P_TOPOSTINGDATE and P_FROMPOSTING DATE are added and variable are set for them. P_TOPOSTINGDATE and P_FROMPOSTING DATE are Parameters in the source CDS View, and the parameters have to be filtered in “Filter: Fixed Values”.

 

“Sheet Definition” tab:

In “Column” Area, 3 measures are added: “Credit Amount in Company Code Currency”, “Debit Amount in Company Code Currency”, “Ending Balance in Company Code Currency”.

In “Rows” Area, “Company Code” and “G/A Account” are added and hierarchy “YCOA” is set in “G/L Account”.

In “Free” Area, “Controlling Area”, “Controlling Area” and “Cost Center” are added.

Now the Query setting is completed. Push “Save” button to save the query.

 

Run “Data Preview”.

Enter the values in the Selection and push “Start Selection”.

 

Result is displayed.

BW Query can be executed with the Transaction RSRT in Backend System as well.

 

This scenario is explained in the Best Practice Explorer “Integration between SAP S/4HANA and SAP BW (‏BGB‏)”.

 

Referential Info:

  • BW-MT has to be used as BEX Query Designer is not supported as of NetWeaver 7.51, although BEX Query Designer works technically. (it is Not in “Technical Release Information” in PAM SAP NETWEAVER >= 7.51). It is possible to open the BW Query created with BW-MT in BEX Query Designer, and vice versa. (I have to confess I sometimes still use BEx Query Designer… )
  • When the version of BW-MT is >=1.18, select the menu “Search for TransientProviders” as below.

 

  • To open the BW Query created on top of CDS View in BW-MT, the only way is to use “Open BW Object” and search with the Query name. It is not displayed under the InfoProvider in BW Repository tree at the moment as CDS View (Transient Provider) is not displayed in the BW Repository Tree in BW-MT.
  • Search with tech name.

 

Value: What are possible with BW Query?

Below is the comparison of the functional availability among 1) Analytic Query of CDS View, 2) BW Query on CDS View and 3) BW Query on BW Provider including BW Provider for BPC for S/4HANA and Open ODS View using CDS View/HANA View as a source.

 

No Function Analytic Query BW Query CDSView BW Query BW Prov Comments
1 Restricted Key Figure

OK

OK

OK

It might not work to filter with hierarchy nodes at the moment.
2 Offset in Variables

OK

OK

3 Calculated Key Figure

OK

OK

OK

4 The 2nd Structure

OK

OK

5 Global Structure

OK

OK

6 Parent/child node for Elements of structure

OK

OK

OK

Annotation @AnalyticsDetails.query.elementHierarchy for Analytic Query.
7 Constant Selection

*

OK

OK

By using Window function in AMDP of Table Function, it is possible also in CDS View, but has to be done on Provider level.
8 Highlight

OK

OK

Highlight doesn’t work for Fiori Multidim Rep.
9 Sort by an attribute desc

OK*

OK

OK

Possible with @AnalyticsDetails.query.sortDirection: #DESC
10 Display Result Rows Only if more than one child

OK

OK

11 Cumulative Value

OK

OK

12 Customer Exit Variable

*

OK

OK

*Lookup entry for Analytic Query can derive variable value, but some complex logics might not be able to be implemented.
13 Local Calculation

OK

OK

14 Condition

OK

OK

15 Exception

OK

OK

Exception doesn’t work for Fiori Multidim Rep.
16 Text Variable

OK

OK

Unavailable when exposed as OData Service
17 Hierarchy

OK

OK

OK

18 Hierarchy Variable

OK

OK

OK

19 Hierarchy Node Variable

OK

OK

OK

20 Exceptional Aggregation

OK*

OK

OK

Limited. only available aggregations are AVG, COUNT, COUNT_DISTINCT, FIRST, LAST, MAX, MIN, NHA, STD, SUM, but more aggregations are available in BW (see SAP Help).
21 Cell Editor

OK

OK

22 Currency/Unit Conversion

OK*

OK*

OK

Unit Conversion based on material not possible / this is also not possible in CDSV case
23

Navigation Attribute

(attributes of master view)

OK*

OK

OK

*In Analytic Query, it is not possible to set navigation attr, but possible to set in the interface view.
24 Formula in Calculated Column

OK*

OK

OK

*@AnalyticsDetails.query.formula is used. Available formulas are limited in Analytic Query.
25 Replacement path Current Member

OK

OK

26 Variable Replaced with a Query

OK*

OK

OK

In CDS View, by using inner join, it is possible to filter with the value of another query.
27 Calculated Attribute

OK

OK

OK*

Calculated Attributes can be created in the Interface View.

* Possible only by using Virtual Characteristic function (complex).

28 Elimination of Internal Business Volume

OK*

As KF InfoObjects for internal Elimination has to be assigned to the target measures, it is only possible for BW Providers.
29 DCL

OK

OK

N/A

30 Analysis Authorization including “:” (Aggregation Authorization)

N/A

N/A

OK

By using Aggregation Authorization, only aggregated figure can be displayed but drill down to detail is not possible.
31 Parameter

OK*

OK*

N/A

For parameter of Analytic Query, only single value can be input. For BW Query, Variable can be used like Parameter.
32 ODdata Generation

OK

OK*

OK*

UI annotation might have to be added in addition.

 

Example:

Query Definition:

In this sample BW Query,

  • Exit Variable is used in Restricted Key Figure “Actual” in which the Current Fiscal Year is derived for filtering automatically.
      • Range value can be derived in one Variable, which is not possible in Parameter in CDS View.
  • Offset is used to filter in Restricted Key Figure “Prev. Year”, in which “Current Fiscal Year – 1” is calculated for filtering automatically. (Not possible in Analytic Query).
  • The Second Structure “Account” is created, and Restricted Characteristic “Revenue”, “Cost” (filtered with G/L Account values) and Calculated Characteristic “GP” (“Revenue – Cost”) are created in the structure. “Cost” is set as Childe Node in it.
      • Revenue:

 

Result:

 

 

Other Scenarios:

  • Constant Selection: By using Constant Selection, the value of the result is displayed in each records. “Amount(C)” and “Quantity(C)” are set to be Constant Selection. By using them, “Amount by Storage Location” can be calculated (“Amount(C)” * “Quantity” / “Quantity (C)” ). 
    • Use Exception Aggregation and Plant/Material is used as Exception Aggregation Element. Note that the runtime would be longer because it is calculating by each Plant/Material.

 

  • FIX Operator: Net Sales > To count the number of sales representatives having net sales greater than the threshold value, Formula: “Net Sales > 0,15 * (Net Sales CS Sales Representative)” is used for each Sales Personal using Exception Aggregation. “Net Sales CS Sales Representative” is Constant Selection Value. The formula has to be “Net Sales > FIX( 0,15 * (Net Sales CS Sales Representative)” as without FIX, Exception Aggregation overwrite Constant Selection so that it would not be calculated as expected.

 

  • Range filtering using Offset: Variable ZV2010 for Fiscal Year is created (Single value). Range filtering is possible from “(the input value in ZV2010) – 2” to “(the input value in ZV2010) ”. Offset is used to set “-2”.

 

  • Current Member: “Previous Year” is a Restricted Measure in which the key figure is filtered on Calmonth with Replacement Path Variable “CURRENT_MEMBER” – 12 (offset)”.
    • Current Member is available for BW Query on BW provider. In BW Query created on CDS View, it is necessary to set is as time attribute with semantics annotation , e.g. “@Semantics.calendar.yearMonth: true”.  If foreign key association is set in the previous view, this annotation might not work. Then it might be needed to use field in the table instead of that in the Time VDM, e.g. use yearmonth in scal_tt_date, instead of YearMonth in I_CalendarDate.
    • Other examples:
      • Rolling window ( e.g. average of last 3 months)
      • Simplified modeling e.g. Year To Date calculations
    • See also Current Member Variables.
  • Elimination of Internal Business Volume: In the query, you use the country hierarchy. The (internal business volume) amount for Europe and the country is eliminated, because the amount of $50 was counted from Germany to the UK.
    • This function is available only in BW Query based on BW Provider. If you want to use it for CDS View, Open ODS View or Composite Provider has to be created and InfoObjects have to be associated. It should not be so difficult for BPC for S/4HANA as InfoObjects for master data have been prepared.

 

Referential Info:

You can check the behaviors of some sophisticated calculations in S/4HANA by activating sample contents with Transaction RSFC.

See also RSFC SAP BW Demo content for BI 7.x.

 

 

Message to BW and the ones who want to create values from S/4HANA

I know you.

I know you have already known the most tangible and promising value of S/4HANA is in short Analytics, whatever conceptual words SAP says like “Intelligent Enterprise” or “Digital Transformation”. After many discussions about new and conceptual words, in practice, many customers turn out to start with Analytical applications. This is because one of the most important value of SAP ERP is just the integrated data in it as is called “Enterprise Resource” Planning. In addition, the two most important innovations in S/4HANA are HANA and Simplified Data model, or Universal Journal. To enjoy those innovations, the simplest ways is Analytics.

I know you have already known although so many people talk about frontend technologies, in reality, business users are far more interested in sophisticated KPI, so more importance and greater challenges should rather be in the backend or data source, so you are concerned there are not so many people in SAP who talk about the data source.

I know you who have contributed to customers and business users by realizing KPIs using BW or some other analytical solutions, so have been concerned missing critical OLAP functions in Analytic Query of CDS View and the complexity of the technologies in S/4HANA to realize that. I hope if this blog could help as BW Query has long history (since 1999) and have included ideas coming from all over the world as functions to realize their requirements, and I know you have already known it.

I write this blog for you, who have contributed to customers with your great experiences of BW and who should now be expected to do the same or more for S/4HANA customers.

I want you.

I want you to make Analytics happening.

I believe business users and customers want you and wait for you to enjoy the great potential values of S/4HANA.

 

Thanks.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Philipp Nell

    Hello,

     

    This is a fantastic blog. Many thanks, Masaakisan, especially for the table with the functional details at the end.

    Kind regards, Philipp

     

    (1) 
  2. Ahmed Riyaz

    Hello @Masaaki Arai,

    We followeed below link to set up analysis authorization.

    https://blogs.sap.com/2017/05/22/cds-view-row-level-authorizations-with-data-control-language-dcl/

    Subsequently used authorization variable on the field setup for analysis authorization which is ready for input. However the field does not get prefilled with authorized values when running the BEx report created on top of the CDS view. However the output is as desired i.e. only authorized data is coming in output.

    The authorization variable on CDS view field does not work to prefill with authorized values ? Or I need to follow different approach to make the authorization variable to show up authorized values ?

    (0) 

Leave a Reply