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.
- 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.
- 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.
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-MTand 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.
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)”.
- 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||BWQuery BW Prov||Comments|
|1||Restricted Key Figure|
|It might not work to filter with hierarchy nodes at the moment.|
|2||Offset in Variables||OK|
|3||Calculated Key Figure|
|4||The 2nd Structure|
|6||Parent/child node for Elements of structure|
|Annotation@AnalyticsDetails.query.elementHierarchy for Analytic Query.|
|7||Sign reverse in element|
|8||FIlter with Hierarchy Node|
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.
|Highlight doesn’t work for Fiori Multidim Rep.|
|9||Sort by an attribute desc|
|Possible with @AnalyticsDetails.query.sortDirection: #DESC|
|10||Display Result Rows Only if more than one child||OK|
|11||Access type; Master data||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|
|in Measure or attribute.|
|13||Customer Exit Variable|
|*Lookup entry for Analytic Query can derive variable value, but some complex logics might not be able to be implemented.|
|15||Structure name change||OK|
|Change name is not used in Fiori Design Studio.|
|Exception doesn’t work for Fiori Design Studio.|
|Unavailable when exposed as OData Service|
|21||Hierarchy Node Variable|
|Limited. only available aggregations areAVG, COUNT, COUNT_DISTINCT, FIRST, LAST, MAX, MIN, NHA, STD, SUM, but more aggregations are available in BW (see SAP Help).|
|Unit Conversion based on material not possible / this is also not possible in CDSV case|
(attributes of master view)
|*In Analytic Query, it is not possible to set navigation attr, but possible to set in the interface view.|
|26||Formula in Calculated Column|
|*@AnalyticsDetails.query.formula is used. Available formulas are limited in Analytic Query.|
|27||Replacement path Current Member||OK|
|28||Variable Replaced with a Query|
|In CDS View, by using inner join, it is possible to filter with the value of another query.|
Calculated Attributes can be created in the Interface View.
* Possible only by using Virtual Characteristic function (complex).
|30||Elimination of Internal Business Volume|
|As KF InfoObjects for internal Elimination has to be assigned to the target measures, it is only possible for BW Providers.|
|32||Analysis Authorization including “:” (Aggregation Authorization)|
|By using Aggregation Authorization, only aggregated figure can be displayed but drill down to detail is not possible.|
|For parameter of Analytic Query, only single value can be input. For BW Query, Variable can be used like Parameter.|
|UI annotation might have to be added in addition.|
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)
- 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. useyearmonth inscal_tt_date, instead ofYearMonth inI_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.
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.
- Notice the 2C at the starting. This is a Transient provider. Copy the BEX Query tech name and logon to SAP S/4HANA, and go to RSRT T code.
- In RSRT, once you give the query tech name (and hit enter), click on Technical Information:
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.Can we use CDS view in composite provider? ›
Data from S/4HANA CDS views can be enriched with BW master data. By using Composite provider, you can combine transactional data from S/4HANA with historical and plan data in BW/4 HANA. The prerequisite for this scenario is HANA Smart Data access is configured in BW/4HANA system and connects S/4HANA system.What is CDS view in SAP BW on HANA? ›
CDS Views in SAP HANA Overview and Tutorial
We will also learn how to expose CDS Views in SAP HANA as an SAP OData Service to replicate data to the Cloud. There are various protocols to access and extract data from SAP applications for analytics. This is one method using SAP CDS Views on SAP HANA via the ODP framework.
Select the project and open it. Open the project in the SEGW navigator on the left. Open in the project the folders Data Model > Data Source References > CDS-Entity Exposures > Entity Types. Here you see the names of the ABAP CDS views associated with this OData service.How do you expose CDS view to SAC? ›
Sap Analytics Cloud Load Data from CDS View from S/4 HANA - YouTubeWhat are different types of CDS view in SAP HANA? ›
CDS views exist in 2 flavours, namely, ABAP CDS views and HANA CDS views.Where are standard CDS views on s4hana? ›
- While in the ABAP perspective, press the keys CTRL+SHIFT+A simultaneously to bring up the development object search box.
- Enter the pattern you want to search for in the system (for instance, so search all CDS views beginning with a “Z”, enter the pattern “Z*”
They provide enhancements in terms of data modelling and enable improved performance when combined with SAP HANA database. ABAP CDS Views allow developers to create semantically rich data models which the application services expose to UI clients.How do you display data in composite provider? ›
Navigate to InfoArea under BW Project → Context menu → New → Composite Provider. Enter the Composite Provider name and description. Click Finish. General − Shows the properties of Composite Provider.
- Step 1: Create a calculation view in HANA.
- Step 2: Create input parameters based out of Calendar day. ...
- Step 3: Create an expression as shown below. ...
- Step 4: Create a Composite Provider (HCPR) on top of this HANA view and consume the input parameters as well.
ABAP CDS views are database independent whereas HANA CDS views are database dependent. The CDS objects created using HANA CDS is not controlled by ABAP dictionary and hence cannot be consumed in ABAP Programs or Open SQL. HANA CDS views aim to support the development of native SAP HANA applications.How do you make CD views on s4hana? ›
- Start the SAP HANA studio.
- Open the SAP HANA Development perspective.
- Open the Project Explorer view.
- Create the CDS-definition file which will contain the view you define in the following steps. ...
- Define the underlying CDS entities and structured types. ...
- Define a view as a projection of a CDS entity.
SQL-Fetched Data Gets Processed at the Database Level
In the case of ABAP CDS views, the views reside at the application level in the Data Dictionary (SE11), but a CDS view can pass more logic than an ABAP dictionary view from the application server to the database through SQL.
- In your package, create a CDS view entity. Select the package, then choose New > Other from the context menu, then choose Data Definition. Add the following: ...
- Choose or create a transport request, then choose Next. Do not choose Finish.
- Choose Use template then choose Define View Entity.
- Finally, choose Finish.
- In your package, create a CDS view entity. Select the package, then choose New > Other from the context menu, then choose Data Definition.
- Add the following: Name: Z_I_TRAVEL_R_XXX. ...
- Choose or create a transport request, then choose Next. ...
- Choose Use template then choose Define View Entity.
- Finally, choose Finish.
A projection view is a direct project on exactly one CDS DDIC-based view or CDS view entity and exposes a subset of its elements. The underlying CDS entity used in the projection view is called a projected entity. The projected entity can be a CDS view without parameters.How do you track the data records once executed the CDS view in Eclipse environment? ›
The SQL Editor can be found under “Diagnostics” folder. Copy and paste the SQL statement with variables. Press “Execution Trace” button. Fill in the values for the variables and execute.How consume CDS view in SAP HANA? ›
- Open Eclipse/ HANA Studio.
- Select system and expand content folder and choose package where calculation view needs to be created.
- Right Click and Create New Calculation View.
- Add required tables/ views and select columns as per logic. ...
- Now save and activate the view.
- Go to (Main Menu) Connection Connections (Add Connection) ...
- Expand Connect to Live Data and select SAP S/4HANA.
- In the dialog, enter a name and description for your connection. ...
- Set the connection type to Direct.
- Add your SAP S/4HANA host name, HTTPS port, and Client.
Annotations for which a different component is entered in the "Framework" column are component annotations. The component defines technical and semantic attributes of CDS objects required by specific software components, such as ODATA, UI, and Analytics.What are the limitations of CDS view? ›
The first limitation is you cannot debug Core Data Service views. The second limitation is that the Core Data Service view cannot return more than one result set as output. The third limitation of Core Data Service view cannot join two fields with data type and length mismatches in two tables.How CDS views are consumed? ›
They usually combine data from different sources. They are reused by other composite views and consumed by consumption views. Analytical CDS views like fact sheets, cubes, and dimensions are composite interface views.What is the difference between join and association in CDS view? ›
Associations are a different type of gathering data toghether from multiple tables. We call them Join-on-demand. This means that the data in the joined (associated) tables is not accessible directly at run time, but only on demand. It is triggered only when the user needs that data.Which reads the data from CDS views? ›
S/4 HANA – View Browser
The View Browser is a tool used to explore ABAP Core Data Services(CDS) views supplied by SAP and custom CDS views created by customers.
SAP ABAP- How to find Custom CDS View in SAP? - YouTubeHow do you take CDS view in Fiori? ›
Finally Create a CDS View(Consumption) on the transaction view. Publish the oData for this consumption view as in next in our Fiori App we will refer to this view. Make search enable. Let's go to the webIDE ( prerequisite- the cloud connector must be established to teh backend system to consume the oData Service ).How use CDS view with parameters in ABAP program? ›
demo=>main( ). SELECT is used to access a CDS view with parameters as a data source. Actual parameters are assigned to the input parameters of the view. The values of these actual parameters can be defined by input.What is the difference between CDS views and AMDP? ›
CDS VS AMDP :
CDS views can be created to read and process data at DB layer. Whereas AMDP can be created to process and modify data at DB layer. AMDP is used to work with stored procedures, which further go to HANA DB layer and execute that. This functionality can't be achieved by Open SQL and CDS.
When you create Information views in SAP HANA, data from BW data is published. These SAP HANA views point directly to data and tables that are managed by the BW. The data from SAP BW can be directly consumed in HANA Modeling views. You can generate SAP HANA views for queries as InfoProviders in HANA database.
In Composite Provider screen, enter the name of the Composite Provider (up to 10 characters) and click the Create button. The graphical modeling environment appears. You can drag and drop the required InfoProviders (from the InfoProvider tree on the left of the screen) onto the modeling area.Can we use calculation view in composite provider? ›
On the General tab you can configure settings of your CompositeProvider. You can decide if you want to create an External SAP HANA View, to use your CompositeProvider with a Calculation View for example. You can also define if your CompositeProvider can be added to another CompositeProvider.How do I convert Bex query to HANA view? ›
To migrate Bex queries to HANA views, generate HANA views from the underlying infoProviders of the Bex query and use them as base layer views to construct reporting layer HANA views based on these generated views.What is the difference between Multiprovider and composite provider? ›
The multiproivder seems to perform select from all the individual infoproviders parallelly whereas the composite provider seems to cluster these 20 infoproviders into 4 parallel processes as shown in the access counter field.Can we use Composite provider in composite provider? ›
4) A composite provider that is enabled with “This Composite provider can be added to another composite provider” having Binding Type Union, cannot be used as a right part provider in new composite provider.What is the difference between CDS view and calculation view? ›
The first difference lies in the architecture behind the two technologies. On the one hand with Calculation Views, the views are objects belonging to the SAP HANA database layer and not to the application layer. CDS Views on the other hand belong to the ERP application layer (ABAP layer).Is CDS view database dependent? ›
ABAP CDS views are database independent whereas HANA CDS views are database dependent. The CDS objects created using HANA CDS is not controlled by ABAP dictionary and hence cannot be consumed in ABAP Programs or Open SQL.Can we use function module in CDS view? ›
As we all know that 'READ_TEXT' is a function module and cannot be used in CDS Views. Only Open SQL with certain capabilities can be used in CDS Views.How will you create an OData service for ABAP CDS view using mapping? ›
- Create the CDS view: Create the CDS view from Eclipse. Right click on the package icon, and from the context menu select New->other ABAP repository object. ...
- Create Gateway project: Create the DDIC structure to map fields of a CDS view to your custom model. ...
- Test from Gateway client:
ABAP CDS - cast_expr - ABAP Keyword Documentation. Casting in a SELECT statement of a CDS view. The cast expression converts the value of the operand operand to the dictionary type specified by dtype. The result has the type dtype.
INTRODUCTION: ABAP Core Data Service Views also known as ABAP CDS Views. There are so many limitations in SE11 views but we can come out of all the limitations and create a powerful views with the help of ABAP CDS Views. In general SE11 views and ABAP CDS Views both will create a database views at the backend.How do I create a basic CDS view in HANA studio? ›
- Right click on the package. ...
- Choose DDL Source under Dictionary objects node. ...
- Enter the view name and a description for your new DDL SQL view. ...
- As expected since I'm creating my first CDS view in local ABAP package, $TMP no change recording is enabled for package $TMP.