• Home
  • Testing
  • SAP
  • Web
  • Must Learn!
  • Big Data
  • Live Projects
  • Blog

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.

SAP HANA Reporting

In this tutorial, you will learn-

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.

SAP HANA Reporting

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 HANA Reporting

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

SAP HANA Reporting

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

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

      SAP HANA Reporting

      Or

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

SAP HANA Reporting

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.

SAP HANA Reporting

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

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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

SAP HANA Reporting

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.

SAP HANA Reporting

A pop-up for publishing connection to repository appears –

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

SAP HANA Reporting

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

SAP HANA Reporting

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".

SAP HANA Reporting

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.

SAP HANA Reporting

A Pop-up for Select SAP HANA Connection displayed-

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

SAP HANA Reporting

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.

SAP HANA Reporting

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.

    SAP HANA Reporting

    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).

SAP HANA Reporting

Step 7) In this step, save all object.

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

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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
  1. 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 HANA Reporting

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.

SAP HANA Reporting

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

Click on Choose data Source option from Data Explorer-

SAP HANA Reporting

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

Select Browse option from SAP HANA Platform Section.

SAP HANA Reporting

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

SAP HANA Reporting

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.

SAP HANA Reporting

A message for connection successful will be displayed.

SAP HANA Reporting

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

SAP HANA Reporting

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

SAP HANA Reporting

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.

SAP HANA Reporting

  1. 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 SAP HANA Reporting button.

SAP HANA Reporting

Report Output will be displayed as below-

SAP HANA Reporting

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.

SAP HANA Reporting

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-

SAP HANA Reporting

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.

SAP HANA Reporting

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 HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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.

    SAP HANA Reporting

  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 Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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

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

SAP HANA Reporting

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.

SAP HANA Reporting

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.

SAP HANA Reporting

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

SAP HANA Reporting

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.

    SAP HANA Reporting

    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.

    SAP HANA Reporting

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

SAP HANA Reporting

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.

 

YOU MIGHT LIKE: