Documentation
Demos
Support
Blog
Start Free Trial
Search results

Dynamic Connection String

The Dynamic Connection String feature allows you to modify the connection string of data sources in dashboard view mode. This feature enables you to view the modified connection string on the Bold BI application itself and on embedded dashboards using the Dashboard Embedding feature.

NOTE: To embed the Bold BI application, please refer Embed Bold BI documentation.

How Dynamic Connection String Works

When creating live mode connections, it is necessary to configure a Web API that will provide the modified connection string.

The configured API will be triggered for each data source created with Dynamic Connection String Configuration during dashboard rendering. The connection string in the data source will be updated and the dashboard will be rendered based on the response from the API.

If the API cannot be accessed or if the API returns any errors, the dashboard will display an error message indicating that the connection is invalid.

How to Configure the API details for a data source

  1. Create a live mode connection with any database management systems such as MSSQL, MySQL, PostgreSQL, etc.

  2. In the connection input form, click the Dynamic Connection String switch to enable the Dynamic Connection String feature for the data source.

    Dynamic Connection String

  3. Click the Configure button to open the dialog for Dynamic Connection Configuration.

    Dynamic Connection String Configuration

  4. In the Dynamic Connection Configuration dialog, the External API option will be selected by default. Please fill out the form for the Web API and click Save. The API will be validated by triggering a Head request. Please ensure that the provided API supports the HTTP HEAD method.

  5. At the bottom of the dialogue, there is a pinned option to choose the Configuration Mode and User Identity for the Dynamic Connection Configuration.

    Configuration Mode

    The Dynamic Connection String can function in different modes, which can be used to limit its functionality to the embedded application, the server, or both.

    Embedding The dashboards can only be rendered with a modified connection string when they are embedded using the Dashboard Embedding feature.
    Server The Bold BI application only allows for rendering the dashboards with a modified connection string.
    Both It is possible to render the dashboards with a modified connection string on both the Bold BI application and dashboards embedded using the Dashboard Embedding feature.

    User Identity

    The identity that should be passed to the custom Web API is meant to enhance the usage of a modified connection string at runtime. This can hold the logged-in user’s information, such as their Email and Full Name.

  6. After saving the configuration, please proceed with the creation of the data source.

How to use Custom Attribute in Dynamic Connection String

  1. At the beginning of the dialogue, there is a radio button that allows you to select either the External API or Custom Attribute for the Dynamic Connection Configuration.

    Dynamic Connection String

  2. Select the Custom Attribute option for the Custom Attribute in Dynamic Connection Configuration.

    Dynamic Connection String

  3. Please enter the attribute name in the Custom Attribute section, which was created on the site. Ensure that the custom attribute was created at the Site Level.

  4. At the bottom of the dialog, there is a pinned option to choose the Configuration Mode. In Configuration Mode, select the Both option and click Save.

  5. After saving the configuration, follow the Uses of custom attribute in configuring custom attribute to complete the configuration.

NOTE: The configuration can be modified or removed at any time by accessing the Edit Connection dialog for the data source.

How to create the Web API

We can utilize any technology to develop the Web API. The return type and content type of the API should be JSON.

The Dashboard Service will pass the following arguments when triggering the API.

Arguments

requiredParams Comma separated values. The API should return the value with the keys mentioned in the argument.

Eg: MSSQL required Parameters for the connection string are DataSource, InitialCatalog, Username, Password, IntegratedSecurity,AdvancedSettings, CommandTimeout, Schema

datasourceName Name of the data source
datasourceId GUID of the data source as string.
customIdentity The string value that holds the user identity information based on the identity type chose on Dynamic Connection String Configuration. Also it can be override while the dashboard embedded using the Dashboard Embedding feature
identityType The string value that holds the type of the user identity chose on Dynamic Connection String Configuration either `Email` or `Full Name`. Also it should be `Custom` while custom identity is passed on dashboard embedded using the Dashboard Embedding feature

NOTE: During the validation of the HEAD request, the above arguments will not be passed to the API.

Parameters for the connection string

