Walk through: Set up Notifications on SQL Sever Source

Walk throughs ››
Parent Previous Next

In this Walk Through

This section demonstrate how to set up change Notification for a SQL Server DataView. You will need a SQL Server DataSource configured with at least one DataView.

The test scenario uses the database set up in the walk through "Set up a SQL Server DataSource" to be notified when a Rule of Credit has been modified and automatically reflect the progress onto the parent Activity in Navisworks by re-applying a Colour Filter.

Read, Write and Advanced Operations

We will use the same read query as defined in the walk through "Set up a SQL Server DataSource", but there is no need to define Write permissions nor Advanced Actions. You can either create and configure a new DataSource by repeating the steps: Create a New SQL DataSource and Loading data from the server or use the same DataSource as you configured in the previous walk through, and simply edit the Notifications section.

SQL Server Prerequisites and Permissions for Notifications

The Notifications system for SQL DataSource requires some options to be activated on the database and elevated permissions for the user. Refer to the section Set up the SQL server for Notifications of Microsoft SQL Server DataSource documentation for more information on how to set up the server.

Notifications Queries

Now that the server is set we can define the Notification queries, each query will be 'registered' in the database, and whenever a change occurs that will change the result of the query, the database will send a notification.

In the Notifications tab, activate the option Subscribe to Notifications.

Notification can be received in one of two ways:

warning


It is possible to define several queries, but listening to notifications comes at a performance cost for the SQL server, so you should limit the number of queries and their scope to the minimum required.

The read query that we have defined uses several statements that are not valid for notifications (refer to the section Listen to Notification of the Microsoft SQL Server DataSource documentation for an exhaustive list), so we will define a different query for notifications.


For our scenario we only need to be notified of changes to the column Percent Complete of the table Rules_Of_Credit. The simple Select statement below will do:


SELECT [Percent Complete]

FROM dbo.[Rules_Of_Credit]


Click the Try Subscription(s) button to check that everything is in order.



The DataView is now set up to receive notifications from the database, but we need to tell BimSens what to do when it receive a notification. BimSens can automatically refresh the data and automatically re-apply a Colour Filter.

Activate Auto Refresh on Notifications

In the Options dialogue, select the Refresh tab and check the option Refresh when the DataView Notifies of Changes. From this point if a change is made to the Percent Complete column of the Rules_Of_Credit table BimSens will immediately be notified and refresh the data being displayed in the grid.

If the DataView is not the Active DataView when the notification is received, it will only be refreshed the next time you activate the DataView.

Automatically Re-Apply a Colour Filter on Notifications

When a notification is received a Colour Filter can automatically be re-applied. There is no restriction on which Filter to apply but since we are listening to change made to the Percent Complete column we will create the Filter on that column:

Percent Complete can be anywhere between 0 and 1 so rather than targeting specific values we will define a gradient; to make values easier to identify, we'll define the colour ranges 0-25% red, 25-50% orange, 50-99% blue, 100% green:

Since each activity can have multiple rules of credit, potentially with different progress, we will use multi match resolution set to average

Finally we'll set the filter to be the Auto Colour Filter of the DataView:

Test it!

All set, you can now test the system by making changes on the database and see how BimSens reacts.

An easy way to make a change if you have done the Set up a SQL Server DataSource walk through is to open a second BimSens window, either in Navisworks or using the standalone version, and load the same Project configuration. You can then use the Action to create new rules of credit for the activities or change the Percent Complete property of existing rules.

BimSens will refresh the data and re-apply the colour filter as soon as you make a change (if you haven't messed up something in the set up).