3

I am currently working on creating my first Data_warehouse using sql-server

I have a Date dimension i want to populate it using SSIS it has a field called ISO_year

can somebody tell me how do i get it ?

i tried this query ::

select year(getdate()) -- but i dont think is this ISO_year ?

And i need to know which is best practice to load dimensions into DB using ssis ?

i reffered this http://michaelmorley.name/how-to/create-date-dimension-table-in-sql-server

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • 1
    What does your 'spec' say for ISO_Year? Who built the table and gave it a column called ISO_Year? A date dimension is a big list of dates. If your Date column (unique) is called MyDate then ISO_Year is probably YEAR(MyDate). Whatever date the record is for, I guess ISO_Year is the year that it falls into. – Nick.Mc Apr 03 '14 at 06:39
  • 1
    @ElectricLlama Surprisingly, ISO-Year is not necessarly the year - as per answer. I was also surprised ;) – TomTom Apr 03 '14 at 06:55
  • Interesting to know! I figured it wouldn't be a calendar year. – Nick.Mc Apr 03 '14 at 10:59

2 Answers2

10

Here is a function for iso_year, the logic behind it is that the thursday of the week from the parameter date determine the year:

CREATE FUNCTION [dbo].[f_isoyear]
(
@p_date datetime
)
RETURNS int
as
BEGIN
  RETURN datepart(yy, dateadd(wk, datediff(d, 0, @p_date)/7, 3))
END
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • +1) i have declared 2 dates start=1/1/2000 and end=1/1/2100 but its taking hell lot of time .to process .pls help on this ! – vhadalgi Apr 03 '14 at 07:43
  • i am following this answer http://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/ and call function in insert its taking lot of time to complete ?? – vhadalgi Apr 03 '14 at 08:03
9

Here is a Connect item that requests a function to calculate ISO_YEAR.

DATEPART - ISO_YEAR for ISO_WEEK

In the workaround section you have this function that you can use.

CREATE FUNCTION [dbo].[ISOyear](@date DATETIME)
returns SMALLINT
AS
BEGIN
     DECLARE @isoyear SMALLINT = CASE
         WHEN Datepart(isowk, @date)=1
             AND Month(@date)=12 THEN Year(@date)+1
         WHEN Datepart(isowk, @date)=53
             AND Month(@date)=1 THEN Year(@date)-1
         WHEN Datepart(isowk, @date)=52
             AND Month(@date)=1 THEN Year(@date)-1             
         ELSE Year(@date)
        END;
     RETURN @isoyear;
END;
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    +1. GOOD catch. I was not even aware of the ISO-Date-functionality, though once you read it it makes a lot of sense. – TomTom Apr 03 '14 at 06:54
  • thanks... but how to use it .. i mean i am doing this `insert into dateky,ddat,iso_year values(something,getdate(),)` – vhadalgi Apr 03 '14 at 07:06
  • @user3425160 I see now that you actually have asked two questions. "And i need to know which is best practice to load dimensions into DB using ssis ?" Sorry, I can't help you with that. This answer with the code explains what a ISO_YEAR is and how it differs from the regular YEAR. – Mikael Eriksson Apr 03 '14 at 07:10
  • +1) how do i call the function in my insert statement ? – vhadalgi Apr 03 '14 at 07:10
  • You call the function like this `dbo.ISOyear(SomeDate)` where `SomeDate` is the date value you want to calculate the ISO_YEAR for. You don't actually need the function. You can just use the case expression directly in the insert statement. – Mikael Eriksson Apr 03 '14 at 07:13
  • i have declared 2 dates start=1/1/2000 and end=1/1/2100 but its taking hell lot of time .to process .pls help on this ! – vhadalgi Apr 03 '14 at 07:42
  • You shouldn't be loading your date dimension table with GETDATE(). Go find some info on calendar tables and read some more. – Nick.Mc Apr 03 '14 at 10:57
  • I was so sure my answer would be picked. Oh well I guess readability beats performance. – t-clausen.dk Apr 03 '14 at 19:11
  • @t-clausen SO is indeed strange sometimes. Have a look at http://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column The accepted answer was dead wrong until the edit and that is totally over complicated. And it has 24 up votes. – Mikael Eriksson Apr 03 '14 at 19:55