Aggregate Storage Time-Based Analysis

In This Section:

Introduction

Understanding Date-Time Dimensions

Understanding Linked Attributes

Designing and Creating an Outline for Date-Time Analysis

Modifying or Deleting Date-Time Dimensions

Loading Data Mapped to Dates

Analyzing Time-Based Data

The information in this chapter applies only to aggregate storage databases and is not relevant to block storage databases. Also see Comparison of Aggregate and Block Storage.

Introduction

Most data analysis includes a time (history) perspective. Aggregate storage applications provide built-in time-based functionality through time dimensions and date-time dimensions.

Aggregate storage time dimensions are similar to block storage time dimensions. They enable tagging the accounts dimension for such time balance calculations as first, last, average, and to-date values. Time dimensions are easily modifiable.

Aggregate storage date-time dimensions enable the first, last, and average time balance support. In addition, Administration Services provides the powerful Create Date-Time Dimension wizard for quick date-time dimension setup based on special calendars, plus linked attribute dimension setup enabling crosstab reporting of time-related data. Date-time dimensions have a rigid structure and are not easily modified after they are created.

The remainder of this chapter discusses date-time dimensions and the linked attribute dimensions that can be associated with them. For information about time balance calculations in aggregate storage outlines, see Performing Time Balance and Flow Metrics Calculations in Aggregate Storage Accounts Dimensions.

Understanding Date-Time Dimensions

Date-time dimensions contain a date hierarchy based on the selection of one of several standard corporate calendars. Dimension and member properties contain text strings that Essbase Server uses to manage the hierarchy, as shown in Figure 161, Sample Date-Time Hierarchy.

Figure 161. Sample Date-Time Hierarchy

This image shows a sample data-time hierarchy with the text strings that are used to manage the hierarchy.

The dimension member at the top of the hierarchy shows the comment:

/* TI-HIER,TP[3,1,4,5]!TI-MBR,TSPAN[1–1–2008,12–31–2009]! */.

The TI-MBR,TSPAN[date1, date2] information enclosed between exclamation points (!) defines the time range covered by the dimension; in this case two years, Jan. 1, 2008–Dec. 31, 2009. Positioned in front of the time span, the array TI-HIER,TP[3,1,4,5] describes the hierarchy. The first number in the array, 3, indicates that the hierarchy contains three time periods. The remaining numbers in the array indicate what time periods are included, based on the following possible levels:

  • 1—Year

  • 2—Semester

  • 3—Trimester

  • 4—Quarter

  • 5—Month

  • 6—Period

  • 7—Week

  • 8—Day

Array TI-HIER,TP[3,1,4,5] indicates that the array contains three time periods: year, quarter, and month.

Using the same time span TI-MBR,TSPAN syntax, each member comments field defines the time range that it covers; for example, member “Quarter 1 of Gregorian Year 2008” covers Jan. 1, 2008–Mar. 31, 2008, and member “Quarter 2 of Gregorian Year 2008” covers Apr. 1, 2008–Jun. 30, 2008.

Understanding Linked Attributes

Analyzing data in terms of relative position within time periods can be useful; for example, knowing that some costs increase in the last week of each quarter can help you balance expenditures. Linked attributes enable data analysis based on relative time periods such as the first month of a quarter, or the fourth week of a month.

Linked attribute dimensions can be associated only with the date-time dimension. Defining linked attribute dimensions is part of the Create Date-Time Dimensions wizard. The linked attribute dimensions that you create are based on the time depths you select when you define the calendar for the date-time dimension. For example, as shown in Figure 162, Sample Date-Time Dimension with Linked Attributes, if you select year, quarter, and month time depths and create linked attributes, the wizard creates three linked attribute dimensions (default dimension names): “Quarter by Year,” “Month by Year,” and “Month by Quarter.” Each attribute dimension contains members with sequential numeric names, indicating a relative position that will be used in analyzing the members associated with that attribute.

Figure 162. Sample Date-Time Dimension with Linked Attributes

The image shows a date-time hierarchy with linked attributes, as described in the text preceding the table.

Linked attributes enable you to compare sales of the first month of a quarter against sales of the second and third months of a quarter in a simple crosstab analysis, as shown:

This image shows a crosstab analysis, as described in the text preceding the table.

Note:

