Clarity Portlets NSQL session1

Overview

In this session we will take an introductory look at Clarity’s data model, how to create new attributes, and how to configure views with Clarity Studio. After the discussions there are some exercises.

You can download this programme as a PDF here.

Data Model

The Clarity data model is an amalgamation of its original products’ data models, and sometimes finding your round it can be difficult. Clarity’s Technical Manual is the only documentation released by CA, but it omits a lot of the tables that people would like to know more about whilst including redundant tables that used to hold data for features long since abandoned.

This section contains details of the main tables we will use in the course and provides the links between them. The complete table layouts are in Appendix A. Further details may be found in the CA Clarity Technical Manual.

The tables that hold custom attributes (ODF_CA_xxxxx) are omitted from the diagrams to avoid cluttering them. They are discussed under Clarity Objects.

Resources and Users

Table SRM_RESOURCES (R) holds every resource (human, equipment, material, expense), in the system, together with resource roles.
Table CMN_SEC_USERS (U) holds details for every user of the system. A user is always a resource, but a resource isn’t necessarily a user. There is a an overlap of data between this table and SRM_RESOURCES
Table CMN_SEC_GROUPS (G) holds the details of Clarity’s security groups.
Table CMN_SEC_USER_GROUPS (UG) holds the users that comprise each group


R.USER_ID = U.ID

G.ID = UG.GROUP_ID

U.ID = UG.USER_ID

Projects, Teams, Tasks and Assignments

INV_INVESTMENTS (I) holds details of every project (and also other investment types).
PRTEAM (TM) holds the resources on a project’s team.
PRTASK (TK) holds the tasks for a project.
PRASSIGNMENT (A) holds the resources assigned to tasks.
SRM_RESOURCES (R) is the same table as above.


I.ID = TM.PRPROJECTID

I.ID = TK.PRPROJECTID

A.PRTASKID = TK.PRID

A.TEAM_ID = TM.PRID

TM.PRRESOURCEID = R.ID

A.PRRESOURCEID = R.ID

I.MANAGER_ID = R.USER_ID = U.ID

There is also a view SRM_PROJECTS (SP) that will be encountered. This used to be a table, but was migrated to a view to ensure existing code wouldn’t break when the shift to investments was made in Clarity 8.1. The link is SP.ID = I.ID (= TM.PRPROJECTID, = TK.PRPROJECTID).

Timesheets, Time Entries, Time Slices

PRTIMESHEET (S) holds basic timesheet data
PRTIMEENTRY (E) holds the data for a timesheet row in BLOB form
PRJ_BLB_SLICES (B) holds data for time entries (and several other entities) in relational form PRTIMESHEETPRTIMEENTRYPRJ_BLB_SLICES


E.PRTIMESHEETID = S.PRID

B.PRJ_OBJECT_ID = E.PRID where slice request ID indicates a time entry

S.PRRESOURCEID = R.ID

E.PRASSIGNMENTID = A.PRID

Several tables contain BLOB (binary large object) columns to hold the so-called time curves. The format of them is not readily available and without special programming they cannot be manipulated. The time slice table PRJ_BLB_SLICES and its various siblings contains the slice data in relational format, and it is normally these tables that are queried to find time slice data.

Organisational Breakdown Structure (OBS)

Clarity represents the hierarchy of an organisation using OBSs. These are ad-hoc tree structures with as many nodes and branches as are required to define the organisation’s structure. Several OBSs can be defined, though for performance reasons CA recommend a maximum of 5 for use with the datamart.

Data such as investments or resources can be associated to any node, and this allows data to be retrieved by level.

PRJ_OBS_TYPES (PT) holds the OBS definitions (name etc)
PRJ_OBS_LEVELS (PL) defines the levels for each OBS in PRJ_OBS_TYPES
PRJ_OBS_UNITS (PU) holds the units by type
PRJ_OBS_ASSOCIATIONS (PA) holds the links between projects, resources etc., and OBS units


PU.TYPE_ID = PT.ID

PL.TYPE_ID = PT.ID

PA.UNIT_ID = PU.ID

OBS units are associated with data (such as resources and projects) by their IDs and table names. So, to associate a resource with a unit:

PA.RECORD_ID = R.ID and PA.TABLE_NAME = ‘SRM_RESOURCES’

To associate a project with a unit we use the original table name for projects (which is now a view):

PA.RECORD_ID = SP.ID and PA.TABLE_NAME = ‘SRM_PROJECTS’

