Bold BI – MSSQL Stored Procedure: OpenQuery vs Internal Cache Mode

When configuring a stored procedure–based data source in Bold BI, you can choose between two execution modes: OpenQuery and Internal Cache. Understanding these modes helps you optimize dashboard performance and manage load on your MSSQL server effectively.

1. OpenQuery Mode

How It Works

In OpenQuery mode, Bold BI executes the stored procedure directly on the linked MSSQL server for every widget query.

• Each widget generates its own query based on the widget configuration, and the query will be executed on the MSSQL server.
• Every dashboard load or interaction triggers a fresh execution on the MSSQL server.

Example

If the dashboard has 20 widgets:

• On dashboard load, 20 individual queries with stored procedure calls are executed.
• If the user applied sorting or Top N filtering on the dashboard, additional Open calls will be included in the applied widgets.
• Each interaction (filters, sorting, refresh, parameters) re-executes queries on the MSSQL server.

When to Use OpenQuery Mode

Use OpenQuery mode when:

• You need real-time/live data for every interaction.
• The number of users is limited.
• The MSSQL server can handle multiple procedure executions.
• Dashboard data accuracy must always be up-to-the-second.

2. Internal Cache Mode

How It Works

In Internal Cache mode:

1. Bold BI runs the stored procedure once during dashboard load.
2. The retrieved result is stored in a temporary table in the Bold BI data store database, such as MSSQL or PostgreSQL, or MYSQL.
3. All widget interactions (filtering, sorting, grouping) use the temporary table instead of querying the MSSQL server repeatedly.

Example

If your dashboard contains 20 widgets:

• The stored procedure executes only once on the MSSQL server.
• All 20 widgets use the cached temporary table for their queries.
• This significantly reduces the MSSQL server load.

When to Use Internal Cache Mode

Choose this mode when:

• Your dashboard has many users accessing it simultaneously.
• You want to minimize load on the MSSQL server.
• Dashboard interactions don’t require real-time data.
• The dataset can be stored in Bold BI’s internal database.

3. When to Choose Which Mode

Choose OpenQuery Mode If:

• ✅ You need live data during filtering and interactions.
• ✅ You have limited users.
• ✅ Database load is not a concern.
• ✅ You need up-to-date data for every action.

Choose Internal Cache Mode If:

• ✅ Your dashboard has high user traffic.
• ✅ You want to reduce MSSQL database load.
• ✅ Fetching fresh data only during initial load is acceptable.
• ✅ You want faster user interactions (filtering/sorting).

4. Performance Considerations

OpenQuery Mode

Pros

• Real-time data for each request.
• Always reflects updated values.

Cons

• Heavy load on the MSSQL server.
• Not suitable when many users access the dashboard.

Internal Cache Mode

Pros

• Significantly reduces database load.
• Faster widget interactions, since operations happen inside Bold BI.
• Ideal for high-traffic dashboards.

Cons

• Data is refreshed only on initial load.
• Dashboard initial load will take more time as it moves the data to the temporary table.
• Requires temporary storage in the Bold BI database.

5. Summary Table

Conclusion

Selecting the correct mode impacts both performance and data freshness:

• Choose OpenQuery when real-time data is needed.
• Choose Internal Cache when scalability and reduced DB load matter most.

Both modes serve different needs—pick the one that aligns with your dashboard usage and performance expectations.

Additional References

• Connecting to Stored Procedure
• Bold BI MS SQL connector