Linked attributes cannot be assigned to year time-depth members.

Linked attribute dimensions reflect two time components, which are easier to understand if we use the default names; for example, “Quarter by Year.” Members of the “Quarter by Year” dimension are associated with quarter time-depth members such as “Quarter 1 of Gregorian Year 2008” in Figure 162, Sample Date-Time Dimension with Linked Attributes. The first time component, “Quarter,” is called the association level, because it indicates the date-time dimension members to which the attributes are associated.

The second time component in this example, “Year,” is called the attachment level. The attachment level indicates a member level in the associated date-time dimension. Each member of the linked attribute dimension contains a number that indicates a relationship between the association level and the attachment level. “Quarter by Year: 1” is the first attribute member “Quarter by Year: 2” is the second member, and so on.

The date-time dimension members associated with linked attribute “Quarter by Year: 1” are the first quarters of their respective years. All date-time dimension members with the attribute “Month by Quarter: 1” are the first months of their respective quarters. Thus linked attributes enable analysis of information based on a common periodic relationship such as first, second, and third.

Designing and Creating an Outline for Date-Time Analysis

The Administration Services Create Date-Time Dimension wizard uses your option selections to design a time-analysis structure including date-time dimension through calendar templates. You choose a calendar type, customize it to your business requirements, and select other options to fit your time-analysis needs. The wizard creates the following outline components:

  • The date-time dimension.

    See Understanding Date-Time Dimensions.

  • (Optional) Linked attribute dimensions and member associations.

    See Understanding Linked Attributes.

  • (Optional) Attribute dimensions associated with day-level members. These are standard Boolean or text attributes based on specific dates or days of the week. You can use these attributes to include or exclude day-level information. For example, the wizard enables defining the following types of situations:

    • Assigning a Boolean attribute dimension such as “Weekend” to selected days of the week, resulting in every day of the week having a [Weekend].[True] or [Weekend}.[False} attribute.

    • Assigning attributes for special days or holidays that you designate.

    • Assigning text attributes such as “Monday” and “Tuesday” to appropriate weekdays.

The Administration Services Create Date-Time Dimension wizard builds all members for the calendars and time ranges specified.

Preparing for Creating Date-Time Dimensionality

Before running the Create Date-Time Dimension wizard, consider the following points:

  • What time period must the database model? You must provide the start and end dates.

  • What type of calendar fits your business use?

    See Understanding Date-Time Calendars.

  • What time-period granularity should the database reflect in its outline? Does business analysis drill down to the week level, or can weeks be omitted because reporting is by month or a higher level? Are any business measures analyzed and reported down to the day level? Are semester or trimester breakdowns required?

  • Would analysis of data within time relationships be valuable? For example, within a business calendar containing quarters, would the ability to see trends for each closing quarter be useful, such as whether sales increase toward the close of each?

The Create Date-Time Dimension wizard is a powerful tool with many options. Consider some practice runs to better understand the dimensions it builds. See also the topics for each page of this wizard in Oracle Essbase Administration Services Online Help.

Understanding Date-Time Calendars

The Create Date-Time Dimension wizard calendar templates determine the hierarchical structure of members in the date-time type of dimension. For all calendars, you must select a date-time dimension name, the calendar type, the first day of the week, and the beginning and ending dates for which you want to build date-time dimension members. You also must select time depths, which determine the calendar member hierarchy. The year time depth is required for all calendars. The availability of other time depths depends on the calendar type and other time depths selected. For example, semester and trimester time depths are mutually exclusive.

If a time depth is selected, the wizard creates, within each year, a member for each instance of that time depth. For example, if the month time depth is selected, 12 month members are created in the hierarchy under each year. A naming rule indicates a naming pattern for each member, which includes a number for the relative position within its year ancestor. For example, in the Gregorian calendar shown in Figure 162, Sample Date-Time Dimension with Linked Attributes, the month members follow a default naming pattern that includes the month number such as “Month 4 of Gregorian Year 2008.”

Note:

When selecting naming rules for time dimensions, it is possible to create duplicate members. This can cause the create date-time dimension procedure to fail unless the outline is set to allow duplicate member names. If the outline is not set to allow duplicate member names, you must avoid duplicates.

