Requirements for an analysis

A standard SQL statement can be initiated in order to conduct an analysis. For this analysis to be successful the predefined database views have to be used. The new structure can be seen here .

​Please note the comments further down on the page!

An Overview of the Tables

Explanation of the Table Contents

the clientID field always references the id of the view_identity

view_activity

id: The unique identification number used throughout the entire database

name: Name of the activity

clientId: A number referencing a specific client

creationTime: The exact point in time when the activity was initiated

creationUserId: A number referencing the impacted user

instanceId: A number which references an impacted instance in which the activity is active

start: The exact point in time when this activity was started

end: The exact point in time when the activity was finally terminated

loopCount: The number of runs of an activity

duration: The maximum duration of processing time assigned to this activity

escalationTime: The calculated point in time for the next escalation within the activity

estimatedEnd:​ Shows the estimated end-date of the acticity

milestone: This entry displays if this activity is considered to be a milestone indicating the achievement of a certain benchmark

mileStoneDate: The point in time when this milestone has to be accomplished

elapsedTime: Shows the time which has already been taken for task completion

inTime: Shows if the activity is still running in accordance with the time-plan

timeBuffer: Calculated “buffer” time for the activity

calculatedEST: The calculated earliest starting time

calculatedEFT: The calculated earliest ending time

calculatedLST: The calculated latest point in time for starting

calculatedLFT: The calculated latest point in time for ending

valueEST: The net time specification for the earliest starting point (contains the Business Calender)

valueEFT: The net time specification for the earliest ending point(contains the Business Calender)

valueLST: The net time specification for the latest starting point(contains the Business Calender)

valueLFT: The net time specification for the latest ending point(contains the Business Calender)

type: Returns the type of the activity (scriptnode, tasknode, mailnode, etc.)

view_definition

id: The unique identification number used throughout the entire database

name: The name of the process definition

clientId: A number referencing a specific client

creationTime: Point in time at which the process definition was deployed

creationUserId: The unique ID of the user who deployed the task instance

description: A description of the process definition

version: Version in which the process definition is found

escalationStatus: Information pertaining to the escalation status o the process

owner: The user or group responsible for the process

starter: User or group who has the right to start the definition

deployer: User or group who has the right to deploy the definition

archived: Tells whether or not the instance has already been archived

view_effort

id: The unique identification number used throughout the entire database

name: The name assigned to the effort

clientId: The number corresponding to the impacted client

creationTime: The point in time at which this effort was made

creationUserId: The unique ID of the user who is responsible for causing the effort

effortType: The type of effort (“time invested” or “money spent”)

value: Information on the value of the effort without using an unit

description: Description of the effort

costCenterName: The name of the cost center responsible for the effort

instanceId: The number corresponding to the impacted instance

parentFolderId: The number corresponding to the impacted virtual index

view_identity

id: The unique identification number used throughout the entire database

name: Name of the user

displayName: Contains the complete name and user name (i.e. John Doe (J. Doe))

clientId: A number corresponding to the client at hand

creationTime: The point in time when this identity/user was created

lastLogin: Contains the time at which the user last logged in

creationUserId: The number corresponding to of the user who created this user/group/client

identityType: The type of identity/user (e.g., user, group, client)

email: The e-mail address of the user or group

firstname: The first name of the user

lastname: The surname of the user

archived : Indicates if this iuser/group/client has already been archived

blocked: Indicates if this iuser/group/client is blocked

userID: Contains a number pointing to a user who belongs to this community

parentID: Contains the number, which points to the group who belongs to this community

view_instance

id: The unique identification number used throughout the entire database

name: The name of the process instance

definitionName: The name of the superordinate process definition

clientId: A number affiliated with client of the process instance

creationTime: The point in time at which the process instance was started

key: This displays the value generated by the YearIDGenerator, assuming that this functionality is enabled

creationUserId: A number corresponding to the user who started this process instance

definitionId: A number corresponding to the process definition that is at the base of this instance

instanceDescription: An additional description of the process instance

