How to add new Columns, Rename Columns or Tables and Delete Columns in a Data Source Through REST API

Introduction

Bold BI’s REST API provides a seamless way to programmatically interact with the platform, including the ability to rename columns or tables and delete the columns within a data source. This is particularly useful when there have been changes to the underlying SQL database and those changes need to be reflected within Bold BI.

REST API for Obtaining an Access Token

Before making any changes, it is necessary to authenticate with the Bold BI server to obtain an access token. This token must be included in the header of all API requests to ensure they are authorized.

Authentication Endpoint

Request Body Schema

{
    "username": "string",
    "password": "string",
    "grant_type": "password"
}

• username: The email address of the user.
• password: The password of the user.
• grant_type: The type of credentials used to authorize the request for an access token. The value allowed is password.

Response

{
    "access_token": "string",
    "token_type": "string",
    "expires_in": "string",
    "Email": "string"
}

Example

Request	Response

REST API for adding new columns

Once authenticated, you can proceed to rename tables or columns by sending a PUT request to the appropriate endpoint.

API Endpoint

• URL: http://{domain}/bi/api/site/{sitename}/v4.0/datasources/columns
• Method: PUT
• Content Type: application/json
• Authorization: Bearer <access_token>

Request Body Schema

[
    {
        "DataSourceId": "string",
    },
    {
        "DataSourceId": "string",
    }
]

REST API for Renaming Tables and Columns

Once authenticated, you can proceed to rename tables or columns by sending a PUT request to the appropriate endpoint.

API Endpoint

• URL: http://{domain}/bi/api/site/{sitename}/v4.0/datasources/columns
• Method: PUT
• Content Type: application/json
• Authorization: Bearer <access_token>

Request Body Schema

[
    {
        "DataSourceId": "string",
        "ColumnInfo": [
            {
                "Type": "string",
                "SourceTableName": "string",
                "TargetTableName": "string",
                "SourceColumnName": "string",
                "TargetColumnName": "string"
            }
        ]
    },
     {
        "DataSourceId": "string",
        "ColumnInfo": [
            {
                "Type": "string",
                "SourceTableName": "string",
                "TargetTableName": "string",
                "SourceColumnName": "string",
                "TargetColumnName": "string"
            }
        ]
    }
]

Syntax to rename table name only

[
    {
        "DataSourceId": "string",
        "ColumnInfo": [
            {
                "Type": "Table",
                "SourceTableName": "OldTableName",
                "TargetTableName": "NewTableName"
            }
        ]
    }
]

Syntax to rename column name only

[
    {
        "DataSourceId": "string",
        "ColumnInfo": [
            {
                "Type": "Column",
                "SourceColumnName": "OldColumn1",
                "TargetColumnName": "NewColumn1"
            },
            {
                "Type": "Column",
                "SourceColumnName": "OldColumn2",
                "TargetColumnName": "NewColumn2"
            }
        ]
    }
]

Syntax to Rename Both Table Name and Column Name

[
    {
        "DataSourceId": "string",
        "ColumnInfo": [
            {
                "Type": "Table",
                "SourceTableName": "OldTableName",
                "TargetTableName": "NewTableName"
            },
            {
                "Type": "Column",
                "SourceColumnName": "OldColumnName",
                "TargetColumnName": "NewColumnName"
            }
        ]
    }
]

Steps to Rename Table Names

This section provides a step-by-step guide on how to rename table names in MSSQL data sources using Bold BI’s REST API.

Step 1: Obtain Access Token

First, you need to obtain an access token for authentication with the REST API. Use the following endpoint as explained in this article:

http://{domain}/bi/api/site/{sitename}/token

Replace {domain} with your Bold BI server domain and {sitename} with the name of your site.

Step 2: API Endpoint for Renaming Tables

The API endpoint to rename columns or tables is:

http://{domain}/bi/api/site/{sitename}/v4.0/datasources/columns

Step 3: Identify Data Source ID

Locate the data source ID you wish to update by navigating to the data source listing page and clicking on the “info” option.

Step 4: Prepare Request Schema Body

Prepare the request schema body for the API with the following structure. Here is an example of how the request body would look if you want to rename SalesQA to ProductsQA and SalesProd to ProductsProd:

[
	{
		"DataSourceId": "your-data-source-id",
		"ColumnInfo": [
			{
				"Type": "Table",
				"SourceTableName": "SalesQA",
				"TargetTableName": "ProductsQA"
			},
			{
				"Type": "Table",
				"SourceTableName": "SalesProd",
				"TargetTableName": "ProductsProd"
			}
		]
	}
]

Replace your-data-source-id with the actual ID of your data source. Adjust the SourceTableName and TargetTableName as per your requirements.

Step 5: Execute the REST API

Execute the REST API with the prepared request body. Ensure that the request is authenticated using the access token obtained in Step 1.

REST API for Deleting Columns

Once authenticated, you can proceed to deleing columns by sending a PUT request to the appropriate endpoint.

API Endpoint

• URL: http://{domain}/bi/api/site/{sitename}/v4.0/datasources/columns
• Method: PUT
• Content Type: application/json
• Authorization: Bearer <access_token>

Request Body Schema

[
    {
        "DataSourceId": "string",
        "ActionType": "DELETE",
        "ColumnInfo": [
            {
                "Type": "string",
                "TargetColumnName": "string"
            }
        ]
    }
]

Syntax to Delete a Multiple Column Name

[
    {
        "DataSourceId": "string",
        "ActionType": "Delete"
        "ColumnInfo": [
            {
                "Type": "Column",
                "TargetColumnName": "DeleteColumnName"
            },
            {
                "Type": "Column",
                "TargetColumnName": "DeleteColumnName"
            }
        ]
    }
]

Conclusion

By following these steps, you can successfully rename columns/table names and delete columns in your data source using Bold BI’s REST API. This allows for better organization and management of your data within Bold BI.

Additional References

• How to generate Access Token from User Management Server using Bold BI REST API?