QPR ProcessAnalyzer Objects in Expression Language: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
(48 intermediate revisions by the same user not shown)
Line 1: Line 1:
==[[DataFrame in Expression Language|DataFrame]]==
==Filter==
 
Filters contain a set of filter rules used to filter cases and events in models. Filters are objects located in the models. Filters are owned by the creator user, and when a filter publish mode is private, only the creator can use it.
== Datatable ==
Datatables are used to store data in QPR ProcessAnalyzer persistently. Datatables consist of one-to-many named columns and zero-to-many rows. The datatable entity in the expression language is used to access Datatables metadata, such as name, description and audit information. The actual data contents can be accessed by using the DataFrame property, which causes the actual data to be loaded from the database into memory for calculations.
 
Datatables have many usecases:
* [[#Model|Models]] can load their data from Datatables.
* Datatables can be used to store data extracted from external systems
* Intermediate ETL calculation results can be stored to Datatables
* It's possible to visualize data from Datatables directly in the dashboards without building a QPR ProcessAnalyzer model
 
Datatables are stored to QPR ProcessAnalyzer database as database tables. Each datatable belongs to a [[#Project|project]].


{| class="wikitable"
{| class="wikitable"
!'''Datatable properties'''
!'''Filter properties'''
! '''Description'''
! '''Description'''
|-
||ColumnNames (String*)
||Array of Datatable column names in the order they are in the DataTable. The DataTable doesn't need to be loaded into memory to get the column names.
|-
||Columns (Dictionary*)
||
Array of Datatable column metadata in dictionaries. Each dictionary has '''Name''' and '''Datatype''' properties.
Example: 3rd column data type:
<pre>
DatatableById(123).Columns[2].Datatype
</pre>
|-
||Configuration (Dictionary)
||Returns the datatable configuration as dictionary. Example:
<pre>
DatatableById(123).Configuration.Columns[0].Datatype
</pre>
|-
||ConfigurationJson (String)
||Returns the datatable configuration as json string.
|-
|-
||CreatedBy (User)
||CreatedBy (User)
||User who created the datatable.
||Returns the user who created the filter.
|-
|-
||CreatedDate (DateTime)
||CreatedDate (DateTime)
||Timestamp when the datatable was created.
||Returns date when the filter created date.
|-
||DataFrame (DataFrame)
||Contents of the datatable as a [[#DataFrame|DataFrame]].
|-
||DataSourceType
||Physical storage location of the datatable data. Options:
* '''Local''': data is stored to QPR ProcessAnalyzer's own database in SQL Server (defined by the database connection string in the appsettings.json file).
* '''SqlServer''': data is stored to an SQL Server database (defined by the [[PA_Configuration_database_table#General_Settings|SqlServerConnectionString]] setting).
* '''Snowflake''': data is stored to Snowflake.
* '''Spark''': data is stored to Databricks.
* '''Redshift''': data is stored to AWS Redshift.
|-
|-
||Description (String)
||Description (String)
||Datatable description. The datatable description may contain line breaks.
||Returns description of the filter.
|-
|-
||Id (Integer)
||Id (Integer)
||Datatable Id. Datatable Id is generated by QPR ProcessAnalyzer when the datatable is created.
||Returns id of the filter.
|-
||LastImportBy (User)
||User who the last time imported or modified the data in the datatable. Modification covers adding new rows, modifying rows and deleting rows.
|-
||LastImportDate (DateTime)
||Timestamp when data was last time imported or modified in the datatable.
|-
|-
||LastModifiedBy (User)
||LastModifiedBy (User)
||User who last time modified the datatable properties. This does not cover the data contained by the datatable (for that, use the ''LastImportBy'' by property).
||Returns user who modified the filter.
|-
|-
||LastModifiedDate (DateTime)
||LastModifiedDate (DateTime)
||Timestamp when the datatable properties were last time modified (such as name, description or containing project). This does not cover the data contained by the datatable (for that, use the ''LastImportDate'' by property).
||Returns date when the filter last modified.
|-
|-
||Name (String)
||Model
||Name of the datatable.
||Returns model where the filter belongs to.
|-
|-
||NameInDataSource
||ModelId (Integer)
||Name of the table in the underlying datasource (e.g., Snowflake or SQL Server) containing the datatable data. This property is handy if need to load data directly to the table in the datasource.
||Returns model where the filter belongs to.
|-
|-
||NColumns (Integer)
||Name (String)
||Number of columns in the datatable.
||Returns the name of the filter.
|-
|-
||NRows (Integer)
||Project
||Number of data rows in the datatable.
||Returns project where the filter belongs to.
|-
|-
||Project (Project)
||ProjectId (Integer)
||[[#Project|Project]] the datatable belongs to.
||Returns project id where the filter belongs to.
|-
|-
||ProjectId (Number)
||PublishMode (String)
||Id of the project the datatable belongs to.
||Returns publish mode of the filter, one of the following: '''Private''', '''Public''', or '''Default'''.
|-
|-
||SqlDataFrame (SqlDataFrame)
||Rules (Dictionary)
||Returns [[SqlDataFrame_in_Expression_Language|SqlDataFrame]] that can be used to access the contents of the DataTable.
||Returns a dictionary containing the filter rules in the filter.
|}
|}


{| class="wikitable"
{| class="wikitable"
!'''Datatable functions'''
!'''Filter Function'''
!'''Parameters'''
!'''Parameters'''
! '''Description'''
! '''Description'''
|-
||AddColumn
||
* Column name (String)
* Data type (String)
||
Adds a new column to the Datatable. Existing rows in the Datatable will get ''null'' values for the created column. The function returns the updated Datatable entity.
Available data types are ''String'', ''Integer'', ''Float'', ''DateTime'', ''Boolean'', ''Duration'' (Timespan) and ''Any'' (can contain any type of data).
Example: Add three columns to the Datatable with id 123.
<pre>
DatatableById(123)
  .AddColumn("MyColumn1", "String")
  .AddColumn("MyColumn2", "Float")
  .AddColumn("MyColumn3", "DateTime")
</pre>
|-
|-
||DeletePermanently
||DeletePermanently
||(none)
||(none)
||
||
Deletes the Datatable permanently. This will lead to a lost of all data in the Datatable. Datatables cannot be moved to the recycle bin, so they can only be deleted permanently.
Deletes the filter permanently. To delete own filters, the '''Filtering''' permission is needed, and to delete any filters the '''ManageViews''' permission is needed.
 
Example: Delete permanently Datatable with id 123.
<pre>
DatatableById(123).DeletePermanently()
</pre>
|-
|-
||Import
||Modify
||Data to import (DataFrame)
||Dictionary
||
||
Imports data to the datatable from given DataFrame. Columns between datatable andDataFrame are matched with names (the column order does not matter). Columns where there are no values in the DataFrame are imported as ''null'' values.
Modifies filter properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: ''Name'', ''Description'', ''PublishMode'', and ''Rules''.
 
Following parameters affect the behavior of the import:
* '''Append''': When ''true'' (default), existing data in the datatable is preserved and the imported data is created as new rows. When ''false'', existing rows in the datatable are removed (i.e. import overwrites the existing data). Values of not imported columns will be set to ''null''. Note that when Append=false, and the imported data doesn't have any rows, the import just removes all rows from the datatable.
* '''ImportExistingColumnOnly''': When ''true'' (default), the appending import does not create any new columns. When ''false'' and there are columns in the imported data that are not in the datatable, the appending import creates the new columns to the datatable. Note that this setting does not have effect, when using non-appending import.
* '''MatchByColumns''': Array of column names, which are used to match the imported data with the existing data in the datatable. When matching rows are found, they are updated instead of creating new rows. When no match is found, a new row is created for the imported row. By default, the matching is not used.
 
If the imported DataFrame and the target datatable are located in the same system, the import is performed within the system, so no data is transferred from the system to in-memory. Otherwise, the DataFrame is first loaded into memory and imported into the target datatable.


The import function returns the Datatable object itself (to allow chaining).
The function returns the updated filter object. Requires ''GenericWrite'' permission for the Project and global ''CreateModel'' permission.


Example:
Example:
<pre>
<pre>
DatatableById(123).Import(
FilterById(1)
  ToDataFrame(
.Modify(#{
    [
"Name": "My filter",
      ["Case 1", "A", DateTime(2021,1,2)],
"Description": "My description",
      ["Case 2", "B", DateTime(2021,1,5)],
"PublishMode": "Public",
      ["Case 2", "A", DateTime(2021,1,9)]
"Rules": #{
    ],
"Items": [
    ["Case", "Event type", "Time"]
#{
  ),
"Type": "IncludeCases",
  #{"Append": true}
"Items": [
)
#{
 
"Type": "CaseAttributeValue",
DatatableById(123).Import(
"Attribute": "Account Manager",
  ToDataFrame(
"StringifiedValues": [
    [
"0Mary Wilson"
      ["Case 1", "A", DateTime(2021,1,2)],
]
      ["Case 2", "B", DateTime(2021,1,5)],
}
      ["Case 2", "A", DateTime(2021,1,9)]
]
    ],
}
    ["Case", "Event type", "Time"]
]
  ),
}
  #{"MatchByColumns": ["Case"]}
}
)
)
</pre>
|-
||Merge
||Data to merge (DataFrame/SqlDataFrame)
||
Modifies the datatable by merging its contents with contents of given DataFrame. Merging works with the same principle and parameters as merging between in-memory [[DataFrame_in_Expression_Language#Merging_DataFrames|DataFrames]]
Examples:
<pre>
let target = ToDataFrame([[0, "zero", "target"], [1, "", "target"]], ["id", "text", "frame"])
  .Persist("test_data");
let source = ToDataFrame([[1, "one", "source"], [2, "two", "source"], [3, "three", "source"]], ["id", "text", "frame"]);
target.Merge(source, "id").DataFrame.ToCsv()
Returns string (one key, default parameters, identical dataframe columns):
id;text;frame
0;zero;target
1;one;source
2;two;source
3;three;source
target.Merge(source, "id", ["text"]).DataFrame.ToCsv()
Returns string (one key, default parameters, identical dataframe columns, copy only text column from source):
id;text;frame
0;zero;target
1;one;target
2;two;
3;three;
target.Merge(source, "id", ["text"], _remove).DataFrame.ToCsv()
Returns string (one key, default parameters, identical dataframe columns, copy only text column from source, remove rows found only in source):
id;text;frame
0;zero;target
1;one;target
target.Merge(source, "id", ["text"], _remove, false).DataFrame.ToCsv()
Returns string (one key, identical dataframe columns, copy only text column from source, remove rows found only in source or only in target):
id;text;frame
1;one;target
target.Merge(source, "id", _remove, _remove, false).DataFrame.ToCsv()
Returns string (one key, identical dataframe columns, remove all rows):
id;text;frame
target.Merge(source, "id", [], _remove, false).DataFrame.ToCsv()
Returns string (one key, filter out all the non-matching rows):
id;text;frame
1;;target
</pre>
|-
||RemoveColumns
||
Column names (String*)
||
Removes the given columns from the datatable. Data in the removed columns is also lost permanently. The function returns the updated datatable entity.
Example: Remove three columns from the datatable with id 123.
<pre>
DatatableById(123).RemoveColumns(["MyColumn1", "MyColumn2", "MyColumn3"])
</pre>
|-
||RenameColumns
||
Column name mappings (Dictionary)
||
Changes names of one or several columns in the datatable. The function returns the updated datatable entity.
Examples: Rename one/two columns in the datatable with id 123.
<pre>
DatatableById(123).RenameColumns(#{"MyColumnNewName": "MyColumnOldName"})
DatatableById(123).RenameColumns(#{
  "MyColumnNewName1": "MyColumnOldName1",
  "MyColumnNewName2": "MyColumnOldName2"
})
</pre>
|-
||Synchronize
||(none)
||
Refreshes the datatable information from the datasource (e.g., Snowflake) to QPR ProcessAnalyzer. This function needs to be called when the underlying table in the datasource is modified for QPR ProcessAnalyzer to be aware of the modifications. This is typically done right after the ETL run (to add data to the tables) has completed. Note that when data is imported to datatables using QPR ProcessAnalyzer user interface or scripts, calling the synchronize function is not needed.
Calling synchronize will perform the following actions:
* Clears datatable metadata cache, such as the row count and datatable contents.
* Changes the last imported date to current time.
* Refresh columns metadata from datasource. Columns with unsupported data types are treated as strings.
* If columns are changed, the last modified date is updated to current time.
Calling synchronize requires the same permissions as for importing data, i.e., ''GenericWrite'' for the project and global ''CreateModel'' permissions.
Example:
<pre>
DatatableById(123).Synchronize();
</pre>
|-
||Truncate
||(none)
||
Truncates the datatable, i.e., removes all rows from it. Truncate is a very efficient operation to remove all rows comparing using the delete operation.
Example:
<pre>
DatatableById(123).Truncate();
</pre>
</pre>
|}
|}


Function to get a datatable by id:
Function to get filter id:
{| class="wikitable"
{| class="wikitable"
!'''Function'''
!'''Function'''
Line 282: Line 97:
! '''Description'''
! '''Description'''
|-
|-
||DataTableById
||FilterById
||
||
datatable id (Integer)
* Filter id (Integer)
||
||
Return datatable with given id. An exception is given if a datatable with the given id does not exist or user doesn't have permissions to it.
Returns Filter object corresponding to the provided filter id.
 
Examples:
<pre>
DataTableById(27)
Returns: DataTable for the data table having id 27.
</pre>
|}
|}


== Model==
==Model==
Notes:
* For in-memory models that are offline, the object counts represent the situation when the model was last time online (loaded into the memory). ''null'' is returned if the model has never been loaded into the memory.
* If [[Case_Level_Permissions|Case permissions]] are used for the model, and user doesn't have '''GenericWrite''' permission for the model, ''null'' is returned for data security reasons. Users that have the '''GenericWrite''' permission, see null when the model is offline, and when online, they see counts where the case level permissions settings are applied.
* Properties ''CaseAttributes'', ''EventAttributes'' and ''Eventlog'' work only for the in-memory models and they require the model to be loaded into the memory. If the model is not in the memory, it is loaded when these properties is used. Other model properties down require the model to be in the memory.


{| class="wikitable"
{| class="wikitable"
Line 328: Line 141:
||DefaultCalendar (BusinessCalendar)
||DefaultCalendar (BusinessCalendar)
||Returns the default [[Business_Calendar|business calendar]] of the Model. Returns ''null'', if there are no calendars in the Model or no calendar has been set as a default calendar. Note: UI allows to set only one business calendar for a Model, which is also the default calendar.
||Returns the default [[Business_Calendar|business calendar]] of the Model. Returns ''null'', if there are no calendars in the Model or no calendar has been set as a default calendar. Note: UI allows to set only one business calendar for a Model, which is also the default calendar.
|-
||DefaultFilter (Filter)
||Default filter of the model. Returns ''null'' if the model does not have a default filter.
|-
|-
||DefaultFilterId (Integer)
||DefaultFilterId (Integer)
Line 352: Line 168:
||EventLog (EventLog)
||EventLog (EventLog)
||EventLog containing the entire model (i.e. event log where no filters have been applied). Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
||EventLog containing the entire model (i.e. event log where no filters have been applied). Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
|-
||Filters (Filter*)
||Returns an array of all [[#Filter|filters]] in the model.
|-
|-
||Id (Integer)
||Id (Integer)
Line 364: Line 183:
||Name (String)
||Name (String)
||Model name.
||Model name.
|-
||NBookmarks (Integer)
||Number of bookmarks in the model.
|-
|-
||NCache (Integer)
||NCache (Integer)
Line 372: Line 188:
|-
|-
||NCaseAttributes (Integer)
||NCaseAttributes (Integer)
||Number of [[#AttributeType|CaseAttributes]] in model.
||Number of [[#AttributeType|CaseAttributes]] in model. Works only for in-memory models.
|-
|-
||NCases (Integer)
||NCases (Integer)
||Number of [[#Case|Cases]] in the model.
||Number of [[#Case|Cases]] in the model. Works only for in-memory models.
|-
|-
||NEventAttributes (Integer)
||NEventAttributes (Integer)
||Number of [[#AttributeType|EventAttributes]] in model.
||Number of [[#AttributeType|EventAttributes]] in model. Works only for in-memory models.
|-
|-
||NEvents (Integer)
||NEvents (Integer)
||Number of [[#Event|Events]] in model.
||Number of [[#Event|Events]] in model. Works only for in-memory models.
|-
|-
||NEventTypes (Integer)
||NEventTypes (Integer)
||Number of [[#EventType|EventTypes]] in the model.
||Number of [[#EventType|EventTypes]] in the model. Works only for in-memory models.
|-
||NFilters (Integer)
||Number of filters in the model.
|-
|-
||NOpens (Integer)
||NOpens (Integer)
Line 393: Line 206:
|-
|-
||Project (Project)
||Project (Project)
||[[#Project|Project]] the model belongs to.
||[[#Project|Project]] where the model belongs to.
|-
|-
||ProjectId (Integer)
||ProjectId (Integer)
||[[#Project|Project]] id the model belongs to.
||[[#Project|Project]] id where the model belongs to.
|-
|-
||Status (String)
||Status (String)
Line 405: Line 218:
* '''Online''': The model is in the memory and ready for analysis calculation. If the model is dropped from the memory, its status changes to ''offline''.
* '''Online''': The model is in the memory and ready for analysis calculation. If the model is dropped from the memory, its status changes to ''offline''.
|}
|}
Notes:
* For models that are offline, the object counts represent the situation when the model was last time online (loaded into the memory). ''null'' is returned if the model has never been loaded into the memory.
* If [[Case_Level_Permissions|Case permissions]] are used for the model, and user doesn't have '''GenericWrite''' permission for the model, ''null'' is returned for data security reasons. Users that have the '''GenericWrite''' permission, see null when the model is offline, and when online, they see counts where the case level permissions settings are applied.
* Using ''CaseAttributes'', ''EventAttributes'' and ''Eventlog'' properties requires the model to be loaded into the memory. If the model is not in the memory, it is loaded when these properties is used. Other Model object properties down require the model to be in the memory.


{| class="wikitable"
{| class="wikitable"
Line 425: Line 233:
<pre>
<pre>
ModelById(123).CalendarByName("MyCalendar")
ModelById(123).CalendarByName("MyCalendar")
</pre>
|-
||CreateFilter (Filter)
||Parameters dictionary
||Creates a filter to a model. Requires ''GenericWrite'' permission for the project and global ''CreateModel'' permission. If a filter with that name already exists in the model, an exception is thrown.
The parameters dictionary may have the following properties:
* '''Name''': Name of the filter. This property is mandatory.
* '''Description''': Description of the filter. This property is optional.
* '''Rules''': Filter rules for the filter defined as a dictionary according to the [[Filtering_in_QPR_ProcessAnalyzer_Queries|filter json format]]. This property is mandatory.
* '''PublishMode''': Publish mode of the filter which is one of the following: ''Private'', ''Public'' or ''Default''. This property is optional, and the default value is ''Private''.
Example:
<pre>
let newFilter = modelById(1).CreateFilter(#{   
  "Name": "My Filter",
  "Rules": #{
    "Items": [#{
      "Type": "IncludeCases",
      "Items": [#{
        "Type": "CaseAttributeValue",
        "Attribute": "Account Manager",
        "StringifiedValues": [ "0Robert Miller" ]
      }]
    }]
  },
  "PublishMode": "Public"
});
}
</pre>
</pre>
|-
|-
Line 430: Line 266:
||(none)
||(none)
||Deletes the Model permanently. The model doesn't need to be in the recycle bin to be able to delete it permanently.
||Deletes the Model permanently. The model doesn't need to be in the recycle bin to be able to delete it permanently.
|-
||Modify (Model)
||Dictionary
||
Modifies model properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: ''Name'', ''Description'', ''ProjectId'', and ''Configuration''.
The function returns the updated model object. Requires the ''GenericWrite'' permission for the project and the global ''CreateModel'' permission.
Example:
<pre>
ModelById(1)
.Modify(#{
"Name": "My model",
"Description": "My description",
"ProjectId": 2,
"Configuration": #{
"DataSource": #{
"Cases": #{
"DataSourceType": "datatable",
"DataTableName": "My cases datatable",
"Columns": #{
"CaseId": "Case Name"
}
},
"Events": {
"DataSourceType": "datatable",
"DataTableName": "My events datatable",
"Columns": #{
"CaseId": "Case Name",
"EventType": "Event Type",
"Timestamp": "Start Time"
}
}
}
}
}
)
</pre>
|-
|-
||ResetPreprocessings
||ResetPreprocessings
Line 532: Line 406:
let myProject = ProjectById(123);
let myProject = ProjectById(123);
myProject.CreateDatatable("Snowflake datatable", #{"Connection": myProject.CreateSnowflakeConnection()});
myProject.CreateDatatable("Snowflake datatable", #{"Connection": myProject.CreateSnowflakeConnection()});
</pre>
|-
||CreateModel (Model)
||
* Parameters dictionary
||Creates a model to a project. Requires ''GenericWrite'' permission for the Project and global ''CreateModel'' permission. If a model with that name already exists, an exception is thrown.
Parameters dictionary has the following properties:
* '''Name''': Name of the model. This property is mandatory.
* '''Description''': Description of the model. This property is optional.
* '''Configuration''': Configuration dictionary for the model. This property is technically optional, but a working model requires at least datasource settings to be defined.
Example:
<pre>
ProjectById(1).CreateModel(#{   
  "Name": "My model",
  "Description": "My description",
  "Configuration": {
    "DataSource": {
      "Cases": {
        "DataSourceType": "datatable",
        "DataTableName": "My cases datatable",
        "Columns": {
          "CaseId": "Case Name"
        }
      },
      "Events": {
        "DataSourceType": "datatable",
        "DataTableName": "My events datatable",
        "Columns": {
          "CaseId": "Case Name",
          "EventType": "Event Type",
          "Timestamp": "Start Time"
        }
      }
    }
  }
});
</pre>
</pre>
|-
|-
Line 802: Line 714:
|}
|}


 
Function to get User by user id:
[[Category: QPR ProcessAnalyzer]]
{| class="wikitable"
!'''Function'''
!'''Parameters'''
! '''Description'''
|-
||UserById
||
* User id (Integer)
||
Returns User object corresponding to the provided user id.
|}

Revision as of 21:43, 11 September 2023

Filter

Filters contain a set of filter rules used to filter cases and events in models. Filters are objects located in the models. Filters are owned by the creator user, and when a filter publish mode is private, only the creator can use it.

Filter properties Description
CreatedBy (User) Returns the user who created the filter.
CreatedDate (DateTime) Returns date when the filter created date.
Description (String) Returns description of the filter.
Id (Integer) Returns id of the filter.
LastModifiedBy (User) Returns user who modified the filter.
LastModifiedDate (DateTime) Returns date when the filter last modified.
Model Returns model where the filter belongs to.
ModelId (Integer) Returns model where the filter belongs to.
Name (String) Returns the name of the filter.
Project Returns project where the filter belongs to.
ProjectId (Integer) Returns project id where the filter belongs to.
PublishMode (String) Returns publish mode of the filter, one of the following: Private, Public, or Default.
Rules (Dictionary) Returns a dictionary containing the filter rules in the filter.
Filter Function Parameters Description
DeletePermanently (none)

Deletes the filter permanently. To delete own filters, the Filtering permission is needed, and to delete any filters the ManageViews permission is needed.

Modify Dictionary

Modifies filter properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: Name, Description, PublishMode, and Rules.

The function returns the updated filter object. Requires GenericWrite permission for the Project and global CreateModel permission.

Example: 

FilterById(1)
	.Modify(#{
		"Name": "My filter",
		"Description": "My description",
		"PublishMode": "Public",
		"Rules": #{
			"Items": [
				#{
					"Type": "IncludeCases",
					"Items": [
						#{
							"Type": "CaseAttributeValue",
							"Attribute": "Account Manager",
							"StringifiedValues": [
								"0Mary Wilson"
							]
						}
					]
				}
			]
		}
	}
)

Function to get filter id:

Function Parameters Description
FilterById
  • Filter id (Integer)

Returns Filter object corresponding to the provided filter id.

Model

Notes:

  • For in-memory models that are offline, the object counts represent the situation when the model was last time online (loaded into the memory). null is returned if the model has never been loaded into the memory.
  • If Case permissions are used for the model, and user doesn't have GenericWrite permission for the model, null is returned for data security reasons. Users that have the GenericWrite permission, see null when the model is offline, and when online, they see counts where the case level permissions settings are applied.
  • Properties CaseAttributes, EventAttributes and Eventlog work only for the in-memory models and they require the model to be loaded into the memory. If the model is not in the memory, it is loaded when these properties is used. Other model properties down require the model to be in the memory.
Model properties Description
Calendars (BusinessCalendar*)

Returns all business calendars stored to the Model as an array. Returns an empty array, if there are no business calendars stored to the model. Note: UI allows to set only one business calendar for a Model.

CaseAttributes (AttributeType*) CaseAttributes in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
CasesDatatable (Datatable) Returns the Datatable the model uses as a datasource for cases. Returns null if the cases Datatable is not defined or if model uses other than the Datatable datasource.
Configuration (Dictionary) Returns the Model configuration as dictionary. Example: 
ModelById(123).Configuration.DataSource.Events.DataTableName
ConfigurationJson (String) Returns the Model configuration as JSON string.
CreatedBy (User) User who created the model.
CreatedDate (DateTime) Timestamp when the model was created.
DefaultCalendar (BusinessCalendar) Returns the default business calendar of the Model. Returns null, if there are no calendars in the Model or no calendar has been set as a default calendar. Note: UI allows to set only one business calendar for a Model, which is also the default calendar.
DefaultFilter (Filter) Default filter of the model. Returns null if the model does not have a default filter.
DefaultFilterId (Integer) Default filter id of the model. Returns null if the model does not have a default filter.
Description (String) Model description. The model description may contain line breaks.
DeletedDate (DateTime) Timestamp when Model was deleted (moved to the recycle bin).
DeletedBy (User) User how deleted the Model.
EstimatedMemory (Integer) Returns an estimation of how much memory in bytes the model requires.
EventsDatatable (Datatable) Returns the Datatable the model uses as a datasource for events. Returns null if the events Datatable is not defined or if model uses other than the Datatable datasource.
EventAttributes (AttributeType*) EventAttributes in the model returned in the alphabetical order. Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
EventLog (EventLog) EventLog containing the entire model (i.e. event log where no filters have been applied). Using this property requires that the model is loaded in the memory. If the model is not in the memory, it's loaded when this property is used.
Filters (Filter*) Returns an array of all filters in the model.
Id (Integer) Model Id. Model Id is generated by QPR ProcessAnalyzer when the model is created.
LastModifiedBy (User) User who last time modified the model properties. Note that datatables containing the eventlog data are separate objects having similar fields to track the last modification and last data import.
LastModifiedDate (DateTime) Timestamp when the model was modified the last time.
Name (String) Model name.
NCache (Integer) Number of objects related to the model when the model is loaded into the memory.
NCaseAttributes (Integer) Number of CaseAttributes in model. Works only for in-memory models.
NCases (Integer) Number of Cases in the model. Works only for in-memory models.
NEventAttributes (Integer) Number of EventAttributes in model. Works only for in-memory models.
NEvents (Integer) Number of Events in model. Works only for in-memory models.
NEventTypes (Integer) Number of EventTypes in the model. Works only for in-memory models.
NOpens (Integer) Number of times analysis has been requested from the model.
Project (Project) Project where the model belongs to.
ProjectId (Integer) Project id where the model belongs to.
Status (String)

Memory availability status of the model. There are the following statuses:

  • Loading: The model is currently loading into the memory. When the loading is ready, the status changes to online. If the loading fails, the status changes to offline.
  • Offline: The model is currently not loaded into the memory. The model needs to be loaded into the memory, so that analyses can be calculated from the model (occurs automatically when an analysis is requested).
  • Online: The model is in the memory and ready for analysis calculation. If the model is dropped from the memory, its status changes to offline.
Model Function Parameters Description
CalendarByName (BusinessCalendar)

name (String)

Returns a business calendar stored to the Model by the name of the calendar. Business calendars can be stored to models in the model properties. Returns null, if a calendar with the provided name is not stored to the model.

Examples: 

ModelById(123).CalendarByName("MyCalendar")
CreateFilter (Filter) Parameters dictionary Creates a filter to a model. Requires GenericWrite permission for the project and global CreateModel permission. If a filter with that name already exists in the model, an exception is thrown.

The parameters dictionary may have the following properties:

  • Name: Name of the filter. This property is mandatory.
  • Description: Description of the filter. This property is optional.
  • Rules: Filter rules for the filter defined as a dictionary according to the filter json format. This property is mandatory.
  • PublishMode: Publish mode of the filter which is one of the following: Private, Public or Default. This property is optional, and the default value is Private.

Example: 

let newFilter = modelById(1).CreateFilter(#{    
  "Name": "My Filter",
  "Rules": #{
    "Items": [#{
      "Type": "IncludeCases",
      "Items": [#{
        "Type": "CaseAttributeValue",
        "Attribute": "Account Manager",
        "StringifiedValues": [ "0Robert Miller" ]
      }]
    }]
  },
  "PublishMode": "Public"
});
}
DeletePermanently (none) Deletes the Model permanently. The model doesn't need to be in the recycle bin to be able to delete it permanently.
Modify (Model) Dictionary

Modifies model properties. The parameter is a dictionary containing the properties to be changed. Following properties can be changed: Name, Description, ProjectId, and Configuration.

The function returns the updated model object. Requires the GenericWrite permission for the project and the global CreateModel permission.

Example: 

ModelById(1)
	.Modify(#{
		"Name": "My model",
		"Description": "My description",
		"ProjectId": 2,
		"Configuration": #{
			"DataSource": #{
				"Cases": #{
					"DataSourceType": "datatable",
					"DataTableName": "My cases datatable",
					"Columns": #{
						"CaseId": "Case Name"
					}
				},
				"Events": {
					"DataSourceType": "datatable",
					"DataTableName": "My events datatable",
					"Columns": #{
						"CaseId": "Case Name",
						"EventType": "Event Type",
						"Timestamp": "Start Time"
					}
				}
			}
		}
	}
)
ResetPreprocessings (none)

Removes all cached items related to the Model, e.g. preprocessings and calculation results. In practice, the Model is reset to a state where it was right after the model was loaded into memory.

Restore (none) Restores the Model from the recycle bin back to the original location.
TriggerNotifications (Boolean) Notification names (String*) Triggers the given notifications for the Model. Notifications are given by their names. Triggering means that the configured rules are run and notification emails are sent as defined by the rules. If the notification names parameter is not provided, all notifications in the Model are triggered.

The function return true if any notification were triggered, otherwise false. 

ModelById(123).TriggerNotifications(["Notification 1", "Notification 2"]);
Triggers notifications Notification 1 and Notification 2 in model id 123.

ModelById(123).TriggerNotifications();
Triggers all notifications in model id 123.

Function to get Model by model id:

Function Parameters Description
ModelById
  • Model id (Integer)

Returns Model object corresponding to the provided model id.

Project

Project properties Description
CreatedBy (User) User who created the Project.
CreatedDate (DateTime) Timestamp when the Project was created.
Datatables (Datatable*) Returns all Datatables in the project.
DeletedDate (DateTime) Timestamp when the Project was deleted (moved to the recycle bin).
Description (String) Project description. The project description may contain line breaks.
DeletedBy (User) User who deleted the Project (moved to the recycle bin).
Id (Integer) Id of the Project.
LastModifiedBy (User) User who last modified the Project.
LastModifiedDate (DateTime) Timestamp when the Project was last modified (refers to the project name, description and parent, not the contents of the project).
Name (String) Name of the Project.
Models (Model*) Models that are in the Project.
Parent (Project) Parent project, i.e. a Project where the Project is located in the hierarchy of Projects. Returns null for root level Projects.
Scripts (Script*) Scripts that are in the Project.
Project functions Parameters Description
CreateDatatable (Datatable)
  • Datatable name (String)
  • Additional parameters (Dictionary)
 Creates datatable to the project. The first parameter is the name of the datatable. After creation, there are no columns or rows in the datatable. The function returns the created datatable entity. In the additional parameters, the datasource type can be specified to create a Snowflake stored datatable.

Example of creating a local datatable: 

ProjectById(123).CreateDatatable("My datatable")

Example of creating Snowflake stored datatable: 

let myProject = ProjectById(123);
myProject.CreateDatatable("Snowflake datatable", #{"Connection": myProject.CreateSnowflakeConnection()});
CreateModel (Model)
  • Parameters dictionary
 Creates a model to a project. Requires GenericWrite permission for the Project and global CreateModel permission. If a model with that name already exists, an exception is thrown.

Parameters dictionary has the following properties:

  • Name: Name of the model. This property is mandatory.
  • Description: Description of the model. This property is optional.
  • Configuration: Configuration dictionary for the model. This property is technically optional, but a working model requires at least datasource settings to be defined.

Example: 

ProjectById(1).CreateModel(#{    
  "Name": "My model",
  "Description": "My description",
  "Configuration": {
    "DataSource": {
      "Cases": {
        "DataSourceType": "datatable",
        "DataTableName": "My cases datatable",
        "Columns": {
          "CaseId": "Case Name"
        }
      },
      "Events": {
        "DataSourceType": "datatable",
        "DataTableName": "My events datatable",
        "Columns": {
           "CaseId": "Case Name",
           "EventType": "Event Type",
           "Timestamp": "Start Time"
        }
      }
    }
  }
});
DatatableByName (Datatable) Datatable name (String)

Returns Datatable by its name located in the project. Returns null, if Datatable with that name does not exist in the project.

Example: 

ProjectById(123).DatatableByName("MyDatatable1")

Example: Get datatable by name, and create it if it doesn't exist: 

let project = ProjectById(123);
let datatableName = "MyDatatable1";
let datatable = project.DatatableByName(datatableName);
if (datatable == null) {
  datatable = project.CreateDatatable(datatableName);
}
DeletePermanently (none) Deletes the Project permanently. Note that the Project doesn't need to be in the recycle bin to be able to delete it permanently.
Restore (none) Restores the Project from the recycle bin back to the original location.
ModelByName (Model) Model name (String)

Returns Model by its name located in the project. Returns null, if Model with that name does not exist in the project.

Example: 

ProjectById(123).ModelByName("My Model 1")
ScriptByName (Script) Script name (String)

Returns Script by its name located in the project. Returns null, if Script with that name does not exist in the project.

Example: 

ProjectById(123).ScriptByName("MyScript1")

Function to get Project by id:

Function Parameters Description
ProjectById Project id (Integer)

Returns Project object corresponding to the provided project id.

Script

Scripts are entities that contain executable code, that can be run. Usually scripts contains ETL routines but also other kind of tasks are possible.

Script properties Description
Code (String) Script code.
CreatedBy (User) User who created the Script.
CreatedDate (DateTime) Timestamp when the Script was created.
CurrentRunStart (DateTime) Timestamp of the current run start. Null if the script is currently not running.
Description (String) Description of the Script.
Id (Integer) Id of the Script.
Language (String) Either of the following scripting language: Expression or SQL. When language is Expression, the script is run as an expression script, and when language is SQL, the script is run as an SQL script (using the sandbox database).
LastModifiedBy (User) User who last modified the Script.
LastModifiedDate (DateTime) Timestamp when the Script was last modified.
LastRunEnd (DateTime) Timestamp of the last completed script run end (either successful completion or failure). Null if the Script hasn't been run yet.
LastRunResult (String) Result of the last run. Options are:
  • Completed: The last run was completed successfully.
  • Failed: An error occurred during the last run, so likely the script did not complete as intended.
  • Aborted: Script run was manually stopped prematurely by a user, so the script did not proceeded in the end.

Null if the Script hasn't been run yet.

LastRunStart (DateTime) Timestamp of the last completed script run start time. Null if the Script hasn't been run yet.
Name (String) Name of the Script.
OperationId (Integer) Id of the operation which runs the Script. Null if the script is currently not running.
Project (Project) Project where the Script is located. Null if the script is in the global context.
ProjectId (Integer) Id of the project where the Script is located. Null if the script is in the global context.
Status (String) Current status of the script. Options are:
  • Ready: Script is not running. In this status, the script can be started (changing the status to Running).
  • Running: Script is running. In this status, the script can be stopped (changing the status to Stopping). Calling stop just requests a script to stop, and the actual stopping occurs some time later.
  • Stopping: Script has been requested to be stopped, but it's still running. In this status, neither start nor stop can be called for the script. When the script eventually stops, its status changes to Ready.
Script functions Parameters Description
Run (Object) Dictionary of parameters

Runs the script using the provided parameters. The parameters are available in the script as variables (see the example). Any type of variables can be passed to the script. Note that if the script assumes certain variables, but that they are not passed to the script, the script run will throw an error.

For SQL scripts, the passed parameters are available in the script as variables in format @_parameter_<ParameterName> where <ParameterName> is the name of the parameter, e.g. parameter_myParameter1. Only string type of parameters can be used, so any other type of data in parameter values is converted into strings.

The return value of the script is returned by the Run function. Expression scripts return a value with the return statement or alternatively the result of the last line of the script is the return value. If the script does not return any value, the Run function returns _empty. For SQL scripts, the return value is the last dataset produced by the script (returned as a DataFrame) (SQL scripts might create several datasets using the --#ShowReport command or the Show parameter).

When a script is called using the Run function, the called script status does not change, because the it's the parent script that is Running. Also the called script log is not filled, but instead the logging goes to the calling script.

It's possible to call a script using the Run function several times simultaneously.

If there is an error when running the called script, the Run function throws the error to the calling script.

Scripts are run in the script entity context, so for example the following properties are available:

  • Id: Script id
  • Name: Script name
  • Project.Id: Project id where the script is located
  • Project.Name: Name of the project where the script is located

Example: Following script (id 123) raises a specified number to a specified power: 

return Pow(numberToRaise, exponent);

The script can be called as follows: 

let runResult = ScriptById(123).Run(#{
  "numberToRaise": 4,
  "exponent": 2
})
Returns: 16

Function to get a script by the script id:

Function Parameters Description
ScriptById
  • Script id (Integer)

Returns Script object corresponding to the given script id. If script with the given id doesn't exist or user doesn't have permissions to it, an error is given.

User/Group

User objects represents users and user groups. Note that some properties can only be used for users and some for groups.

User/group properties Description
Description (String) Description of the user.
Email (String) Email address of the user.
FullName (String) Full name of the user or group name.
GlobalPermissions (String*) Array of global permissions of the user. Global permissions come from the global roles assigned to the user and groups that the user belongs to. Note that to get the effective permissions for certain objects, also project specific permissions need to be taken into account.
GroupMemberNames (String*) Array of names of members of a user group. This property is available for groups.
GroupMembers (User*) Array of members of a user group. This property is available for groups.
GroupNames (String*) Array of names of user groups the user belongs to. This property is available for users.
Groups (User*) Array of user groups the user belongs to. This property is available for users.
Id (Integer) Id of the user, which is unique for every user.
IsActive (Boolean) Returns true only if the user is active (not disabled).
IsGroup (Boolean) Returns true if the user is a user group.
Name (String) Login name of the user or group.
Roles (Object**)

Returns all roles of the user (both global and project roles) as a nested array structure.

Example: 

ToJson(Users.Where(name == "qpr").Roles)
Returns (for example):
[
  [{"calcId": "Project:1"}, "Administrator"],
  [{"calcId": "Project:2"}, "Analyzer"],
  [{"calcId": "Project:3"}, "Viewer"],
  [null, "RunScripts"],
  [null, "Administrator"]
]
User/group functions Parameters Description
EffectivePermissionsFor (String Array)
  • Project to get permissions

Returns effective (actual) permission of the user to the given project. Project is given as a project object (not as a project id). Effective permissions determine the actual permissions that the user has, i.e. a combination of all permissions assigned to the user and groups the user belong to, including both project specific and global roles.

Permissions for the EffectivePermissionsFor function are as follows:

  • All users can query their own permissions
  • To get permissions for any user, the user needs to have ManageUsers permission.

Note that inactive users don't have any effective permissions, so the EffectivePermissionsFor function does not return any permissions for those users.

Examples: 

EffectivePermissionsFor(ModelById(1234).Project)
Returns (for example): ["EditDashboards", "Filtering", "GenericRead"]

Function to get User by user id:

Function Parameters Description
UserById
  • User id (Integer)

Returns User object corresponding to the provided user id.

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=QPR_ProcessAnalyzer_Objects_in_Expression_Language&oldid=23542"