Connecting Bold BI to ODBC data source

Bold BI Dashboard designer (Enterprise) supports connecting to datasources through Open Database Connectivity (ODBC).

Note: The ODBC Connector is available in On-Premises Deployment but not in the Syncfusion Managed Cloud Server.

Choose an ODBC data source

The Dashboard Designer (Enterprise) supports connecting to ODBC data sources through the following two connection modes.

• Data Source Name (DSN).

• Drivers

Data Source Name (DSN)

Create a DSN using the 32-bit driver architecture and Bold BI will retrieve the 32-bit DSN architecture types from the windows registry.

• If Bold BI is installed in IIS, it will only show the system DSN because all applications hosted in IIS are considered to be running on the system user privilege.

By default, IIS (inetinfo.exe) is configured to run in the system account context. However, you can use the services console to change the identity of various services so that they can run in a user account rather than the system account. The system DSN will only work under the hosted application in IIS.

• If the Bold BI is installed in IIS Express, we will list out the system and user DSN from the installed machine. So that you can connect using both the system and user DSN.

ODBC Drivers

ODBC data sources can also be connected through drivers. If Bold BI is installed in IIS Express, list the drivers from the installed machine so that you can connect using the driver.

To configure the ODBC data source, follow these steps:

1. Click on the Data Sources button in the configuration panel to add a new data connection.

   

2. Click CREATE NEW to launch a new connection from the connection panel.

   

3. Select the ODBC connection in the connection panel.

   

NOTE: You can also create a data source from the home page by clicking on the Data Sources menu on the left menu panel and selecting Create Data Source from the data sources page.

Connect to ODBC

Create an ODBC data source

After clicking the data source, the NEW DATA SOURCE configuration panel opens. The following ODBC drivers are supported:

• SQL Server
• PostgreSQL
• MySQL
• Oracle
• Exasol
• DB2
• SAP-HANA
• Vertica
• Microsoft Access
• Elasticsearch
• FileMaker
• Firebird
• Azure Databricks (Supported only in DSN Connection Type)

Follow the given steps to create an ODBC data source:

1. Enter a name and description (optional) for the data source.

2. A drop-down box for ODBC Connection Type.

   • DSN
   • Driver

Connect using the DSN Connection Type

1. Choose a DSN name from the drop-down list.

2. Enter a valid ODBC server username in the UserName text box.

3. Enter a valid ODBC server password in the Password text box.

4. Enter a valid ODBC server database name (optional) in the Database text box.

   

In future, you can edit the connection information using Edit Connection option.

Connect using the Driver Connection Type

1. Enter a name and description (optional) for the data source.

2. Enter a valid ODBC server or host name in the ServerName text box.

3. Enter the port in which the server is running in Port text box.

4. Enter a valid ODBC username in the UserName text box.

5. Enter a valid ODBC password in the Password text box.

6. Enter a valid ODBC database name in the Database text box.

   

Data Preview

1. Click Connect to connect the ODBC server with configured details.

The schema represents the collection list that is retrieved from the ODBC server. This dialog box displays the list of schemas in a tree view and their corresponding values.

2. Now, the data design view page with selected table schema opens. Drag the table.

   

   You can use the Code View options for passing query to display data.

   

3. Click Save to save the data source with a relevant name.

Connect using custom attribute and dashboard parameter

We have added support for custom attributes and dashboard parameters in the data source connection. You can now connect to the data source using custom attributes or dashboard parameters.

Custom Attribute

Dashboard Parameter

Note: Refer to the Dashboard Parameter Documentation and Custom Attributes Documentation for more details.

Bold BI hosting with x86 and x64 bit wise for ODBC data source connection

If Bold BI is installed in IIS, we will list the system DSN values from the installed machine. By default, the application will be hosted in 32-bit mode in IIS. Therefore, we will only list the 32-bit System DSN values in the Bold BI ODBC connector.

Following steps to connect the 64-bit values:

