About the Series
This is the third article of the series, Introduction to Microsoft SQL
Server 2000 Analysis Services. As I stated in the first article, Creating Our First
Cube, the primary focus of this series will be an introduction to
the practical creation and manipulation of multidimensional OLAP cubes.
The series is designed to provide hands-on application of the fundamentals of MS
SQL Server 2000 Analysis Services ("Analysis Services"), with
each installment progressively adding features designed to meet specific real-world needs. For more information on the series, as well as the hardware
/ software requirements to prepare for the exercises we will undertake,
please see my initial article Creating Our
First Cube.
Introduction
In the first article of the series, we
used the Cube Wizard to build an initial cube with the assistance of the
Dimension Wizard. Included in the dimensions we built through this
wizard-guided process was a calendar time dimension. In this article we
will recreate the calendar time dimension, this time focusing on the
process through which the Dimension wizard converts existing time/date fields
to a time dimension, along with its hierarchy of levels and members.
Next, we
will expose ways to customize the predefined, time-related properties that the
wizard establishes in building the time dimension, suggesting options for
customization of these properties to enhance the cube, from the dual
perspectives of user-friendliness and the reporting needs of the organization.
We will create an example of an alternate time dimension for fiscal
time reporting, and then we will discuss some of the considerations surrounding
the simultaneous housing of both hierarchies in the same OLAP cube structure.
In this article, we will:
- Discuss briefly the sources of time dimension data;
- Recreate the calendar time dimension that we
built "automatically" in Lesson One;
- Examine the setpoints of the time--related
properties that the Dimension Wizard generates as part of the
dimension build process;
- Explore the expressions that the Dimension
Wizard puts into place to create hierarchical levels from a date /
time column;
- Modify the dimension properties to facilitate
ease of use by Information Consumers;
- Customize the time dimension hierarchy to fit the
business environment;
- Create a fiscal year time
hierarchy;
- Explore considerations with regard to handling multiple
time hierarchies in the same cube.
Page 2: Introducing Time Dimensions
See All Articles by Columnist William E. Pearson, III
Introducing Time Dimensions
While I have
encountered numerous instances of OLAP cubes that do not require a time
dimension over my years of Business Intelligence consulting, I tend to agree
with most experts and business users alike that the time dimension is a
pervasive fixture in the typical OLAP database. As much of OLAP reporting
surrounds financial data, the element of time is consistently relevant in a big
way, with a wide range of levels of detail occurring among different business
environments. While it is not unheard of to find the occasional activity-based
OLAP cube, for example, that completely ignores time as a factor, this is
certainly the exception, and not the common rule. And while a lowest level of
months, with groupings of quarters and years, seems to be
the standard design for corporate financial time dimensions, I have come
across situations (for example a cube that reports on turbine operating metrics
for a large machinery manufacturing organization) where the lowest levels of
detail stored for reporting purposes are captured in minutes, and even seconds.
Because time
measurement follows relatively common patterns and parameters, the
wizard-guided creation of the time dimension can often suffice as adequate in
most material respects. And even when variations exist within the desired
presentation of the data in its end reporting incarnations, we can often simply
modify the work of the wizard to customize the time dimension to fit the needs
of organizational Information Consumers with only a small amount of
manipulation. Later in our lesson, we will discuss some of the situations where customization might
enhance the "off the shelf" time dimension, and discuss options that Analysis
Services provides us in making those alterations.
Where Do You Find the Time...?
Over my consulting career, I have seen the time/date data required to populate a cube's time
dimension taken from one of two main sources in the data warehouse: the fact
table or a separate time dimension table. While this is done in
various ways, most of which are dependent upon schema design and many of which
are beyond my influence, (as they exist upon my arrival), there are many
reasons for selecting a separate time dimension table, given the choice. The
most compelling of these reasons are the advantages that are imparted in terms
of space savings and processing efficiency when a separate dimension table,
joined by integer keys to the fact table, provides the date/time data to
populate the date dimension. Other factors that favor a separate dimension
table, such as the capability to store other-than-typical information about the
assorted levels and members (such as seasonal and other groupings that cannot
be readily derived from a single date/time field alone by the dimension
wizard), as well as the capacity of the dimension table to be shared among
multiple fact tables, add fuel to the argument for a separate table.
However, as it is often
seems to be the case that we "inherit" the designs of others (the designer(s)
are often long gone by the time I arrive on the site -- or at least no one will
admit having had the final say in the design of the warehouse), we are
fortunate to be able to derive time dimension hierarchies from a wide variety
of sources, particularly from variations of the two main sources I mentioned above.
In our example, we will work with time_by_day, a separate dimension table, in the FoodMart
2000 database that is deployed as a sample within a Typical installation of
Analysis Services, to explore the creation of both a calendar and a
fiscal time dimension as a central part of our lesson.
Page 3: Preparation for Creating a Dimension from the FoodMart 2000 Database
Preparation for Creating a Dimension from the FoodMart 2000 Database
To begin the steps of
the lesson, we will create a new database/data source for the FoodMart
2000 database. While many of us may have already set the
database up from the previous lesson, we will perform setup again quickly
for the benefit of anyone who has not. We will call the database the same
name as we used in the previous lesson, so if we already have a database with
the same name, and we perhaps want to recreate it for review purposes, we'll
need to either delete the existing database or simply call the new database
something else.
As we
learned in Lesson One,
the OLAP
database we create will organize cubes, roles, data sources, shared dimensions,
and other objects that we will cover in this and later lessons, in addition to
the objects that we are currently using. We will call our OLAP database MyCube2
(be careful here -- you cannot rename a database in the Analysis
Services console once it is created), setting it up initially by
right-clicking on the Server we see at the left of the console. Keep in
mind that, among other objects, the database can contain multiple cubes, each
with a single data source. In addition to setting up our database, we will
link a data source to our database before we start to construct our
cube.
- Start Analysis Manager, then right-click on
the Analysis Server name (shown as MOTHER in Illustration
1 below).
Illustration 1: Right-Click on the Analysis Server
We click New Database, and the Database dialog box
appears. We fill in the Database Name, MyCube2, and a
description that might be of value to a user or developer down the road. Let's
simply add "Tutorials Database," here -- the description is optional, of
course. The dialog appears as below.
Illustration 2: The Database Dialog Box
- Click OK.
- Expand MyCube2's database/cube icon,
(clicking in the plus (+) sign to the left of the icon will do the
trick).
Note that MyCube2 appears in the left-side tree,
complete with predefined, empty folders for object storage later, as shown in Illustration
3 below.
Illustration 3: The New Database with Directory Structure
We will need to connect to the data source from which we
wish to draw values.
-
Right click the folder within
MyCube2 called Data Sources, and on the popup shortcut menu,
select New Data Source.
The Data Link Properties dialog box
appears, with its Provider, Connection, Advanced and All
tabs, as shown in Illustration 4 below. Beginning with the Provider
tab (where the dialog opens by default):
-
Select the Microsoft Jet
4.0 OLE DB Provider (the native MS Access OLE DB provider).
(For more on this, review the on-line documentation for OLE DB
Providers, and data sources
in general).
Illustration 4: The Data Link Properties Dialog -- Provider Tab
-
Click Next.
The focus moves to the Connection tab.
- Select the FoodMart 2000 database.
FoodMart 2000.mdb
is located, by default, in the [Install
Directory]:\Program Files\Microsoft Analysis Services\Samples director;
Click on the ellipses (...) button, to navigate to the .mdb as
shown in Illustration 5 below.
Illustration 5: Selecting the FoodMart 2000 Database
-
Click Open, returning to the Connection
tab, as we see below:
Illustration 6: The Data Link Properties Dialog -- Connection Tab
-
Click the Test Connection button in the bottom
right hand corner of the Connection tab.
Page 4: Preparation for Creating a Dimension (Continued)
We should get a verification dialog confirming that the
source has been established in our definition (as shown in Illustration 7).
Illustration 7: Verification of the Connection to the FoodMart 2000 Database
-
Click OK, and the Microsoft Data Link
verification dialog box closes.
We will leave all the other Data Link Properties at their
default setpoints for now.
-
Click OK on the Data Link Properties
tab.
The Data Link Properties dialog closes,
and we can see that the new source appears under the Data Sources folder
in the tree area, on the left side of the Management Console, displaying
the actual file name, as shown below.
Illustration 8: Initial view of Our Newly Created Data Source
As mentioned in Lesson One, we might want to
make this a more intuitive -- or at least shorter-name in order to keep a tidy
appearance. As a simple "rename" capability is not in the cards, we will have
to be a bit creative here; A right-mouse click on our new data source allows a Copy
action, which will act as a workaround for renaming the object in question.
-
Right-click the new data
source.
-
Click Copy.
-
Highlight the Data Sources
folder.
-
Select Paste from the
popup menu.
This causes Analysis Services to
indicate that a duplicate has been detected, and to prompt us for a unique name
to rectify the confusion. We will respond to the new name request with MyFoodMart2,
using the dialog box that appears (as shown below in Illustration 9).
Illustration 9: Changing the Name of the Newly Copied Cube as a Means of Renaming
The Duplicate Name dialog thus again
acts as our agent of change, and, once we click OK, adds the
newly named data source under the data sources folder.
-
Click OK to close the Duplicate
Name dialog.
All that remains is to delete the original
data source, from which we cloned MyFoodMart2.
-
Right-click the original data
source, and select Delete on the popup menu, then click the Yes button,
to organize our new data source folder.
Our tree should now resemble that shown in Illustration
10.
Illustration 10: Changing the Name of the Newly Copied Cube as a Means of Renaming
In Lesson One we used the Cube Wizard, together
with the subsidiary specialized wizards (including the Dimension Wizard),
as called by the Cube Wizard, to rapidly create a simple cube to
explore the various aspects and steps of the process from a relatively high
level. In this lesson, the focus is the creation and manipulation of the time
dimension. We will, however, need a cube structure in place to house the
dimension hierarchies that we intend to build. We will now create the cube in
which our dimension will reside, and then move the focus solely to the handling
of dimension structures for the remainder of the lesson.
We will use the Dimension Wizard,
which we will access from the Cube Wizard in creating a cube shell
structure for the reasons we have mentioned, to create our first time dimension
hierarchy, Calendar Time. We will then return to dimension manipulation
via the Dimension Editor to create a Fiscal hierarchy, to further
illustrate our options, and the processes involved, in creating multiple time
hierarchies, complete with discussion regarding their uses.
We now have an OLAP database in place, linked to a valid data
source (the FoodMart 2000 database). Our preparation for the lesson
(and for the creation of any dimension) is complete. The next step in
our preparation will be to initialize the Cube Wizard, and to set up our
basic table structure. Dimensions are obviously of little immediate use if we
do not have data working within them. We will select a fact table along with
dimension table sources to allow us to get a feel for how the time dimensions,
our true concern in this lesson, interact with the data to construct OLAP
cubes, and to enable OLAP reporting.
Page 5: Creating the Cube with the Cube Wizard
Creating the Cube with the Cube Wizard
The Cube Wizard will
first guide us through the selection of our fact table, which houses the
measures upon which we seek to report. We will create a basic cube shell by
taking the following actions:
18.
Right-click the new Cubes
folder under the MyCube2 database we created above.
19.
Select New Cube from the
initial shortcut menu.
20.
Click Wizard, as shown
in the illustration below.
Illustration 11: Initializing the Cube Wizard
The Cube Wizard
springs to life, and we see the Welcome dialog appear, as depicted in Illustration
12 below.
Illustration 12: The Cube Wizard Welcome Dialog
21.
Click Next.
The
list of tables available in the MyFoodMart2 Data Source appears.
22.
Select sales_fact_1997
from the Data Sources and Tables pane on the left.
The
Details pane on the right is populated with the columns of the sales_fact_1997
table, as shown below:
Illustration 13: The Details of the sales_fact_1997 Table are Displayed
23.
Click Next.
24.
Add the following measures,
by double-clicking each, in the following order:
7 unit_sales
7 store_cost
7 store_sales
The selected measures
fields move to the Details pane as shown below:
Illustration 14: The Details Pane Displays the Column Selection
25.
Click Next.
Page 6: Managing a Calendar Time Dimension
We now enter the
dimension phase of the guided cube build, where we will begin our exploration
of the Calendar Time dimension.
Creating the Calendar Time Dimension with the Dimension Wizard
The Cube Wizard
now prompts us to select dimensions for our cube. The dimensions are, as yet,
undefined, and at this stage we will launch the Dimension Wizard to
begin designation of the dimensions.
26.
Click the New Dimension
button.
The
Dimension Wizard -- Welcome dialog appears, as shown in Illustration
15 below:
Illustration 15: The Dimension Wizard Appears
27.
Click Next.
The
Dimension Wizard prompts us to choose how we want to create the
dimension in the dialog that appears next.
28.
Click the radio button
alongside the Star Schema: A Single Dimension Table option, as shown
below:
Illustration 16: Select the Single Table Option
29.
Click Next.
We are prompted at the
next dialog to select the dimension table.
30.
Select the time_by_day
table in the Available Tables pane to populate the Details pane
to its right, as depicted in Illustration 17.
Illustration 17: Select time_by_day as the Dimension Table
We have the luxury of
choosing a separate time dimension table from the sample database -- a luxury
not afforded in some real world scenarios, as we discussed in the Where Do
You Find the Time...? section above.
31.
Click Next.
In the next dialog, we
are prompted to select the dimension Type. The Time selection
provides many pre-defined setpoints, and is based upon various common
assumptions that, in many cases, provide accurate and speedy setup of the time
dimension as part of rapidly creating a cube structure through the use of the
wizards. We will investigate these setpoints later in the lesson, and
determine which might serve as opportunities for customization.
32.
Click the radio button
immediately to the left of the Time Dimension option to select it.
The associated selector
dropdown list below our chosen option becomes activated, allowing us to choose
from more than one date field, when the wizard detects the presence of multiple
possible date columns. The dialog now appears as shown below:
Illustration 18: Selection of a Time Dimension Dimension Type
Page 7: Managing a Calendar Time Dimension (Continued)
The Dimension Wizard
provides two general options for Dimension Type, as we see above. A Time
Dimension is a specialized dimension that we use to represent standard time
periods in our cubes. The Time Dimension option does not appear as a
selection when we have chosen (on the earlier dialog of the Dimension Wizard)
to use multiple tables as the source for cube dimension data, because a time
dimension is always based upon a single field, as we have seen. The
single-field criteria obviously precludes the need for multiple tables, so the
wizard factors this concept into the creation process in cases where we choose
multiple tables to source the cube, and does not offer the Time Dimension
option.
33.
Leaving the selector at the_date
(the only selection, as it turns out, in our example), click Next.
We are now prompted to
select the levels of our new time dimension hierarchy. While the month
level is common to a large number of organizations' OLAP reporting structures,
let's go a bit further and select lower levels to further enrich our
comprehension of the capabilities of Analysis Services with regard to
time dimension manipulation and usage.
34.
Select the Year, Quarter,
Month, Day option (the default, as it is the top member of the list) in the
selector dropdown list. Leave the Year Starts On selections at their
defaults, as shown in Illustration 19.
Illustration 19: Selection of the Year, Quarter, Month, Day Option
35.
Click Next.
36.
Click Next at the dialog
that appears for Advanced Options to skip this dialog for now.
The Finish the
Dimension Wizard dialog appears.
37.
Input the word Time
in the Dimension Name box.
38.
Click (to place a check in) the
Create a Hierarchy of a Dimension check box.
39.
Type CalendarTime
into the Hierarchy Name box.
40.
Leave the Share this
Dimension with Other Cubes checkbox at its default (checked).
The completed dialog
should appear as follows:
Illustration 20: Finishing the Dimension Wizard... Option
41.
Click the "+" sign to
the left of the All New Dimension icon in the Preview tree.
We see the various
hierarchical levels in the new dimension appear as a preview. Note that the
levels go all the way down to the Day level, and that the number
of days in each month reconcile with our expectations for those appearing
in the standard calendar. Notice, too, that, although our choice of dimension
tables was sales_fact_1997, the year 1998 appears in the hierarchy
preview.
This is because our
choice for the date dimension table, time_by_day, includes both years,
demonstrating one of the strengths that we discussed for using a separate date
dimension table earlier: we can share the dimension table among multiple fact
tables, and thus decrease the size of the overall database (above and beyond
our already saving space by planting a single integer key on each row of
the fact table (approximately 4 bytes in size) instead of placing a date/time
field on every row of the fact table (approximately 8 bytes in size, with
much potential redundancy, over potentially millions of lines).
42.
Click Finish.
The Dimension Wizard
completes the creation of our new Time dimension and its CalendarTime
hierarchy. We see, at this point, that the Dimension Wizard has created
a dimension called Time.CalendarTime, as shown in the illustration
below:
Illustration 21: The New Dimension Appears Option
The name Time.CalendarTime,
appearing in the wizard and used as the object identifier throughout
Analysis Services, represents the dimension name as Time, with a
hierarchy name of CalendarTime. We will complete the cube creation
process at this stage, as we have the new Time dimension in place, by
taking the following steps.
43.
Click Next.
A message box appears,
indicating that the Fact Table Row Count is about to begin, and warning
us that the process may take some time, as shown below.
We will proceed, as we
know that our tables are relatively small.
44.
Click Yes to begin the
count process.
45.
Next, name the cube by typing MyCube2
into the Cube Name box of the Finish the Cube Wizard dialog, as
illustrated below:
Illustration 22: Name the Cube and Finish the Cube Wizard
46.
Click Finish.
The wizard disappears,
and Cube Editor (with the Schema pane showing) appears, as
follows:
Illustration 23: The Cube Editor, with Schema Pane as Shown, Appears
47.
Select File
> Save from the top menu to save the
work so far.
48.
Select File
> Exit to close the Cube Wizard
at this stage.
The Design Storage
dialog appears, as shown below.
Illustration 24: Design Storage Dialog
We will skip this
operation for the present.
49.
Click No to dismiss the Design
Storage dialog.
We
return to the Analysis Manager console.
Page 8: An Examination of Time-related Properties within a Time Dimension
An Examination of Time-related Properties within a Time Dimension
Let's take a look at
some of the time-specific properties that have been established by the
wizard for our new dimension/hierarchy set.
50.
Right-click the Time.CalendarTime
dimension under the Shared Dimensions folder for MyCube2.
51.
Select Edit from the
shortcut menu, as seen in Illustration 25 below.
Illustration 25: Select Edit to Resurrect the Dimension Editor
52.
Select the new Time.CalendarTime
dimension in the Dimension tree.
53.
Expand the Properties
pane beneath the dimension tree.
54.
Select the Advanced tab.
We
view the properties as partially shown below:
Illustration 26: The Advanced Properties Tab within the Dimension Editor
Notice that the Type
property exhibits the expected Time setpoint. The Type property,
for both a dimension and a dimension level, is, in general, not critical in
most respects. It exists primarily for the benefit of some client
applications, and specifically from the perspective of certain MDX functions
that use the setpoint to ascertain the default date in specific
situations; it has no direct impact from the perspective of the Analysis
Server. (For more information on this topic, do a topic search in the Books
Online that are installed with the Typical MS SQL 2000
Analysis Services installation and also available on the installation CD or
from the Microsoft Website.)
55.
Click on the All Caption
property.
56.
Type Calendar Time, as
shown in Illustration 27, into the associated value field.
Illustration 27: Changing the All Caption Value in the Advanced Properties Tab
57.
Press Enter.
58.
Click on the Basic Tab.
59.
Click on the Year level
within the Dimension tree, to display the Year member's Basic
properties tab.
The Basic Properties
Tab of the Year level member appear as follows:
Illustration 28: The Basic Properties Tab -- Year Level Member
Note the position and
setting of the level Name property. While the Dimension Wizard automatically creates member
names, these names may not include a description or other identification
that is optimal within the reporting conventions of the organization for which
we are creating the cube and its component structures. The Name that we
see displayed in the Basic Properties tab typically appears in
conjunction with headings in the browser, and we may want to display them
elsewhere in a reporting scenario. We can customize Names easily enough,
so as to make reporting self-evident; this is particularly useful, as we will
see, if we create multiple hierarchies within the dimension and want
Information Consumers to be able to identify precisely the meanings of the
fields they see in their reports.
We could easily change
the physical Names of the levels, but this would only affect the level Name
itself, and not those of its members. Let's go a step further to cause
the automatic creation of custom member names that will more closely meet the
example reporting environment of a group of Information Consumers. (Remember
that the default value for a given Member Name Column is the same as the
value assigned to the Member Key Column.)
60. Change the Member Name
Column property values for each of the levels within the
hierarchy, clicking on each in turn, from the Wizard-created Name
Column Property to the less ambiguous Custom Name Column Property,
as follows:
Note: The expressions I present in the Custom Name Column
Property column can be cut and pasted to the Member Name Column
property value field, if typing is cumbersome. The objective of this
exercise is to gain a conceptual understanding, not to attempt to learn
MDX expressions (although a study of MDX is certainly a wonderful time
investment).
|
|
|
|
|
|
|
|
|
|
|
|
|
Member
|
|
Wizard-created Name Column
Property
|
|
Custom Name Column Property
|
|
|
|
|
|
|
|
Year
|
|
DatePart('yyyy',"time_by_day"."the_date")
|
|
'Cal'+' ' +Format(DatePart('yyyy',"time_by_day"."the_date"))
|
|
|
|
|
|
|
|
Quarter
|
|
'Quarter
' + Format$(DatePart('q',"time_by_day"."the_date"))
|
|
'Cal
Q' + Format$(DatePart('q',"time_by_day"."the_date")) + '
- '+ Format$(DatePart('yyyy',"time_by_day"."the_date"))
|
|
|
|
|
|
|
|
Month
|
|
Format("time_by_day"."the_date",'mmmm')
|
|
'Cal' + ' ' +Format("time_by_day"."the_date",'mmmm')
+ ' - ' +Format$(DatePart('yyyy',"time_by_day"."the_date"))
|
|
|
|
|
|
|
|
Day
|
|
Format("time_by_day"."the_date",
'd')
|
|
Format("time_by_day"."the_date",
'mm-dd-yy')
|
|
|
|
|
|
|
A sample view of the
resulting dimension hierarchy Preview is as follows:
Illustration 29: Sample View of the Effects of the Modifications to the Name Column Property
(Note,
too, the change in the top dimension Name, which now appears as Calendar
Time.)
Customizations
similar to those we have implemented in the Name Column Properties above
provide numerous key benefits, among which are included the ability to use the
headings on reports and in browser activities, and so forth, in such a way that
we do not have to refer to higher levels in the hierarchical tree to specify
the fields in a way that is not ambiguous. If we create another hierarchy for
the Time dimension, as we show later, an Information Consumer can still
readily discern exactly which member is being retrieved at the member level,
removing the possibility of confusion between similar, but possibly very
different, members and their corresponding measures.
We can
also make simple format changes for ease of use of OLAP data by the targeted
audience, as we did at the Day level, where we simply changed the
existing digits to the actual date format (we could have added full years vs.
2-digit years, of course, for those concerned with the 2-digit presentation;
this is simply an exercise to demonstrate examples from a very large population
of possibilities. To make reporting easier for special situations, for
instance, we might even add such properties as Day in Week, whereby we
could analyze data by day of the week and so forth, by adding a leaf level to
the Time dimension. The range of possibilities is, indeed, substantial.
61.
Save the work up to this point
by selecting File >
Save
from the top menu. (We will be prompted to save changes upon attempting to
leave any given dimension for which we have enacted changes in the Editor.)
Page 9: Building and Managing a Fiscal Date Hierarchy
Building and Managing a Fiscal Date Hierarchy
As most of us have
encountered at some point in our business careers, many organizations use
"alternative" calendars, either solely, or, more likely, in some way in
conjunction with calendar date systems. The Dimension Wizard can often
create these fiscal (and other) date hierarchies, for the most part, from a
Date/Time field occurring in the database, although we may have to make
manual adjustments to select properties to achieve the full benefit of our
intended designs. We have seen that modification of the expressions that
support the hierarchical levels is straightforward enough; we have also mentioned
that custom levels can be added with minimal effort to support even the most
demanding of Information Consumer needs.
Creating the Fiscal Time Hierarchy with the Dimension Editor
Let's explore the
creation of a fiscal time hierarchy that will share the Time
dimension. This will provide many benefits, such as elimination of redundancy,
the ability to perform "alternate" drilldowns and zooms, and other efficiencies
from a consumption perspective. We will take the following steps to achieve
our ends:
62.
Select File from the top
menu within the Dimension Editor.
63.
Select New Dimension.
64.
Click Wizard from the
shortcut menu, as shown in the illustration below:
Illustration 30: Initialize the Dimension Wizard from the Dimension Editor
65.
Click Next at the Dimension
Wizard Welcome dialog.
66.
Select Star Schema: A
Single Dimension Table on the next wizard dialog.
67.
Click Next.
68.
At the Select Dimension
Table dialog, select time_by_day as the dimension table once again.
69.
Click Next.
70.
Select Time Dimension on
the Select Dimension Type dialog, once again.
71.
Leave the_date as the Date
Column in the selector box.
72.
Click Next again.
73.
At the Create the Time
Dimension Levels dialog, leave the Select Time Levels option at the
default of Year, Quarter, Month, Day again.
74.
Select 1 and October
in the respective Year Starts On selector boxes.
75.
Click Next.
76.
Click Next again, to
skip the Select Advanced Options dialog.
77.
At the Finish the Dimension
Wizard dialog, type (or select via the dropdown list) Time in the Dimension
Name box, once again.
78.
Place a check in the Create
a Hierarchy of a Dimension checkbox by clicking the checkbox.
79.
Type FiscalTime
into the Hierarchy Name box.
The completed dialog
should appear as follows:
Illustration 31: Finishing the Dimension Wizard...
80.
Click the "+" sign to
the left of the All New Dimension icon in the Preview tree.
We notice that, instead
of two years in the Preview, we now see three years (1997,
1998 and 1999). This is because, even though the dates we
have entrained from the time_by_day dimension table fall (as we have
learned) in the 1997 and 1998 calendar date range, we now
have defined years as crossing the boundaries of standard calendar time,
and have thus defined dates into a 1999 year group, for example, by
stating in our Year Starts On setpoints earlier, that a new year
begins with October 1, 1998 -- a year that is defined as 1999, in our
new fiscal grouping scenario.
Expanding the hierarchy
in the Preview pane down to 1999's Days level will
bear this out. We see that Quarter 1 of the 1999 Year
level consists of dates within the months of October, November
and December, confirming the creation of the levels within the
parameters we have specified. We have created a fiscal time hierarchy,
just as we intended.
81.
Click Finish.
We arrive again at the Dimension
Editor, having created our second Time dimension hierarchy.
82.
With the Time.FiscalTime
dimension selected, click the Advanced tab in the Properties
section at the bottom of the Dimension tree.
83.
Change the All Caption
property value to read Fiscal Time.
84.
Modify the Type property
value to Standard (the top item in the selection list, accessed via the
dropdown arrow).
The affected values of
the Time.FiscalTime dimension should appear as shown in Illustration
32:
Illustration 32: Property Values for the New Time.FiscalTime Dimension
Modifying the Type
property value allows us to distinguish between the Time.CalendarTime
and Time.FiscalTime hierarchies at the property level, should the
need arise to ascertain which is the default time hierarchy.
While order of addition (that is, the status of "first added") would
accomplish the same thing; An MDX function for which the default
property was relevant would see the Time.CalendarTime hierarchy as the
default, since it was added first -- of the Time-type dimensions present
in the cube we have constructed.
Let's finish the
tutorial by making modifications to the Member Name Columns for the
levels of the Time.FiscalTime hierarchy to make them consistent with the
conventions we adapted for their Time.CalendarTime hierarchy
counterparts. The purpose was, after all, to illustrate this as a means of
uniquely identifying similar, yet different, members of corresponding levels in
the two hierarchies -- without having to refer to other levels that might not be
visible or conveniently accessed.
85.
Change the Member Name
Column property for each of the levels within the Time.FiscalTime
hierarchy, clicking on each in turn, from the Wizard-created Name
Column Property to the less ambiguous Custom Name Column Property,
as follows:
|
|
|
|
|
|
|
|
|
|
|
|
|
Member
|
|
Wizard-created Name Column
Property
|
|
New Name Column Property
|
|
|
|
|
|
|
|
Year
|
|
DatePart('yyyy',iif
(Month("time_by_day"."the_date") < 10 or (Month("time_by_day"."the_date")
= 10 and Day("time_by_day"."the_date") <
1),"time_by_day"."the_date",DateAdd('yyyy', 1,
"time_by_day"."the_date")))
|
|
'Fisc'+'
' +Format(DatePart('yyyy',iif
(Month("time_by_day"."the_date") < 10 or (Month("time_by_day"."the_date")
= 10 and Day("time_by_day"."the_date") <
1),"time_by_day"."the_date",DateAdd('yyyy', 1,
"time_by_day"."the_date"))))
|
|
|
|
|
|
|
|
Quarter
|
|
iif(IsNull("time_by_day"."the_date"),
Null, CInt(((12 + month("time_by_day"."the_date") - IIf(Day("time_by_day"."the_date")
< 1, 10 + 1, 10)) mod 12 + 1) / 3 + 0.4))
|
|
'Fisc
Q' + Format$( iif(IsNull("time_by_day"."the_date"), Null,
CInt(((12 + month("time_by_day"."the_date") - IIf(Day("time_by_day"."the_date")
< 1, 10 + 1, 10)) mod 12 + 1) / 3 + 0.4)))+ ' - '+
Format(DatePart('yyyy',iif
(Month("time_by_day"."the_date") < 10 or (Month("time_by_day"."the_date")
= 10 and Day("time_by_day"."the_date") <
1),"time_by_day"."the_date",DateAdd('yyyy', 1,
"time_by_day"."the_date"))))
|
|
|
|
|
|
|
|
Month
|
|
Format("time_by_day"."the_date",'mmmm')
|
|
'Fisc'
+ ' ' +Format("time_by_day"."the_date",'mmmm') + ' - ' +
Format(DatePart('yyyy',iif
(Month("time_by_day"."the_date") < 10 or (Month("time_by_day"."the_date")
= 10 and Day("time_by_day"."the_date") <
1),"time_by_day"."the_date",DateAdd('yyyy', 1,
"time_by_day"."the_date"))))
|
|
|
|
|
|
|
|
Day
|
|
Format("time_by_day"."the_date",'mmmm')
|
|
Format("time_by_day"."the_date",
'mm-dd-yy')
|
|
|
|
|
|
|
A Preview of the
resulting dimension hierarchy should bring back results similar to those shown
in Illustration 29 above, with the classifications of the members
into Fiscal groups, and the presence of 1999, etc., being
the only significant differences.
One point, however,
that bears special consideration at this juncture, is the fact that the Day
members of the Time.FiscalTime hierarchy are treated identically to the Day
members of the Time.CalendarTime hierarchy. This can be noted most
strongly in the results shown in the partial depiction of the Preview
pane for the Day members under the new Fisc 1999, Fisc Q1 -
1999 and Fisc October-1999 groups, as shown in Illustration 33 below.
Illustration 33: Day Members Retain Actual Dates in Calendar and Fiscal Hierarchies
We see clearly that,
even though the first quarter of 1999 is a Fiscal quarter, its
members consist of 1998 dates -- indeed, the Day level is the
common level from which both hierarchies spring.
86.
Select File
>
Save from the top menu to save the
work we have completed in the tutorial.
87.
Select File
>
Exit to return to the Analysis
Manager console.
88.
Select Console
>
Exit from the Analysis Manager
Console top menu to close Analysis Services.
Page 10: Next in Our Series...
Next in Our Series...
In this, the third article of the Introduction to MSSQL Server 2000 Analysis Services series, we recreated the calendar time dimension that we built in our first lesson, this time focusing on the process through which the Dimension wizard converts existing time/date fields to a time dimension, with its hierarchy of levels and members. We discussed considerations surrounding the sources of time dimension data, detailing the reasons that a separate dimension table is, in most cases, the best approach to take, given control over the selection process. Next, we exposed ways to customize the predefined time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube from the often intersecting perspectives of user-friendliness and the reporting needs of the organization.
We examined the setpoints of the time--related properties, as generated by the Dimension Wizard, as part of the dimension build process, exploring the expressions that the Dimension Wizard puts into place to create hierarchical levels from a date/time column. We then practiced making modifications to the dimension properties to facilitate ease of use by Information Consumers, as well as to customize various reporting attributes to fit the business environment more closely.
Finally, we created an example of an "alternate" time dimension for fiscal time reporting, and then we discussed some considerations relevant to the simultaneous housing of both hierarchies, within the same Time dimension, inside our OLAP cube structure.
style='color:windowtext'>In our next lesson, we will explore the intricacies of Parent-Child dimensions, and discuss the considerations and options that surround them in such areas as recursive column sources for their population, differences between Parent-Child dimensions and standard dimensions, and various actions that must be handled differently in their creation and maintenance.
Our ongoing examples will continue through the next lesson, Parent-Child Dimensions, after which we will arrive at the fifth lesson, Working with the Cube Editor. In Lesson Five we will pull together the components that we have constructed in Lessons Two through Four, to assemble a cube similar to, but more sophisticated than, the cube we generated in our first lesson.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.
Introduction to MSSQL Server Analysis Services Series