BW Query on CDS View, OData from BW and value of B...

Masaaki · ‎08-08-2018

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.

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:

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.

edwin_ryan2 · ‎08-09-2018

Great blog, thanks Masaaki.

Ed

douglasmaltby · ‎08-11-2018

Excellent blog, Masaaki! Thanks for sharing your insight!

PhilMad · ‎08-28-2018

Hello,

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

Kind regards, Philipp

former_member187192 · ‎10-03-2018

Thanks a lot Masaaki, for sharing your experience.

Excellent blog for all the BW folks out there.

Regards,

Mayank

former_member574282 · ‎10-16-2018

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 ?

former_member542544 · ‎08-07-2019

Hello Masaaki,

I do not see "Search for TransientProvider" checkbox while creating a new BEx query in eclipse BWMT. I see "Show master data objects" checkbox instead. Is there a setting that I am missing or do I need to get a different BWMT version? Please help.

Thanks for your help,

Krishna

Masaaki · ‎08-08-2019

Hi Julie,

Pls check if "Referential Info:" could help. in the older ADT version, the menu was in the context menu.

Thanks, Masa

former_member216276 · ‎09-04-2019

Hi Masaaki,

Again another great blog but just one issue we are facing is assigning our Eclipse query (built on /ERP/SFIN_M01) we can not assign it to a role. We have created a custom role via PFCG which is not available to us via Eclipse also the roles that are in the list can not be assigned as the save button is greyed out. Could you help or point us int the right direction as to what we may be missing?

Many Thanks,

Bal.

Masaaki · ‎09-08-2019

HI Bal,

Sorry, I can not understand the issue.

What do you want to do?

if you want to use the query in FIori launchpad, see below.

https://blogs.sap.com/2018/07/11/how-to-create-custom-fiori-multidimensional-reporting-application-i...

regards, Masa

AlexeyLantsman · ‎12-01-2019

One of the best blogs I've ever seen! Thank you, Masaaki!

Best Regards, Alexey

SudhanshuSurma · ‎06-16-2020

Great blog indeed, thanks Masaaki!

former_member40251 · ‎06-16-2020

Hi Masaaki!

I need to create an app (KPI), like the "Procurement Overview Page" you showed in this post, with different charts in the same page.

I created a CDS based on I_ActualPlanJrnlEntryItemCube, with @odata.publish = true, with the key fields, categories and dimensions I wanted. Created the service, service binding, activated, and I tried to create a smart report with app "Manage KPI and reports" but in this app I'm not able to build anything like this one you mentioned, only one single chart with some drill down options. I'm talking about S4Hana 1909 On Premise.

Do you have an example of creating smart multidimensional reports using KPIs for a standard (or extended) CDS view?

Kind regards

Mayumi

Masaaki · ‎06-17-2020

Hi Mayumi,

Only KPI app, including one single chart or view or one single chart, and "Report" could be created with Manage KPIs and Reports.

if you want to create Overview Page, you have to use Web IDE in SAPCP, but I am afraid it is not easy.

sorry, does "smart multidimensional reports" mean OVP?

hope if my blog below would help.

https://blogs.sap.com/2019/06/08/analytics-in-s4hana-real-shape-of-embedded-analytics-and-beyond-emb...

Thanks, Masa

baljinder_nagra · ‎12-20-2020

Hi Masa - I am trying my best to leverage S/4 cds modelling for analytics - but I keep getting pushed over to use BW query to solve some problems. I have evolving issue and with each CDS based solution I come up with I get a new one....sequenced below.

Restricted measures (or characteristic structure elements) - using hierarchy node filter. I need to make filters at query level on a Profit Center hierarchy node value within a restricted structure element - but it doesn't seem possible. Is that true?

Due to inability to restrict on hierarchy node - I created a amdp based CDS view to flatten the hierarchy into a column based "level" hierarchy. This way I can use columns to filter the hierarchy nodes. But now I have a new problem - my level hierarchy is time-dependent [0..*]. I cannot add fields from this association to my cds query - they only exist as "display attributes". Is there a concept of navigation attribute in CDS query - specifically in regards to time-dependent navigation attributes?
- Now I'm thinking to add a key date parameter to my cds cube (which will take me away from S/4 hana standard VDM) and apply key date parameter to the association so I can turn it to a [0..1] cardinality. That way I can add fields explicitly from the association to my cube and have access to it in the query for filters. But I heard parameters defined on cube cds is not recommended (Basic Rules: 2551243 CDS modeling considerations in BW) due to performance.

