Use the Time dimension and MDX functions to slice your data by time periods
Of the several misconceptions surrounding the Time dimension, one of the biggest is that an OLAP cube must have a Time dimension. The truth is that some OLAP applications, such as activity-based costing, don't have a Time dimension at all. Activity-based costing usually doesn't need a Time dimension because the costing is based on activity, not time. The second most common misconception is that an OLAP cube can have only one Time dimension. Some insurance applications have several Time dimensions, including Underwriting Date and Accident Date. Cash-flow applications also usually have several time dimensions.
A basic rule of the OLAP model is that all dimensions must be equivalent in structure and operational capabilities. However, most OLAP tools, including SQL Server 2000 Analysis Services and SQL Server 7.0 OLAP Services, allow one exception: You can declare more than one dimension as a Time dimension, which has attributes that other dimensions don't. After you've designated a dimension as a Time dimension, Analysis Services and OLAP Services can automatically create levels such as Year, Quarter, and Month and can also generate member names. In addition, you can use many time-aware MDX functions, such as Ytd(), to analyze your data according to time periods. Let's look at how to create and work with Time dimensions in Analysis Services and OLAP Services.
Creating a Time Dimension
Some people create a view from the fact table, such as
SELECT [Fact_Table].[Date]
FROM [Fact_Table]
GROUP BY [Fact_Table].[Date]
and use the view as a source for the Time dimension. This method has a couple of drawbacks. First, it's inefficient: The fact table is usually much bigger than the dimension table, and accessing a view of the fact table is the same as accessing the underlying base table. Another disadvantage of using the fact table as a source for the Time dimension is that the dimension won't contain a date that had no activity. Thus, this method can create gaps in the dimension sequence by skipping weekends, holidays, and so on. If you want these gaps, remember to exclude irrelevant dates from your Time dimension table.
A better way to create a Time dimension is to create a special Time dimension table in your data warehouse to hold all relevant dates. Simply create the table in Microsoft Excel, then use Data Transformation Services (DTS) to import the table into the data warehouse. This approach to creating a Time dimension significantly improves dimension and cube processing because you don't need to query the fact table to get the Time dimension members. And if the table's date field is of any time data type (e.g., smalldatetime), Analysis Services' and OLAP Services' Dimension Wizard, which you use to create dimensions, detects that the dimension could be a Time dimension and prompts you to confirm its choice, as Figure 1 shows. After you confirm that the dimension is a Time dimension, the Dimension Wizard helps you create the Time dimension's levels (e.g., Year, Quarter, Month, Day), as Figure 2 shows. You can also define the first day and month of the year; the default is January 1.
The Dimension Wizard also creates member names for you. Although these generated names are accurate, they don't always include the member's specific time period. For example, if you select 2 years of data at the Month level, you'll have two members named January, two members named February, and so on—with no way to distinguish which month belongs to which year. The solution is to customize your Time dimension member names. The sidebar "Customize Time Dimension Member Names," below, shows you how to give your members meaningful names, such as Q1/1999 for the first quarter of 1999, 1/1999 for January 1999, and 5/1/1999 (European notation) or 1/5/1999 (US notation) for January 5, 1999. You should also ensure that for all levels, the ORDER BY property is set to Key; otherwise, Analysis Services and OLAP Services sort the members by name instead of by date. To ensure that ORDER BY is set to Key, open the Dimension Editor by selecting and right-clicking the Time dimension in the tree pane and selecting Edit. Then for each level, select the Advanced tab in the Properties pane, and examine the ORDER BY property.
Even if you skipped these wizard options because you marked the dimension as Standard instead of Time or because the date field's data type isn't a date type, you can always convert a Standard dimension to a Time dimension. From the Dimension Editor, select the root of the tree pane, and in the Properties pane, select the Advanced tab, then change the dimension type from Standard to Time. Next, for each level, change the level type from Standard to the relevant type (e.g., Month).
To make reporting easier, you can add properties such as Month and Day In Week to the Time dimension members' leaf level. For example, if your leaf level is Day, you can easily add a property such as Day In Week, which lets you analyze your data by the day of the week. Figure 3 shows how to define properties such as Month and Day In Week. From the Dimension Editor, select Insert, Member Property, then the date field; enter the property's name in the Name field; and adjust the source column and data type as necessary. Table 1 shows the source column and data type information for the Month and Day In Week properties.
If you use the Month property, you can create a virtual dimension called Month based on this property. A virtual dimension is a logical dimension that you base on a member property, on columns in the physical dimension's tables, or on a column from a physical dimension. Adding a virtual dimension to a cube doesn't increase the cube's size because a virtual dimension, unlike a physical dimension, doesn't have aggregation data. Virtual dimensions also don't affect cube processing time because Analysis Services and OLAP Services calculate them in memory when needed. However, queries that use virtual dimensions might run slower than queries that use physical dimensions. After you create the virtual dimension Month, you can chart Months versus Years, as the screen in Figure 4 shows. (I used the Knosys ProClarity client tool to create this chart, but almost any client tool would work.) This chart shows the Month virtual dimension on the horizontal axis and the Time dimension's Year level charted against the month. So although the Time dimension is a single dimension, we can chart two levels of the dimension—one versus the other—by using a virtual Time dimension.
Prev. page
[1]
2
3
next page 
