Training Course #1: Query Designing

1. Training Course #1: Query Designing Please view in slide show Page Down/Up or mouse click to navigate through the show

2. Queries A “query” identifies a condition of data within an application that should cause an event to trigger. KnowledgeSync lets you design queries. The only requirement for designing queries is a thorough understanding of an application’s database schema. Queries can check for virtually any condition of data in an application, such as: • An opportunity overdue for closing • Sales reps with fewer than 6 activities this week • A client whose credit status was changed

3. Queries & Events (I) A query is one component of an event. A single event may use a single query, or it may use multiple queries. An event may use multiple queries to check for a complex combination of conditions, such as: Checking for customers who have purchased < $10,000 over the last year, but have called into support more than 12 times.

4. Queries & Events (II) An event may also use multiple queries to check for conditions across multiple applications, such as: An event that looks for customers who have pending sales (in a CRM application) But are also on credit hold (in a financial application)

5. Queries & Events (III) A single query may also be used in multiple events. For example, a query that checks for pending sales > ‘x’ dollars may be used in: An event that looks for sales > $10,000 and in An event that looks for sales > $25,000 and in An event that looks at sales > $50,000

6. Tour Menu Please select the component of KnowledgeSync Query Designing that you would like to learn about next: Designing a Query (click here) Designing “Linked” Queries (click here) Query Designing Helpful Hints (click here) What to Do Next (click here) Exit Presentation

7. How to Begin All query designing is done in the KnowledgeSync Event Manager. Log into that module (username “Admin”, no password). You will be presented with a window like the following:

8. We will design a query for the “Training” application. Click on the plus sign (+) next to the “Training” Application in order to display the sub-branches beneath it. Return to Tour Menu

9. We’ll be working in the Query Definitions branch. Click on the Query Definitions branch to display a listof pre-defined queries for the Training application. Return to Tour Menu

10. Queries can be divided into three groups: • Record-Level Queries • Aggregate Queries • Value-Change Queries Return to Tour Menu

11. Record-level queries are those that are triggered by the condition of individual database records. These queries look for specific conditions in database records, and, if those conditions are met, the query is “triggered.” (The queries with the arrows pointing at them are record-level queries.) Return to Tour Menu

12. Aggregate queries are those that are triggeredby a group of records that meet a certain threshold. These queries look for specific conditions in database records, then perform an aggregatetest on that group of records, and, if both the record-level and aggregate conditions are met,the query is “triggered.” (The queries with the arrows pointing at them are aggregate queries.) Return to Tour Menu

13. Value Change queries are those that are triggered when a record’s value has been changed. These queries are very useful, as they enable you to detect when someone haschanged the value of any field in an application. (The queries with the arrows pointing at them are value-change queries.) Return to Tour Menu

14. The list on the right shows examplesof the types of conditions that you can detect using a query. • Click on the “New Query Definition”button at the top-left of this windowto create a new query . . . Return to Tour Menu

15. When designing a query, the first thing youspecify is the query’s name and whether itcan be used in an event. Return to Tour Menu

16. The “Active” flag simply indicates that thisquery is eligible to be used in one or moreevents. Return to Tour Menu

17. KnowledgeSync automatically displays all of thetables from the corresponding application. The first step in designing a query is to identifywhich tables contain the information youwish to test against for certain conditions. You simply click on the table name you wish toinclude in this query, and then click on the “Add Table” button. Return to Tour Menu

18. This query will be designed to retrieve any ordersthat were picked today but have not been invoiced. We select the “Order Header” table since that is where general order information is stored. We select the “AR Customer” table since that lets usretrieve the customer’s name. (The “orders” tablestores only the customer’s account number.) And we select the “Salesperson” table since that letsus retrieve the name of the salesrep associated tothe order. Return to Tour Menu

19. 1) If multiple tables are selected, KnowledgeSync’s“intelligent linking wizard” helps you link (or join)the tables together. 2) It’s VERY important which table you select FIRST whencreating these links. You MUST specify the query’s primary table – that is, thetable that contains the records that will cause this queryto trigger. Since this query is designed to retrieve “orders”, the “Orders” table must be the first table we link from. Return to Tour Menu

20. Click on the “Add Table Link” button. Return to Tour Menu

21. Since this is our first link, we start out bychoosing the “Orders” table. Return to Tour Menu

22. KnowledgeSync automatically fills in the “Link to”table name, which we can either keep or override. Return to Tour Menu

23. Tables are linked together via a common field (column)of data. Once we specify the name of the column in the“Orders” table that contains the customer number, KnowledgeSync automatically searches for (and loads)the corresponding field from the “Customer” table. (And the “Link Type” automatically defaults to “Left Outer Join” which is the standard linking method inbusiness applications.) Return to Tour Menu

24. Once we save our first link, we are ready to followthe same steps to link the “Orders” and “Salesreps”tables. Return to Tour Menu

25. Note that if the database (or ODBC driver) that you areusing does not support standard linking syntax, you canclick on the checkbox below and manually enter the SQL“from clause” that links the tables together. Return to Tour Menu

26. The “Columns” tab is where you select the fields ofdata that you might wish to include in an outgoing alert message and in an event’s “response actions”when this query’s conditions are met. The list titled “Available Columns to Query” at the top of this tab is a list of all the fields of data fromthe tables you selected previously. Return to Tour Menu

27. For example, if you wish to use the customer’s “ordernumber” in an alert message or response action, you simply highlight the corresponding field in the list at the top of this window and click on the buttoncalled “Add Column to Query.” Return to Tour Menu

