This chapter describes how to create and manage data objects in Oracle Business Activity Monitoring (Oracle BAM), including creating different data object types, creating different column types and hierarchies, assigning row security and permissions, and managing data.
This chapter includes the following sections:
For a general introduction to data objects, including how to add data objects to projects and view data object information, see Working with Data Objects. You should know how to work with data objects as a user in the Designer role before creating data objects as a user in the Administrator role.
Process data objects in BAM are based on the process star schema. This is the star schema standard adapted for use in process analytics. Fact tables contain measures such as process running times, and dimension tables contain dimensions such as process names.
Before you create a data object, you should determine the following:
The type of data object you need: simple, derived, external, or logical
For a simple data object, whether you need a stream, archived stream, or archived relation
The columns you need and whether they are measures, dimensions, or attributes
The data types of the columns you need: VARCHAR (or String), INT, DECIMAL, FLOAT, or DATETIME
Whether you need primary columns for joins
Whether you need index columns
Whether you need hierarchies
Whether you need calculated fields
Whether data will be filtered
How long data will be retained
The types of row security and permissions you will be granting to other users
See the following for information about the ready-to-use data objects provided with BAM:
See Integrating with Oracle Business Process Management for more information on ready-to-use data objects for BPM.
See Integrating with Oracle SOA Suite for more information on ready-to-use data objects for SOA.
A data object contains no data when you first create it. You must load or stream data into data objects using the technologies discussed in the following topics:
"Using Process Analytics" in Developing Business Processes for Oracle Business Process Management
"Configuring BPEL Process Analytics" in Developing SOA Applications with Oracle SOA Suite
You can view definitions (metadata) for all BAM data objects using the following WSIL interface:
http://host:port/OracleBAMWS/WebServices/Wsil/inspection.wsil
Note:
BAM has the following system data objects, which you must not delete or change:
AlertHistory — Stores alert event history.
BAM_ENTITY — Stores metadata for all BAM data objects.
PreseedingFileHistory — Tracks the use of preseeding files, which are templates for data object and project creation.
In addition, it is strongly recommended that you do not change any data objects in the oracle folder. Doing so could break dependent entities such as the pre-assembled BPM dashboards and other Oracle samples. Oracle BAM also ships with the ColorThemes data object which you must not delete but can edit. You can enter six-digit HEX color codes prefixed with a number or hash sign, like #FF006A, for example. You can store up to 15 colors per theme. Blank or empty colors are allowed, even when in the middle of a row. That is, you can assign colors 1 to 3, skip 4 and 5, then assign 6-10 and stop there. You can also create custom color themes using this data object which show up in the Predefined Style drop-down in the General properties for Business Views. See General Properties for more information.
Oracle BAM enables you to create different data object types to maintain various kinds of data, including external data, persisting outside BAM.
This section includes the following topics:
See Data Object Types for an explanation of each data object type, the difference between a stream and a relation, and what it means for a data object to be archived.
Note:
You must not create a data object column named “Type” or “type”. It is a reserved word and even though you will not see a warning at creation time, you will be unable to save a query with this column name.An external data object is a connection to a table in an external database. You cannot change its data using Oracle BAM.
You can export and import its metadata only using BAMCommand. See Using BAM Command for more information.
This chapter includes information about columns and hierarchies and outlines how you can add them to a data object.
This section includes the following topics:
The data types include:
VARCHAR (or String) columns contain a sequence of text characters. The maximum length that Oracle BAM 12c supports is 2000 characters. Note that decreasing varchar field limit may result in data purges.
INT columns contain numbers from -2^63 ~ 2^63 - 1.
Boolean columns are INT columns with a size of 1 and values of 0 for false and 1 for true.
FLOAT columns are double-precision floating point numbers.
Oracle BAM Float truncates numeric data that has very high precision. If you do not want to see loss of precision, use the Oracle BAM Decimal type (NUMBER in an Oracle database) with the scale you want.
A FLOAT in Oracle BAM is stored as an Oracle database data type BINARY_FLOAT. The precision is not guaranteed to be exactly the same as entered by the user, and when data flows from one system to another, the data types get mapped to each other (as for example SQL float to Java float). Oracle BAM does not do any manipulation with the data sent by the database. It is the Oracle Java Database Connectivity (JDBC) driver that loses the last few digits.
DECIMAL columns include decimal points with a scale number defined. The number is stored as a string, which uses a character for each digit in the value.
The Oracle BAM Decimal data type is stored as a NUMBER (38, X) in the Oracle database. The first argument, 38, is the precision, and this is hard-coded. The second argument, X, is the scale, and you can adjust this value. The scale value cannot be greater than 38.
DATETIME columns are timestamps generated to milliseconds.
A DATETIME field is stored as an Oracle database data type TIMESTAMP(6). Depending on how the DATETIME field is populated, Oracle BAM may fill in the time stamp value for you. For instance, in Oracle BAM you cannot specify the value for DATETIME when adding a row, but if the value for DATETIME is specified in a BAMCommand import file, the specified value is added as the value of the DATETIME field instead of the current time.
The format of DATETIME values in BAM is as follows, with 24-hour time:
yyyy-MM-dd'T'HH:mm:ss.SSSZ
For example, the following DATETIME value represents November 12, 2013 at 12:15:52 am, plus 103 milliseconds, in the GMT -8 (Pacific) time zone:
2013-11-12T00:15:52.103-08:00
This procedure applies to simple and derived data objects. You cannot add columns to an external data object from within Oracle BAM. A different procedure applies to logical data objects; see Adding Columns to a Logical Data Object.
Note:
If you delete or hide a column in a simple or derived data object, then queries, views, and other entities that depend on the data object are broken and must be edited as described in Managing Changes in a Project.
To add columns to a simple or derived data object:
This procedure applies to logical data objects. You cannot add columns to an external data object from within Oracle BAM. A different procedure applies to simple and derived data objects; see Adding Columns to a Simple or Derived Data Object.
Note:
If you delete a column from a logical data object, a query that references the deleted column continues to run until the dashboard that references the query is closed.
To add columns to a logical data object:
Indexes improve performance for large data objects containing many rows. Without any indexes, accessing data requires scanning all rows in a data object. Scans are inefficient for very large data objects. Indexes can help find rows with a specified value in a column.
If the data object has an index for the columns requested, the information is found without having to look at all the data. Indexes are most useful for locating rows by values in columns, aggregating data, and sorting data.
You can add index columns to simple and derived data objects.
To add an index column:
In Oracle BAM, you can add hierarchies to data objects to provide drill paths for business views. When a Designer user selects a hierarchy for drill-down in a view, the Viewer user can drill down and up the hierarchy of data, displaying data at different levels of detail.
You can create and edit multiple, independent hierarchies in any type of data object. There are two hierarchy types: non-datetime and datetime.
Note:
Hierarchies cannot consist of calculated fields.
You can create a column with a calculation based on other columns in the data object. The calculator includes aggregate functions, string functions, and datetime functions that you combine with existing column data to create calculated fields. You can also create calculated fields based on other calculated fields.
For example, if a field called Activity Running Time is in milliseconds, and you prefer to see the data in minutes, you can create a calculated field named Activity Running Time in Minutes and give it the following calculation expression:
Activity Running Time (millisecs)/60000
You can add calculated fields to logical data objects.
Note:
If you modify a calculated field in a logical data object, a query that references the field continues to run until the dashboard that references the query is closed and the query is resaved.
To add a calculated field:
As an administrator, you can define parameters at the data object level inside calculated fields. This is different from project-level parameters that Designers can specify.
When you create a simple data object, you specify the maximum numbers of columns with different data types, and you cannot change these numbers later. For derived data objects, you can view these numbers on the General subtab.
To view the numbers of columns:
Permissions determine who has access to the metadata of a data object or to the data as a whole. Row security determines who has access to specific types of data. Permissions and row security apply to all types of data objects.
This section includes the following topics:
To change permissions for a data object:
Go to the Administrator page.
In the left navigation pane, click the arrow to the left of Data Objects.
All data objects in the current project are displayed in a list.
Right-click the data object and select the Security menu item.
The data object security settings open in a new tab.
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 or deny permissions, check or uncheck the following permissions listed in the Grant Permissions and Deny Permissions tables.
Read — Permission to view the metadata of the data object.
Write — Permission to edit the metadata of the data object. Write permission automatically includes Read permission.
Remove — Permission to delete the data object.
Select — Permission to select, or read, the data in the data object.
This is the minimum permission needed for using the data object in business queries, business views, dashboards, and other Designer entities.
Delete — Permission to delete data object rows.
Update — Permission to update or insert data object rows.
Security — Permission to set these permissions for other users for this data object.
Click Save.
You can add security filters to data objects so that only specific users can view specific rows in the data object. This can be useful when working with data objects that contain sensitive or confidential information not intended for all Designer or Viewer users.
If a user has a dashboard open, and you change that user's security filter, it does not affect the currently open dashboard. If the user reopens that dashboard, it has the new security filter settings applied. Security filter settings are included in the business queries behind the dashboard views.
Note:
To ensure that row security changes are applied to alerts, resave the alerts or restart the Oracle BAM server.
To add a row security filter to a data object:
This section contains information about modifying or removing data from a data object both manually and automatically.
This section includes the following topics:
To add rows to a data object automatically, you must load or stream data using the technologies discussed in the following topics:
"Using Process Analytics" in Developing Business Processes for Oracle Business Process Management
"Configuring BPEL Process Analytics" in Developing SOA Applications with Oracle SOA Suite
You can manually add rows to simple and derived data objects.
To add a row to a data object manually:
You can edit rows in simple and derived data objects.
To edit a row in a data object:
You can purge data in simple and derived data objects.
To perform periodic purges automatically, see Setting Data Retention in a Data Object.
To purge a data object:
You can export data from all data object types.
See Export for information on exporting data using BAMCommand.
To export data from a data object:
You can set data retention for simple and derived data objects.
To purge data manually, see Purging Data in a Data Object.
To set data retention for a data object:
You can filter data only in logical data objects.
To filter data in a data object:
For a dimension table that changes infrequently, you can check the Slow Changing Dimension option to improve the performance of continuous queries.
You can set this option for simple and derived data objects.
To specify slow-changing dimensions:
Note:
If a logical data object joins one data object with Slow Changing Dimension set and another without, and an alert is configured to fire when a row is inserted into the logical data object, make sure the row is inserted into the data object with Slow Changing Dimension set first, or the alert may not fire.
You can organize data objects in the left navigation pane by creating folders and subfolders for them. A folder is a Display Name convention for grouping data objects.
You can perform the following folder operations:
Create one or more folders by including one or more forward slashes (/) in the Display Name when creating or renaming a data object. Renaming means changing the Display Name. Text between slashes becomes names for folders and subfolders.
Create a new data object in an existing folder by including the folder in the Display Name.
Create a new data object in an existing folder by right-clicking the folder and selecting Create. The Display Name includes the folder by default.
Move data objects in and out of folders by renaming them, which means changing their Display Name values.
You cannot rename a folder in one step. To rename a folder, edit the Display Name of each of the data objects in the folder.
Delete all data objects in a folder by right-clicking the folder and selecting Delete.
Folders have no other functions. For example, you can set user permissions only on individual data objects, not on folders.
This section outlines the different ways in which you can modify data objects.
You can edit, rename, and delete data objects just as you can any other Oracle BAM entity.
Use the following procedure to open, edit, and save a data object. When you edit a data object, the changes propagate to all entities that use the data object.
To edit a data object:
Renaming a data object changes the Display Name.
To rename or move a data object:
This section provides syntax and examples for operators and functions you can use in calculations when creating calculated fields in a data object.
Note:
When you use an aggregation function such as an average in a calculated field, the first data point 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 precision for values after the decimal is not guaranteed.
This section contains the following topics:
Table 14-1 describes the operators you can use to build calculated columns.
Table 14-1 Operators Used in Calculated Fields
| Operator | Function |
|---|---|
|
+ (plus sign) |
Add |
|
- (minus sign) |
Subtract |
|
* (asterisk) |
Multiply |
|
/ (slash) |
Divide |
|
% (percent sign) |
Modulus |
|
( ) (parentheses) |
Parentheses determine the order of operations |
|
!= (exclamation point and equal sign) |
Logical NOT |
|
&& (double ampersand) |
Logical AND |
|
|| (double pipe) |
Logical OR For example if ((CallbackClientTime == NULL) || (ReceiveInputTime == NULL)) then (-1) else (CallbackClientTime-ReceiveInputTime) |
|
== (double equal sign) |
Equality |
|
= (equal sign) |
Assignment |
Field names containing any special characters, such as the operators listed in Table 14-1, double quotation marks, or spaces, must be surrounded with curly braces {}. If field names contain only numbers, letters and underscores and begin with a letter or underscore they do not need curly braces. For example, if the field name is Sales, the correct way to enter this in a calculation is Sales. However, if the field name is Sales+Costs, the correct way to enter this in a calculation is {Sales+Costs}.
Double quotes must be escaped with another set of double quotes if used inside double quotes. For example, Length("""Hello World, "" I said").
Returns an aggregated value from the current time back to a specified time. The time unit can be YEAR, QUARTER, MONTH, DAY, or HOUR.
Oracle Structured Query Language (SQL) supports this function. Oracle Continuous Query Language (CQL) does not support this function.
Syntax:
AGO(expr,time_unit,offset)
Example:
AGO(SUM(SALES),YEAR,1)
Returns the average of all values for the given field. Avg can accept one field parameter of type Integer, Float, or Decimal.
Syntax:
Avg(Number)
Example:
Avg(Revenue)
Creates a Case clause in a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.
Syntax:
Switch(w) Case(x):(y) Default(z)
Example:
Returns the smallest integer greater than or equal to the specified value. Ceiling(2.9) returns 3 and Ceiling(-2.3) returns -2. Ceiling can accept one field parameter of type Integer, Float, or Decimal or a numeric value may be entered.
Syntax:
Ceiling(Number)
Examples:
Ceiling(Total) Ceiling(3.7)
Concatenates several strings into one. Concat can accept multiple field parameters of type String, or string values may be entered.
Syntax:
Concat(String1,String2, ... ,StringN)
Example:
Concat("Hello ","World")
Returns a count of all non-null values. Count can accept one field parameter of any type.
Syntax:
Count(Field)
Example:
Count(SaleComplete)
Returns a count of distinct values in a field. CountDistinct can accept one field parameter of any type.
Syntax:
CountDistinct(Field)
Example:
CountDistinct(Salesperson)
Adds an offset to the field value. The first parameter for DateAdd must be a field of type DateTime, and the last seven parameters maybe a field of type Integer or an integer value. Zeros may be used where no offset is needed.
CQL does not support this function. This function does not add the milliseconds portion of a datetime value.
Syntax:
DateAdd(DateTime, Years, Months, Days, Hours, Minutes, Seconds, Milliseconds)
Example:
DateAdd({Last Modified}, 0, 0, 7, 0, 0, 0, 0)
//adds 7 days to the Last Modified value
DateAdd({Last Modified}, 0, 0, DaysToFollowup, 0, 0, 0, 0)
//adds DaysToFolowup number of days to the Last Modified value
Returns the amount of time between two dates or times. The time unit can be SQL_TSI_YEAR, SQL_TSI_QUARTER, SQL_TSI_MONTH, SQL_TSI_WEEK, SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE, or SQL_TSI_SECOND.
CQL does not support this function.
Syntax:
DATEDIFF(time_unit,time1,time2)
Example:
DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW())
Returns the day name for a date. DayName accepts one field parameter of type DateTime.
Note:
All return values are the length of the longest day name. Shorter names include trailing spaces. To remove these extra spaces, use the TrimEnd function.
Syntax:
DayName(DateTime)
Example:
DayName({Last Modified})
Returns the day of the month for a date, in the range 1 to 31. DayOfMonth accepts one field parameter of type DateTime.
Syntax:
DayOfMonth(DateTime)
Example:
DayOfMonth({Last Modified})
Returns the day of the week for a date, in the range 1 to 7. DayOfWeek accepts one field parameter of type DateTime.
Syntax:
DayOfWeek(DateTime)
Example:
DayOfWeek({Last Modified})
Returns the day of the year for a date, in the range 1 to 366. DayOfYear accepts one field parameter of type DateTime.
Syntax:
DayOfYear(DateTime)
Example:
DayOfYear({Last Modified})
Creates a Default clause in a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.
Syntax:
Switch(w) Case(x):(y) Default(z)
Example:
Specifies the third clause of an If-Then-Else statement. Else can accept fields, expressions, and values of any type as parameters.
Syntax:
If(x) Then(y) Else(z)
Example:
IF(Process Instance Status=="ACTIVE") THEN(DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW())) ELSE(DATEDIFF(SQL_TSI_DAY,Process End Time,NOW()))
Returns the largest integer less than or equal to the value of the specified field. Floor(2.9) returns 2 and Floor(-2.3) returns -3. Floor can accept one field parameter of type Integer, Float, or Decimal or a numeric value may be entered.
Syntax:
Floor(Number)
Examples:
Floor(Sales) Floor(46.75)
Returns the hour value in the range 0-23. Hour accepts one field parameter of type DateTime.
Syntax:
Hour(DateTime)
Example:
Hour({Last Modified})
Specifies the first clause of an If-Then-Else statement. If can accept fields, expressions, and values of any type as parameters.
Syntax:
If(x) Then(y) Else(z)
Example:
IF(Process Instance Status=="ACTIVE") THEN(DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW())) ELSE(DATEDIFF(SQL_TSI_DAY,Process End Time,NOW()))
Returns a specified value, y, if the test value, x, is null. IfNull accepts two parameters that can be fields of any type or values of any type. However, the two parameters must be of the same data type.
Syntax:
IfNull(x,y)
Example:
IfNull(Quantity, 0)
Returns the length of the string. Length accepts one parameter that can be a field of type String, a string value in quotes, or an expression containing strings or fields of type String.
Syntax:
Length(String)
Example:
Length(Description)
Length("string")
Length(Concat(Description,"Description"))
Converts the string to lowercase letters. Lower accepts one parameter that can be a field of type String, a string value in quotes, or an expression containing strings or fields of type String.
Syntax:
Lower(String)
Example:
Lower(Description)
Lower("Description")
Lower(Concat(Description,"Description"))
Returns the maximum value of the specified field or expression. Max accepts one field parameter of any type, or another valid expression.
Syntax:
Max(x)
Example:
Max(Quantity) Max(Concat(Description," overstock"))
Returns the median of all values for the given field. Median can accept one field parameter of type Integer, Float, or Decimal.
Syntax:
Median(Number)
Example:
Median(Revenue)
Returns the minimum value of the specified field or expression. Min accepts one field parameter of any type, or another valid expression.
Syntax:
Min(x)
Example:
Min(Quantity) Min(Concat(Description," overstock"))
Returns the minute value in the range 0-59. Minute accepts one field parameter of type DateTime.
Syntax:
Minute(DateTime)
Example:
Minute({Last Modified})
Returns the month value for a date in the range 1-12. Month accepts one field parameter of type DateTime.
Syntax:
Month(DateTime)
Example:
Month({Last Modified})
Returns the month name for a date. MonthName accepts one field parameter of type DateTime.
Syntax:
MonthName(DateTime)
Example:
MonthName({Last Modified})
Does nothing. Use in a control function when you want one alternative to do nothing.
Syntax:
NOOP(Param)
Example:
IF("Process Running Time (millisecs)"/3600000 > 48)
THEN("Overdue")
ELSE(NOOP(1))
Returns the current date and time. Now does not accept any parameters.
Syntax:
Now()
Example:
DateAdd(Now(), 0, 0, 7, 0, 0, 0, 0)
Returns the percent the value represents of the total values for the specified field. PercentOfTotal accepts one field parameter of type Integer, Float, or Decimal.
CQL does not support this function.
Syntax:
PercentOfTotal(Number)
Example:
PercentOfTotal(Quantity)
Returns the quarter value in the range 1-4. Quarter accepts one field parameter of type DateTime.
Syntax:
Quarter(DateTime)
Example:
Quarter({Last Modified})
Returns the rank of a value in comparison to other values in the column. Values are not consecutive when ties occur. For example, if two values have a rank of 3, the next rank is 5.
CQL does not support this function.
Syntax:
RANK(column)
Example:
RANK(SALES)
Repeats a string for the specified number of times. Repeat accepts two parameter, the first of which may be a string value or a field of type String, the second of which may be an integer value or a field of type Integer. Either parameter can use an expression that returns a string for the first parameter and an integer for the second value.
Syntax:
Repeat(String,Integer)
Example:
Repeat("string", 5)
Repeat(Description, 2)
Repeat(Description, Quantity)
Repeat(Concat(Description," overstock"),Quantity+2)
Returns a string, x, with all occurrences of the string, y, replaced by the string z. Replace accepts three field parameters of type String, or string values.
Syntax:
Replace(String,String,String)
Example:
Replace(Description, "ing", "tion")
Rounds the specified value in the first parameter to the number of decimal places specified in the second parameter, rounding up if the number in the N+1 decimal place is 5 or greater, and rounding down otherwise. Round accepts two parameters that can be fields of type Integer, Float, or Decimal, or numeric values.
Syntax:
Round(Number,N)
Example:
Round(Sales,2)
In this example, if Sales value is 12.345, it is rounded to 12.35.
Returns the second value in the range 0-59. Second accepts one field parameter of type DateTime.
Syntax:
Second(DateTime)
Example:
Second({Last Modified})
Returns the standard deviation of the values in a column.
Syntax:
STDDEV(column))
Example:
STDDEV(PROCESS_OPEN_HOURS))
Returns the number of standard deviations from the mean for a particular value.
CQL does not support this function.
Syntax:
STDDEVSFROMMEAN(column))
Example:
STDDEVSFROMMEAN(PROCESS_OPEN_HOURS))
Returns a substring z characters long from string x, starting at position y. Substring requires three parameters, the first of which must be a string value, or a field of type String, and the second and third of which must be an integer or field of type Integer.
Syntax:
Substring("source_string", start_position, substring_length)
Example:
Substring("some string", 6, 3)
// returns "str"
Returns a summation of all values for the specified field. Sum accepts one field parameter of type Integer, Float, or Decimal.
Syntax:
Sum(Number)
Example:
Sum(Total)
Creates a Switch statement. Switch can accept fields, expressions, and values of any type as parameters.
Syntax:
Switch(w) Case(x):(y) Default(z)
Example:
Specifies the second clause of an If-Then-Else statement. Then can accept fields, expressions, and values of any type as parameters.
Syntax:
If(x) Then(y) Else(z)
Example:
IF(Process Instance Status=="ACTIVE") THEN(DATEDIFF(SQL_TSI_DAY,Process Start Time,NOW())) ELSE(DATEDIFF(SQL_TSI_DAY,Process End Time,NOW()))
Trims the whitespace characters (space, tab, carriage return, line feed, page feed, form feed, and so on) from the end of the string. TrimEnd accepts one field parameter of type String. You can also enter an expression that returns a string value.
Syntax:
TrimEnd(String)
Example:
TrimEnd(Description) TrimEnd(Concat(Description,Subcategory))
Trims the whitespace characters (space, tab, carriage return, line feed, page feed, form feed, and so on) from the beginning of the string. TrimStart accepts one field parameter of type String. You can also enter an expression that returns a string value.
Syntax:
TrimStart(String)
Example:
TrimStart(Description) TrimStart(Concat(Description,Subcategory))
Converts a string to uppercase letters. Upper accepts one parameter of type String. You can also enter an expression that returns a string value.
Syntax:
Upper(String)
Example:
Upper({License Plate Number})
Returns the variance of the values in a column.
Syntax:
VARIANCE(column))
Example:
VARIANCE(PROCESS_OPEN_HOURS))
Returns the variance from the mean for a particular value.
CQL does not support this function.
Syntax:
VARIANCESFROMMEAN(column))
Example:
VARIANCESFROMMEAN(PROCESS_OPEN_HOURS))
Returns the week for a DateTime value, in the range 0 to 53, because there might be the beginning of a week 53, where Sunday is the first day of the week. Week 1 is the first week with a Sunday in this year.
For example, in the year 2006, January 1st is a Sunday, so there is no week 0. The year starts with week 1 and continues to week 53. Week 53 of 2006 includes only one day, which is December 31st (also a Sunday). The Monday through Saturday following this (January 1-6 of 2007) are in week 0 of 2007.
Syntax:
Week(DateTime)
Example:
Week({Last Modified})