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