In this chapter of the walkthrough, we go through how a user creates an query that monitors all sales without a sales price in Adventureworks.

The first thing we do is create the query task

1. Open TimeXtender Orchestration & Data Quality and right-click the node Tests
2. Select New
3. Then click Query

   

4. In the popup type Sales without price

Basic query configuration

Next, we want to create a SQL query that retrieves all sales orders without a price from the database Adventureworks.

1. Select the data provider AdventureWorks. If the data provider has not been created, follow the Data Provider Walkthrough and then select it
2. Paste the following SQL query into the textbox

SELECT eSalesOrderID],ORevisionNumber],nOrderDate],t1.CustomerID  
      ,/Status],t2.AccountNumber as CustomerAccount,
      t3.FirstName + ' ' + t3.LastName LSalesPerson]
      ,t1.SalesPersonID
      ,/SubTotal],uTaxAmt],[Freight],FTotalDue]

FROM rSales]. SalesOrderHeader] t1

inner join sales.customer t2 on t1.CustomerID = t2.CustomerID
inner join person.person t3 on t1.SalesPersonID = t3.BusinessEntityID

WHERE ISNULL(TotalDue, 0) <= 0

Configure columns

Now we have a very basic query set up.

The next thing we want to do is format the columns to make the query‘s output look good.

1. Start by saving the previous query by clicking on the Save icon
2. Then Execute the query

   

3. In the Properties window on the right, click the Edit... button next to Column Formatting

   

4. On the left panel, select the column OrderDate
5. In the Caption textbox type Order Date
6. In the Format As dropdown, select Date and Time

   

7. On the left panel, select the column TotalDue
8. In the Format As dropdown, select Number and check Use 1000 separator

When this is done, our query‘s output will format our columns, so the OrderDate and TotalDue columns will have space in their caption and their values will be formatted as a date/number.

Query description and action

Next, we‘re going to write a description and an action for the query.

We do this to make the process of fixing failures easier. The description field should as the name indicates, describe what the query does. The action field should give a detailed description of what actions need to be done, to resolve failures should they arise.

1. Open the Exception Manager.
2. In the Description field, paste: Gives a list of sales that are missing a sales price in the database
3. In the Action field, paste: Check the sales order and make sure it doesn‘t have a price. Send an email to the IT department containing the SalesOrderID and ask them to review the sale.

   

Exception owner

Our next job is to enable and configure the exception manager. When a query is run and it returns results, the exception manager creates an exception for each row and keeps track of the exception until the query result is no longer contained in the row.

Our first step is to enable the exception manager and nominate an owner of each exception. The query owner is the default owner of all exceptions.

If you select user mapping, the owner of each exception depends on the row‘s user mapping column, where TimeXtender Data Quality tries to map the column‘s value to an TimeXtender Data Quality user. If the mapping fails, by default the query owner is set as the exception owner. This can be overridden to automatically create a new TimeXtender Data Quality user and make it the owner of the new exception.

1. Open the Exception Manager popup with the Common tab open
2. Check the Enable Exception Manager
3. In the Query owner dropdown, select your own TimeXtender Data Quality user
4. In the User Mapping Type dropdown, select AdventureWorks Sales. If the type is not in the dropdown, please follow the External User walkthrough to create it.
5. In the User Mapping Column, select SalesPersonID

   

We‘ve now enabled user mapping. This means that the exception manager will always try to map the SalesPersonID column to an TimeXtender Data Quality user, using the AdventureWorks Sales mapping type. If the mapping fails, we will be the owner of the exception, since we‘re the query owner.

Exception emails

Next, we‘re going to configure emails that the exception manager will send.

1. Open the Exception Manager popup with the Notifications tab open
2. Select Add notification -> Email
3. Give the notification a name, default value is Email
4. In the line Send exceptions email, check New
5. In the line Include these exceptions in email, check Open
   (New should be checked and disabled)
6. In the Email subject field, type Exceptions – Sales without price
7. In the Email template field, select Exception Manager Template

