Connector for SAP HANA — Features and settings
You can connect to and access data from SAP HANA databases with the connector for SAP HANA. On this page, you can find information about the capabilities, available settings, and things to keep in mind when you work with data connections to SAP HANA.
Page content
Connector features
The following functionality is available when you access data with the connector for SAP HANA.
| Feature | Supported? |
|---|---|
| Load methods |
|
| Custom queries | Yes, for multidimensional data |
| Stored procedures | Yes, for relational data
Note: Data from stored procedures in SAP HANA can only be
imported. The External (in-database) load method is not available.
|
| Custom connection properties | No |
| Single sign-on with identity provider | No |
| Authoring in web client | No |
| Included in Spotfire distribution on Linux | Yes |
Data source properties
The following are the supported data source properties that you can configure when you create a data connection with the connector for SAP HANA.
| Option | Description |
|---|---|
| Servers |
The name of the server where your data is located. To include a port number, add it directly after the name preceded by colon. Example with port number: MyDatabaseServer:1234 SAP HANA supports resilient connections, which means you can provide multiple servers, and if the connection to the first server fails, the next server will be used instead. To add several servers, use semicolon as separator. Example with several servers: MyDatabaseServer01;MyDatabaseServer02;MyDatabaseServer03 Example with several servers including port number: MyDatabaseServer01:1234;MyDatabaseServer02:1234;MyDatabaseServer03:1234 Note: Tenant databases in SAP HANA each have a dedicated port number. Make sure you use the correct port number to connect to the intended tenant database in the SAP HANA instance. See the official documentation from SAP to learn more about port assignment for tenant databases. |
| Connect to |
Select which type of SAP HANA data you are going to connect to:
|
| Connect using SSL |
The SAP HANA connector has SSL support. Note: If you are using Kerberos authentication with delegated credentials on your Spotfire Server, you cannot use this connector with SSL in Spotfire web clients. The ODBC driver provided by the database vendor expects to find SSL certificate information in user specific directories or Windows registry entries. These are not available on the server when using Kerberos with delegated credentials. |
| Include system schemas | Select this check box if you want system tables to be included. |
| Show column labels for calculation views | Select this check box if you want to use display names, instead of technical names, for columns originating from calculation views. |
| Language | Make a selection in the drop-down list to view data in a specific language. |
| Authentication method |
The authentication method to use when logging into the database. Choose from Username and password, or Kerberos authentication. Username and password With Username and password authentication, the authentication is done using a database user. Database credentials can be stored, unencrypted, as part of the analysis file, using a setting in the Data Source Settings dialog. If credentials are found in the analysis file, they will be used to automatically authenticate against the database. If no credentials or credentials profiles are found in the analysis file, all who open the file will be prompted for database credentials. Note that there will be no prompting for credentials if the credentials embedded in the analysis file fail. Kerberos When using Kerberos authentication, the access token of the logged in user will be used. Users that have been given the appropriate access rights to SAP HANA will be able to connect and read data. Domain credentials are not stored in the analysis file. |
| Connection timeout (seconds) |
Specifies the maximum time, in seconds, allowed for a connection to the database to be established. You can also set this timeout as a preference in the Administration Manager, which will be used if you don't set a timeout in the connection dialog. If you set a timeout in the connection dialog this value overrides the settings in the Administration Manager preference. If you specify a timeout neither in the connection dialog nor in the preference, the default value of 15 seconds will be used. Note: If you set the connection timeout to zero, it will be
interpreted as no timeout. This means that there will be no upper limit for
trying to execute the command. This is generally not recommended.
Note: By default, this field is left blank, and the default
timeout settings in the data source driver will be used.
|
| Special property settings |
Use this control to specify additional ODBC connection properties that you want to use to connect to your SAP HANA instance. This enables you to configure and use connection properties that are supported by the SAP HANA ODBC driver, but for which there are no dedicated controls in Spotfire. In the Special property settings control, each row represents a connection property. To add and configure a connection property, enter the name of the connection property in Property field, and enter the value in the Value field. If you set a timeout in the connection dialog this value overrides the settings in the Administration Manager preference. If you specify a timeout neither in the connection dialog nor in the preference, the default value of 600 seconds will be used. For more information about the available and supported ODBC connection properties, see the official documentation from SAP; SAP HANA Client Interface Programming Reference. Note: The connection property names that you enter in the Property field must be exact. Depending on the version of the ODBC driver you use, the property names might be case sensitive. When you enter a name and value for a connection property, a new row is added where you can add additional properties. To remove a connection property that you have added, click the leftmost cell in the row to select the entire row, and then press the Delete key. Tip: If your keyboard does not have a Delete key, use the corresponding keyboard shortcut instead. On a Mac computer, for example, the keyboard shortcut is Fn + Backspace. |
Supported data types
When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the SAP HANA connector supports.
| Database data type | Spotfire data type |
|---|---|
| BINARY | Binary |
| BINTEXT | Binary |
| BLOB | Binary |
| VARBINARY | Binary |
| BOOLEAN | Boolean/Bool |
| SMALLDECIMAL | Currency/Decimal |
| DATE | Date |
| SECONDDATE | DateTime |
| TIMESTAMP | DateTime |
| INTEGER | Integer/Int |
| SMALLINT | Integer/Int |
| TINYINT | Integer/Int |
| BIGINT | LongInteger/Long |
| DOUBLE | Real/Double |
| FLOAT | SingleReal/Float |
| REAL | SingleReal/Float |
| ALPHANUM | String |
| CHAR | String |
| CLOB | String |
| NCHAR | String |
| NCLOB | String |
| NVARCHAR | String |
| SHORTTEXT | String |
| STRING | String |
| TEXT | String |
| VARCHAR | String |
| TIME | Time |
| DECIMAL(p, s) / DEC |
When p = 0 and s = 0: Currency/Decimal When p <= 9 and s = 0: Integer When 9 < p <= 18 and s = 0: LongInteger When p <= 15 or p >= 29: Real/Double Else: Currency/Decimal |
Supported functions
Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions.
The following are the functions that the SAP HANA connector supports.
| Function type | Functions supported |
|---|---|
| Date and Time |
YEAR, QUARTER, MONTH, MONTHNAME, WEEK, DAYOFWEEK,
DAYOFYEAR, DAYOFMONTH, DAYNAME, HOUR, MINUTE, SECOND, MILLISECOND, NOW,
ADD_DAYS, ADD_MONTHS, ADD_SECONDS, ADD_YEARS, ISOWEEK, NEXT_DAY, LAST_DAY,
YEARS_BETWEEN, MONTHS_BETWEEN, DAYS_BETWEEN, SECONDS_BETWEEN, LOCALTOUTC,
UTCTOLOCAL
|
| Conversion | SN
|
| Math |
Note: Math functions are only available for relational SAP HANA
data, and cannot be used for information views data.
ABS, ACOS, ASIN, ATAN, ATAN2, BITAND, BITCOUNT,
CEIL, COS, COSH, COT, EXP, FLOOR, LN, LOG, MOD, POWER, ROUND, SIGN, SIN, SINH,
SQRT, TAN, TANH, UMINUS
|
| Operators |
%, -, +, *, /
|
| Statistical |
SUM, AVG, MIN, MAX, COUNT, UNIQUECOUNT
Statistical functions only available for relational data:
|
| Text | ASCII, CHAR, CONCAT, LCASE, UCASE,
LOWER, UPPER, LEFT, RIGHT, LENGTH, LOCATE, LPAD, RPAD, TRIM, LTRIM, RTRIM,
NCHAR, REPLACE, SUBSTR_AFTER, SUBSTR_BEFORE, SUBSTRING, UNICODE
|
| Miscellaneous |
WIDTH_BUCKET
|
Binning is also supported by SAP HANA.
For binning, use the function
WIDTH_BUCKET (see the official SAP HANA documentation
from SAP).
You can also use the Spotfire function
BinByEvenIntervals.
Non-supported functions
The following functions are not supported in this version of the connector.
Math: GREATEST, LEAST, HEXTOBIN, BINTOHEX
Date and Time: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_UTCDATE, CURRENT_UTCTIME, CURRENT_UTCTIMESTAMP, EXTRACT