Data Source Parameters
Amazon Athena Database,RegionEndpoint,OutputLocation,AwsAccessKeyId,AwsSecretAccessKey
Amazon Aurora MySQL ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
Amazon Aurora PostgreSQL ServerName,Database,UserName,Password,Port,SslMode,TrustServerCertificate,AdvancedSettings,CommandTimeout
Amazon RDS MariaDB ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
Amazon RDS MySQL ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
Amazon RDS PostgreSQL ServerName,Database,UserName,Password,Port,SslMode,TrustServerCertificate,AdvancedSettings,CommandTimeout
Amazon RDS SQL DataSource,InitialCatalog,UserName,Password,IntegratedSecurity,AdvancedSettings,CommandTimeout
Amazon Redshift ServerName,Database,UserName,Password,Port,SslMode,TrustServerCertificate,AdvancedSettings,CommandTimeout
Azure Synapse Analytics DataSource,InitialCatalog,UserName,Password,IntegratedSecurity,AdvancedSettings,CommandTimeout
CDATA with MySQL endpoints ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
ClickHouse ServerName,Database,Port,UserName,Password,AdvancedSettings,CommandTimeout
Elasticsearch ServerName,Database,Port,UserName,Password,AuthenticationType,AdvancedSettings,ConnectionType
Google BigQuery (This is supported only for Service Account Authentication) ServiceId, Service, Provider, Database, IsServiceAccount, ClientID, Type, ClientEmail, PrivateKey
Google Cloud MySQL ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
Google Cloud PostgreSQL ServerName,Database,UserName,Password,Port,SslMode,TrustServerCertificate,AdvancedSettings,CommandTimeout
InfluxDB ServerName,Database,UserName,Password,Port,AuthenticationType,AdvancedSettings,CommandTimeout
MariaDB ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
ODBC ServerName,Port,UserName,PassWord,Database,DsnName,odbctype,Driver,AdvancedSettings,CommandTimeout
Oracle ServerName,Port,Service,Database,UserName,Password,AdvancedSettings,CommandTimeout
PostgreSQL ServerName,Database,UserName,Password,Port,SslMode,TrustServerCertificate,AdvancedSettings,CommandTimeout
Presto ServerName,Port,UserName,Password,Database,CatalogName,AuthenticationType,AdvancedSettings
SingleStore ServerName,Database,UserName,Password,Port,AdvancedSettings,CommandTimeout
SnowFlake ServerName,Database,UserName,Password,AdvancedSettings,CommandTimeout
SparkSQL ServerName,Port,Database,UserName,Password,AuthenticationType,AdvancedSettings
SQL server DataSource,InitialCatalog,UserName,Password,IntegratedSecurity,AdvancedSettings,CommandTimeout

Return Type

Type: ApiResponse class


    public class ApiResponse
    {
      public bool Status { get; set; }

      public string Message { get; set; }

      public object Data { get; set; }
    }

The API should return the response as the above Class object.

APIResponse Class Properties

Status Represents whether the action is successful or not. Set as true if the response is valid connection string.
Message Holds the status message it can be a simple “Success” or an error message when the status is false.
Data Holds the Connection string builder data.

Example Response

    {
      Status: true,
      Message: "Success",
      Data: {
              DataSource: "<server>",
              InitialCatalog: "<database>",
              Username: "<username>",
              Password: "<password>",
              IntegratedSecurity: "false",
              AdvancedSettings: "",
              CommandTimeout: "300"
      }
    }

NOTE: If the response or the connection string is invalid, then the dashboard will not be rendered, and an error message will be displayed on the widgets.

Data Retrieval Error

How to pass the Custom Identity to the viewer Service

  1. The custom identity can only be passed to the viewer when the dashboard is embedded using the Dashboard Embedding feature.

  2. The custom identity should be passed through the dynamicConnection API.

Example

    var dashboard = BoldBI.create({     
   dynamicConnection: {
        isEnabled: true,
        identity: "",
   }
   });
   dashboard.loadDashboard();

NOTE: The value of the identity property should be a string, which will not be processed at the Bold BI end. This information will be passed to the API without any modifications.

Dynamic Connection String Configuration within same Database

  1. To configure the dynamic connection string for a dashboard’s data source using the same database with a different schema, we can utilize the necessary “Schema” parameter in the modified connection string.

  2. The API response from the Custom Web API should include the connection parameter schema in the modified connection string.

Example Response

    {
      Status: true,
      Message: "Success",
      Data: {
              DataSource: "<server>",
              InitialCatalog: "<database>",
              Username: "<username>",
              Password: "<password>",
              IntegratedSecurity: "false",
              AdvancedSettings: "",
              CommandTimeout: "300",
              Schema: "<schema>"
      }
    }

