IBM Cognos Dynamic Cubes

78 IBM Cognos Dynamic Cubes

4.4 Calculated members

Calculated members are members that the modeler can create by using expressions. They

are appended to the end of the list of members in the level of the children of their parent.

Cognos Cube Designer validates the syntax of expressions. It does not check semantic, data

type, and binding errors. After a cube is started, the dynamic cube engine validates the

semantics of the calculated member and calculated measure expressions. The expression

editor does not limit functions to the valid ones for a specific context.

Several restrictions apply to Cognos Dynamic Cubes calculated members.

Do not use the

following relational constructs in expressions to define calculated members:

򐂰 Value summary functions (Not Member Summary functions)

򐂰 Value Analytic functions (rank, first, last, percentile, percentage, quantile, quartile, distinct

clause, prefilter clause); (Summaries/Member Summaries)

򐂰 Value Summary functions (standard-deviation-pop, variance-pop, distinct clause, prefilter

clause)

򐂰 All running- or moving- summary functions (Summaries)

򐂰 All FOR clauses in aggregate functions (Summaries/Member Summaries)

򐂰 Date and time constants (Constants)

򐂰 All business date and time functions (Business Date and Time functions)

򐂰 Like, lookup, string concat '||', trim, coalesce, cast (Common Functions)

򐂰 MOD function (Common Functions)

4.5 Modeling time dimensions for relative time

Time awareness is an important modeling and reporting requirement. Many queries and

functions require some point in time to know when the current period is and to perform

relative time calculations, such as a rolling time window. With relative time capability, report

authors can easily write a report, which contains calculations that depend on a current period.

The report can be run at any time and return the correct results for the updated period without

the report author having to update the report. The cube can be updated.

Built-in relative time members can also save time for report authors and consumers.

In addition, you might have data, such as planning data, that extends into the future, or the

period of interest passed but the actual time period you are in has not closed. The last time

period value is not necessarily the current period for your reports. For example, assuming

your fiscal months correspond with calendar months, on August 14 your current period is

probably still July. You need a method to specify this current period.

The current period property exists for each level in a Time dimension. You can seed it with a

set value or with an expression. If you use an expression, it must be a scalar function, that is,

it must resolve to a single value. The value for the current period must match the business key

value for the period that you want as the current period.