time dimension from a single date table?

[hoohay]

is it possible to construct attributes for a time dimension (year, month, day) out of a single date table that contains a transaction date field only? or do i NEED to create lookup tables for year, month, day, etc.

 

[User14]

I don't think you can. It is preferrable to create lookup tables for year, month, day and if necessary for the transformations also.

 

[hoohay]

Thanks for the reply User14. A few related questions....

Could I, for example, for quarterly data, create filters that have 'date between 01/01/02 and 03/31/02' for 'Qtr 1 2002' and use it on reports or in metrics?

How would this limit our reporting?

Would this lead to poor processing?

Thanks in advance,

hoohay

 

[nlim]

actually you probably can get away with using a date field for your time dimensions.

year id = year(date)
month id = year(date)*100 + month(date,MM)
month desc = some function to cast the month in text
quarter id = year(date)*100 + int(Month(date)/3) assuming int function rounds up.

that way july 8 2003 will become
year=2003
quarter=20033
month=200307

it's probably just as easy to create a view off the date table...no real limitations in the tool...

 

[OV]

Yes you can create all the filters that you mentioned.
The speed will depend on the amount of data that you are querying and the way your database is indexed. If have multiple years of data and no indexing on that date field, your report may take forever.
On my databases, I typically build my clustered indexes(SQL server) on some transaction date fileds that is commonly used in filtering. Typically, that is also the date filed on which I base my nightly ETL

 

[reisw]

I thing to remember is that if you create any aggregate tables at say a month level you need to have a lookup table with distinct values otherwise you will get lots of double counting. Microstrategy needs lookup tables at any point where you create an aggregate table.

 

[nlim]

more specifically, MSTR needs lookup tables with distinct rows for aggregate tables. So if you have a year aggregate table, you need a year lookup table with 1 row for 2002, 1 row for 2003 etc...

 

[hoohay]

thank you all for your responses! great help!

Nlim,

As in your first response, i have created the year, quarter, and month attributes using the date functions, but when assigning parent / child relationships, there aren't any links between the attributes. Will creating a hierarchy for the time dimension be the best way to provide drilling functionality or is there some other way I can link these attributes?

hoohay

 

[nlim]

you just need to specify that year is a parent of month, and so forth. You can do it in the attribute editor. Then you will have a time hierarchy automatically.