►The SAP HANA Live has been implemented in our SAP Suite on HANA Server or in our SAP HANA Sidecar Server, depending on our current SAP HANA Database Server implementation
►We want to modify the delivered scenarios to reflect our reporting requirements and to include any custom fields that have been added to the standard SAP tables, as well as including custom tables that are part of our SAP implementation
►The following scenario has been used for explanation:
- Customer IDES Company, have their own classification of countries (Developed, Emerging, Transitional) and their own classification of customers (Large, Medium, Small) that they use in their custom applications
- They want to report Sales Orders based on these additional classifications as well. Since SAP Hana Live delivered by SAP already includes standard views for reporting on sales orders they have decided to extend these views to suit their requirements
►This document consists of step-by-step instructions on the following 4 scenarios to satisfy the customer Sales Order reporting requirements
- Include Unexposed Field from a Standard Model
- Exclude unnecessary fields from Query view
- Include Custom Table(s)
- Include Custom Field(s)
►The SAP HANA Live scenario as delivered contains the following views:
- Private Views
(The base views that directly use the SAP Tables)
- Reuse Views
(It consist of one or more “Private” views joined in combination to produce a composite view that forms a reusable unit related to a particular area or function within the system)
- Query Views
(The top level which consist of one or more “Reuse” views joined in combination to expose the major attributes and measures that are needed to report on the various business scenarios; The only views that are exposed to the Client Tools to be used for reporting)
►Since all of these different types of views are based on Standard SAP tables, they do not include any custom fields that may have been added to these tables in the SAP system, nor do they contain any custom tables that may have been added. The standard “Query Views” generally also contain more fields than are required for any particular reporting scenario
►The purpose of this document is to show how to enhance the standard SAP HANA Live implementation to enable enterprise specific reporting which uses any custom data and caters for the specific requirements of the enterprise
Some of the important rules while modeling a query view:
- Do not extend SAP delivered Views directly. Use Query/Reuse views for extensions by copying over to customer namespace
- To find a list of Query and Reuse views refer to http://help.sap.com/hba SAP Hana Live for SAP ERP/CRM/SCM etc. Views that are not mentioned here are Private Views and should not be copied or modified
- For all graphical calculation views the flag ‘Enforce SQL Execution’ must be set to ‘true’
- For all enhancements, graphical calculation views must be used. If they are not sufficient, use script based calculation views
- Avoid using Attribute and Analytical views unless there is a requirement to use features that are only available in these types of views
Customization Option I – Inclusion of Unexposed Field from a Standard Model:
- Browse standard SAP Hana Live content for standard query view
- Create a new query view in the customer namespace
- Enhance the views with relevant reuse views
- Activate and Test the view
►In the Sales Order query view, the customer wants to see the Sales Organization, Sales District and also wants to see the ‘Total Net Amount’ based on these columns. Start by browsing through the SAP delivered SAP HANA Live (SHL) content for Business Suite. All the content can be found in the package: sap > hba
►For the Sales Order query, the SalesOrderQuery (calculation view) in the ‘ecc’ folder of the SAP Hana Live content.
Customization Option II – Exclude unnecessary fields from Query View:
►Standard SAP Hana Live content contains query views which are designed as a reference for creating customer views for reporting. Depending on requirements, fields can be included or excluded by copying and modifying query views into the customer package and namespace. The example below details how unnecessary fields can be excluded from a view. It is always good practice to only expose the fields that are really needed for the reporting purpose.
►In the sales order query view, there are some exposed fields such as SalesGroup and SalesOffice that the customer does not want to report on
►Identify the unnecessary fields:
- Copy the zSalesOrderDistrictQuery query view and call it zSalesOrderReducedQuery, then review the view, and look at the output and identify the fields that are unnecessary for the report. For the example, SalesGroup and SalesOffice are not needed.
►Exclude the unnecessary fields from the query view:
- Select the Aggregation node, then select the 4 unnecessary fields from the Output section and press <Delete>, click on ‘Yes’ and the fields are removed from the Query.
- This still leaves these fields in nodes further down, but since they cannot be selected from any reporting tool that uses the view, and all the joins are Left Outer Joins, they should have no negative impact on performance.
- Save and activate the query view
Customization Option III – Include Custom Tables:
►For this scenario the customer wishes to classify countries by using one of three classifications, where the classifications consist of ‘Developed’, ‘Transitional’, and ‘Emerging’. Since the SalesOrderQuery view can be used to expose the country code, the requirement can be satisfied by using a custom table that contains the Country Code and the Classification, and joining that to a standard view that exposes the Country Name, then adding the resulting custom view into the query
Create a Custom Reuse View
►In order to enhance the existing Country reuse view to use the custom country classification table, the Country View needs to be copied and renamed into the customer namespace and customer package, and then modified.
- Create a custom reuse view in the custom package and name it zCountryClassification.
- Edit the newly created reuse view zCountryClassification and display the properties and ensure that the ‘Multidimensional Reporting’ is set to ‘disabled’ and ‘Execute in’ is ‘set to ‘SQL Engine’.
- Locate the ‘Country’ reuse view in the package sap hba ecc, then drag and drop it onto the reuse view zCountryClassification, and add a Projection node and project all the fields from the ‘Country’ view.
- Drag and drop the custom table onto the view, add a Projection node, then project all the fields from the custom table.
- Create a Join, add the Country Projection and the Classification Projection then create a left outer join between the relevant key fields, in this case being the Country field. Rename the Classification field to a user friendly name such as CountryClassification.
Customization Option IV – Include Custom Fields:
►When one or more custom fields are added to a standard table that is used in the SAP HANA Live Models and those custom fields need to be added to the Models, then the most straight-forward way of doing this is to create a custom Calculation View that exposes the custom fields and Master Data (if required) and then create a copy of the Query Calculation View and add in the newly created Calculation View.
Create a new Calculation View for the custom fields
►Create a new Graphical Calculation View in the customer specific package with an appropriate unique name that conforms to your custom object naming convention.
►Locate the table with the custom field(s) in the appropriate schema and drag and drop it into the new view.
►Create a Projection node and link the table to the node, then select the key fields that are required to connect the table contents to the Query View as well as the custom field(s) and add them to the output.
►If there is a requirement for Master Data to be added for the custom field, then drag and drop the appropriate Master Data table onto the view.
►Create a Join node and connect the Master Data table and the projection and join on the appropriate fields. The join type should be set to “Left Outer” and the cardinality should be set to “n..1”.