Date Dimension out of a simple table

Hi all,

I have a very simple table were I want to have a Time dimension.

Table --> Date | Country | Region | City | Office..........

Time Dimension --> Year, Semester, Quartal, Month

Region Dimension --> County, Region, City, Office

This is one fact table and I have no Problems in creating the Region Dimension but problems with the Time Dimension. The Date Column contains Date and time.

If I use Time dimension I need to add columns into my table with Year, Semester, Quartal, Month. I don't want to do that.

If I use Serverbased time dimension I have to enter the start Year and the end Year, but the table is growing and I don't want to devide my Time column into one Date column and into one Time column.

In SQL 2000 it was possible to use one Column to create this simple dimension. What I'm doing wrong?

How can I solve this?

Thanks in advance

Hello. First, here is a link on how to create dates for a time dimension.

http://blogs.conchango.com/jamiethomson/archive/2007/01/11/T_2D00_SQL_3A00_-Generate-a-list-of-dates.aspx

Next. In SSAS2005 you will have to create the levels in the time hiearchy on you own. It is not that hard. In the dimension table or in the data source view(as a named calculation) you can use the TSQL-functions like YEAR(), QUARTER(), MONTH() and DATEPART() . They are all listed in Books On line under time functions for the database engine.

HTH

Thomas Ivarsson