SSAS and Time Dimension

Question

SSAS and Time Dimension

Ben Scorer

Old Hand

Points: 355
More actions
May 17, 2006 at 4:24 am

#152979

Hi All,
I have been using sql2000 and analysis services for some time now and am moving all of my stuff to 2005.
I have a question around date columns. In Analysis 2000 dates were automatically grouped i.e. year->quarter->month->day. Is there an easy way to set this up in 2005 or do i have to build hierachies and time dimensions for these. Also when you build hierachies the year, quarter, etc.. fields are displayed on the field list along with the new hierachy - very annoying.
I have several date fields in my cubes and this seem a real hassle to setup when sql2000 did it so easily.
Thanks,
Ben

Viewing 7 posts - 1 through 6 (of 6 total)

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

Yaniv Mor SSC Eights! Points: 936 More actions · Answer 1

Hi Ben,

Dimension structure in SSAS2005 is a bit different. To create a proper time dimension, you will have to add attributes (year, quarter, month etc.) to the time dimension, then use the attributes to build a hierarchy. The attributes will be based on columns in the underlying dimension table, or you can add calculated columns using the data source view. This means that these columns will not be stored in the relational database itself, rather in the AS schema only. If you have the columns ready you can use the dimension wizard to scan the required attributes and automatically create the hierarchies.

I'm not too sure I got the "fields are displayed on the field list along with the new hierarchy..." part. You may want to explain this further.

This may look a bit more complex then AS2000, but really, this makes the dimension structure much much more versatile and "true" to dimensional modeling.

HTH,

Yaniv

Ben Scorer Old Hand Points: 355 More actions · Answer 2

Hi Yaniv,

Thanks for the reply.

I base my cubes on a single view which is created from a flatted table (fact) in the database. I put all of the information i need into the one table (i.e. codes, descriptions, etc..) These tables serve 2 purposes, our analysis team use them for reports and they also used for the cubes.

For example i have a date field (well i have many) in the cube but when i create the cube using the wizard i do not get the grouping on the date i would have expected (i.e. 2005->Q1->Jan->12). I have done like you suggested and lets say the date field in the table was called ReferralDate, i have created ReferralDateYear, ReferralDateQuarter, ReferralDateMonth and ReferralDateDay in the view. I have then created a ReferralDate hierachy and added the fields above in that order. This works fine, but on the field lists when the users view the cube they see the ReferralDateYear...Days fields along with the ReferralDate (hierachy) field. This just seems a long way round to do something AS2000 did so easily. Some of my cubes will have upto 6 date fields.

Thanks,

Ben

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Hi Ben,

have you tried adding 'Time Intelligence' to your model? SSAS can build time from either data you supply or build and populate atime dim itself (i've not tested this so can't offer an opinion on how good/bad/indifferent it is).

We typically (read always) maintain a seperate time dim table (the overhead for these types of time tables is v.minimal) with several calendars within it (fiscal, gregorian, fiscal2, etc). I haven't tested the dim wizard but would be suprised if SSAS2K5 doesn't support building a time dim from a supplied time dim table.

Cheers,

Steve.

Ben Scorer Old Hand Points: 355 More actions · Answer 4

Hi Steve,

Yes, tried building a time dim (based on the single view) but it looks like it will not point at just one date and give me the format i require (as AS2000 did), you have to map a year field, month field, etc..

Cheers,

Ben

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 5

Hi Ben,

Yeah, not a nice feature reduction, but I musta dmit it is more in line with an academic approach to building your cube/datamart (ie you shouldn't be building the time dim by scanning the fact table).

In theory, you could still do what you want, you'd just have to use functions over that single time field/column that you have to get the year, month etc.

Have you tried a 'Server Time' dimension?

Cheers,

Steve.

Ben Scorer Old Hand Points: 355 More actions · Answer 6

Hi Steve,

I have built the individual elements of the date field (.i.e. year, quarter, month and day) and then Built a date hierachy (for all 6 date fields!! - this gives an extra 30 fields in the field list that the user really has no interest in). I have not figured out how to hide a field yet, if you include it in the hierachy then it displays it. Seems a bit of a long winded way when AS2000 just did this.

Thanks for your help.

Ben