Should I just go for a BW query solution here and avoid the hassle? Appreciate any insights!

Masaaki · ‎12-20-2020

Hi Baljinder,

Yes, as a consultant, I think now is the time to go for BW Query.

it seems the SAP DEV team has already been aware of the missing functions not in Analytic Query but in BW Query, including the issue you are facing, it would take some more time for enhancing Analytic Query.

So, for the time being, BW Query can (only) be the workaround.

This is the main motivation why I wrote this blog. BW experts would realize they could not realize typical reporting requirements only within Analytic Query function but intermediate solution should be mandatory to make analytics happen.

Thanks, Masa

former_member675481 · ‎04-16-2021

Hi Masa,

Really great blogs. Thanks.

You mention a lot benefit of BW query over CDS query. Is there any drawbacks of using BW query? Should we just start to using BW query instead of CDS query in the S/4 landscape? I don't see any SAP standard development using the BW query.

Just curious.

Thanks

Hansen Chen

Masaaki · ‎04-17-2021

Hi Chen,

BW query on top of CDS view can be created technically, and is supported. (a customer got the confirmation in customer incident). This scenario is not the direction of SAP DEV team.

I wrote this blog as I know customers would face the difficulties for missing functions in analytic query of CDS View, but which can be compensated with BW query.

Now the PM team are aware of the missing functions so is now enhancing. So, now or in the future, this scenario might no longer been needed.

Thanks, Masa

former_member675481 · ‎04-17-2021

Thanks Masa for the response. Read all your blogs multiple times. They are all very informative and thorough. They are great help for people starting on the embedded analytics. Hope you can wrote more in the future.

One question. Do SAP have any direction on merging the CDS side and BW side? Now most of the analytics are happening on the CDS side. But there are still a couple modules using the embedded BW planning function(like BPC and CPM). I saw SAP deliver some standard virtual cube in those scenario. It is build on top of multiple layer of CDS. And then map to a calculation view on HANA. Then finally map to a virtual provider. The virtual provider can finally be joined with those real time planning cube to do analytics. It is not an elegant solution. More like a work around. It lose the connection on the content deliver on the CDS side. Not sure whether there is a better way of doing this.

Masaaki · ‎04-18-2021

Hello Hansen,

no plan to replacing BPC contents to CDS View based, as the focus is on SAC planning in SAP DEV side, although I personally think CDS View based BPC contents is expected.

Regards, Masa

thomas_quaebicker · ‎04-19-2021

Hi Masaaki,

will it be possible in the future, to create by FIORI based "Query Designer" a report with 2 structures like we are used to use in BEx Query Designer?

My customers are using the same method in Report Painter on ECC and they are now looking for a follower solution. Currently they are evaluating BEx Query Designer based on BW but this will have the disadvantage that reports will not be real time. Currently I would recommend to use BEx Query Designer on top of CDS-View.

Kind regards,

Thomas

Masaaki · ‎04-21-2021

Hello Thomas,

it is possible to create the 2nd structure in Custom Analytical Queries, and CDS View created with ADT.

Pls refere to the blog below.

https://blogs.sap.com/2020/09/24/analytics-on-universal-journal-the-heart-of-sap-s-4hana/#Sales/Gros...

Thanks, Masa

anupkulkarni · ‎07-01-2021

Hi Masa,

I have been following this blog since 2018, great to hear about SAP PM team being aware and looking into the problem. Is there any strategic plan to retain UI of query editor in ADT for CDS query? I can't imagine how we can handle complex maze of queries with two structures, cell editor, reusable CKF/RKF etc in a text editor.

Another problem with BW query in embedded analytics is that the field names are not same as semantic objects in S/4. This prevents us from using intent based navigation or jump targets. Is there any work happening in this area? Based on your comments, I sense that SAP is going in the direction of improving CDS query than switching over BW query. Then the UI part plays a key role, without that CDS query would be very difficult to create and maintain.

Thanks,

Anup

Masaaki · ‎10-07-2021

Hello Anup,

sorry for answering late.

I dont hear about the Query Designer in ADT, but Fiori Custom Analytical Queries is evolving.

Regarding the field mapping when jump to the app, in my test, the characteristic value of BW query on CDS View works when jumping to CDS View based app.

