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
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.
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 /
- Modify the dimension properties to facilitate
ease of use by Information Consumers;
- Customize the time dimension hierarchy to fit the
- Create a fiscal year time
- Explore considerations with regard to handling multiple
time hierarchies in the same cube.