Depending on the calendar template and chosen time depths, you may need to define year, month, or period characteristics (semantic rules) such as period starting or ending dates, week count, or how months or weeks are grouped within their parent members. These characteristics affect the rules by which members are built and named. For some calendars, availability of year, period, or month semantic rules is dependent on other year, period, or month options selected. See “Create Date-Time Dimension Wizard—Select Calendar Hierarchy Panel” in Oracle Essbase Administration Services Online Help.

The Create Date-Time Dimension wizard provides templates for the following calendar types:

Gregorian

The Gregorian calendar is the standard 12-month calendar, January 1–December 31. Time depths are year, semester, trimester, quarter, month, week, and day.

Fiscal

Fiscal calendar definitions are based on company financial reporting requirements and can start on any date. Weeks In fiscal calendars have seven days. The 12-month reporting period includes two months of four weeks and one month of five weeks, in a repeated three-month quarterly pattern, (4-4-5, 4-5-4, or 5-4-4 weeks). If the year has 53 weeks, one month can have an extra week.

The week definition determines how to divide the calendar year into weeks. You can adjust the week definition to make a 52 week or 53 week year. Time depths are year, semester, trimester, quarter, month, week, and day.

Retail

The retail calendar, from the National Retail Federation, is modeled to analyze week-over-week data across years. It has a 4-5-4 quarter pattern with leap weeks every five to six years. The starting date differs from year to year but always falls in early February. When comparing year over year, standard practice is to omit the first week of a 53-week year to normalize for the extra week while the years keep the same holidays. Available time depths are year, semester, quarter, month, week, and day.

Manufacturing

The manufacturing calendar defines a 13-period year, made up of seven-day weeks. Manufacturing periods are divided into three quarters of three periods each and one quarter of four periods. All periods have four weeks, except for 53-week years, in which one period has five weeks.

When you define the 13 periods, you specify which quarter has the extra period. If the year has 53 weeks, you must specify which period will have the extra week. If you specify that the year starts on a specific date, you must indicate whether the year has 52 weeks or 53. If the year has 52 weeks, you may need to specify both 52-week and 53-week options, to pare the weeks first down to 53, then pare them down to 52. Available time depths are year, semester, quarter, period, week, and day.

ISO 8601

The ISO 8601 calendar contains seven-day weeks. The year can start before or after January 1 and is modeled to start on a day such that the first week of the ISO calendar contains the first Thursday of January. The first day of the week is Monday. Year, week, and day time depths are required for the ISO calendar, with no additional time-depth options.

Modifying or Deleting Date-Time Dimensions

The only way to modify a date-time dimension is manually. Because of its tight structure, modifying a date-time dimension can be risky. In most cases, it is better to delete the dimension and use the Create Date-Time Dimension wizard to recreate it with the changes built in by the wizard, particularly if changes involve adding or removing members.

Even for minor changes, you must be aware of the comments structure described in Understanding Date-Time Dimensions. The TI-HIER and TI-MBR,TSPAN sections must be at the beginning of the comments field. You can add comments after the last ! mark, such as:

TI-MBR,TSPAN[1-1-2006,1-31-2006]! This is the First Month

Note:

If you edit the TI-HIER and TI-MBR,TSPAN sections in member comments, you must ensure that the correct members exist throughout the hierarchy, and you must edit dependent ranges for ancestors or descendants. For example, if you change the span of a given month, you must also change the span of the quarter.

If you delete a date-time dimension, also delete all associated attribute dimensions. Linked attributes have meaning only when they are whole; that is, all members exist and be associated in the correct sequence. An outline is invalid if you disassociate a linked attribute from only a few members.

Be aware of the verification rules summarized in the following sections.

Verification Rules for Date-Time Dimensions

  • The dimension is tagged as “Date-Time”.

  • The dimension is a single-hierarchy, stored dimension.

  • The hierarchy is balanced; all level 0 members are of the same generation.

  • Members are all tagged with the addition (+) consolidator.

  • Member comment fields for each member contain the following information in the left-most part of the comment. (Details are described in Understanding Date-Time Dimensions.)

    • The member comment field for the top dimension member includes contains the TI-HIER, TP[ ] specification that contains an array that describes the hierarchy structure.

    • The member comment field for each member including the top dimension member contains the TI-MBR, TSPAN[ ] specification that includes the date range for the member, with starting date preceding ending date.

  • Along a generation, date ranges should be contiguous and should increase in ascending order. For example, along the month generation, the date range for each month member must have a starting date that logically follows from the ending date of the previous period. There must be no time gaps, and the span of the parent member must equal the total time span of its children.

