SAP HANA Reporting

We have till now loaded data from SAP Source, Non-SAP, and Flat file through SAP SLT, SAP BODS and created information view (Attribute View, Analytic View, and Calculation View).

Now we will discuss the information view in reporting tools –The drivers like JDBC, ODBC, and ODBO in reporting tool are part of SAP HANA Client.

So by installing SAP HANA Client software (*.exe file) will make all the drivers available on the PC for connecting to reporting tools to SAP HANA.

We will use SAP BO, SAP Lumira, and Microsoft Excel to access SAP HANA information view in this tutorial.

The choice of reporting tool depends upon the type of reports that are required.

In this tutorial, you will learn-

• Reporting In SAP BI (Business Intelligence) Overview
• Reporting In Webi of SAP Business Objects (BO) on HANA
• Reporting In Crystal Report
• Reporting In SAP Lumira
• Reporting In Microsoft Excel

Reporting in SAP BI (Business Intelligence) Overview

SAP BI is a data warehousing and reporting tool.

In BI (Business Intelligence) raw data will be cleaned, business logic applied, processed and provide meaningful information to the user. BI is a SAP Product, which provides the user friendly environment.

SAP BI support many databases, but when we migrate from source data to SAP HANA then architecture will be as below-

SAP BI is three tier architecture –

1. Database Server – In this, data is physically stored in PSA, ODS, Infocube.
2. Application Server – It is used to access data from database server and process data for Presentation server.
3. Presentation Server- It displays the data to the user.

SAP Bex Query Designer (Component of SAP BI) can access SAP HANA View as info provider and display data in Bex.

Reporting in Webi of SAP Business Object (BO) from HANA

SAP Business Objects Web Intelligence (SAP BO WebI) is part of the SAP Business Objects Platform (SBOP) client tools family. SAP Business Objects tool use the JDBC/ODBC driver to connect to the source system.

Features of SAP BO WebI is as below –

• Webi is adhoc reporting tool.
• Webi is used for detail level report.
• Webi display the result in tabular or graphical formats.
• User can create / modify their own queries for the report.

After installing SAP HANA client, JDBC/ODBC driver will be installed on PC.

These drivers act as an intermediate between SAP HANA and client reporting tools when presenting data to the user.

SAP BO Server and SAP BO Platform (SBOP) client tools.

Create Universe using Information Design Tool

Step 1) In this step, we will create a universe Using Information Design Tool (IDT).

HANA can be accessed via ODBC and JDBC drivers, and its tables can be defined and queried with SQL language. Tables are managed with a tool called HANA Studio.

1. Launch IDT by navigating to

Start Menu -> SAP Business Intelligence -> SAP Business Objects BI platform 4 Client Tools -> Information Design Tool

Information Design tool screen will appear. To create a universe we need a project in IDT.

2. Navigate to Project Option as below-
   1. Click On File.
   2. Click on New option.
   3. Select Project option.

      

      Or

      1. Click On New File Icon.
      2. Select Project.

A pop-up for New Project will appear- In this pop-up, enter following details:

1. Enter name of project

2. Click on finish button.

Project Name "WEBI_DHK_HANA" will appear under Local Projects Tab as below.

A project is a local workspace where you store the resources used to build one or more universes.

There are two connections available, detail of each is as below –

1. Relational Connection – To access data from a table and access regular RDBMS use Relational Connection.
2. OLAP Connection – To access data from the application (SAP, Oracle, Microsoft, SAP BO) and data stored in Cube / Information View use OLAP connection.

Step 2) Now we create a Relational connection.

So go to Project - > New -> Relational Connection.

A Pop-Up for New Relation Connection source name appear-

1. Enter Resource Name.
2. Click on Next Button.

A Pop-Up for Database Middleware Driver Selection will appear-

1. Select JDBC Drivers option under SAP HANA Database 1.0.
2. Click on Next Button.

A pop-up for New Relational Connection parameter will be displayed, enter following details into it.

