Custom build localized time dimension

As I am from Denmark I would like the time table to appear in my language, danish, or at least my customers would. That's the main reason for this article. In Analysis Services the only option for the time table generator is to generate it in English, Spanish, French or German, which of course suits most people.

When creating a localised time table, you first have to define a table in your data warehouse. This can be done by using the following script:

CREATE TABLE [dbo].[Time](
[PK_Date] [datetime] NOT NULL,
[Date_Name] [nvarchar](50) NULL,
[Year] [datetime] NULL,
[Year_Name] [nvarchar](50) NULL,
[Quarter] [datetime] NULL,
[Quarter_Name] [nvarchar](50) NULL,
[Month] [datetime] NULL,
[Month_Name] [nvarchar](50) NULL,
[Day_Of_Year] [int] NULL,
[Day_Of_Year_Name] [nvarchar](50) NULL,
[Day_Of_Quarter] [int] NULL,
[Day_Of_Quarter_Name] [nvarchar](50) NULL,
[Day_Of_Month] [int] NULL,
[Day_Of_Month_Name] [nvarchar](50) NULL,
[Month_Of_Year] [int] NULL,
[Month_Of_Year_Name] [nvarchar](50) NULL,
[Month_Of_Quarter] [int] NULL,
[Month_Of_Quarter_Name] [nvarchar](50) NULL,
[Quarter_Of_Year] [int] NULL,
[Quarter_Of_Year_Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED
(
[PK_Date] ASC
)
)

I know you could be in need of other types of date information like the week, but for now this is what I've got. Feel free to extend it. ;-)

The table consist basically of the date, month, quarter and year.
The next step is to fill in the data.
Another script is needed for this of course...

TRUNCATE TABLE [Time]
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @currDate datetime
SET @startDate = '1990-01-01'
SET @endDate = '2012-01-01'
SET @currDate = @startDate
SET LANGUAGE 'danish'
SET NOCOUNT ON

WHILE @currDate < @endDate
BEGIN
INSERT INTO [Time]
VALUES
(
@currDate,
DATENAME(weekday, @currDate)
+ ', ' + DATENAME(dd, @CurrDate) + '. ' + DATENAME(month, CurrDate) + ' ' + DATENAME(year, @CurrDate),
DATEADD(year, YEAR(@currDate) - 1900, 0),
DATENAME(year, @currDate),
DATEADD(quarter, DatePart(quarter, @currDate) -1,
DateAdd(year, Year(@currDate)- 1900,0)),
DATENAME(quarter, @currDate) + '. kvartal ' + DATENAME(year, @currDate),
DATEADD(month, DatePart(month, @currDate) -1,
DateAdd(year, Year(@currDate)- 1900,0)),
DATENAME(month, @currDate) + ' ' + DATENAME(year, @currDate),
DATEPART(dayofyear, @currDate),
'Dag ' + DATENAME(dayofyear, @currDate),
null, --to be updated
null, --to be updated
DATEPART(day, @currDate),
'Dag ' + DATENAME(day, @currDate),
DATEPART(month, @currDate),
'Måned ' + CAST(DATEPART(month, @currDate) as nvarchar(2)),
null, --to be updated
null, --to be updated
DATEPART(quarter, @currDate),
DATENAME(quarter, @currDate) + '. kvartal ')
SET @currDate = DATEADD(day, 1, @currDate)
END

UPDATE [Time] SET Day_Of_Quarter = DATEDIFF(day, Quarter, PK_Date)
UPDATE [Time] SET Day_Of_Quarter_Name = 'Dag ' +
CAST(Day_Of_Quarter AS nvarchar(2))
UPDATE [Time] SET Month_Of_Quarter = DATEDIFF(month,
Quarter, PK_Date) + 1

UPDATE [Time] SET Month_Of_Quarter_Name = 'Måned ' +
CAST(Month_Of_Quarter AS nvarchar(2))

Now the above script includes some Danish words, so of course you will now get a little lesson in Danish, although it should be almost self explaining.
"Dag" equals Day
"Måned" equals Month
"Kvartal" equals Quarter.
"År" equals Year
"Skål" equals Cheers… J
It's important to change the line:
SET LANGUAGE 'danish'
Here you switch danish with your preferred locale..

After running the script you have a time table populated with localized data. Next up is setting up the time dimension in Analysis Services using the just created time table.

First you add the time table to the data source view of your cube.
Next step is creating a new dimension using the wizard, right click the dimensions folder in the solution explorer and select "New Dimension...". The wizard then launches.
First click next at the first informative step. At the next screen shown below just click next:

The next step is to select the data source view in which you just added the time table. Then click next.
Next up you select the dimension type, which have to be Time. The screen should look like this:

Click next. You are now to define the different columns from the time table, which defines different periods of the time dimension. In the example below I've mapped Year, Quarter, Month and PK_Date to their respective fields in the time dimension. You can set up additional fields like Day of year and so on, if you need them.

Click next. The wizard then analyses the structure of the data and propose hierarchies. The proposed one is good for now. It looks like this:

Click finish.
The wizard then generates your new time dimension. It should look something like this:

To finish our work, we have to do some refinements of the dimension. First we have to show the month name instead of the number. This is done by clicking the Month-attribute in the attributes list at the left and then showing the properties for this attribute. You could do this by hitting F4. Now scroll down the bottom of the list of properties and change the NameColumn property from Month to the column Month_Name. It should look like this:

The same must be done with the quarter attribute, too.
If you look at the hierarchy its marked with the warning sign and an error message saying that attribute relationships do not exist between one or more of the levels. This can be fixed at the attribute list at the left.
The attribute relationships can be seen by expanding the nodes with the plus sign. Start by deleting the year attribute relationship at the pk_date attribute. Then you add a attribute relationship to year at the quarter attribute by dragging year towards the <new attribute relationship> underneath the quarter attribute. The same must be done with quarter at the month attribute. It should in the end look like this:

After this the warning disappears.
The last step is to localize the names of the attributes and the level names into your local language, in my case into Danish.