0

I am doing my first ssis and sql project for uni being new to both. I am creating a date dimension but only month and year no days. I have seen loads of examples online for date dim but including days. Is there a way to create monthly date dim as deadline is approaching and still a lot to do. All help much appreciated.

enter image description here

Cheers

Aidan

  • Can you explain what you will be doing with the data in this dimension? How do you plan to represent just a month and year? What other attributes do you need to expose? – Aaron Bertrand Apr 16 at 23:40
0

Without more specific requirements, this is probably where I'd start:

CREATE TABLE dbo.MonthDimension
(
  StartDate date NOT NULL,
  EndDate   AS (CONVERT(date,     DATEADD(DAY,-1,DATEADD(MONTH,1,StartDate))))
               PERSISTED,
  NumDays   AS (CONVERT(tinyint,  DATEDIFF(DAY,StartDate,DATEADD(MONTH,1,StartDate)))),
  Y         AS (CONVERT(smallint, DATEPART(YEAR,StartDate))),
  M         AS (CONVERT(tinyint,  DATEPART(MONTH,StartDate))),
  Q         AS (CONVERT(tinyint,  DATEPART(QUARTER,StartDate))),
  MonthName AS (CONVERT(varchar(20), DATENAME(MONTH,StartDate))),
  CONSTRAINT PK_MonthDimension PRIMARY KEY (StartDate, EndDate)
);

Then to populate it, just substitute your desired start and end dates:

DECLARE @start date = '20000101', 
        @end   date = '20991231';

;WITH x(n) AS 
(
  SELECT 0 UNION ALL 
  SELECT n+1 FROM x 
  WHERE n < DATEDIFF(MONTH, @start, @end)
)
INSERT dbo.MonthDimension(StartDate) 
  SELECT DATEADD(MONTH,n,@start) FROM x
  OPTION (MAXRECURSION 32767);

Partial table data:

StartDate   EndDate     NumDays  Y      M   Q   MonthName
----------  ----------  -------  ----   --  --  ---------
2000-01-01  2000-01-31  31       2000   1   1   January
2000-02-01  2000-02-29  29       2000   2   1   February
2000-03-01  2000-03-31  31       2000   3   1   March
2000-04-01  2000-04-30  30       2000   4   2   April
2000-05-01  2000-05-31  31       2000   5   2   May
2000-06-01  2000-06-30  30       2000   6   2   June
2000-07-01  2000-07-31  31       2000   7   3   July
2000-08-01  2000-08-31  31       2000   8   3   August
2000-09-01  2000-09-30  30       2000   9   3   September
2000-10-01  2000-10-31  31       2000   10  4   October
2000-11-01  2000-11-30  30       2000   11  4   November
2000-12-01  2000-12-31  31       2000   12  4   December
2001-01-01  2001-01-31  31       2001   1   1   January
2001-02-01  2001-02-28  28       2001   2   1   February
2001-03-01  2001-03-31  31       2001   3   1   March

... 1,170 rows in the middle ...

2098-10-01  2098-10-31  31       2098   10  4   October
2098-11-01  2098-11-30  30       2098   11  4   November
2098-12-01  2098-12-31  31       2098   12  4   December
2099-01-01  2099-01-31  31       2099   1   1   January
2099-02-01  2099-02-28  28       2099   2   1   February
2099-03-01  2099-03-31  31       2099   3   1   March
2099-04-01  2099-04-30  30       2099   4   2   April
2099-05-01  2099-05-31  31       2099   5   2   May
2099-06-01  2099-06-30  30       2099   6   2   June
2099-07-01  2099-07-31  31       2099   7   3   July
2099-08-01  2099-08-31  31       2099   8   3   August
2099-09-01  2099-09-30  30       2099   9   3   September
2099-10-01  2099-10-31  31       2099   10  4   October
2099-11-01  2099-11-30  30       2099   11  4   November
2099-12-01  2099-12-31  31       2099   12  4   December
  • hi aaron thanks for your help. I have 3 datasets taken from the oecd that look like above that i posted in my original post. i have set up a country dim, a dim for measurement indicators and i wanted to set up a date dim as my data covers 6 years with the goal of loading a fact table. As my date is year and month i thought i wouldnt need startdate enddate and numdays as per your example. cheers aidan – Browner55 Apr 17 at 6:59
  • I got a solution i used aaron code to poulate a staging table for date then populated my date dim from that as we are not allowed use data unless it has been downloaded from kaggle etc – Browner55 Apr 17 at 9:41
  • @Browner55 I didn't have all of your requirements, what data you needed and how you would query it, you certainly don't need all of the columns I presented, they're just the ones that are most commonly useful in general. Also "not allowed to use data" sounds like a strange limitation, especially since this is code that helps you generate data. – Aaron Bertrand Apr 17 at 11:12
  • hi arron. no worries a good lesson for future post i make on posting as much information as possible. What i meant from "not allowed to use data" is that not allowed to populate the dates in an excel sheet and then load to ssis. Sorry its been a long week trying to get ssis to work properly. thanks again for your help – Browner55 Apr 18 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.