1. Authentication Mode will be selected as "Use Specified User Name and Password".
2. Enter HANA User Name.
3. Enter Password.
4. Select Single server in DATA Source section. Enter Host Name (here best: 30015).
5. Enter Instance Number.
6. Click on Test Connection button to verify the connection.

A pop-up for Test Connection successful will be displayed.

After successful Connection, a connection with name SAP HANA.cnx will be created.

Step 3) To consume Universe by Web Intelligence, Dashboards or Crystal Report for Enterprise, we need to publish the connection. So, now we publish connection for SAP HANA.cnx.

1. Select SAP HANA.cnx connection under Project "WEBI_DHK_HANA" and right click.
2. Select Publish Connection to a repository.
3. A Publish connection with name SAPHANA.cns will be created after successful publish in the repository.

A pop-up for publishing connection to repository appears –

1. Select Business IDT.
2. Click on finish button.

A pop-up "The connection was published successfully" will be displayed.

Now we create Universe by using SAP HANA Business Layer.

Step 4) Create Universe (Universe is a business representation of your data warehouse or transactional database. Universe allow the user to interact with data without knowledge of complexities of the database).

To create universe, we use SAP HANA Business Layer as below -

SAP HANA Business Layer

From SAP BOBI 4.1, SAP provides a new option "SAP HANA Business Layer" while creating the universe using Information Design tool.

Before SAP BOBI 4.1 version, we need to create a derived table while building data foundation and map the variable and input parameters.

SAP HANA Business Layer automatically creates a data foundation and business layer based on selected SAP HANA Views.

SAP HANA Business Layer automatically detects the input parameter and variables.

We Create Universe through SAP HANA Business Layer as below-

1. Select Project Created "WEBI_DHK_HANA".
2. Right click the project and select a new option.
3. Select option "SAP HANA Business Layer".

A Pop-up for SAP HANA Business Layer appears-

1. Enter Business Layer Name.
2. Enter Data Foundation Name.
3. Enter Description.
4. Click On Next Button.

A Pop-up for Select SAP HANA Connection displayed-

1. Tick on Connection "SAPHANA.cnx".
2. Click on Next Button.

A Pop-up for selecting SAP HANA Information Model will be displayed.

1. Now Select Analytic View (AN_PURCHASE_ORDERS) Created under HANAUSER Package.
2. Click on Finish Button.

The analytic view will open in Information Design tool.

Step 5) Detail of Information Design tool will be as below-

1. Under Project- all Relational Connection, Business Layer, and Data Foundation will be displayed.
2. In repository resource section, object (Connection, Business Layer, etc.) stored in the repository will be displayed.
3. Business Layer shows Business view of Analytic view. It is for Functional Person.
4. Data Foundation shows table and column name. It is for Technical Person.

   

   Step 6) In this step,

   Select Business layer section, go to the folder with the name of Analytic View.

   Now follow points as below-

   1. Select data foundation section.
   2. Drag and drop column to Analytic View
   3. It will display objects under Analytic View
   4. Dragged field will be displayed under Analytic View (AN_PURCHASE_ORDER).

Step 7) In this step, save all object.

Go to file and click on "Save All" option to save all objects.

After that, follow the step below.

Step 8) Create a Query and view output.

1. Go to Queries option.
2. A Query panel will be displayed, Select the field to which you want to include in Query.
3. Drag and drop them to "Result object for Query #1 "section.
4. Click on Refresh Button on Result set section.
5. Result will be displayed.

Reporting in SAP Crystal Report

SAP Crystal Report help us to design, explore, visualize and provide report, which can be run on web or enterprises applications. With SAP Crystal Report we can create simple report or complex report.

There are two types of SAP Crystal Reports –

1. Crystal Report 2011 /13 /16: This will be used when –

• If you want to call a HANA stored procedure from Crystal Reports.
• If you want to create SQL Expressions
• If you want to execute a view with parameters or variables and submit non-default values

2. Crystal Report for Enterprises: This will be used when -

• If there are Universe available or needed, then use SAP Crystal Reports Enterprises.

