icCube

Documentation

Create (Measure)

(MDX+)

Creates a measure based on existing measures defined at schema level.

Description


icCube allows for defining new measures based on existing measures defined at schema level. Those measures are equivalent to the ones defined in the schema. The difference is that they do not require extra memory for storing the facts values. Instead of defining several measures in the facts for several aggregations, one can create a single measure using the SUM aggregation in the facts and then create several derived measures redefining the aggregation.

Keep COUNT & DISTINCT COUNT measures in the facts definition as they are a bit specific regarding their internal data structure.

A new measure can be created using the following statement:



CREATE MEASURE [cube-name].[measure-name]

AS [measure-name],

AGGREGATION = AGGR_...,
FORMAT_STRING = '...',
EMPTY_IS_ZERO = true|false




The scope of the new measure is the cube it belongs to. The cube-name is optional if the schema contains a single cube. The mandatory AGGREGATION properties allows for defining its aggregation type:



AGGR_SUM
AGGR_SUM_SQUARE

AGGR_MIN
AGGR_MAX

AGGR_AVG_CHILDREN
AGGR_AVG_LEAVES

AGGR_COUNT
AGGR_DISTINCT_COUNT

AGGR_OPEN
AGGR_CLOSE
AGGR_OPEN_NO_EMPTY
AGGR_CLOSE_NO_EMPTY

AGGR_NONE

AGGR_NO_EMPTY
AGGR_NO_EMPTY_ROWS

AGGR_VECTOR
AGGR_VECTOR_SORTED
AGGR_VECTOR_UNIQUE



Note that such a measure can be created in a SELECT scope as following :



WITH MEASURE [measure-name]

AS [measure-name],

AGGREGATION = AGGR_xyz,
FORMAT_STRING = ...,
EMPTY_IS_ZERO = true|false



See Also

Drop (Measure)

Since Version

6.5.1

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.