How to connect Google Cloud SQL with Helical Insight

Helical Insight an Open Source Business Intelligence (BI) tool which supports Cloud SQL – Google Cloud Platform. In this article we will cover in detail, how you can connect, analyze, share and use other features from Cloud SQL using Helical Insight Business Intelligence Tool.

Prerequisites :

1. SQL instance on Google Cloud Platform

Following are the steps to create report with Helical Insight using Google Cloud SQL:

1. Datasource Creation
2. Metadata Creation
3. Adhoc Reports

Create Datasource

This process invloves establishing a connection between Helical Insight application and a Source Databases.

There are 2 methods available for creating a Datasource in Helical Insight:

1. Front End Approach
2. Back End Approach

Following are the steps shown below to create datasource using available methods :

Method 1 : Front End Approach

1. 1. Now, Login to Helical Insight Application.  ( For default credentials, click here )
   2. Click on “Home” button and select “Adhoc” from the list.
   3. On entering Adhoc Page, click on “Create” button in Datasources.

1. 1. Select the “DataSource Type” from the given list. In this case, “Managed Datasource” will be used.
   2. Now, enter the following details :

Datesource Name : GoogleMySQLCloud  ( any name can be assigned )
Driver : com.mysql.jdbc.Driver
HostName : 192.168.5.10 ( can vary )
Port : 3306
Database Name : SampleDB
URL : jdbc:mysql://192.168.5.10:3306/SampleDB

1. 1. Now, enter database username and password
   2. Click on “Test Connnection” to validate the connectivity.
   3. Now, click on “Save Datasource“.

1. 1. From the same page, click on “Metadata” ( at the top right )

1. Then, click on “Create”.

Further steps will be covered in “Create Metadata” step.

Method 2 : Back End Approach

1. 1. Go to “hi-repository” folder where a Helical Insight Application is installed and then open the folder.

C:\Helical Insight\hi-repository

1. 1. Create a New Folder with any name say “Google Cloud SQL” inside “hi-repository” folder.

1. 1. Now, open a text-editor like notepad, notepad++ , and so on.
   2. Then enter the following details as given below :

<EFWD>
      <DataSources>
           <Connection id="1" name="Google Cloud SQL EFWD" type="sql.jdbc">
                <Driver>com.mysql.jdbc.Driver</Driver>
                <Url>jdbc:mysql://192.168.5.10:3306/Sampledb</Url>
                <User>testuser</User>
                <Pass>testuser</Pass>
           </Connection>
      </DataSources>
</EFWD>

1. 1. • Connection ID : User can assign any ID (numeric only)
      • name : Name of the Datasource to be shown in the Datasource list
      • Url : For creating a URL, use the following MySQL JDBC URL format

jdbc:mysql://ip_address/database-name

1. 1. • User: Database Username
      • Password: Database password

1. After entering the details, save the file as “filename.efwd”

   Note: Saved file has to be present inside the “Google Cloud SQL” folder of “hi-repository“.

2. Now, the Google Cloud SQL connection has been established. ( if entered details are correct )
3. Then, next step involves creation of metadata.
4. Now, login to Helical Insight Application using any web browser.
5. Click on “Home” button and select “Adhoc” from the list.
6. Then, Click on “Metadata” Tab and then select “Create”.

Further steps will be covered in “Create Metadata” step.

Create Metadata

For creating a metadata it is mandatory to have established datasource connection which is explained in create datasource.

1. 1. On clicking “Create” from “Metadata” tab, “Datasource Type” selection page is displayed.

1. 1. Select the “DataSource Type” from the given list.
      • For Method 1 : Select DataSource Type: Managed Datasource
      • For Method 2 : Select DataSource Type: Plain JDBC Datasource

1. 1. Click on the Datasource Name> using which metadata will be created.
      • For Method 1 : Datasource Name “GoogleMySQLCloud” is used. Any name can be assigned during configuration.

1. 1. • For Method 2 : Datasource Name “Google Cloud SQL EFWD” is used. Any name can be assigned during configuration.

1. Now , a metadata page appears where various operations can be performed such as table/column aliasing, defining joins, creating views and apply table/column/data security conditions.
2. Click on “Save” to create metadata.
3. After clicking “Save” button, a window will appear known as “File Browser”. Using File Browser, a metadata file is saved in the file-repository of Helical Insight.
4. Then, click on “Save Metadata” to confirm.
5. Now, metadata file is ready for creating Adhoc Reports.

Adhoc Reports :

Learn how to create Adhoc Report, click here

For further assistance, kindly contact us on support@helicalinsight.com or post your queries at forum.helicalinsight.com