In addition, it it is possible to change the tech field name in WD Grid with BADI. See more in the blog. And the mapping between InfoObject name for BPC Optimized and CDS View field name has been included by default.

https://blogs.sap.com/2021/07/12/how-to-enable-intent-based-navigation-for-reporting-with-web-dynpro...

Thanks, Masa

ABner · ‎10-26-2021

Great blog and approach to using BW Query on CDS. My question is, for CDS views that are not tagged with the #Analytics, they won't show up as transient providers in BW-MT (e.g. "CDS_PO_ITEMS").

What is the best practice for these views? Copy them into a ZCDSxxxxx and add the tag? Or is there a cleaner way to "refer" to them so that they can be used in BW Query?

Masaaki · ‎10-26-2021

Hello Aaron,

Yes, the CDS View has to be set as CUBE or DIMENSION (@analytics.datacategory: #CUBE, or #DIMENSION).

https://wiki.scn.sap.com/wiki/display/BI/CDS+Views%3A+Modeling+considerations+in+Analytic+Engine

regards, Masa

pierre_cohen_sopra · ‎01-21-2022

Hi Masa,

Thanks a lot for all your posts that help us to set up best practices embedded analytics solutions for our customers. I have what sounds a very simple problem : I would like to calculate a rate with average as exceptionnal agregation behavior but by excluding zeroes. With CDS analytics query, average exluding 0 value is not possible. Do you think the good way to do that is to go through a BW query on top of my CDS cube ?

Thanks,

Pierre

former_member829367 · ‎11-16-2022

Hi masa,

Thanks for the blog. I have a requirement of read data into S/4 ABAP from BW query ( which is EMBEDDED HANA). what are the ways to fetch data from BW query to abap please suggest. One of my BW consultant created Odata enable for this BW query but i am unable to consume that Odata to ABAP. this SAP Note:2807978 is not working for me. Raised OSS notes but they are also unable to provide proper solution. Can Anyone please help me on

Thanks - RK

Masaaki · ‎11-16-2022

Hi RK,

Ideas coming to my mind are

Generate HANA External View and read it via AMDP.

Store data in table using APD

OLAP BAPI
1. https://help.sap.com/docs/SAP_S4HANA_ON-PREMISE/64e2cdef95134a2b8870ccfa29cbedc3/649b8f3c855f0454e10...

read data from the source.

But I have never used OLAP BAPI... so personally, 4 is the best and 1 is the second.

Are there some reasons to read data from BW Query, not the source?

regards, Masa

former_member829367 · ‎11-16-2022

Thanks Masa,

Yes, actually the source is a infocube :/CPD/PFP_R01 bur it is storing only L5 wbs element and its values. We need to get aggregation of values(margin,cost ..etc..) from l2,l3,l4,l5 WBS elements, that is served by SAP standard functionality ,so he created query:/CPD/PFP_R01_Q0031.

OLAP BAPI link seems for 4.6 version and based on Bex.

i need those aggregate values to ABAP and will do further calculation and later send it to CPI.

Thanks - RK

zaur_ibrahimov · ‎05-22-2023

Hi, makoon I have created a report using the Manage KPIs and Reports tile. Its analytical list Page. I have created 2 reports. One was from a standard OData service user in Monitor Purchase Order Items (MM_PUR_POITEMS_MONI_SRV) and the second one was created from custom CDS.

But now when I enter applications I can't press on the document number to navigate to another app. Document number material and other fields are gray. I also have other roles assigned to my user so they must become blue and clickable. Why is not working? Is it possible to add? Any help would be appreciated.

Martin_4 · ‎08-11-2023

Hi Masa,

any suggestion for this Community Question:

Publish a Eclipse Query Designer- based CDS Query View into Fiori Query Browser

and makes the Eclipse Query Design approach for S4 perfect.

Best Martin

MarkSargent · ‎01-29-2024

Hi @Masaaki,

Firstly, fantastic blog!

I have a question regarding performance and cache. It appears from my analysis that BW queries on transient providers only use the local cache and not the DB cache.

Do you have any experience in this area? Can you advise regarding cache?

It also appears that CDS view-based analytical queries (i.e. those coded and not built in the query designer) also don't use the DB cache.

I'd love to hear your thoughts on this.

Thanks,

Mark

Masaaki · ‎01-29-2024

Hello Mark,

OLAP cache is not available for transient provider. in case of CDS View, Dynamic or static cache should be used instead. it was the answer when I asked to the dev team.

regards, Masa