This page will guide you on how to create one of the three rule types.
An alert is used to define a data quality or process compliance exception that you want to keep track of and assign to someone to resolve.
Annotate an existing list of terms, fields or tables with explanatory notes and classifications.
Define a data set that requires a data steward to manually update additional attributes for reporting purposes.
Click on Create New Item to be guided through each step you need to create a new rule, such as selecting your data platform, entering your query, custom fields, and communications.
Select the project that will contain this rule.
On this page, click on Create New Item. You will be directed to a wizard that will guide you through all the steps you need to create an alert, reference or glossary.
Complete the following sections whether you are creating an alert, reference or glossary rule type. We have chosen an alert for this example but these steps apply to all rule types.
The first step in creating a rule, no matter the type, is providing a name for the rule. Rule names should be user friendly, unique, and are summaries of the purpose of the rule.
You also have the option to add a description for this rule. Here you can describe the rule and its purpose in more detail. This will display in card and list view of your project.
Then select an agent that will execute this rule. An agent contains all the code that allows rules to run.
Rules consist of two connections; the source connection and the output connection.
First select the source connection, your rule’s query will be retrieving data from this connection.
Then select the output connection and a dedicated schema will be created in this database connection.
If you would not like to use an existing connection, you can also choose to create a source or output connection here using the Add button beside the connection dropdown fields on this page.
(If you have already chosen your connections, skip ahead to add your query.)
Choose the connection type you would like to add.
Provide the details you need to connect to your data platform.
This will add a connection and you will be able to select it in the drop down.
To learn more about connections please read our guide here.
Provide a query here that will select the records from your data source for this rule.
This query should be written using the same SQL variant that your source connection utilizes. Loome Monitor will validate the query as you type and provide any errors or warnings below the query box.
Loome Monitor supports pasting in a query that was written using an external tool such as SQL Server Management Studio or Snowflake Worksheets.
In this next window, select one or more Key Columns for your rule.
Examples of key columns are unique identifiers such as primary keys or a combination of columns to form a composite key. In the event of Key Columns not being entirely unique, Loome Monitor will not behave as expected.
In order to create a rule that will persistently stage your data and perform communication steps accordingly, you will need to choose at least one unique key column and also leave at least one column unselected.
In the example below, you will see that I can select one of the columns available from my query in my data source.
If using a column with the varchar/nvarchar type as a key column, there is a maximum value for the total length of the index for output tables. For multiple key columns, the total length of the combination of all key columns must be lower than the following limits.
|The maximum length for SQL Server 2016 CU1 and below||900 for varchar, and 450 for nvarchar.|
|The maximum length for SQL Server 2016 CU2 and above||1700 for varchar, and 850 for nvarchar.|
You can choose to create custom fields in this next step. (This is not a requirement and you have the choice to proceed without adding any custom fields.)
A custom field is used to capture feedback for your results. Loome will create a dedicated schema for your custom field metadata and it will be independent from the results table generated from this rule. Feedback can be captured from users that have been notified about unresolved results.
Click on Add Custom Field and a slide-out will be expanded with the option to choose from six different field types.
|Date||This will add a field where users can select a date for each row.|
|Number||Users can attribute a number to each row.|
|If you add this custom field, users can attach an email that is related to a result.|
|URL||If you add the URL field, you can add a related URL to this result.|
|Notes||Users can add notes to rows to capture feedback when they view their results.|
|List||Add this custom field when you would like to provide the options that a user can select from when they are providing feedback for a result.|
On the final page of creating a rule, you can choose to add Communication Rules.
You can create multiple steps, and either choose to create a branch that will send communications only on specific conditions or send an overall email to a target audience.
The following section is a guide to create different branches that will only email an audience if a specific condition has been met.
To create a new branch, click on Create first step.
Click on Branching Logic in the slide-out that is expanded.
Add a name that will easily identify the purpose of this branch.
Provide the exact name of the column that this condition will apply to.
In the next dropdown, select the condition type.
Then write the value that this condition will apply to.
For example, a condition for the Active Account column could be ‘Contains’ and ‘Active’ as the value that is used in this column. This means that any unresolved row that contains ‘Active’ in the column ‘Active Accounts’ will be emailed when this rule is executed.
Next, you can select a ‘True’ or ‘False’ step, as seen in the images below.
If a condition is true, the step you configure as the true step will be performed.
If the condition is false, the condition you save as the false step will instead take place.
This will expand the slide-out to add another communication step.
You can then follow our guide in the next section to send emails when the conditions we configured for this step are met.
Using the above example, this means this email will only be sent out for results where the ‘Active Account’ column contains ‘Active’.
If you would like to send an email communication, regardless of any result conditions, then click on ‘Send Email’ as your first step.
You can also follow these steps when creating an email that will only be sent when a condition is met, as per the instructions above.
Provide a communication name so you can easily identify the content and audience of this email communication.
Select whether you would like to:
This will send a single email to a static list of recipients. For example, if you were checking for overdue timesheets, this could be used to send an email that was along the lines of ’50 people have not submitted their timesheets today’.
This will aggregate a single email for all records that relate to a particular email in an email column in the results table. If for example the results table contained unresolved leads in a CRM database, and each lead recorded the email address of an assigned salesperson, Loome Monitor could generate individual emails for each salesperson providing information such as ’You have 24 unresolved leads assigned to you’. So if this results table had 5 rows assigned to another salesperson. They would receive one email for the five rows, instead of 5 emails.
You can send one email per result based on a dynamic email column. This will send one email per result to each assigned email address in the email column of the results table.
You can send one email per result based on a static list that you have entered into the email field manually. This will send one email for each result to the email addresses you add to this communication step in the UI.
If you have chosen to manually add email addresses then follow the next few steps.
You can add the audience for this email communication in the Recipient List.
Add multiple emails by separating them with a semi-colon.
Click on the + Add button once you have provided the email address(es) to this field, and any email addresses that have been added can be viewed below.
If you have chosen to send emails using an email column, under the title Recipient Column select the one that contains the email address(es) you would like to communicate with from the dropdown. This dropdown will display all available columns for this rule.
When this rule is executed, the email addresses in this column will receive the email content created in the next step.
Next, type in the subject of the email communication that will be sent out to users.
Type in the content of the email in the next field.
You can use HTML or you can use the text editor above this field.
Tokens represent available data from the data source your query selected that you can insert into the subject and content of your email.
The types of tokens that are available differ depending on the email type that you selected earlier.
If you selected:
Tokens are available above the subject field.
Select where you would like this data to display.
Drag the token into the content field, and it will be visible where you last clicked.
View the process in the gif below.
For example, if you were to drag in the Results URL link, the audience that is sent this email will be able to follow this link to view the results they were informed about.
Click on the tick button at the top-right corner to save this communication step.
You can repeat this process to add multiple communication steps with different conditions by clicking on the + beside a communication step.
If you would like to delete this communication step, then click on the top-right delete button to confirm that you would like to delete this step.
Click on the Enabled toggle to disable communications when you would like to run this rule but not send any emails. This is useful when you would like to test a rule or check for results that have been resolved without notifying multiple team members.
Click on Create to save and complete this rule.
You will be shown a page where you have the option to either return to your project, edit, or run this rule.
Run this rule to view the results it will generate.
Once you click on Run, you will be able to see the progress of this rule as each step is listed under ‘Execution Steps’.
If the rule execution failed, you can also see which steps have failed in this log. You will also be able to see an error log in the title at the top.
Once you have run this rule, any communication steps you added will have been sent to its recipients during the rule execution process.
Click on the Results button, which is the table icon in the top-right of these execution steps, to be directed to your Results page.
You can also run this rule from the slide-out when editing a rule, and on the page of the rule’s project using the Run button at the bottom corner of each rule card.