Accessing data from SAP HANA

You can connect to and access data from SAP HANA in Spotfire. When you want to connect to SAP HANA, you can either open and reuse a connection that is shared in your Spotfire library, or you can create a new connection.

About this task

Before you begin

Read the System requirements for Spotfire Connectors. Make sure that the SAP HANA data source that you want to connect to is compatible with your version of Spotfire. The system requirements page also specifies what driver, and versions of the driver, that you can use with the connector.
Install the required ODBC driver on your computer. See Drivers and data sources in Spotfire.

Adding data from an existing connection to SAP HANA

If you want to add data from an existing connection or connection data source that is shared in your Spotfire library, you can use the search function in the Files and data flyout to quickly find the data you are looking for.

Use the search phrase 'connector:hana' to find all shared connections and connection data sources in the library that were created with the SAP HANA connector. For more detailed instructions, see Searching the Library.

Creating a new connection to SAP HANA (installed client only):

Procedure

On the Authoring bar, click Files and data .
In the Files and data flyout, click Connect to.
In the list of connectors, click SAP HANA.
In the SAP HANA flyout, click New connection.
In the SAP HANA connection dialog, enter the connection information for your SAP HANA instance. Then click Connect.
For information about the settings in the dialog, see Connector for SAP HANA — Features and settings.
Select the data you want to analyze.
1. In the Available tables in database list, select the tables you want to analyze in Spotfire and click Add >, or create a Custom Query to select your data.
2. You can add related tables to the view by right-clicking on a table in the Views in connection list and selecting Add Related Tables. You can also add custom relations between two tables, to create a single view based on the tables in Spotfire.
3. If desired, you can prompt the end users of the analysis to select specific values in the data only (for example, you can prompt to select data for a single region or product only). You also use prompts to give input values for any SAP HANA parameters defined for your selected data. Add prompting by clicking Define Prompting and specifying new prompts for suitable columns or parameters.
When you are happy with your data selection, click OK to close the Views in Connection dialog.
The final step is a summary view of the resulting data tables that will be added to your Spotfire analysis. Here you can select whether to load the data from SAP HANA as Imported or External data tables.
Click OK.
Any defined prompts will be shown, and as soon as the input has been provided, data is ready to be analyzed in Spotfire.

Troubleshooting

This section lists useful information about requirements, limitations, and best practices for accessing data from SAP HANA.

Granting access to XSA/HDI views

For views and tables that have been created with XS Advanced, through the SAP WebIDE, to be accessible in Spotfire, certain privileges must be set up for your database user. Your database administrator must grant your database user "SELECT", "SELECT CDS METADATA" and "EXECUTE" privileges.

For multidimensional data, your database user must have "SELECT" privilege for the "_SYS_BI" schema.

See the official documentation from SAP for more information. Some helpful tips can be found, for example, in this SAP blog post.

Install the required ODBC driver on your computer. See Drivers and data sources in Spotfire.

HINTS to optimize metadata loading

If it is taking a long time to load the list of tables and schemas in the Views in Connection dialog, the reason could be that the loading of metadata is slow.

You might be able to improve metadata loading performance with HINTS. HINTS is a feature in SAP HANA, with which you can include a WITH HINT clause in queries to guide how to get metadata.

You can configure Spotfire to add HINTS to metadata queries to SAP HANA. Use this preference setting in the Administration Manager:

Connectors > SAP HANA > MetadataQueryHints.

Note: For more information about how to use SAP HANA HINTS, see the official documentation.

Limitations

When you access data from SAP HANA in Spotfire, keep the following limitations in mind.

When you work with multidimensional data, the COUNT function (which is used for example when you select Row Count on visualization axes) will give unpredictable results.
For CDS entities related with unmanaged association, only ON (A=B) {AND C= D)} is supported for adding related tables.
Parent-child hierarchies are not supported. Only level hierarchies are supported.
For CDS entities (listed as database tables in Spotfire) that are defined with XS Classic, it is not possible to add any related tables by clicking Add related tables or Add all related tables.
Hierarchies generated in SQL, with the HIERARCHY generator function, are not supported.