Data Consumer - Microsoft SQL Consumer – Flow Support

The Flow Microsoft SQL Consumer allows you to push Flow Measure data into a Microsoft SQL database, by specifying your own queries to insert/update tables in the database. This can be used when integrating to other systems where staging tables are required, or to utilize other Analytics/BI tools atop Flow data to perform analytics.

Measure data, model attributes, comments, limit exceptions, user details, and event context can be automatically inserted into a separate Microsoft SQL database with its own custom schema.

Configuring a Microsoft SQL Consumer

To create a connection using a Microsoft SQL Consumer, right-click on Data Consumers, and select “New", Microsoft”, then "SQL Database".

This will list the SQL Consumer called “SQL Database”. Select The “SQL Database” consumer to open its editor:

To successfully push data to a Microsoft SQL Consumer, the following properties need to be set.

Name (String)	Name that descriptively identifies the consumer.
Server (String)	Location of the SQL Database (Server Name or IP Address).
Database (String)	Name of the database to be used.
Username (String)	Configure to use SQL authentication. Leave blank for Windows authentication.
Password (String)	Configure to use SQL authentication. Leave blank for Windows authentication.
Timeout (seconds) (Int)	Permitted time to connect to the SQL database. The default is 15 seconds.
Definition (File: JSON definition)	Use a definition file to populate insert queries.
Maximum concurrent (Int)	Maximum concurrent connections allowed against the SQL database.

Note: As per database-type Data Sources, a custom namespace (definition file) can also be configured for database-type Data Consumers. See How do I create a Definition for a database-type Data Consumer?

After configuring the properties, test the connection by clicking the “Test” button. If successful, a pop-up will confirm your connection or display an error message describing the issue that needs to be rectified.

Configure a Measure to Replicate

From your Flow model, select and open the configuration of a measure that you want to replicate to the configured SQL data source. Dragging the data consumer onto the specific context to be replicated will create four sample queries that need to be modified to reflect the schema of the database that you are pushing data to.

Note: Both Calendar and Event Context can be pushed to a Microsoft SQL consumer.

The four data items that can be utilized to push to the SQL database include:

Values	This is where you configure your query to push measure values to a specified schema. Depending on the context, either measure values or event period values can be consumed.
Comment	This is where you configure your query to push comments that have been added to measure values, to a Microsoft SQL database. When a new comment is added (even if against historical measure values) this query will be executed, allowing you to update your Microsoft SQL database with the comments.
Exceptions	This is where you configure your query to push exceptions to a Microsoft SQL database. Exceptions are generated when a measure's value for a time period falls outside the specified High and/or Low limit values.
Users	Details of users that either edited measure values or added comments can be used in the consumer queries.

Depending on the context selected to be pushed for the particular measure, certain placeholders can be utilized in the SQL queries that will be replaced by values received from the Integration Engine, before the query is executed against the database.

Examples of the particular queries and placeholders for both Calendar and Event contexts is found in What Placeholders can I use when configuring a database-type Data Consumer?