Version: all released versions of CranSoft / DataGarage / DSP
Background: To use HANA as a source, you will need to have ODBC access to the database layer and also add a DataSource Type as follows:
Note: You can find information on the ports that need to be available to connect to your HANA DB: Which ports are needed for SAP HANA Studio <... | SCN
First, set up new Database Type (System Administration -> Configuration -> Set Up -> Database Type) as:
Database Type:
Database Type ID 502
Description HANA
Supported Yes
Beginning Qualifier "
Ending Qualifier "
Select Top Format SELECT * FROM (#SQL#) T LIMIT #NUMBER#
Then, use this new Database Type for your HANA (ODBC) database registered as a Data Source (System Adminstration -> Data Sources)
Then, set up Source Connection Type (Collect -> Administrative -> Connection Types -> Sources) as:
Source Connection Type ODBCHANA
Fetchsize 1
Download SQL
SELECT '#SOURCE#' AS SOURCE, TABLE_NAME AS TABLENAME, SCHEMA_NAME AS SCHEMAOWNER, COLUMN_NAME AS COLUMNNAME, POSITION AS COLUMNORDER, DATA_TYPE_NAME AS DATATYPE, LENGTH AS DATALENGTH, LENGTH AS DATAPRECISION, CASE WHEN SCALE IS NULL THEN 0 ELSE SCALE END AS DATASCALE, CASE WHEN IS_NULLABLE='TRUE' THEN 1 ELSE 0 END AS DATANULLABLE FROM SYS.TABLE_COLUMNS WHERE SCHEMA_NAME = '#SOURCEDBO#' AND TABLE_NAME = '#SOURCETABLE#' ORDER BY POSITION, 1, 2, 3
Download Table SQL
SELECT SCHEMA_NAME AS SCHEMAOWNER, TABLE_NAME AS TABLENAME, 'Download Table' DESCRIPTION FROM SYS.TABLES ORDER BY 1 ASC, 2 ASC
Download Primary Key SQL
SELECT '#SOURCE#' AS Source, INDEXES.TABLE_NAME AS TableName, INDEXES.INDEX_NAME AS KeyName, COLUMN_NAME AS ColumnName, POSITION AS ColumnPosition FROM SYS.INDEXES, SYS.INDEX_COLUMNS WHERE INDEXES.CONSTRAINT = 'PRIMARY KEY' AND INDEXES.SCHEMA_NAME = '#SOURCEDBO#' AND INDEXES.TABLE_NAME = '#SOURCETABLE#' AND INDEXES.SCHEMA_NAME = INDEX_COLUMNS.SCHEMA_NAME AND INDEXES.TABLE_NAME = INDEX_COLUMNS.TABLE_NAME AND INDEXES.INDEX_NAME = INDEX_COLUMNS.INDEX_NAME ORDER BY 3,5,4
Download Index SQL
SELECT '#SOURCE#' AS Source, INDEXES.TABLE_NAME AS TableName, INDEXES.INDEX_NAME AS iNDEXName, COLUMN_NAME AS ColumnName, POSITION AS ColumnPosition, 0 AS UNIQUEINDEX FROM SYS.INDEXES, SYS.INDEX_COLUMNS WHERE INDEXES.CONSTRAINT <> 'PRIMARY KEY' AND INDEXES.SCHEMA_NAME = '#SOURCEDBO#' AND INDEXES.TABLE_NAME = '#SOURCETABLE#' AND INDEXES.SCHEMA_NAME = INDEX_COLUMNS.SCHEMA_NAME AND INDEXES.TABLE_NAME = INDEX_COLUMNS.TABLE_NAME AND INDEXES.INDEX_NAME = INDEX_COLUMNS.INDEX_NAME ORDER BY 3,5,4
SAP Table Check SQL
SELECT TABNAME, AS4LOCAL, AS4VERS, TABCLASS, SQLTAB FROM #SOURCEDBO#.DD02L WHERE TABNAME = '#SOURCETABLE#'
SAP Column Download SQL
SELECT TABNAME, FIELDNAME, POSITION, KEYFLAG, INTTYPE, INTLEN, DATATYPE, LENG, DECIMALS FROM #SOURCEDBO#.DD03L WHERE TABNAME = '#SOURCETABLE#' AND LENG > '000000' ORDER BY POSITION
SAP Primary Key SQL
SELECT TABNAME, FIELDNAME FROM #SOURCEDBO#.DD03L WHERE TABNAME = '#SOURCETABLE#' and KEYFLAG = 'X' AND LENG > '000000' ORDER BY POSITION
ODBC Driver Driver=HDBODBC32
OLE Provider <None>
Connection String Example <None>
Left Column Delimiter "
Right Column Delimiter "
*NOTE: Adjust the Connection Data Type Conversion rules as necessary. For our location, we copied the Data Type Conversions from the SQLSERVER Connection Type. Conversion rules for HANA to SQL Server can be found here: http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/801c67ef-ebe0-3010-3085-a7fc864fc586?QuickLink=index&overridelayout=true&59180354393210
Then, use the Connection Type on the DataGarage / Collect Target Source