Whether you are building a new model, adding objects to an existing application, or simply maintaining a Planning Analytics solution, there is opportunity to automate the creation of standard subsets. This can be done via TI process and be applied to all the Dimensions within the model. This ensures that subsets are configured consistently across the environment, which improves useability both from an administrative and end user perspective. Imagine that you are building a report and you would like to filter the data by leaf level Products and leaf level Regions. It is very convenient to already have these subsets in place and know that the definition of a “Level0” named subset is consistent, regardless of what dimension is being accessed.
As a best practice, Dimensions should always have a default subset configured. This helps to prevent someone from accidentally opening a very large view and ensures that there is always something displayed in the subset.
A commonly used subset is a “Level 0” subset which holds all the leaf level elements. This should driven via MDX to capture any new elements added to the dimension. For more information on MDX expressions, please see: Create MDX expressions | Ask QueBIT Knowledge-base
In this example, we are building a new model. However, there are no subsets in place yet.
In this exercise, our objective is to quickly create the following subsets for all dimensions:
- Default: If there is a “Total” member present in the dimension (ex. Total Products), the subset should display all Descendants of the “Total” member; if it is not present, display all elements
- Level0: Display all leaf level elements
- Level0 ID Elems: Display all leaf level elements EXCEPT any dummy elements (ex. No Product, Input Product)
We can automate this via TI Process and utilize the “}Dimensions” Dimension as the data source.
On the Prolog, the DATASOURCENAMEFORSERVER is configured; aliases are optional.
On the Metadata, the first IF statement is filtering out Control Objects (any dimension prefixed with “}”)
Then, it builds each of the respective subsets. Obviously, these MDX definitions can change depending on the environment and business requirements. The advantage to this approach is that everything is maintained in a single TI process and subsets are standardized across all dimensions.
After running this TI process, we now have these 3 subsets (Default, Level0, and Level0 ID Elems) available in all dimensions!
