For ASO cube, EAS provides an automatic process to set up the date-time dimension. It has a comprehensive set of options that accommodate different needs in reality. This function dramatically relieves human labor from creating and manipulating the “Date” dimension. (NOTE: This wizard is available only for ASO cube)

1. With the Outline open, navigate to “Outline” -> “Create date-time dimension”.

(Figure 1)

2.  Choose a different name for the dimension. In this example, we renamed it to “Period”.

1. Select a different day of week as the first day. For example, for Middle East countries, the first day of the week is Sunday.
2. Define the start and end dates of the reporting calendar.  Various companies define fiscal years as opposed to calendar years, and the wizard offers the flexibility to pick a different start date and end date in the “Modeling period” pane.(Figure 2)

3.  Click “Next”.

4.  Select “Add”, and choose from the following options:

1. Gregorian: The Gregorian calendar is the standard twelve-month calendar starting on Jan 01 and ending on Dec 31.
2. Retail: The retail calendar, which is derived from the National Retail Federation, analyzes week-over-week data across years. The calendar uses a 4-5-4 quarter pattern and includes leap weeks every 5 to 6 years. Starting dates, which differ from year-to-year, occur in early February. When one year is compared to another, standard practice omits the first week of a fifty-three week year—to normalize and to enable each year to include the same number of holidays.
3. Manufacturing: The manufacturing calendar defines a thirteen-period year and a 7-day week. The periods are grouped into quarters, with the first three defined by three periods each and the last one defined by four periods. With a 53-week year, one period includes an extra week.
4. Fiscal: Fiscal calendars, which are based on financial reporting requirements, can start on any date.
5. ISO: The ISO 8601 calendar uses seven-day weeks. The year can start before or after the start of the Gregorian new year (Jan 1), but the week that includes the start day must include the first Thursday of the Gregorian year. Monday is the first day of the week. (Oracle Corp. 2014)(Figure 3)

5.  Define semantic rules.  When a hierarchy is selected (in this case, we pick “Fiscal”), the wizard herein provides more detailed setting. By choosing different semantic rules combinations, a 7-day week might be broken into two weeks, especially in the beginning/ending of the year or month.  (TIP: Perform a sanity check at the day level after creating the date dimension.)

(Figure 4)

6.  Select different time depths according to different requirements. Particularly, expand a certain level and change the naming convention. (NOTE: Unfortunately, a customized format is not supported.)

(Figure 5)

7.    The next step is for day-level modeling; if day-level modeling is not required, proceed to Step 8.  Setup day level attributes. As shown in Figure 6, we can predefine holidays and, most importantly, associate day-of-week attributes to day-level members by using “Reserve days” or “Day modeling”. (NOTE: Holidays will be created as a Boolean attribute.) (Figure 6)

     a)    By using the “Reserve days” option, every day-level member will be associated with 7 Boolean attributes for each day of week; for example, “Dec 25 2014” shown below is associated with a set of attributes of “Monday: False”, “Tuesday: False”, “Wednesday: False”, “Thursday: True”, etc… (Figure 7)

       b)     I recommend using the “Day modeling” option to tag the day of week; the wizard will automatically create a “Day of week” text attribute dimension and associate every day with its day of week. It is easy to reference in calculations that need to include/exclude weekends or selected days of the week from the rest. It seems to be redundant using “Reserve days” compared to using “Day modeling”, since “Reserve days” option would associate 7 attributes to each day, while “Day modeling” option only associates 1 attribute. However, when it comes to tagging holidays, using a Boolean attribute advantageous as only two attributes will be needed: “Holidays: True” and “Holidays: False”.

(Figure 8)

8.   Click “Finish”. (NOTE: After the “Date-Time” dimension is created, you must make sure that date ranges in the comment field for ancestors and descendants correspond to each other. For example, if you change the span of a given week, you must also change the span of the month.)

(Figure 9)

The “Create date-time dimension” wizard is an efficient tool when creating a reporting application that requires day-level analysis. The “Date-Time” dimension type is supported only for ASO cube (see Comparison of Aggregate and Block Storage and Loading Data Mapped to Dates), however; with the “Date-Time” feature, “even if the date hierarchy does not span to the day granularity level, the data source can be specified by individual dates and the load process can aggregate values and stores them at the appropriate level by using date strings” (Oracle DBAG).  As with any Essbase reporting project, there are several performance and design issues to consider when building an application that requires analysis at a more granular level than monthly.  First, although it is technically feasible to build a day-level period dimension in BSO, the performance of data retrieves and financial reports may suffer.  Therefore, an ASO cube is going to be the obvious choice in these situations.  Second, the source data should always be analyzed early in the process.  If the data source is providing day-level activity while the Essbase database is built to the weekly level, it will become extremely difficult to integrate the two using simple data load rules.

Tags: ASO, Date-Time Dimension, Create Date-Time Dimension Wizard, Day-level Analysis, Week-level analysis, Time-Based Analysis

Reference:

Oracle Essbase Database Administrator's Guide.

Oracle Essbase Administration Services Online Help.

0 responses