Get week end date 1

[Flybridge]

I have a calendar dimension in the data warehouse that has (amongst other columns) Date and Week Number. For every record I want to add the week end date to the dimension in the OLAP cube.

I assume the parent hierarchy needs to be a hierarchy from the Calendar dimension. Does that hierarchy need to have Week Number in it? (I assume so).

After that, I'm struggling. I can't figure out how to express the MDX, even in the Expression pane.

Any help with the MDX greatly appreciated.

'The world isn't round - it's bent!' Spike Milligan

 

[RiverGuy]

Perhaps I'm missing something, but I don't see your issue. Simply add a new column to your date dimension table in your relational database, populate it with whatever format you want (could be something as plain as the date value of the Saturday encapsulating the date, or something like "Weekend of 2010-07-24"), and then add the attribute and hierarchy to your date dimension in your SSAS DB.

 

[Flybridge]

(I know my name says 'MIS' after it, but it's wrong and I can't see where to change it).

Thanks, but I'm not sure of the SQL to identify the last day of the week to get it's date and insert it against the record. Rather, the ETL tool I use has limited capabilities that way (I probably could work it out but the tool won't let me write it).

Plus, I assumed the hierarchical traversing capabilities would make it easier (!) in MDX. I would prefer to do it in the cube, given the ETL limitations I have.

'The world isn't round - it's bent!' Spike Milligan

 

[RiverGuy]

I've never used an ETL tool to populate a date dimension. A date dimension is one of the easiest tables to populate -- it doesn't depend on data from a source system in almost all cases.

If you are using SQL Server for your relational database, run the following query:

SET DATEFIRST 1
  
CREATE TABLE #dimDate
(DateKey INT,
DayOfWeekName VARCHAR(12),
WeekendName VARCHAR(50))

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @StartDate = '20100101'
SELECT @EndDate = '20101231'

WHILE @StartDate <= @EndDate 
BEGIN
	INSERT INTO #dimDate (DateKey, DayOfWeekName, WeekendName) SELECT CONVERT(VARCHAR(8), @StartDate, 112), DATENAME(DW, @StartDate) 
	,'Weekend of ' + CONVERT(VARCHAR(12), DATEADD(day, 6 - (DATEPART(dw, @StartDate)), @StartDate), 107)
	SELECT @StartDate = DATEADD(day, 1, @StartDate)
END

SELECT * FROM #dimDate 
DROP TABLE #dimDate


SET DATEFIRST 7

You can see how easy it is to get the weekend for each date. And, because each date can only have one weekend, you can easily set this up as a hierarchy in your date dimension in SSAS. So, just set this up in your relational table as you would any other date grouping (Month Name, Year, Holiday, etc.).

 

[Flybridge]

Thanks, I will give that a try.

I should have said that the calendar table comes from an ERP system which generates the dates and other attributes as per the requirements for that system. But I can modify it in the DWH using this script. I have found a way of adding user defined code in the ETL tool so I will give it a go.

'The world isn't round - it's bent!' Spike Milligan

 

[Flybridge]

FYI

The code almost worked. I noticed that the code was recognising Sunday as the last day of the week (which is what we want) but was giving Sunday the Saturday date as the week end date. So I changed the '6 - (DATEPART(dw, @StartDate)' to be '7 - (DATEPART(dw, @StartDate)'.

Also, does SQL default to Sunday as the last day of the week or is there something in yourcode that forces that? I'm just thinking about user requirements. If a customer wanted us to report using Saturday as the week end date, could I force that to happen?

'The world isn't round - it's bent!' Spike Milligan

 

[Flybridge]

Now I'm confused.

When I ran your code, after editing the '6-' bit it ran ok in SSMS with Sunday as the end of the week.

When I run the following code, which uses a date column from my calendar table, Saturday is the last day of the week.

select [Date]
,CONVERT(VARCHAR(12)
,DATEADD(day,7-(DATEPART(dw,[Date])), [Date])
, 103)
as [Week End]
, DATENAME(DW, [Date])
from dbo.Calendar

I have tried editing the '7-' calcualtion, but only '7-' comes close to what I want. I then tried to advance the output of the calculated date by 1 day, but I must be doing somwething wrong as I can't get that to work. It either gets a wrong date or complaind about data conversion errors.

Any ideas as to why it behaves differently, depending on whether it is reading a variable from your script, or a date column from my table?

'The world isn't round - it's bent!' Spike Milligan