Verification Rules for Linked Attribute Dimensions

  • Linked attribute dimensions are tagged as “Linked Attribute.”

  • Linked attribute dimensions are associated with the date-time dimension.

  • Each linked attribute dimension has two levels only, the parent level and the children attribute members.

  • Linked attribute member names are sequential numbers, starting with 1.

  • If attribute dimensions exist, all combinations of attachment and association levels for the date-time dimension must exist. For example, if the date-time dimension includes year, semester, and month time depths, if you create linked attribute dimensions you must create “Month by Year,” “Month by Semester,” and “Semester by Year.”

Loading Data Mapped to Dates

You can load data into the level 0 members of date-time dimensions using date strings instead of member names. Even if the date hierarchy does not span to the day granularity level, the data source can be specified by individual dates. The load process aggregates the values and stores them at the appropriate level.

Loading data based on date provides the following advantages:

  • Data can be loaded from any day-level load file, as long as the data file date format is supported.

  • If you set the data load to “Add to existing cells,” data can be loaded from a day-level load file to a less granular level in the hierarchy; for example to week or month level 0 cells.

Table 201 lists the date format strings you can use when you define the date field in the data load rules file:

Table 201. Date Format Strings

Date Format String

Example

mon dd yyyy

Jan 15 2006

Mon dd yyyy

January 15 2006

mm/dd/yy

01/15/06

mm/dd/yyyy

01/15/2006

yy.mm.dd

06.01.06

dd/mm/yy

15/01/06

dd.mm.yy

15.01.06

dd-mm-yy

15–01–06

dd Mon yy

15 January 06

dd mon yy

15 Jan 06

Mon dd yy

January 15 06

mon dd yy

Jan 15 06

mm-dd-yy

01–15–06

yy/mm/dd

06/01/15

yymmdd

060115

dd Mon yyyy

15 January 2006

dd mon yyyy

15 Jan 2006

yyyy-mm-dd

2006–01–15

yyyy/mm/dd

2006/01/15

Long Name

Sunday, January 15, 2006

Short Name

1/8/06 (m/d/yy)

Note:

Using extra white space not included in the internal format strings returns an error. Trailing characters after the date format has been satisfied are ignored. If you erroneously use a date string of 06/20/2006 with date format mm/dd/yy, the trailing 06 is ignored and the date is interpreted as June 20, 2020. Long Name format is not verified for a day-of-week match to the given date.

Analyzing Time-Based Data

The MDX and Smart View interfaces can take advantage of linked attributes for analyzing periodic relationships within data.

Using Smart View to Analyze Time-Related Data

Smart View provides use of linked attributes in several different ways:

  • Working on the free-form grid. With a date-time member on the grid selected, using Member Selection shows Date-Time as a dimension type and lists the members of that dimension. The Period and Range filters enable you to select and display information using linked attributes.

  • Using Query Designer.

    • Selecting Date-Time dimension on the Query Designer Toolbar includes linked attributes in the attributes list.

    • Using the POV toolbar, you can select a linked attribute, drag it to the free-form grid, and execute the query.

For additional information, see Oracle Smart View for Office User's Guide.

Analyzing Time-Based Metrics with MDX

Table 202 lists the MDX functions that are provided for analysis of date hierarchies. For a complete description of these functions, see the Oracle Essbase Technical Reference.

Table 202. MDX Functions for Analyzing Date Hierarchies

Function

Description

DateDiff

Returns the difference between two input dates.

DatePart

Returns the date part (Year/Quarter/Month/Day/DayOfYear/Weekday) as a number.

DateRoll

To the given date, rolls (adds or subtracts) a number of specific time intervals, returning another date.

DateToMember

Returns the date-hierarchy member specified by the input date.

FormatDate

Returns a formatted date-string.

GetFirstDate

Returns the start date for a date-hierarchy member.

GetLastDate

Returns the end date for a date-hierarchy member.

Today

Returns a number representing the current date on the Essbase computer.

ToDateEx

Converts any date string to a date that can be used in calculations.