• BEx Analyzer
• Virtual Data Models
  • SAP HANA Live
    • Architecture of HANA Live
• From a HANA Model to an OData Service
  • Enable CDS View for Analytic Manager
    • Create Basic View
    • Create Cube View
  • Preview Transient Info Provider
  • Create BExQuery ( EQ )
  • Include Query in BI Content Bundle (TBD)
  • Generate OData Service
    • Activate Gateway Service
    • Include Service in Project Data Model
• Use CDS Annotations for OData Service
  • Create CDS View for Analytics Query
  • Activate the Service
• OData Client Call

BEx Analyzer

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:

• Interface(I)
• Consumption(C)
• Private(P)

See also:

• Virtual Data Models in 2016
• SAP HANA Live & S/4HANA Embedded Analytics

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:

1. HANA View ( Analytical or Calculation or CDS )
2. Transient InfoProvider
3. Easy Query
4. 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.

@Analytics.dataCategory:

• #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.

@VDM.viewType:

• #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

@VDM.private:

• 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 @VDM.viewType: #BASIC.

• view for contacts of digital account

@AbapCatalog.sqlViewName: 'ZIMKT_DIGACC'
@VDM.viewType: #BASIC
@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,
    @EndUserText.label: 'Contact'
    _MarketingPermission.contact_key as Contact
}

• view for contact interactions

@AbapCatalog.sqlViewName: 'ZIMKT_IA'
@VDM.viewType: #BASIC
@EndUserText.label: 'Interaction'
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
{
  @EndUserText.label: 'Contact'
  key cuand_ce_ic_root.db_key as Contact,
  @EndUserText.label: 'Interaction'
  _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

@AbapCatalog.sqlViewName: 'ZIMKT_DIGACC_C'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
@EndUserText.label: 'Cube for Contacts of Digital Account'
define view ZI_Mkt_DigAcc_C as select from ZI_Mkt_DigAcc as digitalAccount
association [1] to ZI_Mkt_Interaction as _interaction
  on $projection.Contact = _interaction.Contact
{
  key digitalAccount.DigitalAccount,
  key digitalAccount.MarketingArea,
  @EndUserText.label: 'Contact'
  key digitalAccount.Contact,
  @EndUserText.label: 'Interactions'
  @DefaultAggregation: #SUM
  count( distinct _interaction.Interaction ) as Interactions,  
  _interaction // Make association public
}
group by
  digitalAccount.DigitalAccount,
  digitalAccount.MarketingArea,
  digitalAccount.Contact

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.

The details of Transient Provider:

Goto->Standard Query

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

See also: Introduction To BEX Query Designer and Query Elements

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: CL_<provider name>_MPC
• Data Provider Class: CL_<provider name>_DPC
• Configure Gateway Model and Service: X
• Model Name and Version: <EQ name>_MODEL
• Service Name and Version: <EQ name>_SRV

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.

@AbapCatalog.sqlViewName: 'ZMKT_DIGACC_Q'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Query view of Digital Account'
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@OData.publish: true
define view ZC_Mkt_DigAcc_Q as select from ZI_Mkt_DigAcc_C
{
  ZI_Mkt_DigAcc_C.DigitalAccount,
  ZI_Mkt_DigAcc_C.MarketingArea,
  ZI_Mkt_DigAcc_C.Contact,
  ZI_Mkt_DigAcc_C.Interactions
}

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

/sap/opu/odata/SAP/ZC_MKT_DIGACC_Q_CDS/ZC_MKT_DIGACC_Q

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

$select=dimension1,dimension2,figure1,figure2

e.g.

https:///sap/opu/odata/sap/ZQ_DIGACC_CONTACTS_SRV/ZQ_DIGACC_CONTACTSResults?$filter=A2CKQH731XOYIUNQD11RXSEL87ON eq ‘@uap4797x’&$select=A2CKQH731XOYIUNQD11RXSEL87ON,A2CNTOY6TG213INT1FU3OEG9467Y,A2CZIMKT_DIGACC_CCONTACT,A00O2SOLU7ADPK7EF25PQKBUOL_F