How to trigger SQL server actions

Center of Excellence · February 19

1. Abstract

This article discusses the possibilities of using ROLAP cubes to write flags into an SQL server, thus triggering actions on the database side, and how to process this flag.

2. Context

Usually, data pre-calculations are loaded into Board from a SQL server, but there are times when we need to initiate actions on the SQL server side. This may be necessary for calculations, especially for new intra-day data or after data has been written back via ROLAP or Bulk Insert. To facilitate this, we require an architecture that allows us to trigger SQL Server actions from within Board.

Although this article focuses on MS SQL Server, the same concepts can be extended to any other SQL relational data source.

3. Content

3.1 Writing a flag

To signal SQL Server for specific actions, we can use a flag written into a database table. One efficient method is using a ROLAP cube, which provides a direct connection to the SQL database. With this approach, not only we can display live data in Board but also write data back to the source database. An alternative method is using bulk inserts, but it may be excessive for simple operations like setting a flag.

The ROLAP cube's dimension is straightforward, typically comprising one technical entity, often with just one dummy entry. When a specific event occurs in Board, either manually or automatically triggered, a standard Dataflow writes "1" into the ROLAP cube. This value is then directly inserted into the source SQL Server table. It's crucial to ensure that the user logging into the SQL server has write permissions for the target table.

Here's an example of what the target table may look like (with the ROLAP cube writing its value into the ‘Start Flag’ field):

3.2 Listening to the flag

Merely writing the flag into SQL Server is insufficient. On the SQL Server side, we need to create a job that periodically checks if there's a "1" in the Start Flag and a "0" in the Running Flag. This check's frequency can vary, typically ranging from once a minute to once an hour.

3.3 Processing the flag

When the listener detects the Start Flag, it writes a "1" into the Running Flag (ensuring it doesn’t start the same job more than once at a time) and utilizes the Transact-SQL functionality "sp_start_job" to initiate SQL jobs, such as via SSIS packages.

After the required jobs have commenced, the system checks their status using functionalities like "sysjobactivity," "sysjobs," and "syssessions." Upon completion it then resets the Start and Running Flags.

3.4 Reporting back to Board

Once the SQL server-side actions are completed, we often need to trigger actions in Board. Here are two possibilities:

3.4.1 Check for runtime completion in Board

We can use the same ROLAP cube that contains the Start Flag to monitor job completion within Board. Since the SQL Server job deletes the flag upon completion, we know the job has finished when the cube's value is 0. Depending on the job's typical runtime, we can use 'Waits' and 'If-then' statements to check the cube's value regularly until it reaches 0. However, it's important to include a break-out logic to avoid endless loops in case the SQL job doesn't finish for any reason.

3.4.2 Triggering a Board procedure from the SQL server

For a more sophisticated approach, we can trigger a Board procedure from the SQL server itself, as it's in the best position to determine when a job has finished. By utilizing Transact-SQL and SSIS, we can connect to multiple endpoints, including Board's Public API, to initiate a Board procedure. This ensures that Board's follow-up logic is only executed once all SQL server steps have been completed, eliminating the need for a looping check within Board.

Center of Excellence · February 19

Thanks @Stefan Böhm for the insights on this article!