Custom Attribute support for Dynamic Connection string

Dynamic Connection String support provides another way to connect the data source using Custom Attributes.

You can edit a data source by following these steps:

  1. Please add the custom attribute at the User level, Group level, or Site level.

Custom Attribute Configuration

Please enter the data source connection string in JSON format in the Custom Attribute window.

Dynamic connection string supported Datasources

Data Source Connection String
  • SQL server
  • Amazon RDS SQL
  • Azure Synapse Analytics 
    •   {
   
      "DataSource": "",
      "InitialCatalog": "",
      "UserName": "",
      "Password": "",
      "IntegratedSecurity": "",
      "AdvancedSettings": "",
      "CommandTimeout": ""
  }
  • PostgreSQL
  • Amazon Redshift
  • Amazon RDS PostgreSQL
  • Google Cloud PostgreSQL
  • Amazon Aurora PostgreSQL 
    •   {
   
      "ServerName": "", 
      "Database": "",
      "UserName": "",
      "Password": "",
      "Port": "",
      "AdvancedSettings": "",
      "CommandTimeout": "",
      "Schema":""    
  }
  • MySQL
  • MariaDB
  • SingleStore
  • Amazon RDS MySQL
  • Amazon RDS MariaDB
  • Google Cloud MySQL
  • Amazon Aurora MySQL
  • CDATA with MySQL endpoints
  • ClickHouse 
    •   {
   
       "ServerName": "",
       "Database": "",
       "UserName": "",
       "Password": "",
       "Port": "",
       "AdvancedSettings": "",
       "CommandTimeout": ""
  }
    Oracle 
      {
   
       "ServerName": "",
       "Port": "",
       "Service": "",
       "Database": "",
       "UserName": "",
       "Password": "",
       "AdvancedSettings": "",
       "CommandTimeout": ""
  }
    InfluxDB 
      {
   
       "ServerName": "",
       "Database": "",
       "UserName": "",
       "Password": "",
       "Port": "",
       "AuthenticationType": "",
       "AdvancedSettings": "",
       "CommandTimeout": ""
  }
    Amazon Athena 
      {
   
       "Database": "",
       "RegionEndpoint": "",
       "OutputLocation": "",
       "AwsAccessKeyId": "",
       "AwsSecretAccessKey": ""
  }
    Elasticsearch 
      {
   
       "ServerName": "",
       "Database": "",
	   "Port": "",
       "UserName": "",
       "Password": "",
	   "AuthenticationType": "",
       "AdvancedSettings": "",
       "ConnectionType": ""
  }
    ODBC 
      {
   
       "ServerName": "",
       "Database": "",
	   "Port": "",
       "UserName": "",
       "PassWord": "",
	   "DsnName": "",
	   "odbctype": "",
	   "Driver": "",
       "AdvancedSettings": "",
       "CommandTimeout": ""
  }
    Presto 
      {
   
       "ServerName": "",
       "Database": "",
	   "Port": "",
       "UserName": "",
       "Password": "",
	   "AuthenticationType": "",
       "AdvancedSettings": "",
       "CatalogName": ""
  }
    SparkSQL 
      {
   
       "ServerName": "",
       "Database": "",
	   "Port": "",
       "UserName": "",
       "Password": "",
	   "AuthenticationType": "",
       "AdvancedSettings": ""
  }
    SnowFlake 
      {
   
       "ServerName": "",
       "Database": "",
       "UserName": "",
       "Password": "",
       "AdvancedSettings": "",
       "CommandTimeout": ""
  }
    Google BigQuery 
      {

     "ServiceId" = "",
     "Service" = "",
     "Provider" = "",
     "Database" = "",
     "IsServiceAccount" = "",
     "ClientID" = "",
     "Type" = "",
     "ClientEmail" = "",
     "PrivateKey" = ""
  }

    NOTE :This is supported only for Service Account Authentication

    1. Open the Dynamic connection string configuration window and navigate to the Custom Attribute option.

    2. Please enter the valid custom attribute in the window provided and click Save.

    Custom Attribute Configuration with Dynamic Connection String

    Please refer to the help document below for instructions on configuring Custom Attribute. Click on the custom attribute for more information.

    Previous
    Next