Wednesday 12 July 2017

SAP MII connection to External HANA database

SAP MII (Manufacturing Integration and Intelligence) is a very powerful tool provided by SAP to connect shop floor systems (PLC / SCADA / Historians) to ERP (SAP ECC / SAP S/4 HANA). By such seamless integration between shop floor and ERP systems, it provides customers better visibility in terms of manufacturing production and it helps in reducing operational costs by providing various dashboards, KPI monitoring and visual representation of machine data. In today’s world where everyone is talking and researching about IoT and the massive amount of data that any manufacturing company is going to deal with, the expectation from the SAP MII customers is to handle huge volume of time-series and tabular data collected from operations/PLCs at the manufacturing plants to perform long-term trend and regression analysis on it near real time. Moreover, customers also want to predict and prevent operational issues such as machine downtimes, material quality and performance bottlenecks in production lines.

MII in its traditional architecture usually has its own Netweaver database and an external database (commonly known as Application Database) where most of transaction data and certain master data is stored. In this blog, we are solely going to concentrate on external application database that MII connects to. Before the birth of HANA database, MII generally had MS SQL or Oracle as its application database. But looking at the computing power of HANA database and its massively fast i/o operations, customers are demanding HANA as their external database that should connect to MII for all the transaction data and analytics.

There are many ways MII can connect to external HANA database but we are going to learn how to connect to HANA database using JDBC API.

SAP HANA provides a driver that enables Java applications to connect to the SAP HANA database with the JDBC application programming interface (API).

Now once we have a confirmation that HANA database has JDBC driver installed and configured,we can perform belowsteps in MII to connect to HANA database.

 1.Go to Data Sevices  / Data Servers :

SAP HANA, HANA, Manufacturing, JDBC

2. Click on Create and below screen will be shown:

SAP HANA, HANA, Manufacturing, JDBC
  • Provide Server Name – Any meaningful name
  • Connector Type: IDBC (As we are going to use JDBC connection, connector type would be IDBC)
  • Description: Meaningful Description
  • Click on Finish
3. Provide Server Details and other information (Critical Step) :

a. Check “Enabled” & “Allow Dynamic Query” Checkboxes in Settings tab:

SAP HANA, HANA, Manufacturing, JDBC

b.Open Connection Tab:

SAP HANA, HANA, Manufacturing, JDBC
  • JDBC Driver:
  • Server URL:
jdbc:sap://<server>:<port>[/?<options>] where <port> should be 3<instance number>15

Lets say Server name in & HANA Database instance is 01.Then Server URL would be: jdbc:sap://
  • User Name: User Name of the HANA Database
  • Password: Password of the HANA Databse
  • Validation Query: Select ‘Hello World’ from Dummy
If we have provided all the correct information then if we go to Status tab, we can see ‘Running’ Status:

SAP HANA, HANA, Manufacturing, JDBC

4. Open MII Workbench, select New SQL Query and newly created data server External-HANA-DB will appear in Available Servers:

SAP HANA, HANA, Manufacturing, JDBC

5. We can see all the tables and can query them:

SAP HANA, HANA, Manufacturing, JDBC

Please note that by directly querying HANA database tables via MII SQL Query will not give us any benefits that HANA’s in-memory database management provides. Hence optimal way to perform this activity is to create tables in HANA as columnar tables and then define the Attribute views (fact tables), Analytic views (measure and dimensions) and Calculation views (complex calculations and data processing). Then MII SQL Query can directly query these views and can enjoy all of the HANA benefits.

No comments:

Post a Comment