6 Creating Business Queries
This chapter explains how to create queries in Oracle Business Activity Monitoring (Oracle BAM).
This chapter includes the following sections:
6.1 Understanding Business Queries
A business query or query is a request for data that matches specified conditions.
A query can fetch from a data object once, on a schedule, or continuously.
A Flat SQL Query is a simple table of data fields and their values. You select the data object and then select the data fields.
A Group SQL Query query is an analysis of one or more numeric data fields, called measures, grouped by non-numeric data fields called dimensions. For example, a group SQL query might analyze sales grouped by country. You select the data object and then select the measures and dimensions.
A Tree Model Query, written in SQL, is an analysis of one or more measures grouped by a hierarchy of dimensions. For example, a tree model query might analyze sales grouped by country, state or province, county or district, and city. You select the data object and then select the measures and a hierarchy.
A Continuous Query is an analysis of data collected in real time. For example, a continuous query might fetch the call volume for a call center every ten minutes. There are multiple continuous query templates, which perform these analyses:
-
Detecting trends
-
Detecting duplicates
-
Monitoring counts
-
Monitoring aggregate values such as averages
-
Detecting missing events
-
Monitoring highest aggregate values
-
Monitoring key performance indicators (KPIs)
You cannot use a continuous query in any view. However, you can add the Insert values into a data object alert action to a continuous query. You can then use the data object that receives the values, called a write-back data object, as the basis for another query and view. See Insert values into a data object for more information.
Continuous queries are written in Continuous Query Language, or CQL. Unlike SQL, which operates on data in a database or cache and responds to client requests, CQL operates on data in-memory and pushes the results to clients.
In any query that fetches measure data, a measure can be any calculated field defined in the data object. For more information about calculated fields in data objects, see Working with Data Objects.
Table 6-1 summarizes the data object types and business view types with which each business query type can be used.
Table 6-1 Query Type Compatibility with Data Object Types and View Types
| This Business Query Type | Fetches Data From | Sends Data To |
|---|---|---|
|
Flat SQL Query |
Any non-stream data object |
List, Action List, and Geo Map views only |
|
Group SQL Query |
Any non-stream data object |
All views except List, Action List, Geo Map, Treemap, or KPI Watchlist |
|
Tree Model Query |
A non-stream data object with at least one hierarchy |
Treemap views only |
|
Continuous Query: Duplicate Detection Template |
A stream data object only |
Alerts only |
|
Continuous Query: all other templates |
Any non-external data object |
Alerts only |
A KPI Watchlist view is only for KPIs; see Creating KPIs for more information.
For more information about data objects, see Working with Data Objects.
For more information about views, see Creating and Using Business Views. For further details about the query requirements for each view type, see Table 8-1.
For more information about alerts, see Creating Alerts.
6.2 Business Query Prerequisites and Uses
Before you can create a query, you must add to your project the data object containing the data fields the query will retrieve and analyze.
For more information about projects, see Planning and Creating Projects. For more information about data objects, see Working with Data Objects.
After you create queries, you can use them in the following ways:
- In a KPI
-
A realtime KPI can use a continuous query to monitor a risk indicator. A scheduled KPI must be based on a group SQL query with no dimensions. See Creating KPIs for more information.
- In a view
-
A view displays the data that a non-continuous query retrieves. You cannot use a continuous query in any view. See Creating and Using Business Views for more information.
- In a dashboard
-
A dashboard contains a group of related views. See Creating Dashboards for more information.
- With a parameter
-
You can filter query data using a user-specified parameter value. See Creating Parameters for more information.
- With an alert
-
A continuous query can initiate an alert action. For example, you can define an action that notifies you when the value of an Internal Costs field exceeds a certain dollar amount. This alert action is specific to the query and does not appear in the left panel navigator under Alerts. As an alternative, you can configure an alert to watch a continuous query as its triggering event. For more information, see Creating Alerts.
6.3 Creating a Flat SQL Query
This section outlines the procedure to create a Flat SQL Query.
To create a flat SQL query:
6.4 Creating a Group SQL Query
This section outlines the procedure to create a Group SQL Query.
To create a group SQL query:
6.5 Creating a Tree Model Query
This section outlines the procedure to create a Tree Model Query.
A tree model query is applicable only to a data object with at least one hierarchy of dimension data fields defined. For example, a hierarchy might consist of the country, state or province, and city. See Working with Data Objects for more information.
You can use a tree model query only in a Treemap view, which is designed to display hierarchical data effectively. See Creating Treemap Business Views for more information.
To create a tree model query:
6.6 Creating a Continuous Query
This section outlines the procedure to create a Continuous Query.
Continuous queries use templates. Each template has its own settings. Therefore, the steps to create a continuous query are different for each template.
To create a continuous query:
6.6.1 Using the Trending Detection Template
This template detects when a numeric field shows a specified percentage change in value for a specified number of consecutive intervals. For example, such a query might detect when the call volume for a call center increases by 10% for two consecutive 30-minute intervals.
To create a continuous query using the Trending Detection Template:
6.6.2 Using the Duplicate Detection Template
The Duplicate Detection Template can only query stream type data objects.
This template detects when a data field has duplicate values within a specified period of time. For example, such a query might detect when the same order is placed twice within a day.
To create a continuous query using the Duplicate Detection Template:
6.6.3 Using the Monitor Count Template
This template monitors the count of one or more data fields. For example, such a query might monitor the number of currently active calls in a call center.
To create a continuous query using the Monitor Count Template:
6.6.4 Using the Moving Aggregation Template
This template monitors an aggregation (average, sum, and so on) of one or more data fields. For example, such a query might monitor the average wait time for calls in a call center.
To create a continuous query using the Moving Aggregation Template:
6.6.5 Using the Missing Event Template
This template detects when an expected event does not occur. For example, such a query might detect when order approval is missing between order placement and order shipment.
To create a continuous query using the Missing Event Template:
6.6.6 Using the Top N Template
This template monitors the top values of a grouped aggregation (average, sum, and so on) of a numeric field. For example, such a query might monitor which three countries have the top sales totals.
To create a continuous query using the Top N Template:
6.6.7 Using the KPI Alert Template
A continuous query that uses the KPI Alert Template is similar to a realtime KPI. However, you can use a realtime KPI in a KPI watchlist view. You cannot use a continuous query in any view. See Creating KPIs for more information about KPIs.
This template detects when a grouped aggregation (average, sum, and so on) of a numeric field shows a specified change from a defined norm. For example, such a query might detect when the call volume for a call center is more than one standard deviation from the average of the last 24 hours.
To create a continuous query using the KPI Alert Template:
6.7 Creating a Pre-defined SQL Query
Oracle BAM allows you to write your own SQL which can then be used for flat or group queries against that SQL. The arbitrary SQL that you enter is treated as a sub-query by the system. The returned result set is treated as set of columns like in a Data Object.
6.8 Editing a Business Query
When you edit a query, the changes propagate to all views that include the query and all dashboards that include these views.
Use the following procedure to open, edit, and save a query.
To edit a query:
6.9 Renaming a Business Query
This section outlines the procedure to rename a Business Query.
Use the following procedure to change the Display Name of a query.
The Display Name is case sensitive and may contain any characters except the forward slash (/), which indicates a folder path. It may have up to 128 characters. It can be changed at any time.
To rename a query:
When you edit the query, the new Display Name is displayed on the tab. However, the internal Name remains unchanged. When you edit the query, this name is displayed on the left in the header.
6.10 Deleting a Business Query
This section outlines the procedure to delete a Business Query. The query is removed from any views or dashboards that reference it.
Use the following procedure to delete a query.
To delete a query:
6.11 Securing a Business Query
A query inherits security settings from the project in which it is created.
For more information about projects, see Planning and Creating Projects.
To change security settings for a query:
-
In the left navigation pane, click the arrow to the left of Business Queries.
All saved queries in the current project are displayed in a list.
-
Right-click the query and select Security Settings from the pop-up menu.
The security tab for the query opens.
-
To add a role or group to whom you can explicitly grant or deny permissions, follow these steps:
-
Click the Add icon in the Grant Permissions or Deny Permissions table.
The Add Application Roles, Groups, and Users dialog opens.
See Managing Oracle BAM Users for information about how to add users to roles and groups.
-
Type a Name for the role or group you are adding.
-
Select from the drop-down List: Application Role or Group.
-
Click Search to populate the Available Members list.
-
To add a member to the Selected Members list, select the member and click the single right arrow.
-
To add all members to the Selected Members list, select the member and click the double right arrow.
-
To remove members from the Selected Members list, use the single and double left arrows.
-
When the Selected Members list is final, click OK.
The Add Application Roles, Groups, and Users dialog closes, and the Name you specified appears in the table.
-
-
To remove a role or group, select the table row and click the Remove icon.
-
To grant permissions, select Read, Write, Remove, or Security for the users, roles, and groups listed in the Grant Permissions table.
-
To deny permissions, select Read, Write, Remove, or Security for the users, roles, and groups listed in the Deny Permissions table.
-
Click Save.
6.12 Filtering Data
You can add data filters to most types of queries. You can also filter data in some alert operations.
A simple example filter is "Sales greater than 12000." When this filter is applied, only rows with values larger than 12000 in the Sales data field are fetched. See When a data field in a data object meets specified conditions and Delete rows from a data object for more information.
By adding logical sub-branches, you can create a hierarchy of filters. (In previous releases, branches were called headers.)
To filter data completely, fetching no rows from the data object, check Filter All Data.
To add a row data filter:
6.12.1 Adding a Group Data Filter
You can add group data filters to business queries, for example, sum of sales or average of sales, or count of region. You can apply the group filters to queries at run-time as well.
- Select the Group Filters tab.
- Click the New Filter icon. The Add/Edit Filter dialog appears.
- Select the data field to be filtered from the drop-down list on the left.
- Select the operation from the drop-down list in the center. The default operation is is equal to.
- Select the item with which to compare the value in the filtered data field.
- Type a Value — A value you specify. Alternatively, select a Parameter — User input through a parameter. See Creating Parameters for more information.
- Click OK to save the filter.
6.12.2 Filter Comparison Operations
You can use the following operations for filter comparisons:
is equal to returns the exact value. For numeric data types, nulls are not fetched for filters using a typed-in value of zero. See Exceptions for Datetime Filter Comparisons for timestamp exceptions.
For numeric data types, nulls are not fetched for filters using an is equal to or is not equal to operation and a typed-in value of zero.
is less than returns values less than specified value.
is less than or equal to returns values less than or equal to specified value.
is greater than returns values greater than specified value.
is greater than or equal to returns values greater than or equal to specified value.
is not equal to returns all values except specified value. For numeric data types, nulls are not fetched for filters using a typed-in value of zero.
is like returns values that match a string pattern. Include an underscore (_) as a wildcard for a single character in a string, and a percent symbol (%) as a wildcard for one character or more. Wildcard characters can be combined, for example, %mm _00 would return all columns (35mm 200, 35mm 400, 35mm 800). Do not enter any spaces in the expression because spaces are treated as characters in the data match.
is not like returns values that do not match a string pattern. You can use the wildcard characters permitted in is like comparison expression.
is null returns values where the column is null.
is not null returns values where the column is not null.
is in list returns values included in a list. See Exceptions for Datetime Filter Comparisons for timestamp exceptions. When you select this option, the text box in which you can type a value expands to allow multiple values. Add as many values as needed, each on a separate line.
is not in list returns values not included in the list.
6.12.3 Exceptions for Datetime Filter Comparisons
You can use datetime data types with the is like or is not like operation, but you must specify the full datetime format, including the time zone. For example:
02/%/2014 08:%:% %M
The datetime value entered is assumed to be in the UTC time zone.
Note that is equal to and is in list filters do not work for timestamp values because Oracle databases use microsecond precision, while Oracle BAM uses second precision.
You can work around this limitation by creating two filters: one using is greater than or equal to and the desired value, and another using less than and the value plus one second.
For example, to create a filter for is equal to 10:05:30 11:14:35 AM:
-
Make sure the type of the branch under which both filters are created is All are true.
-
In the first filter, choose the operation is greater than or equal to and type the value
10:05:30 11:14:35. -
In the second filter, choose the operation is less than and type the value
10:05:30 11:14:36.
See Column Data Types for more information about the BAM datetime format.
6.13 Using Top N Data
You can choose to display only the top values of the first data field in Flat SQL Queries. In Group SQL and Tree Model Queries, you can select the data field to which to apply Top N.
In List and Action List views, you must apply sorting to a field before applying top N. The top N selection applies to the first sorted field.
If two or more values are the same, and not all of them can be included in the top N, which values are included is random. For example, if N is 3 and the top four are 100, 200, 300, and 300, one of the 300 values is omitted.
See Creating and Using Business Views for more information about views.
6.14 Applying Aggregate Functions to Groups
Many query types support adding aggregate functions to groups of data.
The aggregate functions are defined in Table 6-2.
Note:
When you use an aggregation function such as an average in a query, the first data point the query fetches will be null, because the aggregation of no data is null.
The Sum function is only supported for the integer data type. For decimal or float data types, exact values are not guaranteed.
Table 6-2 Aggregate Functions
| Function | Description |
|---|---|
|
Count |
For a field of any type, this function returns a count of the values, including duplicates. |
|
Count Distinct |
For a field of any type, this function returns a count of the unique values, not including duplicates. |
|
Minimum |
For a field of any type, this function returns the earliest, lowest, or alphanumeric first value in the group. |
|
Maximum |
For a field of any type, this function returns the latest, highest, or alphanumeric last value in the group. |
|
Sum |
For a numeric field, this function returns the sum. |
|
Average |
For a numeric field, this function returns the statistical average. |
|
Median |
For a numeric field, this function returns the statistical median. For a datetime field, it returns the middle chronological value. For a string field, it returns the middle alphanumeric value. |
|
Std Dev |
For a numeric field, this function returns the statistical standard deviation. |
|
Variance |
For a numeric field grouped by a dimension, this function returns the statistical variance accounted for by a particular group. |
|
Percent of Total |
For a numeric field grouped by a dimension, this function returns the group sum percentage of the sum of all groups. This function is not appropriate for fields that may sum to zero. |
6.15 Using a Time Group or a Time Series
You can create a query and a corresponding view in which the grouping (X-axis) is based on a datetime field.
To configure a time group or series:
Figure 6-1 shows a bar chart view with time groups for years and quarters.
Figure 6-1 Bar Chart with Year and Quarter Time Groups
Description of "Figure 6-1 Bar Chart with Year and Quarter Time Groups"
Note:
Chart and Graph business views display non-continuous time series differently, in that graph business views space time unit data points along the x-axis evenly.