In our implementation, starts week 1 of 2008.
#How to break overlap dimension progecad code
I just had to implement this functionality in our DW/BI environment and also added a WeekOfYear column to the date dimension, though my code to po***te it was a little different. As a result you occasionally wind up with a week that includes days that are in the next month (and possibly the next year). I don't remember the specifics of the project where I did this but it was something like the week always begins on Saturday and all days in the week belong to that week even if they cross into the next month or even the next year. What if you need the Week to run from Sat to Fri? What would need to be altered to accommodate for that? Populate the ***ulativeWeek column in the date dimension: DECLARE DATETIME SELECT = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Sunday' ĭECLARE DATETIME SELECT = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Saturday' Here is the first part of the script at 4. The logic used to assign the ***ulative week number is to get the minimum date for the first day of the week e.g. Monday, Febru11:28:06 AM - Raymond Barley Which is quite fine and acceptable way to number weeks to me. This way I don't have 201501 week in my system, and weeks order would be 201453, 201502.
#How to break overlap dimension progecad full
To explain this solution: I intended to change first week of year if it didn't have 7 days, I changed it to be the same as last week of previous year.įor example my weeks number would be as follows 201501, and since week 201501 wasn't full seven days, then I change it to 201453. I need to have a uinque week number in system, so I updated the table as follows: Now this solution has the problem of weeks spanning over two years. I saved the week number as follows, assuming I have and values: I think having week number in year is good to keep. I guess it's a bit late to comment here, but I have another approach. Let's walk through the steps to implement the Cumulative Week column. After determining the week number of a particular date, we can use a simple BETWEEN in our WHERE clause to filter the date dimension on a range of weeks and join to the relevant fact table. We will also create a function that will return the week number for any given date. every day in a Sunday through Saturday range is assigned the same number then increment the number on each Sunday. This column will be assigned a sequential number representing the week i.e. There are probably a number of possible solutions but the one that comes to mind is to add a cumulative week number column to the date dimension. Can your think of an easy way to implement this type of functionality where we can determine a week number then simply add or subtract a number of weeks to come up with our range of weeks? An example query would be how many employees were hired in each of the previous four weeks? While SQL Server has the DATEPART function which has an option to give the week number in the year for any given date, the week number resets to 1 when the year changes. They define a week as beginning on Sunday and ending on Saturday without regard to the year. Our business users want to report on some fact data aggregated by week. By: Ray Barley | Updated: | Comments (11) | Related: > Analysis Services Dimensions