Server Time Dimension issues

Last Post 09 Jun 2006 08:04 AM by chrisgoddard. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

18 May 2006 02:40 PM
Here is a scenario that mirrors the type of thing I am trying to accomplish:

I have a date field called 'OrderDate' in the Orders table. I set up a Server Time Dimension for it (e.g. 'Orders - Order Date'). So I now have an Orders dimension and a 'Orders - Order Date' dimension in a regular relationship.

In the Orders table I have a foreign key relationship with a Product table through ProductID. So I set up a dimension called Product with a regular relationship with the Orders dimension. I then want to build a relationship such that I can break down Products (and product costs etc.) by Order Date.

So how do I relate the 'Orders - Order Date' dimension to the Products dimension and measures? I cannot find any way to do this, whether using a reference dimension or otherwise. If OrderDate were a varchar column in the Orders table then I could just include it as an attribute for the Orders dimension and it would automatically be in a regular relationship to the Products dimension, but because the values are broken out in their own dimension this is clearly not possible. What am I missing here?


New Member
New Member

09 Jun 2006 08:04 AM
This is where Analysis Services date attributes are incompatible with SQL DATETIME data type. You will need to create a calculated field on your Orders table view inside Analysis services and use CONVERT to convert your real date to a date Analysis services will understand when linking to your date dimension.

Example calculated field.

CONVERT(DATETIME, datename(dd, orderdate) + '/' + datename(mm, orderdate) + '/' + datename(yy, orderdate), 3)

You should then be able to link your time dimension via the new calculated field

Acceptable Use Policy