NSQL, still valuable even in the
Modern UX
Pemari Consulting Ltd
Andrew Litton
2007 - TODAY
About Me
I have been working in the Project Management
Software consulting business since 1993,
starting with Artemis, and then onto ABT, Niku
and through it’s rebranding to today and Clarity
PPM.
Session Agenda
NSQL, still valuable even in the Modern UX
Background
Column Annotations
Filters/Parameters
Other Annotations
Background
NSQL, still valuable even in the Modern UX
Where is NSQL used?
§ In lookups
• when selecting Dynamic Query on lookup create
§ Portlets – as a Query type data provider
• One query can be used by many portlets
§ XOG Query Soap Calls
• Can call the query directly
Why is NSQL still useful with the Modern UX
§ Lookup attributes are available within the Modern UX
§ Project Channels enable the embedding of Portlet Pages within the Project
screens
Queries
§ Queries created in Classic PPM within Administration Queries
§ Queries can retrieve data from:
• the Clarity PPM transactional database (Niku)
• the Clarity PPM data warehouse (Data Warehouse)
• Any other CSA configured database, i.e. Jaspersoft, using New External Connection
What is NSQL?
An annotated SQL Select statement
SELECT SELECT
c.prid id, @SELECT:c.prid:id@,
c.prname name, @SELECT:c.prname:name@,
p.prname parentname, @SELECT:p.prname:parentname@,
c.prmodtime last_updated_date @SELECT:c.prmodtime:last_updated_date@
FROM prcalendar c FROM prcalendar c
LEFT OUTER JOIN prcalendar p LEFT OUTER JOIN prcalendar p
ON c.prbasecalendarid = p.prid ON c.prbasecalendarid = p.prid
WHERE c.prresourceid IS NULL WHERE c.prresourceid IS NULL
AND 1=1 AND @FILTER@
Note annotations start and end with @
Column annotations
NSQL, still valuable even in the Modern UX
Anatomy of a Select Statement
SELECT <columns> Each column must use @SELECT:…@
FROM <table references> Sub queries are ok to use
WHERE <where clauses> Must contain at least @FILTER@
GROUP BY <group columns> Aggregates data
HAVING <having clauses> Filtering data uses @HAVING_FILTER@
ORDER BY <order columns> Controllable in Clarity
SELECT Column Annotations
§ 2 formats
• For Lookups
@SELECT:<Table.Field>:<Label>@
• For Queries
@SELECT:DIM:USER_DEF:IMPLIED:<DIMENSION>:<Table.Field>:<Label>@
SELECT Column Annotations: Lookups
@SELECT:<Table.Field>:<Label>@
Table.Field the SQL column
Label used in Clarity as the Attribute ID and Label
SELECT
@SELECT:c.prid:id@,
@SELECT:c.prname:name@,
@SELECT:p.prname:parentname@,
@SELECT:c.prmodtime:last_updated_date@
FROM prcalendar c
LEFT OUTER JOIN prcalendar p
ON c.prbasecalendarid = p.prid
WHERE c.prresourceid IS NULL
AND @FILTER@
SELECT Column Annotations: Queries
§ The format is a little more involved
§ Annotation provides information to Clarity to enable grouping/pivoting/charting data in
Portlets
§ 3 types of Query SELECT annotation
• Dimension
• Dimension Property
• Metric
SELECT Column Annotations: Dimension
@SELECT:DIM:USER_DEF:IMPLIED:<DIMENSION>:<Table.Field>:<Label>@
§ DIM indicates the column is the primary key for the dimension
§ There can be only one DIM column per dimension and there MUST be at least 1
dimension defined in an NSQL statement.
§ <DIMENSION> is a user defined name for the dimension, i.e. Project or Resource
§ IMPLIED tells Clarity to derive the attribute type from the SQL Result.
SELECT Column Annotations: Dimension Property
@SELECT:DIM_PROP:USER_DEF:IMPLIED:<DIMENSION>:<Table.Field>:<Label>@
§ DIM_PROP indicates the column is a property of a dimension
§ There can be many DIM_PROP columns per dimension
§ <DIMENSION> is the name of the defined dimension, i.e. Project or Resource
SELECT Column Annotations: Metric
@SELECT:METRIC:USER_DEF:IMPLIED:<Table.Field>:<Label>[:AGG]@
§ METRIC note no DIMENSION
§ There can be many metric columns
§ Can be totalled on a grid or displayed as a value on a chart
§ [:AGG] optional construct and allows the metric to be filtered in the SQL HAVING clause
@SELECT:METRIC:USER_DEF:IMPLIED:COUNT(*):no_projects:AGG@
SELECT Column Annotations: Example Query
Resource Availability by Month
SELECT Column Annotations: Example Query
Resource Availability by Month
hours – is a metric
Two Dimensions – SLICEDATE and RESOURCE
SELECT Column Annotations: Example Grid Portlet
Resource Availability by Month
Filters/Parameters
NSQL, still valuable even in the Modern UX
WHERE Annotations: FILTER
@FILTER@
§ @FILTER@ allows Clarity to filter the columns defined in the @SELECT@ annotations
§ @FILTER@ is replaced on execution with WHERE clauses based on the portlet or lookup
filters
SELECT
@SELECT:R.ID:ID@,
@SELECT:R.UNIQUE_NAME:UNAME@,
@SELECT:R.FULL_NAME:FULLNAME@,
@SELECT:R.IS_ACTIVE:ACTIVE@,
@SELECT:R.PERSON_TYPE:PERSON_TYPE@
FROM SRM_RESOURCES R
WHERE @FILTER@
WHERE Annotations: PARAMS
@WHERE:PARAM:…….@
§ Can be located in different sub queries
§ Do not need to be one of the @SELECT@ annotations
§ A number of sources
• From filters
• Built in
• From the page URL
WHERE Annotations: Built In Parameters
§ Commonly used for security and the selection of the relevant lookup value language
@WHERE:PARAM:USER_ID
@WHERE :PARAM:USER_NAME@
@WHERE :PARAM:LOCALE@
@WHERE :PARAM:LANGUAGE@
WHERE Annotations: Built In Lookup Example
SELECT
@SELECT:R.ID:ID@,
@SELECT:R.UNIQUE_NAME:UNAME@,
@SELECT:R.FULL_NAME:FULLNAME@,
@SELECT:L.NAME:PERSON_TYPE@,
@SELECT:R.PERSON_TYPE:PERSON_TYPE_ID@,
@SELECT:L.LANGUAGE_CODE:LANGUAGE_CODE@
FROM SRM_RESOURCES R
LEFT OUTER JOIN CMN_LOOKUPS_V L
ON R.PERSON_TYPE=L.ID SELECT
AND L.LOOKUP_TYPE='SRM_RESOURCE_TYPE' @SELECT:R.ID:ID@,
AND L.LANGUAGE_CODE=@WHERE:PARAM:LANGUAGE@ @SELECT:R.UNIQUE_NAME:UNAME@,
WHERE R.IS_ACTIVE=1 @SELECT:R.FULL_NAME:FULLNAME@,
AND @FILTER@ @SELECT:L.NAME:PERSON_TYPE@,
@SELECT:R.PERSON_TYPE:PERSON_TYPE_ID@,
@WHERE:PARAM:LANGUAGE@ LANGUAGE_CODE
FROM SRM_RESOURCES R
LEFT OUTER JOIN CMN_LOOKUPS_V L
ON R.PERSON_TYPE=L.ID
AND L.LOOKUP_TYPE='SRM_RESOURCE_TYPE'
AND L.LANGUAGE_CODE=@WHERE:PARAM:LANGUAGE@
WHERE R.IS_ACTIVE=1
AND @FILTER@
WHERE Annotations: User Defined Parameters
@WHERE:PARAM:USER_DEF:<DATA_TYPE>:PARAM_NAME@
@WHERE:PARAM:USER_DEF:<DATA_TYPE>:<COLUMN_NAME>:PARAM_NAME@
§ PARAM_NAME is the name of the attribute displayed within Clarity PPM
§ <DATA_TYPE> value can be STRING, INTEGER, or, DATE
Although FLOAT is accepted Query displays this as a numeric
§ 2nd Form optional and enables support for Multiple Selection.
§ COLUMN_NAME is the SQL column name for the condition
WHERE Annotations: User Defined Example 1
Investment Team Availability by Month
WHERE Annotations: User Defined Example 1
Investment Team Availability by Month
WHERE Annotations: User Defined Example 1
Investment Team Availability by Month
• Change Field Extended type to
Lookup - Numeric
• Pick the relevant lookup
• Portlet filter can display a browse field
for the project selection
WHERE Annotations: User Defined Example 1
Investment Team Availability by Month
WHERE Annotations: User Defined Example 2
Investment Team Availability by Month
WHERE Annotations: User Defined Example 2
Investment Team Availability by Month
WHERE Annotations: User Defined Example 2
Investment Team Availability by Month
• Change Field Extended type to
Lookup - Numeric
• Pick the relevant lookup
• Portlet filter can use a Multiple Select
browse field for the project selection
WHERE Annotations: User Defined Example 2
Investment Team Availability by Month
WHERE Annotations: XML Parameters
@WHERE:PARAM:XML:<DATA_TYPE>:<xpath>@
• Obtains value from the URL of the page of the portlet
• Example : A dashboard page within Project with a URL
https://centos.vm.pemari.com/niku/nu#action:SYSTEM5130026&id=5048013
• So define an XML Parameter to read the id and only display values for the project
@WHERE:PARAM:XML:INTEGER:/data/id/@value@
WHERE Annotations: XML Example
Investment Team Availability by Month
WHERE Annotations: XML Example
Investment Team Availability by Month
Note: Queries can have links defined as well to
enable linking from the portlet to another
page
WHERE Annotations: XML Example
Investment Team Availability by Month
Security Annotation
@WHERE:SECURITY:PROJECT:<entity_id>@
@WHERE:SECURITY:RESOURCE:<entity_id>@
§ Only Project and Resource annotation is supported
§ Can only be used in WHERE clause
§ Replaces annotation with SQL where clause
Security Annotation: Examples
SELECT
@SELECT:R.ID:ID@,
@SELECT:R.UNIQUE_NAME:UNAME@,
@SELECT:R.FULL_NAME:FULLNAME@
FROM SRM_RESOURCES R
WHERE R.IS_ACTIVE = 1
AND @WHERE:SECURITY:RESOURCE:R.ID@
AND @FILTER@
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.ID:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.UNIQUE_NAME:UNAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:FULLNAME@
FROM SRM_RESOURCES R
WHERE R.IS_ACTIVE = 1
AND @WHERE:SECURITY:RESOURCE:R.ID@
AND @FILTER@
Other Annotations
NSQL, still valuable even in the Modern UX
Dynamic Lookups: @BROWSE-ONLY
§ In UI whilst browsing to select on
SELECT active resources that you have rights
@SELECT:R.ID:ID@, to displayed.
@SELECT:R.UNIQUE_NAME:UNAME@,
@SELECT:R.FULL_NAME:FULLNAME@ § If subsequently, selected resource is
FROM SRM_RESOURCES R inactivated, then UI would not
WHERE display the value..
@BROWSE-ONLY:
R.IS_ACTIVE = 1 AND
@WHERE:SECURITY:RESOURCE:R.ID@ AND § With BROWSE-ONLY, SQL within the
:BROWSE-ONLY@ annotation is only included whilst
@FILTER@ browsing.
Hierarchical Queries
Enables expansion of row and
display of sub rows
Can have many levels
Hierarchical Queries
Select dimension property named hg_has_children
• Unique for all rows in the dimension
• If null – then does not show [+]
• Passed to the query on clicking the [+] as the hg_row_id
parameter
@WHERE:PARAM:USER_DEF:STRING:hg_row_id@
• Set to value of clicked [+] row hg_has_children
attribute
• Initial value on portlet display is NULL
Hierarchical Queries
@WHERE:PARAM:USER_DEF:STRING:hg_all_rows@
• Used for Export to Excel
• Set to 1 when exporting otherwise
NULL
• Used to enable/disable sub levels from
being exported
Hierarchical Queries
Common SQL Structure
SELECT @SELECT….
FROM hg_has_children must uniquely identify the
( data to retrieve or not retrieve in each of
SELECT <the parent Level> the sub-queries
UNION
SELECT <the first child level> Some examples can be found on the
UNION Resource Utilisation page – uses OBS
SELECT <the next child level>
UNION
….
)
WHERE @FILTER@
Hierarchical Queries
Example
SELECT @SELECT:DIM:USER_DEF:IMPLIED:DATA:ID:ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:CODE:CODE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:NAME:NAME@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:STARTDATE:STARTDATE@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:DATA:HG_HAS_CHILDREN:HG_HAS_CHILDREN@
FROM (
SELECT I.ID ID, I.CODE CODE, I.NAME NAME, I.SCHEDULE_START STARTDATE,
MAX(CASE WHEN T.PRID IS NULL THEN NULL ELSE I.ID END) HG_HAS_CHILDREN
FROM INV_INVESTMENTS I
LEFT OUTER JOIN PRTASK T ON I.ID = T.PRPROJECTID
WHERE I.ODF_OBJECT_CODE = 'project'
AND @WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@ IS NULL
GROUP BY I.ID, I.CODE, I.NAME, I.SCHEDULE_START
UNION
SELECT T.PRID ID, T.PREXTERNALID CODE, T.PRNAME NAME, T.PRSTART STARTDATE,
NULL HG_HAS_CHILDREN
FROM PRTASK T
WHERE @WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@ IS NOT NULL
AND T.PRPROJECTID=@WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@
)
WHERE @FILTER@
Hierarchical Queries
Example
Hierarchical Queries
Example
Hierarchical Queries
Advice
Filtering: Implement filters using Parameters
Export: When using hg_all_rows, export does not sort rows based on the hierarchy
Layout: All levels must have the same field layout
Sorting: All levels use the same field sorting so add a column for sort order
NSQL, still valuable even in the Modern UX
Questions?
Thank you for attending
NSQL, still valuable even in the Modern UX
Phone
+44 844 736 2500
Email
[email protected] Let us know how we can
improve!
Don’t forget to fill out the
Website feedback forms!
www.pemari.com
