Introduction
Recently I installed a photovoltaic system at my home with the main goal to consume the produced energy by myself. All energy which cannot be consumed by myself will be feed into the power grid. As I am interested to get an overview about produced, self consumed and feed in energy (not just for fun, but also for tax reasons), but the default reporting options for the system delivered by the supplier are not very user friendly, I decided to push the data to a HANA database in SCP to be able to do my reporting on it. Pushing the data from my local network to the HANA database is done using a simple Node.js program which inserts the data via the same SAP Cloud Connector service channel described later for consuming the data for reporting. The source of the data is a frequency converter which offers a REST interface.
To do my reporting tasks as client I use the free version of Microsoft Power BI Desktop. Of course I could use other tools like SAP BusinessObjects Lumira, but with that I would be limited to a just 30-day trial license. As the free version of Microsoft Power BI Desktop fulfills my requirements and is not limited in usage from a time perspective I used this to implement my simple reporting task.
To make the things clear: This post should not be an advertisement for Microsoft Power BI Desktop or other software. All used software is available for free. Thé post should show only another option to connect a "third-party" too to SAP HANA in SCP.
The following image gives a brief overview about the whole scenario:
Setup
Following the setup of all required software components is described in a brief way.
SAP HANA Database
For my scenario I use a HANA Multitenant Database Container on SCP Trial. Details about the setup can be found in blog post https://blogs.sap.com/2016/01/13/sap-hana-multitenant-database-containers-mdc-scenarios-now-on-trial....
I set up a MDC with the name "pfefferfmdc". The name is relevant later when the SAP Cloud Connector service channel is setup.
It has to be considered that the tenant is shut down after 12 hours. Personally I am using a script which uses the HANA Cloud console client which starts the tenant automatically again when it was shut down.
SAP HANA Client packages including ODBC drivers
The SAP HANA Client packages are necessary on the local machine, cause the Microsoft Power BI Desktop client uses ODBC to connect to the HANA Database. The SAP HANA Client packages (trial version) can be downloaded from the SAP market place. It has to be considered that the right version for the local machine has to be (either Windows or Linux in 32-bit or 64-bit edition).The downloaded file has to be unzipped and file "hdbsetup" has to be executed to install the client tools.
Also the SAP HANA Client packages can be used which are distributed with the HANA Express Edition download.
SAP Cloud Connector + service channel to HANA in SCP
The connection from the local machine to the HANA Database in SCP is established using a service channel setup in the SAP Cloud Connector. Therfore the SAP Cloud Connector has to be installed on the local machine or a machine in the local network.
The SAP Cloud Connector can be downloaded from page https://tools.hana.ondemand.com/#cloud.
Information about installing the SAP Cloud Connector and the initial configuration can be found at https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/57ae3d62f63440f7952e57bfcef... and https://help.sap.com/viewer/cca91383641e40ffbe03bdc78f00f681/Cloud/en-US/ec68ee242c3d4c7797fc53bb65a....
After the SAPCloud Connector is installed, initially configured and connected to the SCP (trial) account, the service channel can be configured in the SAP Cloud Connector menu "On-Premise to Cloud".
The add button has to be pressed to add a new service channel.
As type for the service channel "HANA Database" has to be selected. On the next page, the name of the HANA instance has to be selected or manually inserted. This is the name chosen when the MDC was created. In addition a "Local Instance Number" has to be defined. It is necessary later to connect from Microsoft Power BI Desktop to HANA via the service channel. For my installation I used instance number 51.
Finally, the service channel is created.
Download and install Microsoft Power BI Desktop
Microsoft Power BI Desktop can be downloaded for free for your windows machine from page https://www.microsoft.com/en-us/download/details.aspx?id=45331. Just follow the installation steps to install it on your machine.
Connect Microsoft Power BI Desktop to SAP HANA and create a simple visualization
After the installation of Microsoft Power Bi Desktop and its start, following screen appears when the wizard is skipped. On that screen the "Get Data" functionality has to be called to create a data source.
Choose "SAP HANA Database" and press connect.
On the next screen the HANA database server information has to be added. As the HANA database in SCP via the SAP Cloud Connector service channel has to be used, the hostname is the hostname of the SAP Cloud Connector. As I installed it on my local machine the hostname for my HCC is "localhost". As port the pattern 3xx15 has to be applied, where xx needs to be replaced by the local instance number defined when the service channel was created. As I used 51 as local instance number, the port number is 35115 in my case.
As data connectivity options "Import" and "DirectQuery" are available which allows to import data or direct query data from information models like calculation views. As I have stored my Photovoltaic System values on a daily basis in a table "PHOTOVOLTAIC"."DAILY_VALUES" I used the "Advanced Option" for an SQL statement to do a simple select on that table. The user is which is used to connect to the database (asked on a further screen) needs of course the privileges to select the data.
After that is done, in the Power BI Client a query is available which can be used to create visualizations.
A line chart w/o further formatting (just with renaming of labels) looks like following. For my specific use case it displays me the information about produced, self-consumed and feed in energy (data is restricted to values for January 2017).
Switching between different chart types can also easily be applied (for instance to a bar chart).
Conclusion
Independent of the used client, this post should show how easy it is to access a SAP HANA Database in SAP SCP via a SAP Cloud Connector service channel. If the client is a Node.js program, a SAP Client like SAP BusinessObjects Lumira or like in that case a third-party client like Microsoft Power BI Desktop is not really relevant. I hope this post helps people to think about possible use cases using SAP Software and other software from other vendors. A nice tutorial is also to connect Microsoft Power BI Desktop to a HANA Express Edition installation. For that case no SAP Cloud Connector service channel is necessary of course. The connection can be done easily connecting to the host on which HXE is installed (for instance to connect to the SystemDB on HXE 2.0 with an instance 90 the connection string "hxehost:39013" can be used).
- SAP Managed Tags:
3 Comments
