Context:
Consider the following scenario:
CompanyX has been a SAP customer in the Business Intelligence (BI)/Analytics space for quite some time now. They have purchased licenses for many of the BI offerings from SAP like SAP Business Objects Lumira. CompanyX plans to embark on their digital transformation journey and have decided to go with the SAP HANA Cloud Platform (HCP) and SAP HANA.
Henry, The CIO of CompanyX has contacted SAP and purchased a HCP package with a HANA database provisioned in it. With the data now being collated in the HANA database on HCP, Henry wants to understand if he can reuse his existing BI tools and licenses to connect to the database on cloud for his data visualization and reporting purposes.
This is a question that we encounter very frequently when customers try to consume HANA databases on HCP using their on-premise Business Intelligence tools, on-premise data replication tools like SAP Landscape Transformation (SLT), etc. Let us see how we can achieve this…!!
SAP provides a unique tool called as the SAP HANA Cloud Platform, cloud connector or the SAP HANA Cloud Connector (henceforth just called Cloud Connector).
Cloud Connector is a simple on-premises integration agent that allows highly secure and reliable connectivity between cloud applications and on-premises systems. Although the cloud connector is used to consume services and systems from on-premise to build cloud applications and extensions, it also brings in a useful concept called the Service Channel.
A Service Channel allows you to access your cloud databases as if they’re running locally in your network/on-premise. This also means that existing BI tools, database or replication tools can be used to connect to HANA databases running in your HCP account(s).
Recommended Solution:
Henry will need to download and setup the cloud connector within his network, to connect to the HCP Account. He will then, need to create a Service Channel on the cloud connector, to expose the HANA database running on HCP. Now any database or BI or replication tools running on-premise can access this database via JDBC.
Step-by-step guide:
Scenario Description:
This blog will demonstrate a scenario where we will create a HANA MDC on the HCP trial landscape (with SHINE installed) and expose this database using the service channel on the cloud connector and use a BI client like Lumira desktop client, to create a visualization on an existing EPM HANA model.
Prerequisites:
◈ The user should have an account in the HCP trial landscape.
◈ The user should have the SAP HANA Cloud Connector (> version 2.9) setup on his system.
◈ The user should have a BI client like Lumira desktop client installed on his system.
Execution:
First, let us login to the HCP trial account and create a HANA MDC database.
To login, go to https://account.hanatrial.ondemand.com and login with your HCP account credentials, created as a part of the prerequisite.
On logged in, select the account that has been created. If your user is p<user>, then the account name will be generally in the form p<user>trial. Click on the account name link to get into the account.
Navigate to Persistence -> Databases & Schemas and click on New.
In the dialog, change the Database System to HANA MDC <trial> and provide a Database ID. Next provide the SYSTEM User Password and Repeat Password. Ensure Web Access is ON. Click on Save.
The Events page is displayed showing the progress of the database creation. Click on Overview and wait till the status becomes STARTED.
The HANA MDC is created!! Now we will login to the database and check if SHINE (SAP HANA INteractive Education) is installed.
Since the HCP release of 20th Oct 2016, SHINE should be available by default on all MDCs created in the trial landscape. We will also generate some sample data using the Data Generator inbuilt to SHINE, which will help us in visualizing the data in the BI client.
Click on the link SAP HANA Cockpit.
In the SAP HANA Logon screen, enter the username as SYSTEM and the password you provided when creating the MDC in the steps above. Click on Logon
You will receive an informaion pop-up stating that your user does not have the authorizations to access the cockpit and the necessary roles will be assigned to you. Click on OK. You will receive another pop-up showing the roles assigned to your user. Note them for your information and click on Continue.
The cockpit now opens. Click on the tile Manage Roles and Users.
The HANA Web IDE Security page opens. Navigate to Users -> SYSTEM and open the user details. Click on + button in Granted Roles to add the necessary roles to your user.
Search for democontent and select both the Admin and User roles. These are roles required to generate data and view the SHINE content. Click on OK.
Save the SYSTEM user. The required roles are now added.
To open the SHINE data generator open the URL (login if necessary) https://<HANA_MDC_Name><HCP_Trial_Account_Name>.hanatrial.ondemand.com/sap/hana/democontent/epm/admin
In the Settings select Generate Data, enter the number of Purchase and Sales orders as needed and click on Execute.
You will receive a confirmation pop-up, click on OK and the records will get created as indicated by the Progress Log.
Great! SHINE is configured and the required data is generated. Now we will expose the MDC using the Cloud Connector Service Channel.
Login to the SAP HANA Cloud Connector by opening the URL https://<host>:8443, where <host> is the name of host where cloud connector is installed. Example: https://localhost:8443. Provide the user as Administrator and the password (if not changed, default is manage). Click on Login
Click on Connector in the navigation.
Check in the Account Dashboard that the connection to hanatrial is listed and click on the (>) icon to open the details of the account.
In the details check that the Connector State is Green and the status is Connected.
Click on the Account: <trial account name> in the navigation.
Click on On-Premise to Cloud in the navigation to go to Service Channels section and click on + (Add) . In the Add Service Channel dialog, select the Type: as HANA Database and click on Next.
Important Step: Enter the HANA Instance Name: as the HANA MDC database id which you provided when creating it. Remember the name is case sensitive. Choose a Local Instance Number: and enter it. The local instance number is a two digit number (between 00 and 99) and it should not match the instance number of the HANA instance on the cloud. Just to be safe, enter it as 99 (If you get an error check with another 2 digit number). Leave the Enabled as is and click on Finish.
The Service channel should be created, exposing the HANA on cloud as an on premise (local) database and should show the state as Green. Note that the local instance number has been used to generate the JDBC port to connect to HANA as 3<instance number>15.
All the configuration of creating a HANA MDC, generating data and exposing the service channel is complete. Now we will consume this service channel using an On-Premise BI client (in our case Lumira Desktop client).
Open the Lumira desktop client
Once it opens, go to File -> New and in the Add new dataset dialog select the source as Connect to SAP HANA and click on Next.
In the connector page, select Connect to as HANA, enter the Server as localhost, enter the instance as the two digit number provided as the local instance number (in our case 99). Enter the User as SYSTEM and its password. Click on Connect.
If the connection goes through, the SHINE views in the database should be listed. SHINE has some pre-built views and models, which we will use for demo scenario. Select the HANA analytical view PURCHASE_OVERVIEW view and click on Next.
Select the Measures as NETAMOUNT and TAXAMOUNT , select the Dimension as COMPANYNAME and click on Create.
The connection gets created and the Visualize section opens. Select the chart type as Bar Chart. Drag and drop the measures into X Axis and the dimension into Y Axis. You should be able to see a beautiful visualization.
Done!
You have now created a HANA MDC (with SHINE installed), loaded data into it, created a Service Channel using the Cloud Connector and consumed the channel using an on-premise BI client.
This blog is an indicator of the process that can be used to connect to On-premise database tools like SQL clients, development tools like Eclipse and replication tools like SLT, BI Clients; basiclly tools which can use JDBC with a HANA database on the SAP HANA Cloud Platform.
No comments:
Post a Comment