I get my calendar periods from a legacy system extract in the following form:

200402 (which is for Feb 2004)
200301 (which is for Jan 2003)

the above values are all int(integer).

Please help how I can get a Year, Quarter, Month from this period.

Thanks
Karen

I always recommend that users create a standard stand-alone time dimension
table (in the RDBMS). In this table, you would create a member key in the
format you've specified, as an integer in the format YYYYMM and then you
can specify whatever year/quarter/month/day you wish. Process your time
dimension against this table.

This has many advantages over building a time dimension off of a datetime
stamp in the fact table. First, dimension processing is done much faster
since you have a dedicated table. Second, you can build time as a shared
dimension allowing you to coorelate the same "day" between cubes in a
virtual cube. Third, it is easy to represent time members which may be
missing in the data, e.g. weekends, holidays, problems with the feeds, etc.
By having an independent time dimension, you can easily extend it with
member properties not normally seen in just a datetime stamp, e.g. whether
or not a day is in the "Christmas" season; or similar peak selling periods,
whether or not it is the weekend or company holiday. etc.

You will be much happier if you take a few minutes, design a simple table
and populate it for the next 5 years.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Karen Middleton" <karenmiddleol (AT) yahoo (DOT) com> wrote

"Dave Wickert [MSFT]" wrote:
Dave,

This is a great idea. I did this, but I got lots of questions from my users
about why they could select periods of time in the far future. So I layered
a VIEW on top of my date table that includes

WHERE (date_to_hour <=
(SELECT dateadd(d, 30, MAX(reg_date))
FROM dbo.Purchase_3)) AND
(date_to_hour >=
(SELECT MIN(reg_date)
FROM dbo.Purchase_3))

Regards,

Jonathan