Sap Hana

Mindziedatadesigner/connectors

Category: Database Connectors

Introduction

This document is created to help setup a mindzieDataDesigner connector to SAP HANA database. The mindzieDataDesigner is the ETL tool used by mindzieStudio to convert database tables to process mining event logs. The purpose of this document is to help creating the connection string and opening ports on the firewall if required.

Overview

The SAP HANA connector uses ODBC to provide robust connectivity to SAP HANA databases. This connector is optimized for enterprise SAP environments and supports on-premise SAP HANA instances, making it ideal for process mining large-scale enterprise data.

System Requirements

  • Database System: SAP HANA 1.0 or later (2.0 recommended)
  • Deployment Options: On-premise
  • ODBC Driver: SAP HANA ODBC Driver (minimum version 2.4)

Prerequisites

The SAP HANA ODBC driver must be installed on your system. Please refer to SAP documentation for installation instructions.

Connection String Format

Basic ODBC Connection

Driver={HDBODBC};ServerNode=hostname:port;Database=database_name;UID=username;PWD=password;

Connection Parameters

Parameter Description Required Example
Driver ODBC driver name Yes {HDBODBC} or {SAP HANA ODBC Driver}
ServerNode HANA server and port Yes hana-server.company.com:30015
Database Target database/tenant No PRD or SystemDB
UID Username Yes MINDZIE_USER
PWD Password Yes SecurePassword123
CHAR_AS_UTF8 UTF-8 character handling No 1
CONNECTTIMEOUT Connection timeout No 30
COMMUNICATIONTIMEOUT Communication timeout No 0 (unlimited)
RECONNECT Auto-reconnect setting No 1 (enabled)
ENCRYPT Enable encryption No true

Connection Examples

Standard On-Premise Connection

Driver={HDBODBC};ServerNode=hana-prod.company.com:30015;Database=PRD;UID=PROCESS_MINING_USER;PWD=SecurePassword123;CHAR_AS_UTF8=1;

Multi-Tenant Database Container (MDC)

Driver={HDBODBC};ServerNode=hana-server:30013;Database=TENANT_DB;UID=MINDZIE_USER;PWD=password;

High Availability Connection

Driver={HDBODBC};ServerNode=hana-node1:30015,hana-node2:30015,hana-node3:30015;Database=PRD;UID=MINDZIE_USER;PWD=password;RECONNECT=1;

Required SAP HANA Permissions

The following SQL examples show the typical permissions needed for process mining. Replace the sample schema, table, and user names with your actual values:

-- Grant schema access (replace "PROCESS_MINING" with your schema name)
GRANT SELECT ON SCHEMA "YOUR_SCHEMA_NAME" TO YOUR_USERNAME;

-- Grant table-level permissions (replace with your actual table names)
GRANT SELECT ON "YOUR_SCHEMA_NAME"."YOUR_TABLE_NAME" TO YOUR_USERNAME;

-- For system views (if needed for metadata access)
GRANT SELECT ON SYS.M_DATABASES TO YOUR_USERNAME;

Example with sample names:

-- Sample permissions using example names
GRANT SELECT ON SCHEMA "PROCESS_MINING" TO MINDZIE_USER;
GRANT SELECT ON "PROCESS_MINING"."EVENT_LOG" TO MINDZIE_USER;
GRANT SELECT ON SYS.M_DATABASES TO MINDZIE_USER;

Testing ODBC Connections

After configuring your ODBC connection, you can test it using various tools:

Windows ODBC Data Source Administrator

  • Built-in Windows utility (odbcad32.exe)
  • Configure and test ODBC connections
  • Access via Control Panel → Administrative Tools → ODBC Data Sources

Database Client Tools

  • DBeaver: Free, cross-platform database tool with ODBC support
  • HeidiSQL: Windows-based SQL client supporting ODBC connections
  • SQL Server Management Studio: Can connect to SAP HANA via ODBC
  • Toad for SAP: Commercial tool with native SAP HANA support

Microsoft Office Applications

  • Excel: Connect via Data → Get Data → From Other Sources → From ODBC
  • Power BI: Native SAP HANA connector and ODBC support
  • Access: Link tables via ODBC connections

