Sign in

Data Consumer - MySQL Consumer

The Flow MySQL Consumer allows you to push Flow Measure data into a MySQL 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 MySQL database with its own custom schema.

Configuring a MySQL Consumer

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

Select the “MySQL Database” consumer to open its editor:

MySQL_Consumer_-_Selecting_in_Integration_Tab.png

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

MySQL_Consumer_-_Consumer_Properties.png

  • Name (String)
 Name that descriptively identifies the consumer.
  • Server (String)
 Location of the MySQL Database (Server Name or IP Address).
  • Database (String)
 Name of the database to be used.
  • Username (String)
 Configure to use MySQL authentication. Leave blank for Windows authentication.
  • Password (String)
 Configure to use MySQL authentication. Leave blank for Windows authentication.
  • Timeout (seconds) (Int)
 Permitted time to connect to the MySQL 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 MySQL 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.

MySQL_Consumer_-_Test_Connection__1_.png

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 MySQL 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 MySQL 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 MySQL database.  When a new comment is added (even if against historical measure values) this query will be executed, allowing you to update your MySQL database with the comments.
  • Exceptions
 This is where you configure your query to push exceptions to a MySQL 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.
   

MySQL_Consumer_-_Example_Queries.png

Depending on the context selected to be pushed for the particular measure, certain placeholders can be utilized in the MySQL 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?