With TM1 you'll have more time for analysis and other valuable activities without having to give up Excel. Cubewise will show you how.
Let’s examine bedrock’s workhorse process of subset creation, }bedrock.hier.sub.create. This process basically remains the same as in previous versions of the bedrock library. The process will create a static subset in a defined hierarchy of the target dimension. The element(s) to be included in the subset are defined via the use of several auxiliary parameters. We say it’s the “workhorse” process as most of the other hier.sub processes call this one with specific parameter values to achieve specific tasks.
Parameters list, with default values
It looks pretty complicated due to the large number of parameters doesn’t it? But don’t worry, it isn’t as complicated as it seems. Let’s break it down into groups of related parameters and consider each in turn.
There are some basic parameters to define the dimension, hierarchy and subset name:
- pDim – Required: name of the target dimension
- pHier – Optional: name of the hierarchy (if left blank then same named hierarchy is assumed)
- pSub – Required: name of the subset
Next let’s look at the parameters to filter which elements will be added to the subset:
- pConsol – Optional: specified consolidated element (which may be expanded to list of descendants depending on other parameters). Note that blank or * denotes all consolidations.
- pAttr, pAttrValue – Optional: name and value for the attribute to filter by
- pLevelFrom, pLevelTo – Optional: dimension level values to filter by level
- pExclusions, pDelim – Optional: a list of elements to specifically exclude from the subset. pDelim defines the delimiter character between individual elements
The exclusion parameters allow to exclude chosen elements from the subset. The elements can be simply listing them with a chosen separator (given in pDelim parameter). If a leaf level element is specified, it will be removed on its own. If a consolidated element is specified, it will be removed as well as its descendants. The elements can also be chosen by wildcards. Wildcard characters ‘*’ and ‘?’ are accepted. Both ways can be combined (see examples below).
Then there are some additional parameters:
- pLogOutput – Optional: standard bedrock 4 parameter. Indicates whether to log messages to the server message log. (Default is 0)
- pAddToSubset – Optional: if the subset already exists, the new elements will be appended, or the subset will be recreated depending on true or false.
- pAlias – Optional: name of the alias to be applied to the subset.
- pTemp – Optional: this parameter sets the value ‘AsTemporary’ to the TI function creating the subset. When the optional AsTemporary argument is set to 1, the subset is temporary and persists only for the duration of the chain of TI processes in which the subset is created. The default value is 1 (since the main purpose of bedrock is creating objects during other TI processes). To create a permanent subset this parameter must be set to 0.
Despite the large number of parameters we can see that there are only 3 required parameters; pDim, pSub. If we set only these parameters then we will end up with a subset of all elements being created.
Note! In all the following examples if you want to follow along, make sure that the pTemp parameter is set to 0!Otherwise the subset will be created as temporary and you won’t see it. For normal TI processing this parameter is optional (since usually the default value is the correct one) but for any use requiring creation of a permanent subset for later use it is required.
For the examples, we will be using the “Product” dimension from our demo database. When calling }bedrock.hier.sub.create we only need to worry about the parameters where we won’t be using the default values.
Example 1: Expand a consolidation element to its descendants
In the first example we will expand the consolidated element “Bikes”. To properly parametrize the process, we only need to input the name of the dimension pDim, the name of the subset we want to create pSub and the name of the consolidated element pConsol. For the rest of the parameters, we can just leave the default values.
The resulting subset consist of the root element “Bikes” expanded with all its descendants down to the lowest level leaf elements.
Example 2: Filter by level
We can re-create the same subset with the same parameters except this time changing the value for the pLevelToparameter to 0.
This time in the resulting subset only leaf elements are included.
Example 3: Expand chosen consolidation element and filter by attribute
In the next example we will additionally filter our subset by an attribute. To do this we need to input the attribute name in parameter pAttr and the value of the attribute to filter on in pAttrValue. For the purpose of the demonstration a simple attribute TestFilter was created. We are filtering to only include elements with the value “A” for the “TestFilter” attribute.
From the elements under “Bikes,” only the “Mountain Bikes” consolidated element and some of its children have the required value of “A” for the attribute and are consequently allocated as members of the subset.
Example 4: Excluding elements by list and/or by wildcard
All the filtering can be combined. The last one is excluding elements. This filtering is executed by a sub-process }bedrock.hier.sub.exclude which is triggered from the Epilog tab of the main process and modifies the subset after the main process has created it.
The pExclude parameter supports lists & wildcards In this example let’s combine a list with a single character “?” wildcard.
- pConsol = “” OR “*”
- pExclude = “344 & 35?”
Based on the input to pExclude the elements 344, 350 & 352 will be removed from the subset. This time we aren’t specifying the consolidated element “Bikes”, but leaving this parameter blank or using the “*” ALL wildcard. Therefore we expect to see all elements with a value of “A” in the “TestFilter” attribute except the ones we are specifically excluding.
This is the resulting subset.
Example 5: Additional parameters
As a final example, we will append to our subset from example 4 all elements having value “B” for the “TestFilter” attribute. Also, we will modify the subset by setting “Code&Description” as the subset’s display alias.
As the subset “4 test Attr + exclude” already existed and pAddToSubset was set to 1, the new elements are added to the existing subset, rather than the subset being re-created. Note that by default the new elements are appended at the bottom of the list.