Some recommendations in successful connectivity of Explorer with HANA.
Explorer connected to HANA is fast becoming a very popular platform for deploying an accelerated data discovery solution. There is still a great deal of power harnessed with BWA, a stable and mature solution accelerating BW models and agnostic data for Explorer, but the newest in-memory solution with HANA is where most of the attention is given. For this reason I wanted to address some common issues with this setup that I have seen and hopefully answers some questions. I will try to update this blog if I come across new items.
1.Making consistent HANA connections.
In the past, the way connections were specified is via CMC parameters, in CMC -> Applications -> Explorer. This is an older way of specifying connections ( Explorer 4.0 SP02 and older) but still valid. My recommendation is, do not use this method, the preferred way is to specify JDBC HANA connections via Information Design Tool (IDT). One major advantage is that these connections can survive migration to other environments using Promotion Management.
2. Connections show broken (invalid) under Manage Spaces.
There are many reasons for this. The Explorer master server is responsible in generating the data source list which includes HANA connections. When using Internet Explorer if you hover the mouse over the connection sometimes it will specify the reason. Normally a message as such “NewDB server does not respond” is one example. Some things to check for:
- The HANA host is not accessible from the Explorer host. Check network connectivity and port blockage. You may have to add the IP, short name and long name of the HANA server in the hosts file. Try using HANA IP instead of hostname in the connection.
- The user specified in the connection may not have rights to access any of the Analytic or Calculation Views. To test this, use HANA Studio and with the same account right click on the View then select Data Preview to see if you are able to see the data.
- Delete any HANA connections, that are invalid and not needed. If they are needed validate them against a) and b) above to make them valid. I have seen many instances where the master server will flag good connections broken if there are others which are not valid.
Note: To save time in checking connection validity in Explorer, you don’t have to log off and log back in, just close Manage Spaces tab and re-open it, master server will auto rescan the data sources list for changes.
3. It takes a long time for Manage Spaces list to show up.
I have added this section because invalid HANA connections will cause considerable delay in the Manage Spaces list display. By delay meaning over one minute. In reality this should normally take only a few seconds. Invalid HANA connections is one cause for this delay because master server is waiting for the invalid connection to time out. I can say that there is a code change coming in Explorer 4.1 SP03 to handle this specific timeout differently. One exception to the statement that it should take seconds, is that there will be additional delay introduced in instances where there is a really large number of folders in the system (>50K). Master server scans each one looking for Excel files this being another data source option in the list.
4. How to check the list of views that should appear under a connection.
The master server sends out the query below in order to get the list of views available for a specific connection and visible to the user used in the connection. This can be ran in HANA Studio to cross check using same user then check the list generated:
mdx select “COLUMN_OBJECT”, “CATALOG_NAME”, “CUBE_NAME”, “DESCRIPTION” from BIMC_CUBES
5. Query performance checks.
When exploring (loading) an information space there are two queries dispatched to HANA. One for the facets (top section) and one for the visualization (Chart and table section). The facets query will take the most time because it has to aggregate the measure for each facet. To capture the queries, in CMC enable logging to High for the Exploration server and load the information space. In the log file “~\logging\explorerExploration…log” search for string “Execute SQL” and the line will show the select statement dispatched to HANA. The longer query is the one for the facets. Run this query directly in HANA Studio to get an idea of running time and what to expect. Explorer will add a bit of overhead but it should not take much longer than what it takes in Studio. One feature was added to bypass running the facets query till later as far back as Explorer 4. SP06. The option to not load the facets, was introduced specifically for this purpose, a selection available when creating the information space.