1. Open Internet Information Services (IIS) Manager (Start > Run > type ”inetmgr”).
2. Using the navigation pane at the left, browse to Sites and select your Bold BI application site (BoldBIOnPremiseEdition).
3. Right-click the Bold BI application site (BoldBIOnPremiseEdition) and select the Manage Website > Stop.
4. Select the Application Pools and then select and right click BoldBI > Stop.
5. Repeat the step 4 for all other application pools BoldBIDesigner, BoldBIJobs, and BoldUMS.
6. Select the application pool BoldBI and click the Advanced Settings option.
7. Set the “Enable 32- bit Applications” to “False”.
8. Set the same property change in other three application pools (BoldBIDesigner, BoldBIJobs, and BoldUMS) also.
9. Restart the site (BoldBIOnPremiseEdition) and all application pools (BoldBI, BoldBIDesigner, BoldBIJobs, and BoldUMS).
10. If the services are restarted, then go to the ODBC data source to list out the System DSN 64-bit values.

Connecting Bold BI to ODBC Data Source via REST API

Prerequisites

Type while creating the data source needs to be ODBC.

Rest API - v4.0

Parameters for creating Data Source

NOTE: The ability to provide join support is available only during the creation of a new data source. Join in edit connection mode is not supported.

Parameters for edit the connection while upload the dashboard via API

Parameters for adding expressions when creating Data Source

Parameters for editing Data Source

NOTE: To edit a Data Source via API, all parameters are optional. Only the parameter that needs to be changed should be provided.

Parameters for modifying expressions when editing Data Source

Connection Sample for Table Mode

For creating connection using Driver:

"Connection":
{
"Driver": "string",
"Servername": "string",
"Port": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Schemaname": "string",
"Tablename": "string",
"AdvancedSettings": "string",
"CommandTimeout": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}

For creating connection using DSN:

"Connection":
{
"DsnName": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Schemaname": "string",
"Tablename": "string",
"AdvancedSettings": "string",
"CommandTimeout": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}

For creating connection with multiple tables :

"Connection":
{
"DsnName": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Tables": [
            {
                "Tablename": "string",
                "Schemaname": "string"
            },
            {
                "Tablename": "string",
                "Schemaname": "string"
            }
        ],
"JoinInfo": [
            {
                "JoinType": "string",
                "LeftTable": "string",
                "RightTable": "string",
                "JoinFieldInfos": [
                    {
                        "Condition": "string",
                        "LeftField": "string",
                        "Operator": "string",
                        "RightField": "string",
                        "Value": "string"
                    },
                    {
                        "Condition": "string",
                        "LeftField": "string",
                        "Operator": "string",
                        "RightField": "string",
                        "Value": "string"
                    }
                ]
            }
        ],
"AdvancedSettings": "string",
"CommandTimeout": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}

For editing connection:

"Connection":
{
"Driver": "string",
"Servername": "string",
"Port": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Schemaname": "string",
"Tablename": "string",
"AdvancedSettings": "string",
"CommandTimeout": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)",
"NewName" : "Sum",
"Action": "edit"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
"Action": "delete"
}]
}

NOTE: Through the Rest API, the data source can be created or edited with only one table. If different table is provided in edit data source, the table will be replaced. The widgets will be retained only if the schema is same as the previous table.

Connection Sample for Code View Mode

"Connection":
{
"Driver": "string",
"Servername": "string",
"Port": "string",
"Username": "string",
"Password": "string",
"Database": "string",
"Query": "string",
"AdvancedSettings": "string",
"CommandTimeout": "string",
"Expressions" : [{
"Name": "Expression1",
"Expression" : "SUM(numeric expression)"
    },
    {
"Name": "Expression2",
"Expression" :  "UPPER(string expression)"
}]
}

Sample connection for editing the connection while uploading the dashboard via API.

"Connection":  {   
      "ServerName": "string",
      "Database": "string",
      "Port": "string",
      "UserName": "string",
      "PassWord": "string",
      "DsnName": "string",
      "odbctype": "string",
      "Driver": "string",
      "schema": "string",
      "AdvancedSettings": "string",
      "CommandTimeout": "string"
  } 

Related links

Data Transformation

Editing a Data Connection

Dashboard Designer Walkthrough