Time Intelligence Wizard – Part 1 (Attribute Hierarchies)
By David Novick - Technical Writer, Pyramid Analytics
If you haven’t yet worked with the Time Intelligence (TI) Wizard, this blog series is meant for you. First introduced in BI Office 6.0, the TI Wizard provides you with quick and intuitive tools for creating advanced TI functions to be integrated within your reports and story boards. And the good news is that you no longer need to wrestle with MDX code to produce the time-related functions that are so essential to achieving impressive business analytics. The TI Wizard provides a friendly, code-free interface for generating a host of time-date functions for instant use.
One of the exciting aspects of the wizard is that the TI functions you create will dynamically update in the future. For example, if you design a TI function to display a report containing sales figures for the last 6 months, opening the report in the future will display the 6 latest months contained in your database at that time.
The example presented below demonstrates how easy it is to create a “Last 6 Months” function for an attribute hierarchy. Along the way, we will mention some important tips & techniques that will help you to save time and to work in the most efficient way possible.
At this point, you might want to open the Time Intelligence Wizard on your desktop to take a quick look at the interface. To do this, just click on the Time Intelligence icon in the Data Discovery/Analytics ribbon.
In this example, we have set up the wizard for the current TI session as follows:
- Caption: SHOW-LAST-SIX
- Dimension: Date
- Hierarchy: Months
- Level: Months (we checked the box to activate the row)
- Level Type: Month
- Last: 6 Months
- Access: Private (default)
We purposely type in the caption “SHOW-LAST-SIX” using all caps. This allows us to easily recognize where the caption is later employed by the wizard.
The Time Intelligence Wizard has been designed to work with dimensions containing date/time data. In order for the wizard to function correctly, it is necessary to select a date/time dimension and then select the desired hierarchy – which can be either an attribute hierarchy or a user hierarchy. Selecting a different dimension type may cause unpredictable results.
The "Level" displayed in the left-most column stems directly from the dimension and hierarchy choices in the upper part of the dialog. In order for the wizard to function correctly, we need to match the “Level Type” to the “Level”. For example, Year-to-Year, Month-to-Month, Quarter-to-Quarter, etc.
At times, there may be spelling differences between the Level name (at the left) and the Level Type that we select in the list. Some examples might be Months/Month or Date/Day. These differences arise from the fact that when a data model is designed, the exact spelling of the Dimension, Hierarchy and Level names are determined by the model designer. Whereas the Level Types shown in the list are generic types supplied by the wizard.
In this example, we have selected the “Last 6 Months” option and we have DESELECTED all other options. This is an important point, because any TI functions which we leave selected will be generated as part of the report.
When we finish making selections in the main dialog, we click Next to proceed to the Wizard Summary. Or we can click Finish to skip over the Wizard Summary.
The Wizard Summary displays a list of all custom elements that have been created by the wizard in the current TI session. Click Finish to close the Wizard Summary and proceed to the Elements Panel.
When we close the TI Wizard, the Custom Elements created by the wizard will be displayed automatically in the Elements Panel, as shown below. In this example, the wizard has created four entities within the Custom Elements folder.
- SHOW-LAST-SIX - This folder receives its name from the original "Caption" that we entered in the wizard. The folder is created within the Private folder, as we requested in the wizard.
- Last - The subfolder named “Last” is created automatically based on the TI function we selected in the wizard.
- Member - Contains the aggregation of all objects in the set.
- Set - Contains the sales figures for the last 6 months.
The various elements generated by the TI Wizard can be used to drive values within a grid. In this grid example, we have displayed sales figures for the last 6 months of 2010.
- The “Set” displays the row values for the last 6 months (July, August, September, October, November and December).
- The “Member” displays the aggregate value of all 6 months (bottom row).
To view detailed information for the generated set, we position the mouse (without clicking) over the SHOW-LAST-SIX set in the Customer Elements folder. We then wait a few seconds for the properties dialog to appear. We can display the “Member” properties in the same manner.
OK – we wrote at the start that knowledge of MDX code is not required for using the wizard. And that is true!! Most BI Office users will use the Time Intelligence Wizard to product all types of date-related functions without ever viewing a line of MDX code. And that is the main reason that the TI Wizard was developed.
For the techies and programmers among us, it's possible to view/edit the MDX code that has been generated by the TI Wizard. To do this, we just right-click the SHOW-LAST-SIX set and select the Edit Custom Set command, as shown below.
The reason we provide access to the underlying MDX code is that once we create a TI function, it can no longer be edited with the TI wizard. So the only way for analysts and managers to edit an existing TI function would be via the MDX code. But again, this is intended for special cases only. For the most, part, the MDX code exists in the background and does not have to be viewed or edited.
An example of MDX code is shown below. MDX editing is described in greater detail in the BI Office help.
To continue in this blog series, see Time Intelligence Wizard – Part 2 (User Hierarchies).