Create a Time Dimension

It is very likely that you will want to include some kind of Months and/or Years and/or Period dimension in your database, since it is relevant to analyze data over time (thus, generally we will call it a Time dimension). The demonstration in this manual, based on the Northwind database, did not include such a Dimension.

In the table shown in the previous segment, there is a Months field that could be used as the basis for a Months dimension—you could create it from the Measures table or create your own Months table (as done with Regions, above), which would link to the central table.

The general strategy for creating a Time dimension is to first look for the key field in your central Measures table that has the relevant time indicator (it might be OrderDate, for example, rather than ShippingDate). If the field does not exist in the Measures table, it may exist in a table that can be linked to it. For example, in the Northwind database, the OrderID table, which is the Measures table, links to the Orders table, which contains several date fields.

It is possible that you will also want to create a View or additional table that can parse the data as it exists in a field that indicates Time. As an example, consider a Field that appears as follows:

102000

102000

112000

122000

Knowing your database, you would recognize this as a concatenation of Period (first two digits) and Year (following 4 digits). You could create a View that separates the two, then add a table for Period, in which '10' has the Alias 'October' and another table that includes Years—i.e., 1998, 1999, 2000, 20001, etc. Through the use of View Tables and user-defined tables, you could then define links in OLAP Exchange and arrive at a useful Time dimension—i.e., Period, Month, Year, etc.

Use of DATETIME Field

It is possible, in OLAP Exchange, to create a Time dimension whose Detail members are to be Days. You must consider this option carefully, since the Dimension will have Members for each day a transaction occurs in your underlying database. (Ask, Are Days meaningful to me as a way of analyzing my data?)

In the process of Dimension creation, if you select a field in the Define Members dialog box that OLAP Exchange^® recognizes as the type DATETIME, after pressing Next, the following dialog box will appear:

Month Format:

Shows a drop-down box that allows you to select the preferred Month Format.

The succeeding figure details the options that can be selected from the Month Format dropdown menu boxed in the image above.

• Excel Date Format:
  - This format is the default date format in Excel wherein any date is converted into an integer and brought in as a number; the number being the total number of days elapsed from 1st January, 1900 until the actual date in the fact table.
• Excel 1904 Date Format:
  - This format is the other date format in Excel wherein any date is converted into an integer and brought in as a number; the number being the total number of days elapsed from 1st January, 1904 until the actual date in the fact table.

Include Time:

Check this box to include the time in standard am/pm or military time. Once you select the desired format, you can click on Back and check the Partial Fields dialog box to the view values.

Warning


You may want to be careful as this could create a large Dimension – especially if you include TIME, a Member will be created for every transaction.