===== 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 [[dashboard_views|database views]] have to be used. The new structure can be seen [[en:software:dashboard:dashboard_tablestructure_v2|here ]].

<note tip>​Please note the comments further down on the page!</note>
----

===== An Overview of the Tables ===== 
{{ software:dashboard:sqllogo.jpg }}

---- 

===== Explanation of the Table Contents =====

<note tip>the **clientID** field always references the id of the view_identity</note>
=== 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**: [[:en:support:cpm|The calculated earliest starting time]]
  * **calculatedEFT**: [[:en:support:cpm|The calculated earliest ending time]]
  * **calculatedLST**: [[:en:support:cpm|The calculated latest point in time for starting]]
  * **calculatedLFT**: [[:en:support:cpm|The calculated latest point in time for ending]]
  * **valueEST**: The net time specification for [[:en:support:cpm|the earliest starting point]] (contains the [[:en:software:tim:calendar_profile|Business Calender]])
  * **valueEFT**: The net time specification for [[:en:support:cpm|the earliest ending point]](contains the [[:en:software:tim:calendar_profile|Business Calender]])
  * **valueLST**: The net time specification for [[:en:support:cpm|the latest starting point]](contains the [[:en:software:tim:calendar_profile|Business Calender]])
  * **valueLFT**: The net time specification for [[:en:support:cpm|the latest ending point]](contains the [[:en:software:tim:calendar_profile|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 [[:en:software:tim:deploy_processdefinition|deployed]]
  * **creationUserId**: The unique ID of the user who [[:en:software:tim:deploy_processdefinition|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 [[:en:software:tim:actionhandler:yearidgenerator|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 [[:en:software:tim:ad_hoc_tasks|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 [[:en:software:tim:userprofil_profile|userprofile]]
  * **notifyAsignee**: Field "Notify asignee if his task is done by somebody else" in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **notifyByMail**  : Field "Email-Notification preferred" in the [[:en:software:tim:userprofil_profile|userprofile]] or from the [[:en:software:tim:client_profile|client profile]]
  * **costCenter**  : Cost center of the user in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **department**: Department of the user in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **personnelNumber**  : Personnel number of the user in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **phoneNumber**  : Phone number of the user in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **tableRowCount**  : Field "Display table rows" in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **timezoneOffset**  : Timezone of the user in the [[:en:software:tim:userprofil_profile|userprofile]]
  * **clientId**  : A number corresponding to the impacted client
  * **lastModificationUserId**: User who did the last changes on this [[:en:software:tim:userprofil_profile|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 [[:en:software:tim:userprofil_profile|Userprofile]]
  * **supervisorId**  :The unique ID of the supervisor assigned to the user[[:en:software:tim:userprofil_profile|Userprofile]]
  * **companyId**  : Field "Company ID" of the user from the [[:en:software:tim:userprofil_profile|Userprofile]]
  * **companyName**: Field "Company Name" of the user from the [[:en:software:tim:userprofil_profile|Userprofile]]
  * **country**: Field "country" of the user from the [[:en:software:tim:userprofil_profile|Userprofile]]