Each row in PRJ_OBS_UNITS indicates that row’s unit’s immediate parent (if any). The complete hierarchy of each OBS is held in table PRJ_OBS_UNITS_FLAT. A unit (UNIT_ID) is listed as a descendant of all of its ancestors (each BRANCH_UNIT_ID) and also itself. So, for the following structure:

PRJ_OBS_UNITS_FLAT would have the following:


UNIT_IDBRANCH_UNIT_ID

11

21

31

41

51

61

22

42

52

32

33

63

Clarity Lookups

Clarity has two types of lookups, those that are static lists and those that are based on queries. Because it’s a very frequent set of joins, there is a view that collects the values for a static lookup together. It is CMN_LOOKUPS_V (LV) (which combines tables CMN_CAPTIONS_NLS and CMN_LOOKUPS).

CMN_LOOKUP_TYPES (LT) holds the basic information about a lookup
CMN_LOOKUPS (L) holds the internal values for each item in a lookup
CMN_CAPTIONS_NLS (CN) holds the translations of the lookup names (as well as other translations) CMN_LOOKUP_TYPESCMN_LOOKUPSCMN_CAPTIONS_NLS


LT.LOOKUP_TYPE = L.LOOKUP_TYPE

CN.TABLE_NAME = 'CMN_LOOKUPS' and CN.PK_ID = L.ID

Exercise 1.1
Create a query that lists investment names, codes, types, and their internal numeric ID.

Exercise 1.2
Create a query that returns projects, tasks and their internal numerical IDs.

Clarity Objects

Stock Objects
Most objects in Clarity (timesheets are the major exception) can have custom attributes added to them using Clarity Studio. Navigate to Objects, select one and then click on Attributes followed by ‘New’.

Fill in the screen with the attribute’s attributes. If the new attribute is to be based on a lookup select the correct one, but take care as this cannot be altered later by standard means. Lookups come in two flavours, single valued and multiple valued.

When an attribute is added to an object, the object’s underlying database tables and views are altered. For example a new task attribute would be added to the ODF_CA_TASK table, a new project attribute would be added to ODF_CA_PROJECT. The exception to this is when an attribute is based on a multi-valued lookup.

In these cases the attribute is stored outside the main object in table ODF_MULTI_VALUED_LOOKUPS, which holds the selected values when the attribute is given its values.

Once the attribute is created it can be placed on one of the object’s pages, for example the Project Overview or Task Overview page. This is done by clicking on ‘Views’ whilst in the object definition, selecting the appropriate view and then dragging the attribute to the correct place:

Click on the Section Properties icon and pull the new attribute across:

Then save everything.

Exercise 1.3
Create an attribute on the project object controlled by a single valued lookup and add it to the project overview page in the system partition.

Exercise 1.4
Create an attribute on the project object controlled by a multi-valued lookup and add it to the project overview page in the system partition. Configure the field to accept multiple values.

Custom Objects

We can create new objects in Clarity. These can be stand-alone objects or sub-objects of existing objects. For example we could create a new object called Project Approvals. Behind the scenes this would have all its attributes in a new table called ODF_CA_PROJECT_APPROVALS.

All custom object tables have the following basic attributes:


ColumnDescription
 
IDUnique internal ID
 
CODEShown as ‘ID’ on screen
 
NAMEShown as ‘Name’ on screen
 
PARTITION_CODEThe partition this object lives in (or NIKU.ROOT if global)
 
PAGE_LAYOUTThe default layout of this object on screen

CREATED_BYID from CMN_SEC_USERS
 
CREATED_DATEThe date the instance was created
 
LAST_UPDATED_BYID from CMN_SEC_USERS
 
LAST_UPDATED_DATEThe date the instance was last updated

If the object is a sub-object of another object the following columns will also be present to hold the link to the parent object:


ColumnDescription
 
ODF_PARENT_IDID of parent object instance
 
ODF_CNCRT_PARENT_IDConcrete ID of parent object instance

Portlets

A portlet is a means of displaying data from an NSQL query or a Clarity object. To create one go to Clarity Studio, click on portlets and then on ‘New’:

We’ll create a new grid portlet based on the Project Object:

Click on ‘Next’, then ‘Finish & Open’ and finally on ‘Layout’.

Select the columns for the portlet:

Click on Fields and tidy up the display parameters. Repeat for the Filter fields. Portlets are only visible once they are on a Portlet Page. We’ll put the portlet onto a ‘Test’ tab on the Overview page. Select Portlet Pages then Overview then the ‘Test’ tab: