Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 19, 2002

Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions - Page 7

By William Pearson

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

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM