2a. Create a DB Connection and Data Source

Owned by Lakshminarayanan1 S CTOO (Unlicensed)
Last updated: Dec 01, 2021

You can easily create a database connection in Canvas Studio with viable and feasible configurations. To serve the purpose of establishing a connection with your schema, a database connection is created to facilitate the process of fetching the data from the schema. To create a database connection and DB data source, refer the following sections: 

A brief overview on creating a database connection in Canvas Studio is provided as follows:

  1. In Canvas Studio, click Create > Connection. 
  2. In the Create New Connection screen, select your database management system, e.g. MySQL. 
  3. Provide a unique name in the Connection Name field. 
  4. Provide the appropriate Host Name, Port number, DB/Schema Name, User Name and Password.
  5. To test the connection, click Test. 
  6. Once the connection gets tested successfully, click Save to save the connection. 

Create a DB Connection

The step-by-step procedure to create a database connection in Canvas Studio is listed as follows:

  1. Log on to Canvas Studio with your credentials.



  2. On the home page of Canvas Studio, click Create > Connection.



    The Create New Connection page appears. 



  3. In the Create New Connection page, in the Connection Type, click Data Base.

  4. Enter a unique name for the connection in the Connection Name field, e.g. NEW CONNECTION.

  5. Click the database used for your application, e.g. MySQL.  

  6. Enter the server name that hosts the database in the Host Name field.

  7. Enter the server port number in the Port field 

  8. Enter the database or the schema name in DB/Schema Name field, e.g. smartbank. 

  9. Enter the user name to connect to the database in the User Name field.

  10. Enter the password to connect to the database in the Password field.

    To create connections on the SIR environment, it is required to use Natted IP information from the SIR's database server instead of the IP address from SQL developers. Kindly contact the IT team for the Natted IP information for any specific SIR environment. 

  11. Click Test. 

    If the provided credentials are correct, a successful message appears else a failure message appears. If a failure message appears, enter the correct credentials and click Test again.



  12. Click Save.



    A confirmation message, indicating a successful save, appears. 



  13. Click Ok. 

Create a DB Data Source

You can easily create a DB data source in Canvas Studio with viable and feasible configurations. Data sources serve as the vital cog in facilitating the process of fetching the data from the various listed tables, present in the database. 

A brief overview on creating a DB data source in Canvas Studio is provided as follows:

  1. In Canvas Studio, right-click the existing connection and select the Create Data Source option. 
  2. In the Create Data Source screen, provide a unique name in the Data Source Name field. 
  3. Click the Object Name lookup field and select the table, from which you want to fetch the data. 
  4. Click the Column IDs field and select the columns of your choice from the selected table. To select all the columns of the table at the same time, click Select All option. 
  5. Click Save to save the newly created data source. 


The step-by-step procedure to create a DB data source in Canvas Studio is listed as follows:

  1. In Canvas Studio, click Connections on the left pane.
    The existing connections are listed in the Connections and Data Sources on the right pane.



  2. Right-click the connection and select Create Data Source.



    The Create New Data Source page appears.


  3. In the Create Data Source page, enter a unique data source name in the Data Source Name field, e.g. ACCOUNT SUMMARY DS. 

    The Is SQL? toggle is an optional field. This field is used to facilitate the purpose of using SQL statements. 




  4. Click the Object Name lookup field and select the table, view, or stored procedure name from which you need to fetch the data. 

    Stored procedures are enabled for reports only and are yet to be enabled in App Designer. The input parameters for stored procedure can be passed during the configuration of the base report in Canvas Studio and the values can be altered in Sigma screen as well. Refer Creating a Data Source using Stored Procedure for more information on creating a datasource using stored procedure.




  5. Click the Column IDs field and select the columns to be fetched.

    To quickly select all the columns for the report, click the Select All () icon. To remove all the selected columns, click the Remove All () icon. To select specific columns, select the checkbox adjacent to the column names as shown in the following screen shot:


    The Where Clause and Order By fields are optional. 


  6. Click Save. 



    A confirmation message, indicating a successful save, appears. 



  7. Click Ok.