In this step, you have configured the query's exception manager to send emails only when the query creates a new exception that the user is the owner of. The email sent will contain all new and open exceptions the user is the owner of.

Exception mapping

User and CC mapping are not the only ways to map a column. A column can also be mapped to Amount, Category, Unique Id, Start Date and Dimension. Each of these mappings serves a different purpose, so let's take a quick look at what those purposes are, before mapping using the mappings that we need for our query.

• Amount – This mapping gives each exception a monetary value derived from the mapped column.
• Start Date – The column mapped to the start date should contain a date that the datasource row was created.
• Dimension – Dimension mapping behaves a lot differently than the other mappings. For the query itself, it provides a lot more readability, where a column containing a userID can be replaced by there user‘s name instead.
  Mapping multiple queries on the same dimension can also provide a different view of exceptions created, from the perspective of the dimension, rather than the default look, which is from the perspective of a data type.

1. Open the Exception Manager popup with the Mapping tab open
2. For Amount mapping, select Total Due
3. For Unique ID mapping, select Sales Order ID
4. For Start Date mapping, select Order Date
5. For the Customers dimension mapping, select the column CustomerID
6. For the Salesperson dimension mapping, select the column SalesPersonID

   

Exception’s Primary Key

TimeXtender Data Quality uses a unique primary key to differentiate exceptions from each other. The first time a primary key is returned in the results, an exception is created. If the primary key comes up again, the exception is marked as open.

By default, the primary key is a composition key made up of all the columns of the exception. It is strongly recommended to change from the default to either a single unique column or a unique combination of columns.

1. Open the Exception Manager popup with the Primary Key tab open
2. Select the Custom radio box
3. Check the SalesOrderID column

   

There are a few advanced exception manager features found in the Advanced tab, but we‘ll leave those for now and continue with configuring the query itself.

Data type

Next, we want to take a look at the data type configuration. Data types are essentially groups that query, compare queries and then register their exceptions.

By setting up a detailed data type schema in TimeXtender Data Quality, the portal‘s overview will use the schema to create an easily readable tree view in the portal which groups queries and compare queries by data types. Data Integrity data type, all queries and compare queries that are data integrity checks will be found under that node in the portal‘s overview.

1. In the left-hand panel, open Data Quality
2. Then Open the Data Types folder
3. Then right-click Data Types and click Open

   

4. In Data Types, click the Add button
5. Delete the default text and write in Sales and then Save

   

6. Go back to the Main Query window and in the right-hand panel, change Data Type from None to Sales

   

Query task

We‘re now going to create a query task that sends an email anytime a query is run.

1. Click the Tasks tab
2. Press the New button in the toolbar
3. Select the task type Email for the query task, and give it an appropriate name
4. Select your user in the Email To section
5. Make the Email subject be Sales without price results
6. Select the style template Exception Manager Query

   

Trigger condition and return an error

Our next job is to configure the conditions in which the query executes its query tasks and how it finishes its run successfully. There are 2 properties that control that behaviour:

• The Property Trigger Condition
• Return Error

If the trigger condition is met, the query tasks are executed. If the trigger condition is met and the Return Error property is set to Query Returns Results, the execution results in failure.

If a trigger condition is not met, the return error property is irrelevant and the query finishes successfully. If a trigger condition is met, it depends on the return error property if the query finishes successfully or not.

1. Find the Trigger Condition field in the query‘s properties panel
2. Set Trigger Condition as If query returns rows
3. Find the Return Error field in the query‘s properties panel
4. Set Return Error as On Open Exceptions

   

Now anytime we execute the query, the only time the execution will be successful is when there are no open exceptions for the query, and it does not fail to finish execution (such as due to a power outage).

Deployment

Now the final step before our query is in production is to deploy it.

1. Save everything and Press the deploy button

   

2. Write a comment, detailing the changes being deployed
3. Make sure to check Sales without price in the popup
4. Click Deploy