Use SAP HANA in Power BI Desktop
With Power BI Desktop, you can now access SAP HANA databases. To use SAP HANA, the SAP HANA ODBC driver must be installed on the local client computer in order for the Power BI Desktop SAP HANA data connection to work properly. You can download the SAP HANA Client tools from SAP Development Tools, which contains the necessary ODBC driver. Alternatively, you can get it from the SAP Software Download Center. In the Software portal search for the SAP HANA CLIENT for Windows computers. Since the SAP Software Download Center changes its structure frequently, more specific guidance for navigating that site is not available.
To connect to a SAP HANA database, select Get Data > Database > SAP HANA Database as shown in the following image:
When connecting to a SAP HANA database, specify the server name. Then from the drop down and input box, specify the port.
In this release SAP HANA in DirectQuery mode is supported in Power BI Desktop and the Power BI service, and you can publish and upload reports that use SAP HANA in DirectQuery mode to the Power BI service. You can also publish and upload reports to the Power BI Service when not using SAP HANA in DirectQuery mode.
Supported features for SAP HANA
This release has many capabilities for SAP HANA, as shown in the following list:
- The Power BI connector for SAP HANA uses the SAP ODBC driver, to provide the best user experience
- SAP HANA supports both DirectQuery and Import options
- Power BI supports HANA information models (such as Analytic and Calculation Views) and has optimized navigation
- With SAP HANA, you can also use the direct SQL feature to connect to Row and Column Tables
- Includes Optimized Navigation for HANA Models
- Power BI supports SAP HANA Variables and Input parameters
- HDI-container based Calculation Views
- Support for HDI-container-based Calculation Views is in public preview in the August 2019 release of Power BI Desktop. To access your HDI-container-based Calculation Views in Power BI, ensure that the HANA database user(s) you use with Power BI have permission to access the HDI runtime container that stores the views you want to access. To grant this access, you must create a Role that allows access to your HDI container and assign the role to the HANA database user you will use with Power BI (this user must also have permission to read from the system tables in the _SYS_BI schema, as per usual). Consult the official SAP documentation for detailed instructions on how to create and assign database roles. This SAP blog post may be a good place to start.
- Note that there are currently some limitations for HANA variables attached to HDI-based Calculation Views. These limitations are due to errors on the HANA side and will be addressed in future releases of SAP HANA. First, it is not possible to apply a HANA variable to a shared column of an HDI-container-based Calculation View. This limitation can be fixed by upgrading to HANA 2 version 37.02 and onwards or HANA 2 version 42 and onwards. Second, multi-entry default values for variables and parameters currently do not show up in the Power BI UI. This is also due to an error in SAP HANA, however; SAP has not yet announced a fix.
Limitations of SAP HANA
There are also a few limitations to using SAP HANA, shown below:
- NVARCHAR strings are truncated to maximum length of 4000 Unicode characters
- SMALLDECIMAL is not supported
- VARBINARY is not supported
- Valid Dates are between 1899/12/30 and 9999/12/31
For more information about DirectQuery and SAP HANA, check out the following resources: