INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Get week end date

Get week end date

(OP)
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

RE: Get week end date

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.

RE: Get week end date

(OP)
(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

RE: Get week end date

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:

CODE

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.).  
 

RE: Get week end date

(OP)
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

RE: Get week end date

(OP)
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

RE: Get week end date

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close