Create (Category) (MDX+)

Allows for creating new members/hierarchies using MDX expressions.

Description

Categories are a powerful icCube MDX extension for defining new hierarchies based on cube data. Members of these new hierarchies are made of tuple sets and are defined using a MDX expression. Their tuples cannot contain measures and calculated members/measures.

The following is explaining the syntax of the new statements; please refer to the attached tests for more details about their usage.

Categories can be defined at statement level using a syntax similar to calculated members :

        WITH
            CATEGORY HIERARCHY hierarchy-name-expression
                       , DEFAULT_MEMBER_NAME = string-expression
                       , LEVEL_NAME_PATTERN = string-expression

            CATEGORY [STATIC | DYNAMIC] [CALCULATED] MEMBER member-name-expression AS tuple-set-expression
                       , ADD_CHILDREN = true|false

        SELECT
            ...


        STATIC | DYNAMIC    : an optional modifier to specify the evaluation context. The default value is DYNAMIC:
                              in that case, when evaluating the formula the list of members is filtered by the slicer
                              and/or sub-select content. In STATIC mode, slicer and sub-select are ignored.

        CALCULATED          : allows for creating a category member into any hierarchy (i.e., not limited to
                              category hierarchy). This new member is accessible like any other calculated member
                              (i.e., allMembers, addCalculatedMembers). Note that ADD_CHILDREN cannot be used.

        DEFAULT_MEMBER_NAME : a category is created with a default member as its root member. This property
                              defines the name of this member. When not explicitly specified, the default
                              member name is 'All-M'.

        LEVEL_NAME_PATTERN  : when creating new members, new levels might be created. This property is defining
                              the pattern used to define the name of the new levels. The following properties are
                              available :

                              ${hierarchyName} : the name of the hierarchy the level belongs to
                              ${levelDepth}    : the depth of the level being created

        ADD_CHILDREN        : if true, the tuples of the member's expression are added as children of the newly
                              created member.

        

Similarly categories can be defined at session level; the newly categories are available for all the requests within this session. Other sessions do not see these hierarchies. Category members are always STATIC.

        CREATE SESSION CATEGORY HIERARCHY hierarchy-name-expression
                                  , DEFAULT_MEMBER_NAME = string-expression
                                  , LEVEL_NAME_PATTERN = string-expression


        CREATE SESSION CATEGORY MEMBER member-name-expression AS tuple-set-expression
                                  , ADD_CHILDREN = true|false
        

Similarly categories can be defined at schema level (typically from within the script in the schema XML definition). Category members are always STATIC.

        CREATE CATEGORY HIERARCHY hierarchy-name-expression
                          , DEFAULT_MEMBER_NAME = string-expression
                          , LEVEL_NAME_PATTERN = string-expression


        CREATE CATEGORY [CALCULATED] MEMBER member-name-expression AS tuple-set-expression
                          , ADD_CHILDREN = true|false
        

Calculated category member created at schema level can be deleted using the DROP MEMBER statement
(similar to regular calculated members).

See Also

Drop (Category)

XMLA Properties

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