Clarity Data Model
Clarity Data Model
Clarity Data Model
Session 1
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.
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.
www.clarity-consultants.com
Page 1 of 16
Portlet / NSQL Workbook & Reference
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_USER_GROUPS (UG) holds the users that comprise each group
SRM_RESOURCES CMN_SEC_GROUPS
CMN_SEC_USER_
CMN_SEC_USERS
GROUPS
R.USER_ID = U.ID
G.ID = UG.GROUP_ID
U.ID = UG.USER_ID
www.clarity-consultants.com
Page 2 of 16
Portlet / NSQL Workbook & Reference
INV_INVESTMENTS (I) holds details of every project (and also other investment types).
INV_INVESTMENTS
PRTEAM PRTASK
PRASSIGNMENT
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
www.clarity-consultants.com
Page 3 of 16
Portlet / NSQL Workbook & Reference
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).
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
PRTIMESHEET
PRTIMEENTRY
PRJ_BLB_SLICES
E.PRTIMESHEETID = S.PRID
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.
www.clarity-consultants.com
Page 4 of 16
Portlet / NSQL Workbook & Reference
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_ASSOCIATIONS (PA) holds the links between projects, resources etc., and OBS units
PRJ_OBS_TYPES PRJ_OBS_LEVELS
PRJ_OBS_UNITS
PRJ_OBS_ASSOCIATI
ONS
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:
www.clarity-consultants.com
Page 5 of 16
Portlet / NSQL Workbook & Reference
To associate a project with a unit we use the original table name for projects (which is now a view):
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:
2 3
4 5 6
UNIT_ID BRANCH_UNIT_ID
1 1
2 1
3 1
4 1
5 1
6 1
2 2
4 2
5 2
3 3
6 3
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).
www.clarity-consultants.com
Page 6 of 16
Portlet / NSQL Workbook & Reference
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_
TYPES
CMN_LOOKUPS
CMN_CAPTIONS_NLS
LT.LOOKUP_TYPE = L.LOOKUP_TYPE
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.
www.clarity-consultants.com
Page 7 of 16
Portlet / NSQL Workbook & Reference
www.clarity-consultants.com
Page 8 of 16
Portlet / NSQL Workbook & Reference
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’
www.clarity-consultants.com
Page 9 of 16
Portlet / NSQL Workbook & Reference
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:
www.clarity-consultants.com
Page 10 of 16
Portlet / NSQL Workbook & Reference
Click on the Section Properties icon and pull the new attribute across:
www.clarity-consultants.com
Page 11 of 16
Portlet / NSQL Workbook & Reference
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.
Column Description
ID Unique internal ID
CODE Shown as ‘ID’ on screen
NAME Shown as ‘Name’ on screen
PARTITION_CODE The partition this object lives in (or NIKU.ROOT if global)
PAGE_LAYOUT The default layout of this object on screen
CREATED_BY ID from CMN_SEC_USERS
CREATED_DATE The date the instance was created
LAST_UPDATED_BY ID from CMN_SEC_USERS
LAST_UPDATED_DATE The 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:
Column Description
ODF_PARENT_ID ID of parent object instance
ODF_CNCRT_PARENT_ID Concrete ID of parent object instance
www.clarity-consultants.com
Page 12 of 16
Portlet / NSQL Workbook & Reference
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’
www.clarity-consultants.com
Page 13 of 16
Portlet / NSQL Workbook & Reference
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:
www.clarity-consultants.com
Page 14 of 16
Portlet / NSQL Workbook & Reference
www.clarity-consultants.com
Page 15 of 16
Portlet / NSQL Workbook & Reference
To see the portlet, go the Overview page and click on the Test tab.
Exercise 1.5
Create an object-based portlet that displays Resources (names, emails etc). Add it to a new tab on
the overview page.
www.clarity-consultants.com
Page 16 of 16