Picklists are not a new concept. They exist in many applications, including Web pages. Perhaps you know them as “selection lists” or “drop down lists”. As a refresher, a picklist is a list of valid values for a specific element or cube cell. When you define a picklist for an element or a cell, a drop-down menu containing the defined values is available in the specified cell when browsing a cube in any of the IBM Planning Analytics or TM1 clients (PAfE, Websheets, Planning Analytics Workspace (PAW), Architect etc).
Values in cells containing a picklist are validated since a user must select one of the predefined values for the cell. If a user attempts to type in a value that is not valid for the cell (not in the picklist), an error appears indicating that only values from the pick list can be entered in the cell.
Creating a Picklist
To have a picklist you need to have a picklist cube. To manually create a picklist cube, you can do the following:
- Right-click on the cube you want to create a picklist for. Keep in mind this can be any existing cube, even an “}ElementAttributes_” cube. This is handy since often attributes defined for a dimension must be maintained by specific users. Having a picklist of validated values for certain elements will certainly make things easier.
- Select “Create Picklist Cube”. This will create a Control Cube named “}Picklist_CubeName”.
Picklist Techniques
Once you have a picklist cube created, you need to determine what technique you want to use to populate the picklist. There are Static, Subset, Dimensional and Rule-based approaches for populating picklists.
- When you have a short-list of acceptable values that do not change very often a Static picklist makes sense.
- When the allowed values (for a picklist) are elements in a dimension, then take a Subset approach.
- If the allowed values include ALL elements in a dimension, use the Dimensional approach.
- Finally, Rule-based picklists works best if the allowed values for a picklist are situational, for example if you need the list of acceptable values to change based upon inputs or who the user is or based upon some other setting.
For illustration, let’s consider a simple (but practical) picklist use case.
Picklist Use Case
Most IBM Planning Analytics or “PA” models will include some sort of Global Settings or “control” cube. Global Settings cubes are where values are stored that are common across the PA model. For example, source or target folders – where data is read from or written to – can be defined here. This is an example of a common value that doesn’t change often and is used across the model.
Another common use is to use values in the Global Settings cube to drive TurboIntegrator (TI) processes. These values will be read by specific processes and used as runtime parameters. In other words, an application administrator might open the Global Settings cube, manually type in the year that they want to archive data for (for example) and then run the process:
To make things a little easier we could create a Static picklist for the Global Settings cube (using the }PickList cube for the settings cube):
This is nice but in the above example, the only years you can select are 2017 through 2021. Perhaps a better approach would take the Subset approach to populating the picklist. In this example, I used the existing “0-Level Years” subset. This way, as the Year dimension is maintained (as part of an overall metadata maintenance process or updated via an MDX expression) the subset (and the Picklist) will always be up to date:
If you’d rather include ALL elements in a dimension, then you can change your picklist approach to use the Year dimension:
This will change the values in your picklist which may (or may not) be okay, since it will include consolidated elements as selections:
Finally, if you require a more complex picklist, you can implement the picklist using a Rule. Assuming there are already rules created for your Global Settings cube (if not, then right-click the setting cube and select Create Rule…), you can write some logic to populate the picklist. A simple example of this might look like this:
Here I used a cube rule to add some logic to say that if the archive feature of the application is enabled (indicated by a ‘Yes’ value in the settings cube), then populate the picklist with only the years 2021 through 2023, otherwise use the subset indicated and list all of the years (in that subset).
Interested in Learning More?
Are you interested in finding out how to use Picklists and other features of the features in IBM Planning Analytics? Contact QueBIT today. Simple or complex, we can help you take advantage of all of the features of Planning Analytics. Find me on: LinkedIn.