Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


time dimensions

time dimensions

time dimensions

I am new to SSAS and working through the Microsoft SQL Server 2005
Analysis Services Step By Step book. When it comes to creating time
dimensions, the example shows all sorts of different fields for
DayNumberOfMonth, DayNumberOfYear etc. My own tables that I want to
create time dimensions all only have a single datetime field, with the
date of an action time stamped in the format dd/MM/YYYY hh:mm:ss. How
can I create a time dimension that breaks down time into
year/month/quarter etc when I only has this one field to work with?

RE: time dimensions

I usually use a script to create them from within SQL.  Here's an example, run this in Management Studio:


DECLARE @Time TABLE (TheDate DATETIME, Month_Number INT, Month_Name VARCHAR(20), Quarter_Number INT, Quarter_Name VARCHAR(20), Year_Number INT)

SELECT @StartDate = '20000101'
SELECT @EndDate = '20201231'

WHILE @StartDate < @EndDate
    INSERT INTO @Time (TheDate, Month_Number, Month_Name, Quarter_Number, Quarter_Name, Year_Number)
    SELECT @StartDate,
    CONVERT(INT, CONVERT(CHAR(4), YEAR(@StartDate)) +  RIGHT('0' + CONVERT(VARCHAR(2), MONTH(@StartDate)), 2)),
    DATENAME(month, @StartDate) + ' ' + CONVERT(CHAR(4), YEAR(@StartDate)),
    CONVERT(INT, CONVERT(CHAR(4), YEAR(@StartDate)) +  RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(quarter, @StartDate)), 2)),
    'Q' + DATENAME(quarter, @StartDate) + ' ' + CONVERT(CHAR(4), YEAR(@StartDate)),
    SELECT @StartDate =  DATEADD(day, 1, @StartDate )


Your tutorial probably has many more columns than you will end up using.  Another way to create the table if I recall correctly is to create a server time dimension, and I believe it will give you the option of creating a script to create the table, columns and records for you, which you can copy and paste in Management Studio.

RE: time dimensions

I always manually build my time dimensions with a process seperate from what my data.  

Create a Table with the needed columns, it looks like you want at the very least.

1)Date_SK int
2)Date datetime
3)Date_Desc varchar
4)Year int
5)Month int
6)Month_Name varchar
7)Month_Desc varchar
8)QTR int
9)QTR_Desc varchar

Use the various Date Part functions to seperate out year month and those columns.

Use the Desc column to hold a User friendly string that represents the member.

For month you could have May but every year has may so to make it more user friendly you can have May 06  as the description  QTR you would use QTR 1 06.  Date you want to convert to a useable string because if you use the date column you will get the full Date and Time value.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close