We will use crystal reports for enterprises.

Step 1) Login into Crystal Report for Enterprises.

SAP Crystal reports for Enterprises will be displayed as below-

1. It is Report Formatting section, which provides a different tool for formatting.
2. This is an icon for the window (Data Explorer, outline, Group tree, Find).
3. Detail of Data Explorer, outline, Group tree, Find, etc. will be displayed.
4. Report Page Formatting option.
5. Work Area for Report.

Step 2) Now we create a connection for the data source.

Click on Choose data Source option from Data Explorer-

A pop-up for Choose a data source type will be displayed-

Select Browse option from SAP HANA Platform Section.

A pop-up for connecting to the server will be displayed. Click on "New Server" Button.

A window for Server connection will be displayed as below-

1. Click on Add Button.
2. Enter Connection Display Name (saphana).
3. A connection Name will appear in the connection list.
4. Enter HANA Server name.
5. Enter HANA Server Instance Name.
6. Enter HANA username.
7. Click on "Test Connection "Button.
8. A pop-up for Test connection logon. Enter password for SAP HANA user.
9. Click on the OK button.

A message for connection successful will be displayed.

Click on OK Button.

A pop-up for connecting to the Server will display.

Step 3) In this step,

1. We will do the following thing
   1. Select server "saphana."
   2. Click on OK button

A pop-up for entering a password will be displayed, enter the password and then click OK.

One more pop-up will appear for selecting HANA View.

2) In this step, we select SAP HANA View.

1. Select HANA View (Analytic View AN_PURCHASE_ORDERS).
2. Click on OK Button.

3. In this step, a window for Query will open.

Follow below point to create a query-

1. Select required column from the list for the query.
2. Drag and Drop required field in Query.
3. Click on Refresh button.
4. The result set will be displayed.
5. Click on button.

Report Output will be displayed as below-

Reporting in SAP Lumira

SAP Lumira is new software by SAP to Analyze and Visualize data.

By SAP Lumira user can create a beautiful and interactive map, infograpg, and charts. SAP Lumira can import data from Excel, and other source.

SAP Lumira can access information view of SAP HANA directly. SAP HANA Perform visual BI analysis using dashboard.

In SAP Lumira, the following steps need to be done to visualize data.

Now we visualize SAP HANA View in SAP Lumira, so firstly we login in SAP Lumira by Click on SAP Lumira Client icon on desktop as below-

Step 1) Create a Document and acquire a data set

After open SAP Lumira, screen open for work on SAP Lumira, Detail about this screen as below-

1. Application Toolbar – It contains toolbar like File, Edit, View, Data, and Help.
2. Home Link – By using this option we can go to home screen.
3. My Document Sections
   1. Documents
   2. Visualizations
   3. Datasets
   4. Stories
4. SAP Lumira Cloud – By using this option, we can use cloud options.
5. Connections – W can see all connection here.

So, click on Document option on My Items section to create a Documents –

1. Go to file application Toolbar., click on file option
2. Select New Option.

SAP Lumira supports below dataset –

• Microsoft Excel
• Text
• Copy from clipboard
• Connect to SAP HANA
• Download From SAP HANA
• Universe
• Query With SQL
• Connect to SAP Business Warehouse
• SAP Universe Query Panel

Step-2) Connect to SAP HANA

Here we will connect to SAP HANA and access SAP HANA Information View.

1. So select Connect to SAP HANA.
2. Click on Next Button.

A pop-up for SAP HANA Server Credential will be displayed as below-

1. Enter SAP HANA Server name.
2. Enter SAP HANA Instance no.
3. Enter SAP HANA User Name.
4. Enter SAP HANA Password.
5. Click on "Connect" Button to Connect to SAP HANA Server.

After clicking on Connect button, we will connect to SAP HANA and able to access SAP HANA view.

Step 3) Access SAP HANA Analytic View

A window for select SAP HANA View will appear as below-

1. Select SAP HANA View ("AN_PURCHASE_ORDERS" here).
2. Click on Next Button.

Step 4 ) Define Dimension and Measure.

