Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions

Thursday Sep 19th 2002 by William Pearson
Share:

The third article in the advanced SQL series 'Introduction to MS SQL Server 2000 Analysis Services' focuses 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.


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.

  1. 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


  1. Click OK.
  2. 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.

  1. 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):

  1. 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


  1. Click Next.
The focus moves to the Connection tab.

  1. 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


  1. Click Open, returning to the Connection tab, as we see below:



Illustration 6: The Data Link Properties Dialog -- Connection Tab


  1. 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


  1. 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.

  1. 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.

  1. Right-click the new data source.
  2. Click Copy.
  3. Highlight the Data Sources folder.
  4. 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.

  1. Click OK to close the Duplicate Name dialog.
All that remains is to delete the original data source, from which we cloned MyFoodMart2.

  1. 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



Building and 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.

Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved