Designing a Custom Time Dimension Load

Time dimensions provide time-based views of your data. PMF has default Time dimensions that are available, or you can create custom Time dimensions. The Dimension Loader enables you to specify a custom Time dimension that supports any regular or irregular time period, which can be greater than or less than 12 months. You can specify custom start and end dates for each period, and you can choose between weekly or monthly rollups at the lowest dimension level.

Performance Management Framework 5.1.3 now supports up to five levels of logical Time. This provides you with the ability to create a Time Dimension that goes down to display more detail as needed.

You can specify Time in many more flexible ways. You can split Time at up to six levels. For example, you could create a calendar that specifies weeks, months, quarters, half-years, and years.

To configure PMF to support more Time levels, follow the same directions as you would for Alternate Time Dimensions in the PMF documentation. The Time Dimension Loader will allow you to specify up to five levels. You will need to build and deploy your Calendar file to contain the specific dates that map to your Time Dimension levels.

Time dimensions must be three levels, and can represent the standard Y/Q/M (Year/Quarter/Month) time structure, or any other non-standard time structure.

You can configure any arbitrary custom calendar, regardless of regularity, where you define the three-level Time dimension and the trending time parameters. PMF automatically generates the Time dimension values and adjusts inbound dates for measures to map to the correct fiscal periods you specified. If you are setting the default Today date to use the system clock date, PMF automatically maps the current system date to the correct fiscal time period when you run views.

If you select a start month other than January, for example, April, then month 01 of the calendar would be April, month 02 would be May, and so forth. The fiscal year of a custom calendar is the calendar year of fiscal month 12. If April of 2006 is month 01, then April is treated as month 01 of fiscal year 2007, since month 12 is March 2007.

PMF can create almost any arbitrary three-level Time dimension provided that the data is properly defined in an accessible data source. Non-standard Time dimensions can be either regular or irregular. A regular Time dimension always has the same number of lower level entries for each and every upper level entry.

If your year is structured into 13 fiscal periods and every fiscal period always has exactly 4 fiscal weeks, then your data is regular. If you occasionally have a fiscal year with 14 fiscal periods or a fiscal period with 5 fiscal weeks (which can happen in situations where you need to adjust for extra days in the year), then your data is irregular.

PMF can work with both regular and irregular calendars. Irregular calendars require you to set up a custom data source that details how the calendar works.

Creating a Custom Time Dimension From an External Data Source

How to:

Load a Non-Standard Time Dimension From a Custom Data Source

Reference:

Sample Files Containing External Time Data

PMF can create a custom Time dimension from an external data source, provided that the external data source is accessible to the PMF environment and is properly configured. The WebFOCUS adapter must be set up to access the external data source before mapping the dimension.

The external data source can contain regular or irregular time data. PMF determines regularity or irregularity by inspecting the data. If the time data is regular, PMF computes the prior time periods automatically. PMF defines a prior time period as a time period with identical second and third level values when the year (first level) is one year earlier. If the time data is irregular, you must add prior time period information to the time source. For example, if you have a custom calendar with 53 weeks, PMF cannot determine how to map a 53 week year onto the prior 52 week year, and you must define the prior time period information in the source data. If PMF determines that data is irregular and prior date information is missing, the system issues an error message.

All fields and data values must be the same length, in alpha format. For example, if the source data has single digit low values and double digit high values, the single digit values must have leading zeros to make the values equal in length.

The external data source must be a standard WebFOCUS Master File located in the WebFOCUS application path (APP PATH). The following fields must be defined in the WebFOCUS Master File for both regular and irregular Time dimensions.

Field Name	Description
TIME_KEY	Key field used to join to the measures source data.
TIME_LEVEL01_VALUE	Data value of the first level in the Time dimension hierarchy.
TIME_LEVEL02_VALUE	Data value of the second level in the Time dimension hierarchy.
TIME_LEVEL03_VALUE	Data value of the third level in the Time dimension hierarchy.
START_DATE	Calendar date of the first day of the time period.
END_DATE	Calendar date of the last day of the time period.

The following fields must be defined in the WebFOCUS Master File for only irregular Time dimensions.

Field Name	Description
PRIOR_TIME_LEVEL01_VALUE	Data value of the first level of the prior period.
PRIOR_TIME_LEVEL02_VALUE	Data value of the second level of the prior period.
PRIOR_TIME_LEVEL03_VALUE	Data value of the third level of the prior period.

Top of page

Reference: Sample Files Containing External Time Data

The PMF sample data directory includes examples of external time data. The following five (.csv) sample files include Master File definitions and comma-delimited data files: time_source_reg1, time_source_reg2, time_source_irreg1, time_source_irreg2, and time_source_irreg3.

If you want to use these sample files, you must add the appropriate WebFOCUS FILEDEF statement into your WebFOCUS profile. In addition, you must use the alternate Master File definitions for the source of the measure data (centord) for the sample measures. Examples of the appropriate code to use are contained in the following source files: centord_reg1, centord _reg2, centord _irreg1, centord _irreg2, and centord _irreg3.

Top of page

Procedure: How to Load a Non-Standard Time Dimension From a Custom Data Source

Once you have correctly set up a non-standard Time dimension data source, perform the following steps to load the time data into the PMF database.

From the Manage tab, click Dimensions and then select the T-Time dimension to access the standard Time dimension load panel.
From the Table Type drop-down list, select Separate table.
From the Source Table drop-down list, select the Master File containing your custom Time dimension data.
The form displays the default settings for the custom Time dimension.
Click the Sample Data button to verify that your custom time data is correct.
From the Key Field drop-down list, select the key field which links the Time dimension source data to your measures source data.
Supply a Level Name for each of the time levels. The default names are Year, Quarter, and Month, as shown in the following image.

These names are displayed on most standard views.
To map each time level to a field in the source data file, confirm that Field is selected from the Source drop-down list (this is the default), then select TIME_LEVELnn_VALUE from the Field/Define Code drop-down list, where nn represents the level number.
Click the Preview button to display the data to be loaded. For more information, see Interpreting the Dimension Loader Report.
Click the Save button to save the dimension metadata in the database.
Click the Load button to load the dimension data into the database.

Displaying Custom Time Dimension Data

For standard Time dimension data in the Y/Q/M (Year/Quarter/Month) format, PMF displays quarterly data in the format yyyyQq, where yyyy is the four digit year, and q is the actual quarter number, for example, 2006Q1. PMF displays monthly data in the format yyyy/mm, where yyyy is the four digit year, and mm is the actual month number, for example, 2006/01. Note that the letter Q in the quarterly data example, and the forward slash in the monthly data example, were assigned using the Abbr. column in the Dimension Loader for the Time dimension.

For non-standard Time dimension data, you can choose alternate abbreviations of up to 5 characters for each level, or you can leave them blank. For standard Y/Q/M time, when you are displaying information at the monthly level, there is no need to display the value of the quarter. Months 01-03 are quarter 1, months 04-06 are quarter 2, and so on. However, in other situations, the value of the second level might not be obvious. For example, if you are using 13 fiscal periods and 52 weeks in the year, it may not be obvious that week 45 is in fiscal period 12. In these situations, you have the option to always display the second level when viewing at the third level, by selecting the check box in the Req. column for time level 2 in the Dimension Loader for the Time dimension.

If PMF determines that time level 3 has repetitive information and the combination of time level 1 and time level 3 is not unique, then time level 2 is automatically required for all views. In these situations, the Req. check box is activated by default and is not displayed in the Dimension Loader for the Time dimension.