- BEx Analyzer
- Virtual Data Models
- From a HANA Model to an OData Service
- Use CDS Annotations for OData Service
- OData Client Call
BEx Analyzer is an analytical, reporting and design tool embedded in Microsoft Excel. In BEx Analyzer, you can analyze and plan with selected Info Provider data using the context menu or drag and drop to navigate in queries created in BEx Query Designer. You can design the interfaces for your queries by inserting design items (controls) such as analysis grids, dropdown boxes and buttons into your Excel workbook. This allows you to transform your workbook into a query application.
BICS means BI Consumer Services and is a SDK developed and used by SAP.
Virtual Data Models
A virtual data model (VDM) is a structured representation of HANA database views used in SAP HANA Live for SAP Business Suite and follows consistent modeling rules. It provides direct access to SAP business data using standard SQL or OData requests. Business data is exposed through well-defined database views, operating directly on the tables of the SAP Business Suite systems and transforming them into consistent and easily understandable views. These views can be consumed directly by rich client UIs (such as HTML5, SAP BusinessObjects, and Excel) without any additional software layer (an ABAP application system, for example) in between (two-tier architecture).
VDM View types:
SAP HANA Live
SAP HANA Live (previously known as SHAF – SAP HANA Analytic Foundation) is solution for real-time reporting on HANA. It is a separate package that comes with predefined SAP HANA content across the SAP Business Suite.
SAP HANA Live provides SAP-delivered content (similar in concept like SAP BW content), in form of SAP HANA calculation views for real-time operational reporting. The calculation views spans across majority of ECC modules (FI, CO, MM, PP, SD, PS, CRM, GTS, AM and GRC).
The content is represented as a VDM - virtual data model, which is based on the transactional and master data tables of the SAP Business Suite.
Currently more than 2000 views are delivered in HANA Live Package.
Architecture of HANA Live
HANA Live calculation views are designed on top of SAP Business Suite tables. These views are optimized for best performance and analytic purposes. These views form a Virtual Data Model (VDM) that customers and partners can reuse. Data provided by the virtual data model can be presented through multi-purpose analytical UIs, such as SAP BusinessObjects BI Suite UIs, and domain-specific web applications.
From a HANA Model to an OData Service
The data must be transferred through four different object layers:
- HANA View ( Analytical or Calculation or CDS )
- Transient InfoProvider
- Easy Query
- OData Service
These are the steps to get data from HANA views via an OData Service:
Enable CDS View for Analytic Manager
Analytics Annotations enable the analytic manager for multidimensional data consumption, performing data aggregation, and slicing and dicing data. BI frontends like Design Studio and Analysis Office can consume the data via the analytic manager.
- #DIMENSION master data
- #FACT transactional data (center of star schema) without redundancy
- #CUBE factual data used for queries
- #AGGREGATIONLEVEL a projection of cube view
VDM Annotations allow classifying views of the virtual data model in terms of their admissible reuse options and provisioned content. VDM is intended to be interpreted by view browsers and other functionality which is based on the virtual data model. This classification is used only for SAP internal structuring and interpretation of the CDS views. Releasing CDS views for customers and partners is controlled by additional internal classification of the views.
- #BASIC core data basis without data redundancies
- #COMPOSITE data derived and/or composed from the BASIC views
- #CONSUMPTION based upon public interface (for example, BASIC and COMPOSITE) views
- true technical helper views which may only be used by their defining responsibles
Create Basic View
Create two basic CDS views, set the views as VDM basic view type by
- view for contacts of digital account
@EndUserText.label: 'Contacts of Digital Account'
define view ZI_Mkt_DigAcc as select from cuand_da_root
association [0..*] to cuand_ce_mp_root as _MarketingPermission
on cuand_da_root.comm_cat_key = _MarketingPermission.comm_cat_key
@EndUserText.label: 'Digital Account'
key cuand_da_root.digacc_id as DigitalAccount,
@EndUserText.label: 'Communicate Category'
cuand_da_root.comm_cat_key as CommCatKey,
@EndUserText.label: 'Marketing Area'
cuand_da_root.mkt_area_id as MarketingArea,
_MarketingPermission.contact_key as Contact
- view for contact interactions
define view ZI_Mkt_Interaction as select from cuand_ce_ic_root
association [0..*] to cuand_ce_ia_rt as _interaction
on $projection.Contact = _interaction.contact_key
key cuand_ce_ic_root.db_key as Contact,
_interaction.db_key as Interaction,
_interaction // Make association public
Create Cube View
Create a view for analytic manager using annotation
@Analytics.dataCategory: #CUBE and set the VDM view type as #COMPOSITE
@EndUserText.label: 'Cube for Contacts of Digital Account'
define view ZI_Mkt_DigAcc_C as select from ZI_Mkt_DigAcc as digitalAccount
association  to ZI_Mkt_Interaction as _interaction
on $projection.Contact = _interaction.Contact
count( distinct _interaction.Interaction ) as Interactions,
_interaction // Make association public
The cube view will be published as a transient provider for the analytic manager.
Preview Transient Info Provider
Start TX RSRTS_ODP_DIS ( Display Transient InfoProvider ) to select a SAP HANA View.
Start Query Designer to create a new query with a special type, named EasyQuery (EQ).
Create BExQuery ( EQ )
Create a new Query based on the Transient InfoProvider of ZIMKT_DIGACC_C.
All characteristics that shall be included in the EasyQuery have to be in Rows. Add Dimensions and Key Figures to the Query Columns. In the extended query properties choose “Lightweight consumption by Easy Query”. Currently please do not use “By OData”. When saving the query, write it to a transportable package and include it in a transport request.
Check if Easy Query is successful generated. Start transaction EQManager in BW/ODP client( e.g. ANA(333)). The query should appear in the list. A generated function module /BIC/NF_xx indicates indicates a sucessful generation. Field Easy Query and FUnction Module must be filled. If not, try to regenerate the Easy Query with the appropriate button.
It is required to have sufficient authorization and appropriate system settings to create/modify objects in packages $TMP and $EQ_GEN.
Or check it by transaction RSRT Query Monitor
Include Query in BI Content Bundle (TBD)
The BEx query will not be activated automatically in test or customer landscapes. The content that shall be activated in test or customer system needs to be included in a BI content bundle. These bundles have to be activated manually in test systems after transport of the query. They will be activated in customer systems automatically when running the technical configuration. To include a query to a BI content bundle, call transaction BSANLY_BI_ACTIVATION in the development system. Choose a proper bundle and edit it. Add the query below. To do that, choose object type = Query element, Object Technical Name = Query Enterprise ID (obtained from extended properties in Query Designer), Grouping = Only neccessary Objects). Save. Press Check Consitency, The query description should apear.
Generate OData Service
Generate an analytical OData Service: Use Transaction /IWBEP/ANA_SRV_GEN:
- Access Type: Controller for Easy Queries (SAP BW)
- SAP BW: RFC Destination: NONE
- Query Name: Select the Easy Query
- Target Package: your package
- Model Provider Class:
- Data Provider Class:
- Configure Gateway Model and Service: X
- Model Name and Version:
- Service Name and Version:
Check the generated gateway service using transaction /IWBEP/REG_SERVICE, you can also find the entity set external name (e.g. ZQ_DIGACC_CONTACTSResults) in the Configuration menu.
Activate Gateway Service
Start transaction /IWFND/MAINT_SERVICE to add the gateway service in the service catalog, ensure the ICF nodes for OData and system aliases configuration are ok.
Include Service in Project Data Model
Start transaction SEGW, open a project (or create a new one). Under “Data Model” choose include or redefine “OData Service (SAP GW)”. Click next, set Technical Service Name. Click on next and Finish the wizard.
The fieldnames in the generated service look like GUIDs. in order to expose the service with nicer field names (e.g. for consumption in Lumira), beautifaction of the fieldnames can be done. In a next step you can redefine the attribute names of the entity to give thema a speaking name. Select the attribute in your entity type, press redefine and type a new property name:
Generate the service and select option Overwrite Base/Extended Service.
Save the service. The usual steps for OData services apply: Activation, transport, PFCG role assignment, including in TC is like for the standard OData services.
Queries may not be changed in an incompatible way. Thay means, existing fields from already delivered queries may not be deleted.
Fields from delivered OData services my not be removed or renamed, as this would also be an imcompatible change
May be get an error from accessing the OData service when you includes the gateway service in exist gateway project, then you can click the button ‘Load Metadata’ in transaction /IWFND/MAINT_SERVICE to refresh the metadata of the OData service.
Use CDS Annotations for OData Service
Use another approach to expose CDS view as an OData service.
Create CDS View for Analytics Query
Create a CDS View used for Analytics Query by annotation
@Analytics.query: true, which select data from Cube CDS view. And add annotation
@OData.publish: true onto the view to publish it to an OData service.
@EndUserText.label: 'Query view of Digital Account'
define view ZC_Mkt_DigAcc_Q as select from ZI_Mkt_DigAcc_C
The Query can be check in transaction RSRT (format
2C<CUBE_SQL_VIEW>/2C<QUERY_SQL_VIEW>), the result is similar to ‘Easy Query’.
And you can also check the generated OData technical service named
<QUERY_VIEW>_CDS in transaction /IWBEP/REG_SERVICE.
Activate the Service
Activate the service in transaction /IWFND/MAINT_SERVICE manually. Now, you can access the OData service
OData Client Call
When you call the OData service in client, you can filter data by
$filter=dimension1 eq 'value1'
or/and select dimensions and key figures by
https:///sap/opu/odata/sap/ZQ_DIGACC_CONTACTS_SRV/ZQ_DIGACC_CONTACTSResults?$filter=A2CKQH731XOYIUNQD11RXSEL87ON eq ‘@uap4797x’&$select=A2CKQH731XOYIUNQD11RXSEL87ON,A2CNTOY6TG213INT1FU3OEG9467Y,A2CZIMKT_DIGACC_CCONTACT,A00O2SOLU7ADPK7EF25PQKBUOL_F