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 many 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.
- Constant Selection, Current members 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.
- Many BW OLAP functions are available which are impossible or difficult in Analytical Queries of CDS View.
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 (1809~) | 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 | Sign reverse in element | OK | OK | OK | |
| 8 | FIlter with Hierarchy Node | OK | OK | OK | |
| 8 | 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. Constant selection will be available in future release. |
| 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 | Access type; Master data | OK | OK | the lines for the master data value which does not have transaction data is displayed when access type is set to be Master data | |
| 12 | Cumulative Value | OK | OK | in Measure or attribute. | |
| 13 | 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. |
| 14 | Local Calculation | OK | OK | ||
| 15 | Structure name change | OK | OK | Change name is not used in Fiori Design Studio. | |
| 16 | Condition | OK | OK | ||
| 17 | Exception | OK | OK | Exception doesn’t work for Fiori Design Studio. | |
| 18 | Text Variable | OK* | OK | OK | *See Wiki. |
| 19 | Hierarchy | OK | OK | OK | |
| 20 | Hierarchy Variable | OK | OK | OK | |
| 21 | Hierarchy Node Variable | OK | OK | OK | |
| 22 | 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). |
| 23 | Cell Editor | OK | OK | ||
| 24 | Currency/Unit Conversion | OK* | OK* | OK | Unit Conversion based on material not possible / this is also not possible in CDSV case |
| 25 | 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. |
| 26 | Formula in Calculated Column | OK* | OK | OK | *@AnalyticsDetails.query.formula is used. Available formulas are limited in Analytic Query. |
| 27 | Replacement path Current Member | OK | OK | ||
| 28 | 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. |
| 29 | Calculated Attribute | OK | OK | OK* | Calculated Attributes can be created in the Interface View. * Possible only by using Virtual Characteristic function (complex). |
| 30 | 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. | ||
| 31 | DCL | OK | OK | N/A | |
| 32 | 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. |
| 33 | 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. |
| 34 | 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. (possible also in Analytic Query as of 1809)
- Revenue:
- 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.
- See also Fix Operator.
- 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.
- SAP Managed Tags:
33 Comments
