AnsweredAssumed Answered

In-built datamart parameters in NSQL

Question asked by philip_smythe on Mar 7, 2012
Latest reply on Mar 7, 2012 by philip_smythe
I am looking for some slightly more clear examples of how to use the datamart parameters in NSQL so as to provide a quick method of OBS filtering for project and resource queries.
The documentation suggests a query such as the following by way of example:
SELECT
@SELECT:DIM:DATA_MART:OBS:PROJECT@,
@SELECT:DIM:USER_DEF:IMPLIED:PROJECT:P.PROJECT_CODE:PRJ_ID@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(P.ACTUAL_HOURS):ACTUALS:AGG@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(P.ETC_HOURS):ETC:AGG@
FROM
NBI_PROJECT_CURRENT_FACTS P,
@FROM:DIM:DATA_MART:OBS@
WHERE
@FILTER@
AND
@WHERE:DIM:DATA_MART:OBS:P@
GROUP BY
@GROUP_BY:DIM:DATA_MART:OBS@,P.PROJECT_CODE
HAVING
@HAVING_FILTER@
This is a multi-dimensional query where PROJECT and OBS are the two dimension names - and we have a couple of metrics also.
The example includes special constructs in the SELECT, the FROM, the WHERE clauses that do the work.
When saved Clarity automatically attaches a required lookup to the OBS attribute (of some kind of system internal type). A grid portlet based on the query - and with the OBS attribute driving the filter - gives a multi stage OBS lookup filter where we select:
1. Choose the OBS we want to filter by and the unit association mode.
2. Choose the level in the OBS that we want to select a unit from
3. Choose the unit from within that level.

What I would like to know is whether this DATA_MART parameter can only be used in this manner - are there more simple examples of usage that do not involve multi-dimensional queries and simultaneous use of the parameter in SELECT, FROM and WHERE clauses?

I am aware that taking OBS material from the datamart rather than live tables has some shortcomings in terms of data freshness. I am also aware that I could use a custom parameter to filter for the OBS in the NSQL WHERE clause and otherwise use more standard SQL. I know that this area is documented in the Clarity Studio documentation - but not clearly IMHO. What I am trying to understand if whether this method offers any query simplification short-cuts that would ever make it worthwhile using.

Outcomes