creationGroup: A number corresponsing to the group that initiated the process instance

end: This is the exact point in time when the process instance was terminated

archive: This reports if this process instance has already been archived

archivationUserId : This provides information on the UserID of the user who archived this instance

rootToken: This entry gives details about the progress of the process instance (the current node is marked by this token)

parentProcessToken: If the current process instance is only a subordinate process, then this is the token attributed to the related main process

nextEscalationTime: This entry shows the calculated point in time pertaining to the next escalation of the process

processVariableIndex: —-

index1-10 and value1-10: Important information of the process instance, the worth of which stands in value and the position is given over the index.

view_swimlane

id: The unique identification number used throughout the entire database

name: name of the swimlane

clientID: A number corresponding to the client at hand

instanceId: A number corresponding to the corresponding instance

actor: A number corresponding to the associated user

pooledActor: A number corresponding to the associated group

view_task

id: The unique identification number used throughout the entire database

name: Name of the task

clientId: A number referencing a specific client

creationTime: The exact point in time when the task was initiated

creationUserId: A number referencing the impacted user

instanceId: A number which references an impacted instance in which the task is active

start: The exact point in time when this task was started

end: The exact point in time when the task was finally terminated

isOpen: Denotes if a task has already been completed (Caution! not all signal-methods complete tasks)

swimlaneId: Contains a number referencing the swimlane in which the tasks is held

actor: A number corresponding to the associated user

pooledActor: Contains a number referencing the currently associated group

activity: contains a number there corresponds to the superordinate activity

isAdhoc: Shows whether this activity was ranked as an AdHoc-task or not

parentFolderId: A number corresponding to the impacted virtual index

archive: This reports if this task has already been archived

remainingTime: Shows the time which remains for task completion

elapsedTime: Shows the time which has already been taken for task completion

view_token

id: The unique identification number used throughout the entire database

name: The name of the token

clientId: A number referencing a specific client

nodeInstance: Contains the number of the activity corresponding ot this task

instanceId: Contains the number corresponding to the associated instance

parent: The number corresponding to the main process

view_variable

id: The unique identification number used throughout the entire database

name : Name of the instance variable

clientId: A number referencing a specific client

instanceId: A number referencing the corresponding instance

stringValue: Contains the content of the process variable

label: Contains the label associated with the process variable

view_role

identityId: Contains the number corresponding to the currently-associated user

roleId: Contains the number corresponding to the currently-associated role

view_costcenter

id: The unique identification number used throughout the entire database

clientId: A number referencing a specific client

creationTime: The exact point in time when the cost center was initiated

creationUserId: A number referencing the user who created the cost center

name : Name of the cost center

description: Description of the cost center

hourly: Contains the hourly rate of the cost center

currency: The currency in which the cost center is calculated

view_systemconfiguration

id: The unique identification number used throughout the entire database

name : The name of the configuration

creationTime: Contains the creation time of the configuration

lastModificationTime: Contains the time at which the profile was last modified

ignoreLDAPAuthentification: Field “ignore LDAP Authenfication” in the userprofile

notifyAsignee: Field “Notify asignee if his task is done by somebody else” in the userprofile

notifyByMail : Field “Email-Notification preferred” in the userprofile or from the client profile

costCenter : Cost center of the user in the userprofile

department: Department of the user in the userprofile

personnelNumber : Personnel number of the user in the userprofile

phoneNumber : Phone number of the user in the userprofile

tableRowCount : Field “Display table rows” in the userprofile

timezoneOffset : Timezone of the user in the userprofile

clientId : A number corresponding to the impacted client

lastModificationUserId: User who did the last changes on this userprofile

parentFolderId : A number corresponding to the virtual directory

defaultRepresentativeId : Contains a number that corresponds to the user who is stored as the substitute in the Userprofile

supervisorId :The unique ID of the supervisor assigned to the userUserprofile

companyId : Field “Company ID” of the user from the Userprofile

companyName: Field “Company Name” of the user from the Userprofile

country: Field “country” of the user from the Userprofile