The next window for select Measures and Dimensions will be displayed –

1. All Measure will be grouped under Measure sections.
2. All Dimension will be grouped under Dimension Sections.
3. Click on Create Button.

Step 5) Visualize SAP HANA Analytic View in SAP Lumira.

After activating Information View in SAP HANA, a Column View with the similar name of information view, under "_SYS_BIC" Schema created in SAP HANA catalog node.

When we need to access any SAP HANA Information View outside from SAP HANA, We can access it only from "_SYS_BIC" Schema.

A Visualize screen will appear, which select column view under "_SYS_BIC" Schema –

1. Different Chart Type can be selected from Chart Builder Section.
   1. Click On Chart Builder Icon.
   2. Select Column Chart option.

   

2. Click On "+" icon in front of MEASURES Section to Add Measure in Y Axis format.
3. We have added "GROSS_AMOUNT" AND "TAX_AMOUNT" Measure.
4. Click on the icon in front of "DIMENSIONS" Section. A list of all available dimension appear.
5. Select "CATEGORY", "PRODUCT_ID", "PRODUCT_NAME" from Dimension List to display on X Axis.

SAP HANA Analytic view will be displayed in visualize tab of SAP Lumira, in which we have different screen as below-

1. Tool for Ascending / descending, Ranking, Clear, refresh, etc.
2. This can be used for Filtering.
3. The output of SAP HANA Analytic views in SAP HANA lumira.

Reporting in Microsoft Excel

Microsoft Excel has powerful reporting inbuilt option; we can create report quickly by pivot tables and charts.

MS Office uses MDX language ((Multi Dimension Expression language) to access data from SAP HANA.

MDX Language is used by reporting tool to access data from a multi-dimensional object in a database environment. By Only MDX Query we can access sap Hana hierarchies.

We can access only SAP HANA information view which has a property as 'CUBE' in semantic, so we cannot access attribute view by MS Excel.

Connecting Drivers- MS Excel uses ODBO (OLE DB FOR OLAP) drivers for connecting to SAP HANA database.

Now we will access SAP HANA database from SAP HANA as shown in steps below –

Step 1) Connect To SAP HANA Excel-

1. Open MS Excel. Go to Data Tab.
2. Click on "From Other Sources" Icon.
3. Select From Data Connection Wizard.

A screen for Data connection wizard will be displayed as below-

1. Select "Other / Advance" option.
2. Click on Next Button.

A window for "Data Link Properties "will be open as below-

1. Select "SAP HANA MDX Provider "under Provide Tab.
2. Click on Next Button.

A window for data link properties will be displayed –

Enter the following detail as below –

1. Enter Host Name of SAP HANA Database.
2. Enter Instance number of SAP HANA database.
3. Enter Username / password for SAP HANA database.
4. Enter Language name.
5. Click on "Test Connection" to test the connection to SAP HANA Database from Excel.

A message "Test connection succeeded" will be pop-up.

Click on the OK button.

Step 2) Till now we had created a connection from Excel to SAP HANA and tested the connection. Now we access SAP HANA Information View from Excel.

Now a window for "Data connection wizard" will be displayed.

1. Select the package in which contains SAP HANA Information View.
2. Select Information View (Analytic View, Calculation View).
3. Click on Next Button.

   

   A new window for data connection File will appear, enter following details.

   1. Give File Name (AV_SALES).

   2. Tick option "Save password in file", for avoid to enter a password while open Excel file.

   3. A Pop-up for save password security related will be displayed.

   4. Click on Finish Button.

   

Step 3) Now SAP HANA Information View will be displayed in Excel as pivot table as below –

Summary:

We have learned in this tutorial below topic –

• Reporting in SAP BI overview
• Reporting in SAP BO Webi with an example of SAP HANA information View.
• Reporting in SAP Crystal Report Enterprises with an example of SAP HANA information View.
• Reporting in SAP Lumira with an example of SAP HANA information View.
• Reporting in Microsoft Excel by consuming SAP HANA Information view.