In the first part of these blog series I gave a high level introduction of Birst. In this second blog I would like to show you how to connect to HANA via Birst and how to use HANA as a Live source for your models. There are also possibilities of connecting to a BW system and to connect to the DSOs which reside in BW. A likely scenario when you’ve implemented the LSA++ concept where you would no longer report on the reporting layer, but on the underlying DSOs. Additionally, Birst could also connect to a BEx query via a MDX connection.
Birst provides data extraction and connectivity options for a wide variety of databases, flat and structured files, as well as popular cloud and on-premises applications. You can query on-premises data sources in real-time with Birst Live Access, directly from the Birst Business Model (semantic layer)—without the need to extract it to the Cloud. For the latter scenario, BirstConnect is the tool which will make the connection to required datasources.
Before being able to extract data into the Birst cloud, you would have to make some settings, mainly creating a “Birst space”. A Birst space makes it possible to group data sources, models and reporting which logically would belong together.
In our example where I would be connecting to HANA as a Live data source, I choose the option “Discovery”, followed by the Admin option to get data from one of my HANA sources.
In the “Define sources” tab, I need to create a new configuration which I’ve named HANA. If you would press the launch button, this would start a BirstConnect, but one which cannot be directly used. The HANA connection in BirstConnect requires a file (ngdbc.jar) which comes with the HANA client tools. Due to some legal restrictions this cannot be part of the installation of Birst, therefore you would have to download a local copy of BirstConnect and copy the required file to it.
Place the file in the “lib” directory of BirstConnect:
The second step you need to do in order to be able to run the downloaded BirstConnect is to make settings which point to your Birst environment. You do that by downloading the so-called “jnlp” file from Birst. After pressing launch in the Birst admin console (in my case on the created “HANA” connection you download the jnlp file and place that in the root directory of BirstConnect:
We’re almost there. Last step is to make changes to a batch file which will be fired off to make the connection for us. In the commandline folder of BirstConnect you will find a file called cmdUI.bat which needs to be changed:
set JAVA_HOME=C:\Program Files\Java\jdk1.7.0
“%JAVA_HOME%\bin\java” -cp “%BirstConnect_Home%\dist\*;%BirstConnect_Home%\dist\lib\*” -Djnlp.file=”%BirstConnect_Home%\ef989147-2c42-496d-bbe4-4858e58be40c.jnlp” -Xmx1024m com.birst.dataconductor.DataConductorApp
- point BirstConnect to the correct directory where you installed Java. In my case version 7 of the jdk.
- the second part is to point to the install of BirstConnect, in my case it was installed in the root directory
- final part is to point to your jnlp file you just created. After that, we can make our connection to HANA!
After running the cmdui.bat file you will be presented with the following screen where you can make the connection to your HANA environment:
I had quite some issues when using the connection the first time, as I used the HANA “SYSTEM” user. As our HANA system has over 50.000 tables and Birst at the time of this blog has no possibility to limit the number of tables based on the schema, I had to restrict via user authorization. KONIJNR (what’s in a name ;-), has limited access to the HANA tables and is used in the connection.
After pressing the ok and save button, the config will be stored on the Birst server and we are good to go to connect to HANA:
Accessing HANA tables in Birst
Go back to your Birst space and add a new datasource. You will find the defined live source back which can be used to connect:
If you look at the BirstConnect console, you can follow what the connection is doing:
In my example I will use an Analytical view showing “US wage statistics”. Unfortunately Birst has no way to search for a specific table or view. That means that I have to plunge through the list to find the source I need. This is something which in my opinion needs to be improved in a future release.
I can select the table in Birst where the import of the metadata will start:
The nice part of Birst is that it is able to pull in the complete metadata of the used view or table:
However, there are some restrictions. A very notable one is that hidden measures and attributes are still being pulled in, even though they are hidden in the HANA view. This may lead to confusion with end-users. The good part is that Birst is able to remove those columns from the modify data source view in order to circumvent this. A second restriction is that the labels of the fields are not taken over, but the technical names. In real life that would mean that column names will have to be renamed into semanticly friendly names:
An interesting option in Birst is that it can use caching to make results retrieval even faster. I did some checks and making the tick will indeed instantly refresh reports on same selections. To do a small performance test, I will switch it off:
Once the SAP HANA object is in Birst and afjustments have been made, the measures and attributes can be interacted with via the Designer, Visualizer and Dashboard modules.
Using the visualization option and selecting weekly earnings per state name shows the results instantaneously. Not bad for a select on over 2 million records not using the cache!
The query fired off to HANA shows the same instant results:
Thank you for reading this blog!