How to connect multiple Datasource using Helical Insight Application

In our earlier articles, we have talked about how to connect single datasource using Helical Insight. This article talks about how to connect multiple datasources using Helical Insight v1.0.1 .In this case we have referred CSV and MySQL as a datasources.

Steps:

1. 1. Create a folder inside HI Repository. In this case, we have created a folder named “MySQL CSV datasource”.

      C:\Helical Insight\hi-repository\MySQL CSV datasource

1. 1. Inside this folder create another folder and save the CSV inside it. In this case, we have created a folder named “CSV”.

      C:\Helical Insight\hi-repository\MySQL CSV datasoure\CSV

      

   2. Place the CSV which you intend to use in this folder. Please make sure that the CSV which you are putting is having datatype defined in the header.
   3.  

      Datatype format to use in a CSV :

      Column_Name : Datatype_Name

       

      For example, refer to the below in which datatype has been added into the header of CSV.

1. 1. After that create a MySQL-CSV-Model JSON file.

inline: {
  version: '1.0',
  defaultSchema: 'MySQL CSV',
  schemas: [
    {
        name: 'Travel csv',
        type: 'custom',
        factory: 'org.apache.calcite.adapter.csv.CsvSchemaFactory',
        operand: {
            directory: 'C:/Helical Insight/hi-repository/MySQL CSV datasource'
          }
    },
    {
        name: 'Sample Mysql',
        type: 'custom',
        factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory',
        operand: {
            jdbcDriver: 'com.mysql.jdbc.Driver',
            jdbcUrl: 'jdbc:mysql://localhost:3307/SampleTravelData?connectTimeout=10000',
            jdbcUser: 'hiuser',
            jdbcPassword: 'hiuser'
        }
    }
  ]
}

We can enlist as many datasources over here as we want. Since we want to use CSV with MySQL and join them and do certain analysis, two of them have been defined.

1. 1. After that create an EFWD (database connection file) file in

      C:\Helical Insight\hi-repository\MySQL CSV Datasource

      

      <EFWD>
          <DataSources>
              <Connection id="1" name="MySQL CSV Datasource" type="sql.calcite">
                  <model>MySQL CSV datasource/csv-model.json</model>
      	       <driverName>org.apache.calcite.jdbc.Driver</driverName>
              </Connection>
           </DataSources>
      </EFWD>

1. Once the EFWD file is created it will start listing in the Virtual Data Source listing.

Following are the steps to create Report using created Datasource :

1. 1. Go to Metadata

1. 1. Select Datasource Type. In this case datasource type is “Virtual Datasource”.

1. 1. Select the datasource

1. 1. Select the Schemas from the list and then click “Next”. Schemas contain the list of databases.

1. 1. Select the required table from the list which will be used for reporting purpose and then click “Next”.

1. 1. Now, select the required columns from the list and then click “Next”. Here, columns will be displayed based on the table selection.

1. After clicking “Next” a metadata view page will be displayed. In a metadata mode various activities includes such as Table/Column Aliasing, define joins, define views and apply metadata security on table/column.

   

2. Now, define required joins/views between the tables and then click Save.

3. 

   Thus, users can now go to the ad-hoc report interface, connect to this above created metadata and create reports.

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