Search results

Dynamic Connection String

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

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

How Dynamic Connection String Works

While creating live mode connections, you need to configure a Web API that will return the modified connection string.

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

If the API is not reachable or if the API returned any errors then the dashboard will be rendered with an error stating that the connection is not valid.

How to Configure the API details for a data source

  1. Create any live mode connection 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 Dynamic Connection Configuration dialog.

    Dynamic Connection String Configuration

  4. In the Dynamic Connection Configuration dialog, by default, External API will be selected. Fill the form for the Web API and click Save. The API will be validated by triggering the API with a Head request. Make sure that the provided API has HTTP HEAD method support.

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

    Configuration Mode

    The mode in which the Dynamic Connection String should function also can be used to limit the functionality to the embedded application or within the server or both.

    Embedding Allows to render the dashboards with modified connection string only when the dashboards are embedded using the Dashboard Embedding feature
    Server Allows to render the dashboards with modified connection string only on Bold BI application itself
    Both Allows to render the dashboards with modified connection string on both Bold BI application and dashboards embedded using the Dashboard Embedding feature as well

    User Identity

    The Identity that should be passed to the Custom Web API, in order to enhance the usage of modified connection string at run time. This can holds the logged in user information as Email and Full Name

  6. After saving the configuration, complete the data source creation.

How to use Custom Attribute in Dynamic Connection String

  1. At the top of the dialog, there is a radio button to choose the External API or Custom Attribute for the Dynamic Connection Configuration.

    Dynamic Connection String

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

    Dynamic Connection String

  3. Enter the attribute name in the Custom Attribute section, which was created on site. Make sure that the custom attribute was created in Site Level.

  4. At the bottom of the dialog, there is an option pinned to choose the Configuration Mode. In that, select the Both option in Configuration Mode 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 any time by navigating to Edit Connection dialog for the data source.

How to create the Web API

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

The following arguments will be passed by the Dashboard Service 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 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 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,InstanceName,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 is invalid or the connection string is invalid then the dashboard will not be rendered. An error message will be shown on the widgets.

Data Retrieval Error

How to pass the Custom Identity to the viewer Service

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

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

Example

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

NOTE: The value of the identity property should be a string. This string 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. While configuring the Dynamic Connection String for a dashboard’s data source using the same Database with different Schema, we can use the required parameter Schema on the modified connection string.

  2. So the API response from the Custom Web API should includes the connection parameter Schema on 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 using the following steps:

  1. Add the custom attribute in the User level or Group level or Site level.

Custom Attribute Configuration

Enter the data source connection string as 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": "", "SslMode": "", "TrustServerCertificate": "", "AdvancedSettings": "", "CommandTimeout": "" }
  • 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": "", "InstanceName": "", "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": "" }
    1. Open the Dynamic connection string configuration window and then navigate to Custom Attribute option.

    2. Enter the valid custom attribute in that window and click Save.

    Custom Attribute Configuration with Dynamic Connection String

    Please refer the below help document for configuration of Custom Attribute click here.