Command Line Tools

  • isql: Unix/Linux command line tool for testing ODBC connections
  • osql/sqlcmd: Windows command line utilities (limited SAP HANA support)

Simple Test Query

Once connected, test with a basic query:

SELECT CURRENT_TIMESTAMP FROM SYS.DUMMY;

Firewall Configuration

Required Firewall Ports

The following ports need to be opened on your firewall for SAP HANA ODBC connections:

Port Purpose Default Instance (00)
30013 SystemDB SQL Connection System database access
30015 Tenant Database SQL Connection First tenant database
443 SAP HANA Cloud (HTTPS/SSL) Cloud connections only

Port Numbering Scheme

  • System Database: Port 30013 (for default instance 00)
  • Tenant Database: Port 30015 (for default instance 00)
  • Pattern: 3NN13 (SystemDB) or 3NN15 (Tenant DB), where NN = instance number

Note: Port numbers can be customized during SAP HANA installation. Check with your SAP HANA administrator for the exact ports used in your environment.

Additional Considerations

  • High Availability: Multiple ports may be required for cluster configurations
  • Load Balancers: Additional ports may be needed for load balancer configurations

Reference: SAP Note 2477204 - FAQ: SAP HANA Services and Ports (requires SAP support access)

mindzie Server Access

For enhanced security, you can configure your firewall to only allow connections from mindzie servers by whitelisting specific IP addresses. Contact mindzie support to obtain the current IP addresses for the mindzie servers you are using.

CDPOS Change Document Extraction

CDPOS is a crucial SAP table that stores field-level changes to business objects, commonly used in process mining to track detailed modifications. Since CDPOS doesn't contain date fields directly, it must be joined with CDHDR (Change Document Header) for time-based filtering.

Oracle Database Query

-- ORACLE SQL VERSION: GET ALL CDPOS RECORDS FOR LAST 2 YEARS
-- CDPOS does NOT have date/time fields - must join with CDHDR for filtering

-- DOCUMENTATION REFERENCES:
-- 1. CDHDR.UDATE = "Creation date of the change document" (SAP Datasheet)
-- 2. CDPOS.CHANGENR = CDHDR.CHANGENR is the standard join (SAP Community)
-- 3. "These two tables are connected by the change number" (Techlorean)
-- 4. CDHDR contains header info, CDPOS contains field-level details

SELECT CDPOS.*
FROM CDPOS
INNER JOIN CDHDR ON CDPOS.CHANGENR = CDHDR.CHANGENR
WHERE CDHDR.UDATE >= ADD_MONTHS(SYSDATE, -24)  -- Oracle: Last 24 months (2 years)

-- ORACLE SPECIFIC NOTES:
-- 1. Uses ADD_MONTHS(SYSDATE, -24) for 2-year date calculation
-- 2. SYSDATE returns current date/time
-- 3. Alternative: CDHDR.UDATE >= SYSDATE - INTERVAL '2' YEAR
-- 4. Date format in CDHDR.UDATE should be compatible with Oracle DATE type

SAP ODBC Query

-- SAP SQL VIA ODBC CONNECTOR: GET ALL CDPOS RECORDS FOR LAST 2 YEARS
-- CDPOS does NOT have date/time fields - must join with CDHDR for filtering

-- DOCUMENTATION REFERENCES:
-- 1. CDHDR.UDATE = "Creation date of the change document" (SAP Datasheet)
-- 2. CDPOS.CHANGENR = CDHDR.CHANGENR is the standard join (SAP Community)
-- 3. "These two tables are connected by the change number" (Techlorean)
-- 4. CDHDR contains header info, CDPOS contains field-level details

-- IMPORTANT: CDPOS is a cluster table - direct joins may not work via ODBC
-- This query may need to be split into separate queries depending on SAP version

SELECT CDPOS.*
FROM CDPOS
INNER JOIN CDHDR ON CDPOS.CHANGENR = CDHDR.CHANGENR
WHERE CDHDR.UDATE >= ADD_DAYS(CURRENT_DATE, -730)  -- SAP HANA: Last 730 days (2 years)

