Database Reference
In-Depth Information
Creating the date and time dimensions
The date and time dimensions (sometimes also referred to as periodicity dimensions)
are essential for the majority of data warehousing and business intelligence solutions.
Analysis Services offers a couple of different options for creating time dimensions; you
can create a corresponding table that contains the necessary columns in the relational
database, or use a time dimension defined on the Analysis Services server. Creating
the time dimension using a table in the underlying data source isn't very different from
creating other dimensions; the primary difference is that you advise Analysis Services
that the dimension you're creating should be of the time dimension type. This enables
you to use certain MDX functions that are specific to time dimensions. The server-side
time dimension does not have a corresponding relational table; this option is useful
when you do not have necessary permissions to create or alter relational objects. Ad-
ditionally, you don't have to maintain data or use storage space for a server-level time
dimension. However, a server-level time dimension does have some limitations and
might not fit every requirement.
How to do it...
To create a server-level time dimension perform the following steps:
1. Invoke the Dimension Wizard as you would with a regular dimension.
2. The Select Creation Method dialog allows you to pick the type of dimension
you're about to create. As you saw earlier in this chapter (the Creating simple
dimension recipe), you can define a dimension based on an existing table. Ad-
ditionally, you could also have Analysis Services generate a time table with-
in your relational data source as long as you have sufficient permissions to
do so. The other option is to define the time dimension on the server without
creating the dimension table in the data source. For this example, select the
Generate a time table on the server option and click on Next .
3. The following screen allows choosing time periods you wish to include in your
server-level time dimension. As shown in the screenshot, you can choose the
first and last calendar dates included in the dimension, the first day of the
week, time periods (day, month, quarter, year, and so on), as well as the lan-
guage for time members. Unfortunately, the server-level time dimension does
not support time periods under date; for example, you don't have the option to
include hour, minute, or second levels, which could be quite useful for some
scenarios (for example, we might want to examine stock prices by hour). Hav-
Search WWH ::




Custom Search