When analysing data, the date dimension is usually involved.
Attribute and Hierarchies in the Date Dimension.
The following table describes the attributes and the hierarchies in the date dimension.
| Note: Using a fixed attribute like Hour of Day will only show the actual values for this attribute. |
Example:
If you create a report showing the Purchase Amount by Hour of Day, the result will show the Hours where purchases were made but no header that the values on the X-axis are Hours is available. This may make the reading of the data difficult.
Hierarchy/Attribute |
Description |
|---|---|
Day of Week. |
Attribute that defines the day of week in the format mo. - su. |
Hour of Day. |
The hour of the day displayed in numeric format 0-23. |
Year. |
Calendar year using the format YYYY, e.g. 2012. |
Year-Month. |
A flat Year-Month attribute in the format YYYY-MM, e.g. 2011-06. |
Year-Month-Date-Hour. |
Date hierarchy using the following display format: 2012-nov-12-02:00. |
Year-Week-Date. |
Date hierarchy using week number according to the ISO 8601 standard that defines Monday as the first day of week. All weeks also contain seven days. Week 1 may start in the previous calendar year and week 52/53 may end in the next calendar year. The display format looks like: 2012->12->2012-01-01. |
Dates in cubes are always fixed. To be able to create a report such as Last Month’s Sales, several dynamic dates are defined. These set of dates make the life easier to the user. The available dynamic dates are described in the table below:
Name |
Description |
|---|---|
Yesterday. |
Yesterday displayed as date (1 day). |
Last 7 Days. |
Displays the last seven days up to yesterday. (7 days). |
This Week. |
Display the 7 days from Monday to Sunday of this week (7 days) as one column per week. |
This Week (Day level). |
Display the 7 days from Monday to Sunday of this week (7 days) with a column for each day grouped by week. |
Last Week. |
Display the 7 days from Monday to Sunday of last week (7 days) as one column per week.
|
Last Week (Day level). |
Display the 7 days from Monday to Sunday of last week (7 days) with a column for each day grouped by week.
|
This Month. |
Displays the dates on day level of this month (28-31 days), one Column. |
This Month (Day level). |
Displays the dates on day level of this month (28-31 days). One column per day. |
Last Month. |
Displays the dates on day level of last month (28-31 days), one column. |
Last Month (Day level). |
Displays the dates on day level of last month (28-31 days), one column per day. |
Last 12 Months. |
Displays the last 12 months including the current month (12 months). |
This Year. |
Displays the 12 months of the current year. One column. |
This Year (Month Level). |
Displays the 12 months of the current year. One column per month. |
Last Year. |
Displays the 12 months of last year. One column. |
Last Year (Month Level). |
Displays the 12 months of last year. One column per month. |
Hierarchy Levels.
The hierarchies in the date dimension are optimized for presentation, e.g. we display month as a short name such as Apr. or Jun. This makes the presentation look nice:
The drawback is that the values on lower levels in the hierarchy are difficult to separate from one another when the parent context is lost. If we drill down to the month level of the Year-Month-Date-Hour hierarchy in the Member Selection Dialogue the result looks like:
It’s not obvious to know which year these months belong to. The recommendation is to avoid using the Level down button in the date hierarchies and expand the levels from the top instead.
Usage Restrictions.
A certain hierarchy or attribute can only be used once in the analysis function. This includes the dynamic dates (that are derived from one of the hierarchies). The consequences of this restriction can be somewhat difficult to understand.
If the Year-Month-Date-Hour hierarchy has been dragged to the Category area and you try to drag the dynamic date Yesterday to the Filter area. The YMDH hierarchy will be removed automatically.
It is however possible to combine different attributes that are independent of one another such as Hour of Day and Year.
