MSAS: Creating a Time Dimension

Page 1 of 3

Author : Exforsys Inc. Published on: 7th Apr 2005

Time dimensions are part and parcel of OLAP cubes. At the lowest level of detail a time dimension may contain a month, minute or even a second. At the most summarized level it may contain a year, a decade or a century. The repetitive nature of time encourages users to view data in terms of a time dimension. How much sales of x product occurred during the month of March or April in the year 2000 compared to the year 2001? This would be a query on a sales cube with a time dimension.

Ads

A frequent issue that arises while dealing with time dimension is the definition of the year. Many organizations use the fiscal year in place of the calendar year or may be constrained to use both. In such instances Analysis Services permits the user to maintain two different hierarchies when the time dimension is created. These multiple hierarchies must be planned for before the dimension is created.

Creating a time dimension from a single Date/Time Column

Open the Dimension Editor, click on File Menu, point to New Dimension and launch the Dimension Wizard.

Click single table option and click next. Select Time_by_day as the dimension table and Click Next. Click the Time Dimension option, click Next, Select Year, Quarter, Month for time levels, and click Next. Click Next to skip the Advanced options screen.

On the finish Screen type TimeMonth as the name of the dimension, select the check box labeled Create a hierarchy of a Dimension and type Calendar in the Hierarchy Name box that appears.

Click Finish to create the dimension.

The user is back on the Dimension Editor with a dimension named TimeMonth.Calendar. Note that the name of the dimension cannot be changed in the properties pane. Also note that when an explicit hierarchy is created in the Dimension Wizard, Analysis Services creates a compound name—the dimension name followed by a period and the hierarchy name. Technically all dimensions will have a hierarchy. If a dimension name is created without a period, the dimension has one unnamed hierarchy. The impact of creating compound names is that Analysis services makes it possible for a client application to indicate the relationships between the hierarchies.

In the Dimension Editor select the TimeMonth.Calendar dimension. Click the Advanced tab in the properties pane. The value of the Date property is Time. The type property has many possible values but the defaults are Time and standard. The dimension wizard has also given standard names to the levels of time dimension. Level names appear above row headings in a cube browser.

Ads

Change the name of the year level to Calendar Year, the name of the Quarter Level to Calendar Quarter and the Month level to Calendar Month.

Switch to Advanced Tab of the Properties pane and note the value of the Level Type properties as they are selected. The Dimension wizard sets the value of each Level Type to match the type of time data stored in the level.