How to Combine Tables from Multiple SQL Server Databases in Bold BI Using a SQL View

When data is stored across multiple SQL Server databases, Bold BI does not automatically model those tables as a single dataset through standard table selection. A recommended approach is to create a SQL View that performs the cross-database join at the SQL Server level and then connect Bold BI to that view.

Prerequisites

Both databases must be accessible from the same SQL Server instance.
The database user must have SELECT permission on all participating tables.
The database user must have permission to create views in the target database.
Join columns should use compatible data types.
This addresses common issues users encounter.

This approach offers the following benefits:

Keeps join logic within SQL Server.
Simplifies dataset design in Bold BI.
Prevents duplicate joins and data inconsistencies.
Improves maintainability and performance.
In this example:
Database1: SalesDB contains the Orders table.
Database2: CustomerDB contains the Customers table.
A SQL view combines both tables into a single dataset that can be consumed by Bold BI.

Step-by-Step Instructions

1. Confirm Databases Are on the Same SQL Server Instance

Cross-database views using three-part naming (Database.Schema.TableName) require the databases to be accessible from the same SQL Server instance. If the databases are hosted on different SQL Server instances, consider using Linked Servers or ETL processes before exposing the data through a view.

2. Create a SQL View for the Cross-Database Join

Create the view in a reporting database whenever possible to separate reporting workloads from transactional workloads.

In this case, the view is created in the SalesDB.

Example: Cross-Database SQL View

CREATE VIEW dbo.vw_OrdersWithCustomers
AS
SELECT
    o.OrderID,
    o.OrderDate,
    o.Amount,
    o.CustomerID,
    c.CustomerName,
    c.Country
FROM SalesDB.dbo.Orders AS o
INNER JOIN CustomerDB.dbo.Customers AS c
    ON o.CustomerID = c.CustomerID;
GO

This view combines the data from both databases and exposes it as a single object.

3. Validate the View in SQL Server

Run a test query to ensure the join returns correct data without duplicates or missing rows.

Example Code:

SELECT TOP 100
      OrderID,
      OrderDate,
      CustomerName,
      Amount
FROM dbo.vw_OrdersWithCustomers
ORDER BY OrderDate;

4. Create a new Data Source in Bold BI

Log in to the Bold BI application using an account that has the required permissions to create data sources.
In the left navigation pane, click Data Sources.
Click the New Data Source drop-down button.
From the drop-down menu, select Create Data Source.
In the Search Data Source box, enter MSSQL.
Locate the Microsoft SQL Server connector in the filtered results.
Click the Microsoft SQL Server connector to continue.
Enter a name for the data source.
Specify the SQL Server instance in the Server Name field.
Select the appropriate Authentication Mechanism and provide the required credentials.
Choose the database where the vw_OrdersWithCustomers view was created.
Click Connect to load the database schema.

Note: Ensure you select the database that contains the vw_OrdersWithCustomers view. In this example, the view was created in the SalesDB database.

5. Select the SQL View as Your Dataset

In the schema list, locate and select your view (e.g., vw_OrdersWithCustomers).
Drag and drop it onto the designer canvas.
Preview the data to confirm correct results (no duplicates or missing rows).
Save the data source.

6. Use the Unified Dataset in Dashboards

Build charts, grids, KPIs, and other widgets using fields from the combined view.

Performance Best Practices

Best Practice	Why It’s Important
Create indexes on join columns (for example, `CustomerID`)	Improves join performance by allowing SQL Server to locate matching records more efficiently.
*Avoid `SELECT ` in production reporting**	Retrieves only the required columns, reducing data transfer and query processing overhead.
Filter archived or historical data when possible	Limits the amount of data processed, resulting in faster queries and dashboard load times.
Consider indexed views where appropriate	Can improve performance for frequently accessed reporting queries by storing precomputed results.

Note: Indexed views can improve query performance but may increase storage requirements and maintenance overhead when underlying data changes.

Was this article useful?

Dislike

Written by Erick Oloo Awino

Updated:

Comments (0)