-- SAP SQL SPECIFIC NOTES:
-- 1. Uses ADD_DAYS(CURRENT_DATE, -730) for 2-year calculation (SAP HANA)
-- 2. For older SAP systems, may need: CDHDR.UDATE >= '20220101' (hardcoded date)
-- 3. CDPOS is a cluster table - may require special handling via ODBC
-- 4. Alternative for non-HANA: Use DATE subtraction if supported
-- 5. Date format: CDHDR.UDATE is typically YYYYMMDD format in SAP
-- 6. For maximum compatibility, use client-side date parameter:
--    WHERE CDHDR.UDATE >= '?' -- Parameter for 2 years ago date

-- CLUSTER TABLE WARNING:
-- CDPOS is a cluster table in SAP, which may cause issues with ODBC connections
-- Consider using SAP RFC or function modules for better performance
-- Alternative: Query CDHDR first, then CDPOS separately using CHANGENR values

SQL Server (T-SQL) Query

-- T-SQL (SQL SERVER) VERSION: GET ALL CDPOS RECORDS FOR LAST 2 YEARS
-- CDPOS does NOT have date/time fields - must join with CDHDR for filtering

-- DOCUMENTATION REFERENCES:
-- 1. CDHDR.UDATE = "Creation date of the change document" (SAP Datasheet)
-- 2. CDPOS.CHANGENR = CDHDR.CHANGENR is the standard join (SAP Community)
-- 3. "These two tables are connected by the change number" (Techlorean)
-- 4. CDHDR contains header info, CDPOS contains field-level details

SELECT CDPOS.*
FROM CDPOS
INNER JOIN CDHDR ON CDPOS.CHANGENR = CDHDR.CHANGENR
WHERE CDHDR.UDATE >= DATEADD(YEAR, -2, GETDATE())  -- T-SQL: Last 2 years from current date

-- T-SQL SPECIFIC NOTES:
-- 1. Uses DATEADD(YEAR, -2, GETDATE()) for 2-year date calculation
-- 2. GETDATE() returns current date/time
-- 3. Alternative: CDHDR.UDATE >= DATEADD(MONTH, -24, GETDATE())
-- 4. Date format in CDHDR.UDATE should be compatible with SQL Server datetime
-- 5. May need CONVERT() if UDATE is stored as string in YYYYMMDD format:
--    WHERE CONVERT(datetime, CDHDR.UDATE, 112) >= DATEADD(YEAR, -2, GETDATE())

ODBC Driver Installation (mindzieStudio Server Only)

Note: This section is only required for the server running mindzieStudio.

Download

Go to: https://tools.hana.ondemand.com/#hanatools

  1. Create a free SAP account if needed (quick registration)
  2. Find: SAP HANA Client
  3. Download: Windows on x64 64bit version
  4. File name: hanaclient-x.x.x.x-windows-x64.zip

Install

  1. Extract the ZIP file
  2. Run hdbsetup.exe as Administrator
  3. Select "Install new SAP HANA client"
  4. Follow the wizard (accept defaults)
  5. Done!

Verify Installation

Run in PowerShell:

Get-OdbcDriver | Where-Object {$_.Name -like "*HDB*"}

Should show: HDBODBC [64-bit]

Driver Not Listed After Installation

cd "C:\Program Files\SAP\hdbclient"
hdbodbc_cons.exe -i

Troubleshooting

Common Connection Issues

"Cannot connect to server" Error

  • Verify server hostname and port number
  • Check network connectivity and firewall rules
  • Ensure SAP HANA instance is running and accepting connections
  • Validate HANA service status with HDB info

"Authentication failed" Error

  • Verify username and password are correct
  • Check if user account is locked or expired
  • Ensure user has CONNECT privilege
  • Verify password policy compliance

"Driver not found" Error

  • Install SAP HANA ODBC driver from SAP HANA Client
  • Verify driver registration in ODBC Data Source Administrator
  • Check for 32-bit vs 64-bit driver compatibility
  • Ensure driver path is in system PATH

"Table or view does not exist" Error

  • Verify table/view names and schema references
  • Check user permissions on specific objects
  • Use fully qualified names: SCHEMA.TABLE
  • Validate case sensitivity in object names

Sources and References

This documentation is based on the following sources:

Official SAP Documentation

Community and Technical Resources

An error has occurred. This application may no longer respond until reloaded. Reload ??