Time Intelligence Wizard – Part 3 (Parameterization)
by David Novick, Pyramid Analytics Technical Writer
Parameters are one of those smart BI Office features that help you deliver powerful business graphics with just a few clicks of your mouse.
If you already work with parameters in BI Office, you will grasp quickly how Time Intelligence (TI) functions integrate with parameters. If you are new to parameters, this blog will provide you with first exposure to parameter usage. And afterwards you can expand your knowledge by searching for “parameters” in the BI Office help and User Community where you will find numerous help pages, blogs and webinars on the subject of parameters.
The main role of a parameter within BI Office is to create a “parameter slicer” which appears in the Columns or Rows of the selection panel – as opposed to a standard slicer which appears in the Slices zone of the panel. When you see a hierarchy that is highlighted blue in Columns or Rows, it indicates that the hierarchy has been parameterized and will behave as a parameter slicer.
A parameter slicer acts as a runtime filter in order to inject into a query either all or part of an existing hierarchy – depending on how the parameter has been defined by the user (or by the TI wizard). For example, a parameter slicer might list all years in a data model, all months in a given year, or all days in the current month.
The user employs the runtime parameter slicer to replace the "where" part of the query with dynamic content. The key benefit here is that the user can control query logic at runtime without the need to deal with advanced cube structures or complex MDX code.
In terms of parameterization, there are three overall groupings of TI functions, as described below.
- ON ALWAYS (highlighted below in green)
- OFF ALWAYS (highlighted below in red)
- OPTIONAL (highlighted below in yellow)
TI functions marked with a single asterisk (*) are automatically parameterized by the wizard. The parameterization for these TI functions cannot be disabled. These functions are always parameterized because these functions require the user to select a parameter slicer value at runtime. The "Rolling 4 Quarters" function is a good example, since the user needs to pick a specific quarter at runtime to serve as a reference point for the rolling four quarters.
TI functions marked with a double asterisk (**) cannot be parameterized. These functions are never parameterized because they operate within the current time framework, and therefore there is no need for a parameter slicer to be present at runtime. One example is the Current Quarter function.
TI functions with no asterisk are OFF by default and can be parameterized by selecting the “Parameterize All Calculation” checkbox at the top right of the wizard. These functions can can be implemented with parameterization turned OFF or ON.
One example would be the "YTD for Months" function which can be implemented in either of two fashions:
- OFF - The function is based on the current month and therefore the wizard does not create any parameters (no parameter slicer is needed).
- ON – The function is based on the month selected by the user at runtime in the parameter slicer.
This example shows how TI parameters are used to implement the “Rolling 4 Quarters” function.
We set up the wizard to perform the “Rolling 4 Quarters” function on the “Month Dates” hierarchy, as shown below. Note that the "Rolling 4 Quarters" function appears with an asterisk, indicating that parameterization is ON ALWAYS.
In this example, we enter the caption “ROLLING4Q” in all caps for easy recognition in later screens.
Within Data Discovery, we see that the “Month Dates” hierarchy has been automatically highlighted blue in the Rows zone. This indicates that the hierarchy has been parameterized, meaning its contents will be controlled at runtime by the parameter slicer named “ROLLING4Q – Quarter”.
The wizard has automatically named the parameter slicer to be “ROLLING4Q – Quarter” based on two factors:
- The original caption entered in the wizard (“ROLLING4Q” in this example)
- The selected Level Type in the wizard (“Quarter” in this example).
In general, there is no requirement to select, view or edit TI parameters in the Elements Panel. TI parameters exist in “background mode” and operate without any need of user input. However, it is possible to optionally view and edit the TI parameters, as described in the next section.
As we mentioned earlier, there is no requirement to view or edit a TI parameter. But to do so, just right-click the parameter in the Elements Panel and select the Parameters/Edit Parameter command to open the following dialog.
Assigned automatically by wizard. Cannot be changed.
Here we enter a brief description for the TI parameter.
This hierarchy tree should NOT be edited for TI parameters (although it can be edited for standard parameters).
The selection of hierarchy items for TI parameters is performed automatically by the TI wizard based on our choices in the wizard. If the TI results do not meet our business needs, we simply go back to the TI wizard and explore different TI functions. The wizard provides a large selection of TI functions to enable our users to find the right solution for the task at hand.
We can choose one of the four options to specify a default value for the parameter slicer. In this example, we have selected “Last” to indicate that we want the last quarter in the data model to appear by default in the parameter slicer at runtime.
- First - First item in the list.
- Last - Last item in the list.
- Default (Position) - The default will be position based. For example, if we select here the second item in the list, then the second item in the runtime parameter slicer will serve as the default.
- Default (Name) - The default will be name based. The specific item we select here will serve as the default selection in the runtime parameter slicer.
Within Story Board, the parameter slicer named “ROLLING4Q – Quarter” can be used in runtime mode to select any desired quarter to serve as the reference point for generation of the rolling four quarters.
To continue in this blog series, see Time Intelligence Wizard – Part 4 (Functional Reference).