28. We would continue adding fields until we had allthe information that we might wish to use inan event’s alert messages or response actions. Return to Tour Menu

29. When creating a record-level query, the “Column Type”field for all the columns is always left as “Normal.” Thismeans that KnowledgeSync will retrieve the actualvalue of that column. Return to Tour Menu

30. When creating an aggregate query, you can choosefrom five additional “Column Types”. Typically, an aggregate query checks for conditionssuch as: -- More Than $50,000 in sales last week -- An average discount percent greater than 7% -- More than 25 support calls this week Here are a few examples: Return to Tour Menu

31. This query uses an aggregate to “summarize” (or total)the forecast sales per salesrep. This first column selected indicates what field will haveits value summarized. The second column selected indicates how the summarizedtotal will be “grouped.” (Per salesrep) If the second column had been “company name” insteadof “salesrep”, the sales would be totaled per companyinstead of per salesperson. (And if “company” is chosen as a third column, you will get a list showing the forecast sales per salesrep and then – within each salesrep – youwould see the sales further broken down by company.) Return to Tour Menu

32. This query identifies any support reps whose average callduration is greater than ‘x’ minutes. This first column indicates that the average call time will bedetermined. The second column indicates that the average time will be grouped “per support rep.” If the second column had been “customer name” insteadof “support rep”, the average call duration would be calculated per customer instead of per support rep. Return to Tour Menu

33. This query “counts” the number of activities per salesrep andidentifies anyone who has fewer than ‘x’ scheduled activities. When using the “count” function, you should always count the column that uniquely identifies each record within a table (e.g., order ID, customer ID, activity ID, etc.). The second column indicates how the counted records willbe “grouped.” (Per salesrep) If the second column had been “activity priority” insteadof “salesrep name”, the activities would be counted per priority instead of per salesperson. (And if “priority” is chosen as a third column, you will get a list showing thenumber of activities per salesrep – broken down by priority.) Return to Tour Menu

34. Sometimes you’ll find that an application uses columnnames that are cryptic or difficult to understand. In some applications, column names with multiple words are all squeezed together. Using the “Customized Name” field, we can change thedefault names to something more intuitive, or morereadable. Return to Tour Menu

35. Note how we have modified the column names to make them clearer. (Since some ODBC drivers don’t like blank spacesin a column’s customized name, you should use the underline character to separate words.) Return to Tour Menu

36. Notice the “Unique” column. When KnowledgeSync finds a record that meets aquery’s criteria, the record is considered “triggered.” You need to tell KnowledgeSync how it can “remember” which records have been triggered. This is what the “Unique” checkbox is for. Return to Tour Menu

37. Since the “Order Number” uniquely identifies each order,it will also allow KnowledgeSync to remember which order records have been triggered. And so we place a checkmark in the “Unique” column for the Order Number field. Return to Tour Menu

38. Finally, note how you can create columns that usecalculations to derive entirely new values. In this example, the query is multiplying an opportunity’s close probability times the forecastamount of the sale (and then is multiplying that totalby .01) to derive the “weighted value” of the sale. Return to Tour Menu

39. When KnowledgeSync sends out an alert message,it can “roll-up” the details of multiple records intoa single message. As a result, you need the ability to specify theorder in which the triggered records are displayed. This is called the “sorting order.” Return to Tour Menu

40. In the case of this query, we might want to list thematching orders according to their “order date,”with the earliest orders listed first. Just like in the “Columns” tab, we can sort by any fields from the tables we selected. In the “Sort Direction” field, we can choose either“Ascending” or “Descending.” Return to Tour Menu

41. You can even specify “nested” sorting. In this query, if multiple orders have the sameorder date, the orders will then be listed in thealphabetic order by the customer’s name. Return to Tour Menu

42. The “Filters” tab is the most important part of a query. It iswhere you specify what conditions have to occur in order forthe query to be “triggered.” The way a filter works is for you to specify a field, (such as “order total”) an operator (such as “is greater than”) and acompare value (such as “1000” dollars). Here’s how it’s done . . . Return to Tour Menu

43. The list of “Available Columns to Filter” lists all of thefields from the tables you selected for this query. You start by selecting the field you wish to test for aspecific condition. Return to Tour Menu

44. A filter can retrieve records that meet a certaincondition (“is”) or it can retrieve records that do not meet a certain condition (“is not”). Return to Tour Menu

45. KnowledgeSync supports all standard operators,including checking for records with a “null” or blank value. Return to Tour Menu

46. A filter’s “Compare Value” can contain differenttypes of values. For starters, you can comparethe value in a field against a constant value. In this example, the order status must be equalto “Picked”. Return to Tour Menu

47. You can compare the value in one field against the value in another database field. In this example, note how the query is checking to see if an item’s “on-hand”quantity is less than its “re-order level”. Return to Tour Menu

48. You can even compare the calculated value of two or more fields against the value of a constant, against the value of another database field, or against the value of another calculated field. In this example, the filter is adding the numberof units on hand to the number of units on purchaseorder, and is then comparing that total to see if it isless than the quantity on back order plus the quantity on existing sales orders. Return to Tour Menu

49. KnowledgeSync also lets you perform date-relatedcomparisons using an extensive list of “date variables”. If you click on the list button that appears in this field,you’ll be presented with a window like the following . . . Return to Tour Menu

50. And over on the left you can choose from a widevariety of “date substitution variables” so thatKnowledgeSync can automatically determine thedates that it should use in this query. Return to Tour Menu