Date Dimension future flag Update

Hi,

we currently have monthly report that shows sales for last completed month. The sql server agent job is schedule to run on first working day of the month. So when it ran yesterday it failed because the month ended in weekend.

In our date dimension table we have column futureperiod where we set value either 0 or 1. 0 = historic months and 1 = future months.

we have a sp [etl].[SetCurrentDate] where we have:

UPDATE etl.DimDate
SET FuturePeriod = 0
WHERE DateID <= @CurrentDateID;

UPDATE etl.DimDate
SET FuturePeriod = 1
WHERE DateID > @CurrentDateID;


IS there any way we alter this update statement to tell them that the previous month ended in weekend so update futureperiod accordingly.  I would really appreciate if someone can direct me.


Thanks,

Regards.

Hi,
when you say "The sql server agent job is schedule to run on first working day of the month. So when it ran yesterday it failed because the month ended in weekend" I'm not sure I see the connection - why would a job fail because a month ended on a weekend and why does that have anything to do with the job being scheduled to run on the first working day of the month?

Also, what you are doing with you Date Dimension seems to be really bad practice and should be unnecessary. Hardcoding a value into your date dimension that is dependent on when you run a query is a really bad idea - as surely it requires you to update the dimension every day and makes all reports dependent on whether this daily update has run or not? Why don't you just put the logic in any query you execute that uses this Dim?
Most BI tools allow you to define variables in their metadata such as Today, Yesterday, this week, last week, last month, etc. that your users can use. If you are not using a BI tool then you can achieve similar functionality with a view

Also, if you do need to run the update, your SQL is not very efficient. Why no.t use one update statement with a CASE expression?

Regards,

Hi Nick,

thank you for update.

   Ø  when you say "The sql server agent job is schedule to run on first working day of the month. So when it ran yesterday it failed because the month ended in weekend" I'm not sure I see the connection - why would a job fail because a month ended on a weekend and why does that have anything to do with the job being scheduled to run on the first working day of the month?


The reporting requirement is, generate a sales report for last 12 months. We don’t receive sales in the weekend therefore when the job run on 1^st working day of new month it needs to generate report until last completed month (irrespective of month ending in weekends). The reporting guys is using mdx like:


Set ListOfPeriods AS Exists(
Descendants(
{LatestYear.Item(0).Item(0).lag(2), LatestYear.Item(0).Item(0).lag(1), LatestYear},
[Date].[Year - Cycle - Month].[Month]
),[Date].[Future Period Filter].&[Past]) –this is the issue.


You are absolutely right that the issue is not related to job. We need to do something as you said using case statement.


In our DWH we have a column future period where it is flag 0 or 1. I have attached a snap shot of the table.

When the datedim is updated on Monday 02/02/2015 it updates 0 (previous month/days) in future column only until 30/01/2015 because 31/01/2015 is in weekend. 






I am not sure how to come up with the update statement that can check if previous month ends in weekend then update 0 in future column else not.






Thanks, 
Regards,

Hi - as I said, hardcoding the concept of previous and future dates into your date dim is not the way to do this. I don't know MDX but a quick search pulled up MDX examples that, with some manipulation, should achieve what you want e.g.

[TIME].[Month].CurrentMember.Lag(1) : [TIME].[Month].CurrentMember.Lag(13)

will, I assume, give you a 12 month window starting with last month - and will work without you having to hack your date dimension

Regards,