Post reply

Year -> Quarter -> Month -> Day of Week hierarchy in AS 2005

  • November 30, 2006 at 10:49 pm

    #170512

    Hi,

    I have a column of smalldatetime type.  Is there a way to build [Year -> Quarter -> Month -> Day of Week] hierarchy straight out of it?  I was trying to do it with wizard and manually, but it turned not as easy as in AS 2000.

    Thanks.

  • December 2, 2006 at 9:54 pm

    #675362

    Looks like you can do it by manually adding calculated columns to the table within the DSV (e.g a field called 'Year' with a function of 'Year(Your_column)') but if you were to do this, why not do it in the/a view?  I guess if you didn't have access/control over the source system this would be a good workaround.  Not quite sure why the Add Business Intelligence (dimension intelligence) doesn't fo this for you.  You could, of course, use the Add BI to create a server based time dimension (ie no source required).=

    Maybe we're both missing something?

     

     

    Steve.

  • December 3, 2006 at 8:14 pm

    #675387

    Putting some calculated columns or building a view could be a solution, but I was looking into using just one column for the entire hierarchy.  In AS 2000 I was doing this using datepart function with the same DateTime column but with different parameters on different levels (e.g. yy, dd, etc.)  It looks like this does not work in AS 2005.  As for using BI wizard and choosing the Time Intelligence, this does not look exactly the same to me, cause it requires existing time dimension and allows year to date, month to date, etc calculation.  All I want is a basic time dimension, but it looks like I have to have separate column in my datasource for each level of the hierarchy in AS 2005.

  • December 3, 2006 at 8:20 pm

    #675388

    You can still use the same function (ie the tsql DATEPART function) but you create the fields in the table within the data source view rather than within the Attributes themselves in the dimension.  This is basically the same as what you did in AS2K just in a slightly different location.  The fields still don't physically exist just like in AS2K it's just now you have to declare them in the DSV.

     

    Steve.

  • December 3, 2006 at 8:39 pm

    #675391

    Ahhh...  Sorry, missed the word DSV!  Great!  This should work for me.  Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply