go backarticles

Articles of SQLschool.gr Team

Calendar Dimension table for BI projects

Antonios Chatzipavlis

Overview

Η χρήση ενός dimension table που περιέχει ημερομηνίες για την ανάλυση δεδομένων είναι ίσως το σημαντικότερο συστατικό. Ένα σωστά δομημένο calendar dimension table δίνει περισσότερες δυνατότητες ανάλυσης. Στο internet κυκλοφορούν αρκετές τέτοιες υλοποιήσεις που προσφέρουν ικανοποιητικό αποτέλεσμα.

Αρκετοί με έχουν ρωτήσει πολλές φορές για το πως πρέπει να είναι δομημένο ένα calendar dimension table. Δεν υπάρχει ξεκάθαρη απάντηση στο ερώτημα αυτό καθώς υπάρχει ο παράγοντας των αναγκών ανάλυσης.

Η προσωπική μου άποψη είναι ότι πρέπει να υπάρχουν τρία βασικά στοιχεία μέσα σε αυτό ώστε να μπορεί να καλύψει ένα μεγάλο μέρος των αναγκών της ανάλυσης των δεδομένων. Τα τρία αυτά στοιχεία που πρέπει να υπάρχουν είναι το Calendar Year, το Fiscal Year και το ISO Year.

Το ευκολότερο στην κατανόηση αλλά και την υλοποίηση είναι το Calendar Year καθώς είναι αυτό που όλοι γνωρίζουμε σαν έτος (1/1/yyyy – 31/12/yyyy).

Τα πράγματα γίνονται λίγο περίπλοκα όταν πρόκειται για τα Fiscal & ISO Years.

Εδώ υπάρχουν αρκετοί κανόνες που πρέπει να υλοποιηθούν σε αυτά, ειδικά για το ISO year.

Για το Fiscal Year δεν υπάρχει κάποιο συμφωνημένο παγκόσμιο πρότυπο, καθώς σχεδόν κάθε χώρα, έχει διαφορετικούς κανόνες για την χρονική περίοδο του.

Τέλος υπάρχουν κανόνες που αφορούν την πρώτη ημέρα της εβδομάδας (Κυριακή ή Δευτέρα) και το πόσες εβδομάδες θα πρέπει να έχει ένα τρίμηνο και πως αυτές θα είναι κατανεμημένες στους μήνες του (4-4-5, 4-5-4, 5-4-4).

Μπορείτε να δείτε για όλα αυτά στα παρακάτω links

Με όλα αυτά σαν βάση θα πρέπει να δημιουργηθεί ένα calendar dimension table.

Calendar Dimension Table

Ο καθένας μας έχει υλοποιήσει την δικιά του εκδοχή για τα περιεχόμενα που θέλει να έχει το Calendar dimension table του. Παρακάτω είναι ένα μέρος της δικιάς μου εκδοχής, που περιέχει τα βασικά στοιχεία που επιθυμώ να υπάρχουν σε αυτό.

Calendar Table - SQL Script

CREATE TABLE dbo.DimCalendar(
    DateKey                 int             NOT NULL,
    DateValue               date            NOT NULL,
    MonthNameNational       nvarchar(15)    NOT NULL,
    DayNameNational         nvarchar(15)    NOT NULL,
    MonthNameLocal          nvarchar(15)    NOT NULL,
    DayNameLocal            nvarchar(15)    NOT NULL,
    IsWeekend               bit             NOT NULL,
    IsLeapYear              bit             NOT NULL,
    CYYear                  int             NOT NULL,
    CYYearName              nvarchar(15)    NOT NULL,
    CYSemester              int             NOT NULL,
    CYSemesterName          nvarchar(15)    NOT NULL,
    CYQuarter               int             NOT NULL,
    CYQuarterName           nvarchar(15)    NOT NULL,
    CYSemesterQuarter       int             NOT NULL,
    CYMonth                 int             NOT NULL,
    CYMonthName             nvarchar(15)    NOT NULL,
    CYSemesterMonth         int             NOT NULL,
    CYQuarterMonth          int             NOT NULL,
    CYWeek                  int             NOT NULL,
    CYWeekName              nvarchar(15)    NOT NULL,
    CYSemesterWeek          int             NOT NULL,
    CYQuarterWeek           int             NOT NULL,
    CYMonthWeek             int             NOT NULL,
    CYDay                   int             NOT NULL,
    CYSemesterDay           int             NOT NULL,
    CYQuarterDay            int             NOT NULL,
    CYMonthDay              int             NOT NULL,
    CYWeekDay               int             NOT NULL,
    CYFirstDate             date            NOT NULL,
    CYLastDate              date            NOT NULL,
    FYYear                  int             NOT NULL,
    FYYearName              nvarchar(15)    NOT NULL,
    FYSemester              int             NOT NULL,
    FYSemesterName          nvarchar(15)    NOT NULL,
    FYQuarter               int             NOT NULL,
    FYSemesterQuarter       int             NOT NULL,
    FYMonth                 int             NOT NULL,
    FYMonthName             nvarchar(15)    NOT NULL,
    FYSemesterMonth         int             NOT NULL,
    FYQuarterMonth          int             NOT NULL,
    FYWeek                  int             NOT NULL,
    FYWeekName              nvarchar(15)    NOT NULL,
    FYSemesterWeek          int             NOT NULL,
    FYQuarterWeek           int             NOT NULL,
    FYMonthWeek             int             NOT NULL,
    FYDay                   int             NOT NULL,
    FYSemesterDay           int             NOT NULL,
    FYQuarterDay            int             NOT NULL,
    FYMonthDay              int             NOT NULL,
    FYWeekDay               int             NOT NULL,
    FYFirstDate             date            NOT NULL,
    FYLastDate              date            NOT NULL,
    ISOYear                 int             NOT NULL,
    ISOYearName             nvarchar(15)    NOT NULL,
    ISOSemester             int             NOT NULL,
    ISOSemesterName         nvarchar(15)    NOT NULL,
    ISOQuarter              int             NOT NULL,
    ISOQuarterName          nvarchar(15)    NOT NULL,
    ISOSemesterQuarter      int             NOT NULL,
    ISOMonth                int             NOT NULL,
    ISOMonthName            nvarchar(15)    NOT NULL,
    ISOSemesterMonth        int             NOT NULL,
    ISOQuarterMonth         int             NOT NULL,
    ISOWeek                 int             NOT NULL,
    ISOWeekName             nvarchar(15)    NOT NULL,
    ISOSemesterWeek         int             NOT NULL,
    ISOQuarterWeek          int             NOT NULL,
    ISOMonthWeek            int             NOT NULL,
    ISODay                  int             NOT NULL,
    ISOSemesterDay          int             NOT NULL,
    ISOQuarterDay           int             NOT NULL,
    ISOMonthDay             int             NOT NULL,
    ISOWeekDay              int             NOT NULL,
    ISODayName              nvarchar(15)    NOT NULL,
    ISOFirstDate            date            NOT NULL,
    ISOLastDate             date            NOT NULL,
    IsNationalHoliday       bit             NOT NULL,
    IsLocalHoliday          bit             NOT NULL,
    HolidayName             nvarchar(100)   NULL
) 
GO

ALTER TABLE dbo.DimCalendar
ADD CONSTRAINT PK_DimCalendar PRIMARY KEY CLUSTERED (DateKey)
GO

Generate Calendar Rows

Για να γεμίσει κάποιος το Calendar dimension table υπάρχουν αρκετοί τρόποι. Προσωπικά δεν μου αρέσουν καθόλου οι τρόποι που χρησιμοποιούν while loops καθώς χρειάζεται περισσότερο χρόνος στην υλοποίηση τους αλλά και στην εκτέλεση τους. Θεωρώ ότι με απλά set operations αυτό μπορεί να γίνει με ευκολία.

Στο παρακάτω script η υλοποίηση είναι με set operations και περιλαμβάνει και τις βασικές αργίες που έχουμε στην Ελλάδα, τις ΗΠΑ και τις υπηρεσίες της ΕΕ. Μπορείτε να προσθέσετε και άλλων χωρών. Ακόμα περιέχει και τους αλγόριθμους εύρεσης του Ορθόδοξου Πάσχα και του Καθολικού Πάσχα που είναι σημαντικό ορόσημο για την εύρεση των αργιών με κινητές ημερομηνίες.

Πριν την εκτέλεση του όμως θα πρέπει να ορίζεται κάποιες τιμές σε παραμέτρους που υπάρχουν σύμφωνα με τις ανάγκες σας, αυτές είναι:


Script Parameters
Parameter Usage
SET DATEFIRST Ορίζουμε την πρώτη ημέρα της εβδομάδας. Έχει ισχύει για στο Calendar & Fiscal year καθώς το ISO year ξεκινάει από Δευτέρα. Το 1 είναι Δευτέρα μέχρι το 7 που είναι Κυριακή
SET LANGUAGE US_ENGLISH Ορίζουμε την γλώσσα που θα χρησιμοποιηθεί. Καλό είναι να μην το αλλάξετε.
SET DATEFORMAT YMD Ορίζουμε τo format των ημερομηνίων. Καλό είναι να μην το αλλάξετε.
@startdate Ορίζουμε την ημερομηνία έναρξης που θέλουμε να έχουμε στο Calendar dimension table.
@numberofyears Ορίζουμε τον αριθμό των ετών που θέλουμε να έχουμε στο Calendar dimension table.
@ISOQuarterType Ορίζουμε τον τύπο κατανομής των τριμήνων στο ISO Year. Οι τιμές που μπορεί να πάρει είναι 445, 544, 454.
@FiscalYearStartMonth Ορίζουμε το μήνα που θέλουμε να ξεκινάει το Fiscal year.
@FiscalYearStartMonthDay Ορίζουμε την ημέρα που θέλουμε να ξεκινάει το Fiscal year.
@FiscalYearEndMonth Ορίζουμε τον μήνα που θέλουμε να τελειώνει το Fiscal year.
@FiscalYearEndMonthDay Ορίζουμε την ημέρα που θέλουμε να τελειώνει το Fiscal year.
@FiscalYear Ορίζουμε την έτος που θέλουμε να έχουμε στο Fiscal year. Για παράδειγμα εάν έχουμε Fiscal Year από 1/7/2019 - 30/6/2020 αν βάλουμε τη τιμή ένα τότε αυτό θα είναι "FY 2019" αν βάλουμε τη τιμή 2 θα είναι "FY 2020". Το σύνηθες είναι να είναι "FY 2020" για αυτό και η αρχική τιμή είναι 2.
@NationalCulture Ορίζουμε τη γλώσσα που τα πεδία που περιέχουν τα λεκτικά των ημερών και μηνών θα έχουν σε μια διεθνή γλώσσα. Για αυτό και έχει σαν αρχική τιμή τα αγγλικά.
@LocalCulture Ορίζουμε τη γλώσσα που τα πεδία που περιέχουν τα λεκτικά των ημερών και μηνών στην τοπική γλώσσα. Επειδή είμαστε στην Ελλάδα έχει επιλεχθεί η ελληνική γλώσσα σαν αρχική τιμή.

ATTENTION
Πριν την εκτέλεση του παρακάτω script θα πρέπει να έχετε εκτελέσει το παραπάνω script που δημιουργεί το Calendar dimension table στην βάση που θέλετε και να εκτελέσετε αυτό στην ίδια βάση.

NOTE
Όλα τα scripts του άρθρου αυτού μπορείτε να τα βρείτε και στο GitHub account μου στο link https://github.com/antoniosch/Calendar-Dimension

Generate Calendar Rows - SQL Script

-- DROP TEMP TABLES
DROP TABLE IF EXISTS tempdb..#T;
DROP TABLE IF EXISTS tempdb..#TT;

-- SET PREFFERED SETTINGS
SET DATEFIRST 7 -- 1=MONDAY , 7 = SUNDAY;
SET LANGUAGE US_ENGLISH;
SET DATEFORMAT YMD;

-- SET PERIOD
DECLARE @startdate DATE                 = '19800101';
DECLARE @numberofyears INT              = 100;
DECLARE @ISOQuarterType INT             = 445;   -- 445 = 4-4-5 , 544 = 5-4-4, 454 = 4-5-4
DECLARE @FiscalYearStartMonth INT       = 7;
DECLARE @FiscalYearStartMonthDay INT    = 1;
DECLARE @FiscalYearEndMonth INT         = 6;
DECLARE @FiscalYearEndMonthDay INT      = 30;
DECLARE @FiscalYear INT                 = 2;  -- 1 = Starting Year , 2 = Ending Year
DECLARE @NationalCulture NVARCHAR(20)   ='en-US';
DECLARE @LocalCulture NVARCHAR(20)      ='el-GR';

DECLARE @enddate DATE                   = DATEADD(YEAR, @numberofyears, @startdate);
DECLARE @cstartdate DATE;
IF ( @FiscalYearStartMonth > 1 ) 
    SET @cstartdate = DATEADD(YEAR, -2, @startdate);
ELSE
     SET @cstartdate = @startdate;

WITH r1
AS
(
    -- GENERATE DATA SET ROWS
    SELECT  TOP (DATEDIFF(DAY, @cstartdate, @enddate))
            ROW_NUMBER() OVER (ORDER BY s1.[object_id]) AS rn
    FROM sys.all_objects AS s1
    CROSS JOIN    sys.all_objects AS s2
    ORDER BY s1.[object_id]
), r2
AS
(
        -- GENERATE DATES OF CALENDAR
        SELECT 
                DateKey     = CONVERT(CHAR(8),DATEADD(DAY,rn-1,@cstartdate),112),
                DateValue   = DATEADD(DAY,rn-1,@cstartdate)
        FROM r1
), r3
as 
(
    SELECT 
            *, 
            MonthNameNational     = UPPER(FORMAT( DateValue, 'MMM', @NationalCulture )),
            MonthNameLocal        = UPPER(FORMAT( DateValue, 'MMM', @LocalCulture )),
            DayNameNational       = UPPER(FORMAT( DateValue, 'ddd', @NationalCulture )),
            DayNameLocal          = UPPER(FORMAT( DateValue, 'ddd', @LocalCulture )),
            IsWeekend             = CASE  WHEN DATEPART(weekday, DateValue) IN (DATEPART(weekday, '19000106'), -- Saturday
                                                                            DATEPART(weekday, '19000107')) -- Sunday
                                            THEN CAST(1 AS bit)
                                        ELSE CAST(0 AS bit)
                                    END,
            IsLeapYear            = CASE     DAY(DATEADD(day, -1, DATEFROMPARTS(YEAR(DateValue),3,1)))
                                    WHEN 29 THEN CAST(1 AS bit)
                                    ELSE CAST(0 AS bit)
                                    END,
            CYYear                = DATEPART(YEAR,DateValue),
            CYDay                 = DATEPART(DAYOFYEAR,DateValue),
            CYSemester            = ((DATEPART(QUARTER,DateValue)-1)/2)+1,
            CYQuarter             = DATEPART(QUARTER,DateValue),
            CYMonth               = DATEPART(MONTH,DateValue),
            CYWeek                = DATEPART(WEEK,DateValue),
            CYMonthDay            = DATEPART(DAY,DateValue),

            ISOYear               = YEAR(DATEADD(DAY,26-DATEPART(ISO_WEEK,DateValue),DateValue)),
            ISOWeek               = DATEPART(ISO_WEEK,DateValue),
        
            
            ISOMonth              = CASE @ISOQuarterType
                                    WHEN 445 THEN
                                        CASE
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 1 AND 4 THEN 1 
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 5 AND 8 THEN 2
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 9 AND 13 THEN 3

                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 14 AND 17 THEN 4
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 18 AND 21 THEN 5
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 22 AND 26 THEN 6
                                            
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 27 AND 30 THEN 7
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 31 AND 34 THEN 8
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 35 AND 39 THEN 9
                                            
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 40 AND 43 THEN 10
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 44 AND 47 THEN 11
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 48 AND 53 THEN 12
                                        END
                                    WHEN 544 THEN 
                                        CASE
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 1 AND 5 THEN 1 
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 6 AND 9 THEN 2
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 10 AND 13 THEN 3

                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 14 AND 18 THEN 4
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 19 AND 22 THEN 5
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 23 AND 26 THEN 6
                                            
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 27 AND 31 THEN 7
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 32 AND 35 THEN 8
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 36 AND 39 THEN 9
                                            
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 40 AND 44 THEN 10
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 45 AND 48 THEN 11
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 49 AND 53 THEN 12
                                        END

                                    WHEN 454 THEN 
                                        CASE
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 1 AND 4 THEN 1 
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 5 AND 9 THEN 2
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 10 AND 13 THEN 3

                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 14 AND 17 THEN 4
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 18 AND 22 THEN 5
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 23 AND 26 THEN 6
                                            
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 27 AND 30 THEN 7
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 31 AND 35 THEN 8
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 36 AND 39 THEN 9
                                            
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 40 AND 43 THEN 10
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 44 AND 48 THEN 11
                                            WHEN DATEPART(ISO_WEEK,DateValue) BETWEEN 49 AND 53 THEN 12
                                        END
                                  END  
    FROM r2
), r4
AS
(
    SELECT  * ,
            ISODay          = ROW_NUMBER() OVER ( PARTITION BY ISOYear ORDER BY ISOMonth ),
            ISOSemester     = IIF ( ISOMonth <=6 ,1,2),
            ISOQuarter      = CASE 
                                WHEN ISOMonth BETWEEN 1 AND 3 THEN 1
                                WHEN ISOMonth BETWEEN 4 AND 6 THEN 2
                                WHEN ISOMonth BETWEEN 7 AND 9 THEN 3
                                WHEN ISOMonth BETWEEN 10 AND 12 THEN 4
                              END,
            FYFirstDate     = CAST ('19000101' AS DATE),
            FYLastDate      = CAST ('19000101' AS DATE),
            FYYear          = CAST (1900 AS INT)
    FROM r3
)
SELECT *
INTO #T
FROM    r4;

WITH fy1
AS
(
        SELECT DISTINCT CYYear,
            FiscalYearStartDate = DATEADD(YEAR , IIF(@FiscalYearStartMonth>1,-1,0) ,DATEFROMPARTS(CYYear,@FiscalYearStartMonth,@FiscalYearStartMonthDay)),
            FiscalYearEndDate = DATEFROMPARTS(CYYear,@FiscalYearEndMonth,@FiscalYearEndMonthDay)
        FROM #T
    UNION
        SELECT MIN (CYYear) - 1,
            DATEADD(YEAR , IIF(@FiscalYearStartMonth>1,-1,0) ,DATEFROMPARTS(MIN (CYYear) - 1,@FiscalYearStartMonth,@FiscalYearStartMonthDay)),
            DATEFROMPARTS(MIN (CYYear) - 1,@FiscalYearEndMonth,@FiscalYearEndMonthDay)
        FROM #T
    UNION
        SELECT MAX (CYYear) + 1,
            DATEADD(YEAR ,IIF(@FiscalYearStartMonth>1,-1,0) ,DATEFROMPARTS(MAX (CYYear) + 1,@FiscalYearStartMonth,@FiscalYearStartMonthDay)),
            DATEFROMPARTS(MAX (CYYear) + 1,@FiscalYearEndMonth,@FiscalYearEndMonthDay)
        FROM #T
)
UPDATE T
SET FYFirstDate   =    (SELECT fy1.FiscalYearStartDate FROM fy1 WHERE    T.DateValue BETWEEN fy1.FiscalYearStartDate AND fy1.FiscalYearEndDate),
    FYLastDate    =    (SELECT fy1.FiscalYearEndDate FROM fy1 WHERE    T.DateValue BETWEEN fy1.FiscalYearStartDate AND fy1.FiscalYearEndDate)
FROM #T AS T;

UPDATE #T
SET FYYear = DATEPART(YEAR,CHOOSE(@FiscalYear,FYFirstDate,FYLastDate));



WITH r1
AS
(
    SELECT  * ,
            FYDay       = ROW_NUMBER() OVER (PARTITION BY FYYear ORDER BY DateValue),
            FYMonth     = DATEPART(MONTH, DATEADD(MONTH, 13-@FiscalYearStartMonth, DateValue)),
            FYSemester  = ((DATEPART(QUARTER,DATEADD(MONTH, 13-@FiscalYearStartMonth, DateValue))-1)/2)+1,
            FYQuarter   = DATEPART(QUARTER, DATEADD(MONTH, 13-@FiscalYearStartMonth, DateValue)),
            FYWeek      = IIF ( DATEDIFF(WEEK,FYFirstDate,FYLastDate) - DATEDIFF(WEEK,DateValue,FYLastDate) = 0 , 1, DATEDIFF(WEEK,FYFirstDate,FYLastDate) - DATEDIFF(WEEK,DateValue,FYLastDate) )
    FROM #T
), r2
AS
(
    SELECT  ISOYear,
            ISOFirstDate  = MIN(DateValue),
            ISOLastDate   = MAX(DateValue)
    FROM r1
    GROUP by ISOYear
), r3
AS
(
    SELECT  
            -- Key Fields
            DateKey,
            DateValue,

            -- Common Fields

            MonthNameNational,
            DayNameNational,
            MonthNameLocal,
            DayNameLocal,
            IsWeekend,
            IsLeapYear,

            -- Calnedar Year Fields

            CYYear,
            CYYearName           = CONCAT('CY ', CYYear ),
            CYSemester,
            CYSemesterName       = CONCAT('CY ', CYYear, ' H0', CYSemester ),
            CYQuarter,
            CYQuarterName        = CONCAT('CY ', CYYear, ' Q0', CYSemester ),
            CYSemesterQuarter    = DENSE_RANK() OVER ( PARTITION BY CYYear,CYSemester ORDER BY CYYear,CYSemester,CYQuarter ),
            CYMonth,
            CYMonthName          = CONCAT('CY ', CYYear, ' M', RIGHT('00'+CAST ( CYMonth as varchar(2)),2) ),
            CYSemesterMonth      = DENSE_RANK() OVER ( PARTITION BY CYYear,CYSemester ORDER BY CYYear,CYSemester,CYMonth ),
            CYQuarterMonth       = DENSE_RANK() OVER ( PARTITION BY CYYear,CYQuarter ORDER BY CYYear,CYQuarter,CYMonth ),
            CYWeek,
            CYWeekName           = CONCAT('CY ', CYYear, ' W', RIGHT('00'+CAST ( CYWeek as varchar(2)),2) ),
            CYSemesterWeek       = DENSE_RANK() OVER ( PARTITION BY CYYear,CYSemester ORDER BY CYYear,CYSemester,CYWeek ),
            CYQuarterWeek        = DENSE_RANK() OVER ( PARTITION BY CYYear,CYQuarter ORDER BY CYYear,CYQuarter,CYWeek ),
            CYMonthWeek          = DATEPART(WEEK, DateValue) + 1- DATEPART(WEEK, DATEFROMPARTS(DATEPART(YEAR, DateValue),DATEPART(MONTH, DateValue),1)),
            CYDay,
            CYSemesterDay        = ROW_NUMBER() OVER ( PARTITION BY CYYear,CYSemester ORDER BY CYYear,CYSemester ),
            CYQuarterDay         = ROW_NUMBER() OVER ( PARTITION BY CYYear,CYQuarter  ORDER BY CYYear,CYQuarter ),
            CYMonthDay,
            CYWeekDay            = ROW_NUMBER() OVER ( PARTITION BY CYYear,CYWeek  ORDER BY CYYear,CYWeek ),
            CYFirstDate          = CONVERT(DATE,DATEADD(YEAR,DATEDIFF(YEAR,'19000101',DateValue),'19000101')),
            CYLastDate           = CONVERT(DATE,DATEADD(DAY,-1, DATEADD(YEAR,DATEDIFF(YEAR,'19000101',DateValue)+1,'19000101'))),

            -- Fiscal Year Fields

            FYYear,
            FYYearName           = CONCAT('FY ', FYYear ),
            FYSemester,
            FYSemesterName       = CONCAT('FY ', FYYear, ' H0', FYSemester ),
            FYQuarter,
            FYSemesterQuarter    = DENSE_RANK() OVER ( PARTITION BY FYYear,FYSemester ORDER BY FYYear,FYSemester,FYQuarter ),
            FYMonth,
            FYMonthName          = CONCAT('FY ', FYYear, ' M', RIGHT('00'+CAST ( FYMonth as varchar(2)),2) ),
            FYSemesterMonth      = DENSE_RANK() OVER ( PARTITION BY FYYear,FYSemester ORDER BY FYYear,FYSemester,FYMonth ),
            FYQuarterMonth       = DENSE_RANK() OVER ( PARTITION BY FYYear,FYQuarter ORDER BY FYYear,FYQuarter,FYMonth ),
            FYWeek,
            FYWeekName           = CONCAT('FY ', CYYear, ' W', RIGHT('00'+CAST ( FYWeek as varchar(2)),2) ),
            FYSemesterWeek       = DENSE_RANK() OVER ( PARTITION BY FYYear,FYSemester ORDER BY FYYear,FYSemester,FYWeek ),
            FYQuarterWeek        = DENSE_RANK() OVER ( PARTITION BY FYYear,FYQuarter ORDER BY FYYear,FYQuarter,FYWeek ),
            FYMonthWeek          = DENSE_RANK() OVER (PARTITION BY FYYear,FYMonth ORDER BY FYYear,FYMonth, FYWeek),
            FYDay,
            FYSemesterDay        = ROW_NUMBER() OVER ( PARTITION BY FYYear,FYSemester ORDER BY FYYear,FYSemester ),
            FYQuarterDay         = ROW_NUMBER() OVER ( PARTITION BY FYYear,FYQuarter  ORDER BY FYYear,FYQuarter ),
            FYMonthDay           = ROW_NUMBER() OVER ( PARTITION BY FYYear,FYMonth  ORDER BY FYYear,FYMonth ),
            FYWeekDay            = ROW_NUMBER() OVER ( PARTITION BY FYYear,FYWeek  ORDER BY FYYear,FYWeek ),
            FYFirstDate,
            FYLastDate,

            -- ISO Year Fields

            r1.ISOYear,
            ISOYearName          = CAST(r1.ISOYear as char(4) ),
            ISOSemester,
            ISOSemesterName      = CONCAT(r1.ISOYear,'-H0',ISOSemester ),
            ISOQuarter,
            ISOQuarterName       = CONCAT(r1.ISOYear,'-Q0',ISOQuarter ),
            ISOSemesterQuarter   = DENSE_RANK() OVER ( PARTITION BY r1.ISOYear,ISOSemester ORDER BY r1.ISOYear,ISOSemester,ISOQuarter ),
            ISOMonth,
            ISOMonthName         = CONCAT(r1.ISOYear,'-M',RIGHT('00'+CAST(ISOMonth as varchar(2)),2) ),
            ISOSemesterMonth     = DENSE_RANK() OVER ( PARTITION BY r1.ISOYear,ISOSemester ORDER BY r1.ISOYear,ISOSemester,ISOMonth ),
            ISOQuarterMonth      = DENSE_RANK() OVER ( PARTITION BY r1.ISOYear,ISOQuarter ORDER BY r1.ISOYear,ISOQuarter,ISOMonth ),
            ISOWeek,
            ISOWeekName          = CONCAT(r1.ISOYear,'-W',RIGHT('00'+CAST(ISOWeek as varchar(2)),2) ),
            ISOSemesterWeek      = DENSE_RANK() OVER ( PARTITION BY r1.ISOYear,ISOSemester ORDER BY r1.ISOYear,ISOSemester,ISOWeek ),
            ISOQuarterWeek       = DENSE_RANK() OVER ( PARTITION BY r1.ISOYear,ISOQuarter ORDER BY r1.ISOYear,ISOQuarter,ISOWeek ),
            ISOMonthWeek         = DENSE_RANK() OVER (PARTITION BY r1.ISOYear,ISOMonth ORDER BY r1.ISOYear,ISOMonth, ISOWeek),
            ISODay,
            ISOSemesterDay       = ROW_NUMBER() OVER ( PARTITION BY r1.ISOYear,ISOSemester ORDER BY r1.ISOYear,ISOSemester ),
            ISOQuarterDay        = ROW_NUMBER() OVER ( PARTITION BY r1.ISOYear,ISOQuarter  ORDER BY r1.ISOYear,ISOQuarter ),
            ISOMonthDay          = ROW_NUMBER() OVER ( PARTITION BY r1.ISOYear,ISOMonth  ORDER BY r1.ISOYear,ISOMonth ),
            ISOWeekDay           = ROW_NUMBER() OVER ( PARTITION BY r1.ISOYear,ISOWeek  ORDER BY r1.ISOYear,ISOWeek ),
            ISODayName           = CONCAT(r1.ISOYear,'-W',RIGHT('00'+CAST(ISOWeek as varchar(2)),2),'-', ROW_NUMBER() OVER ( PARTITION BY r1.ISOYear,ISOWeek  ORDER BY r1.ISOYear,ISOWeek )),
            ISOFirstDate,            
            ISOLastDate,

            -- HOILDAYS        

            IsNationalHoliday    = CAST (0 AS BIT ),
            IsLocalHoliday       = CAST (0 AS BIT ),
            HolidayName          = CAST (NULL AS NVARCHAR(100) )
    FROM    r1
    INNER JOIN r2 on r1.ISOYear = r2.ISOYear
)
SELECT * 
INTO #TT
FROM r3;



-- FIXED GREEK HOLIDAYS

UPDATE #TT
SET IsLocalHoliday = 1, IsNationalHoliday = 1,
    HolidayName = N'Πρωτοχρονιά / New Year''s Day'
WHERE CYMonthDay = 1 AND CYMonth = 1;

UPDATE #TT
SET IsLocalHoliday = 1,
    HolidayName = N'Θεοφάνεια (Greece)'
WHERE CYMonthDay = 6 AND CYMonth = 1;

UPDATE #TT
SET IsLocalHoliday = 1,
    HolidayName = N'Ευαγγελισμός της Θεοτόκου (Greece)'
WHERE CYMonthDay = 25 AND CYMonth = 3;

UPDATE #TT
SET IsLocalHoliday = 1,
    HolidayName = N'Εργατική Πρωτομαγιά (Greece)'
WHERE CYMonthDay = 1 AND CYMonth = 5;


UPDATE #TT
SET IsLocalHoliday = 1,
    HolidayName = N'Κοίμηση της Θεοτόκου (Greece)'
WHERE CYMonthDay = 15 AND CYMonth = 8;

UPDATE #TT
SET IsLocalHoliday = 1,
    HolidayName = N'Ημέρα του Όχι (Greece)'
WHERE CYMonthDay = 28 AND CYMonth = 10;

UPDATE #TT
SET IsLocalHoliday = 1, IsNationalHoliday = 1,
    HolidayName = N'Χριστούγεννα / Christmas '
WHERE CYMonthDay = 25 AND CYMonth = 12;

UPDATE #TT
SET IsLocalHoliday = 1,
    HolidayName = N'Σύναξη της Θεοτόκου (Greece)'
WHERE CYMonthDay = 26 AND CYMonth = 12;

-- ORTHDOX EASTER AND GREEK RELATED HOLIDAYS

WITH
    a
    AS
    (
        -- CALCULATE EASTER DATE FOR EACH YEAR IN DATA SET
        SELECT DISTINCT CYYear,
            DATEADD(DAY,13,DATEFROMPARTS(CYYear,
                            ((((19 * (CYYear%19) + 15)%30) + ((2 * (CYYear%4) + 4 * (CYYear%7) - ((19 * (CYYear%19) + 15)%30) +34)%7) + 114)/31),
                            (((((19 * (CYYear%19) + 15)%30) + ((2 * (CYYear%4) + 4 * (CYYear%7) - ((19 * (CYYear%19) + 15)%30) +34)%7) + 114)%31)+1))) AS GreekEasterDate
        FROM #TT
    ),
    b
    AS
    (
        -- CACLULATE RELATED TO EASTER HOLIDAYS
        SELECT CYYear ,
            DATEADD(DAY,-48,GreekEasterDate)    AS 'Καθαρά Δευτέρα',
            DATEADD(DAY,-2,GreekEasterDate)     AS 'Μ. Παρασκευή',
            DATEADD(DAY,-1,GreekEasterDate)     AS 'Μ. Σάββατο',
            GreekEasterDate                     AS 'Πάσχα',
            DATEADD(DAY,1,GreekEasterDate)      AS 'Δευτέρα του Πάσχα',
            DATEADD(DAY,49,GreekEasterDate)     AS 'Πεντηκοστή',
            DATEADD(DAY,50,GreekEasterDate)     AS 'Αγίου Πνεύματος'
        FROM a

    ),
    c
    AS
    (
        SELECT HolidayDate, HodidayName
        FROM b
    UNPIVOT ( HolidayDate FOR HodidayName IN ([Καθαρά Δευτέρα],[Μ. Παρασκευή],[Μ. Σάββατο],[Πάσχα],[Δευτέρα του Πάσχα],[Πεντηκοστή],[Αγίου Πνεύματος])) AS up
    )
-- UPDATE DATA SET
UPDATE T
SET IsLocalHoliday = 1,
    HolidayName = HodidayName + ' (Greece)'
FROM #TT AS T
    INNER JOIN c ON c.HolidayDate = t.DateValue;


-- CATHOLICS EASTER

WITH
    a
    AS
    (
        -- CALCULATE EASTER DATE FOR EACH YEAR IN DATA SET
        SELECT DISTINCT CYYear,
            DATEFROMPARTS(CYYear,    (3 + ((((24 + 19*(CYYear % 19)) % 30) - ((24 + 19*(CYYear % 19)) % 30)/ 28) - (CYYear + CYYear/4 + (((24 + 19*(CYYear % 19)) % 30) - ((24 + 19*(CYYear % 19)) % 30) / 28)- 13) % 7 + 40) / 44),
                            ((((24 + 19 * (CYYear % 19)) % 30) - ((24 + 19 * (CYYear % 19)) % 30) / 28) - ((CYYear + CYYear / 4 + (((24 + 19 * (CYYear % 19)) % 30) - ((24 + 19 * (CYYear % 19)) % 30) / 28) - 13) % 7)) + 
                            28 - 31 * ((3 + ((((24 + 19*(CYYear % 19)) % 30) - ((24 + 19*(CYYear % 19)) % 30)/ 28) - (CYYear + CYYear/4 + (((24 + 19*(CYYear % 19)) % 30) - ((24 + 19*(CYYear % 19)) % 30) / 28)- 13) % 7 + 40) / 44) / 4)) AS EasterDate
        FROM #TT
    ),
    b
    AS
    (
        -- CACLULATE RELATED TO EASTER HOLIDAYS
        SELECT CYYear ,
            DATEADD(DAY,-3,EasterDate)     AS 'G. Thursday',
            DATEADD(DAY,-2,EasterDate)     AS 'G. Friday',
            DATEADD(DAY,-1,EasterDate)     AS 'G. Saturday',
            EasterDate                     AS 'Catholics Easter',
            DATEADD(DAY,1,EasterDate)      AS 'Easter Monday'
        FROM a

    ),
    c
    AS
    (
        SELECT HolidayDate, HodidayName
        FROM b
        UNPIVOT ( HolidayDate FOR HodidayName IN ([G. Thursday], [G. Friday], [G. Saturday], [Catholics Easter], [Easter Monday]) ) AS up
    )
-- UPDATE DATA SET
UPDATE T
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,HodidayName ,' (Catholics)')
FROM #TT AS T
    INNER JOIN c ON c.HolidayDate = t.DateValue

-- USA HOILDAYS

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Martin Luther King, Jr. Day (USA)')
WHERE CYYear >= 1983 AND CYMonth = 1 AND CYMonthWeek = 3 AND DayNameNational='Mon';

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'President''s Day (USA)')
WHERE CYMonth = 2 AND CYMonthWeek = 3 AND DayNameNational='Mon';

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Saint Patrick''s Day (USA)')
WHERE CYMonth = 3 AND CYMonthDay=17;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Memorial Day (USA)')
WHERE DateKey IN (SELECT MAX(DateKey) FROM #TT 
WHERE CYMonth = 5 AND DayNameNational='Mon'
GROUP BY CYYear, CYMonth
                );

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Independance Day (USA)')
WHERE CYMonth = 7 AND CYMonthDay= 4;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Labor Day (USA)')
WHERE DateKey IN (
                    SELECT MIN(DateKey)
FROM #TT
WHERE CYMonth = 9 AND DayNameNational='Mon'
GROUP BY CYYear, CYMonth
                );

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Columbus Day (USA)')
WHERE CYMonth = 10 AND CYMonthWeek = 2 AND DayNameNational='Mon';

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Halloween (USA)')
WHERE CYMonth = 10 AND CYMonthDay = 31;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Veterans Day (USA)')
WHERE CYMonth = 11 AND CYMonthDay = 11;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Thanksgiving Day (USA)')
WHERE CYMonth = 11 AND CYMonthWeek = 4 AND DayNameNational='Thu';

-- EU INSTITUTIONS HOLIDAYS

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'2nd New Year''s Day (EU)')
WHERE CYMonth = 1 AND CYMonthDay = 2;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Labour Day (EU)')
WHERE CYMonth = 5 AND CYMonthDay = 1;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Europe Day (EU)')
WHERE CYMonth = 5 AND CYMonthDay = 9;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Ascension Thursday (EU)')
WHERE CYMonth = 5 AND CYMonthDay = 21;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'the Friday following Ascension Day (EU)')
WHERE CYMonth = 5 AND CYMonthDay = 22;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Whit Monday (EU)')
WHERE CYMonth = 6 AND CYMonthDay = 1;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Belgian National Day (EU)')
WHERE CYMonth = 7 AND CYMonthDay = 21;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'Assumption (EU)')
WHERE CYMonth = 8 AND CYMonthDay = 15;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'All Saints'' Day (EU)')
WHERE CYMonth = 11 AND CYMonthDay = 1;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'All Souls'' Day (EU)')
WHERE CYMonth = 11 AND CYMonthDay = 2;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 24;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 26;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 27;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 28;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 29;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 30;

UPDATE #TT
SET IsNationalHoliday = 1,
    HolidayName = CONCAT(HolidayName,IIF(LEN(HolidayName)>0,' / ',''),'End-of-year days (EU)')
WHERE CYMonth = 12 AND CYMonthDay = 31;

INSERT INTO dbo.DimCalendar
SELECT *
FROM    #TT
WHERE DateValue >= @startdate
ORDER by DateKey;

SELECT * FROM dbo.DimCalendar;

GO

Calendar View with Friendly Names

Επειδή αρκετές φορές θέλουμε να υπάρχουν φιλικά ονόματα στα πεδία του Calendar dimension table, έχω δημιουργήσει το παρακάτω view το οποίo μπορούμε να χρησιμοποιήσουμε στα data models μας τόσο στα Analysis Services όσο και στο Power BI.

Calendar View with Friendly Names - SQL Script

CREATE OR ALTER VIEW dbo.DimCalendarView
AS
SELECT [DateKey]
      ,[DateValue]
      ,[MonthNameNational]          AS  [English Month Name]
      ,[DayNameNational]            AS  [English Day Name]
      ,[MonthNameLocal]             AS  [Greek Month Name]
      ,[DayNameLocal]               AS  [Greek Day Name]
      ,[IsWeekend]                  AS  [Is Weekend Day]
      ,[IsLeapYear]                 AS  [Is Leap Year]
      ,[CYYear]                     AS  [Calendar Year]
      ,[CYYearName]                 AS  [Calendar Year Name]
      ,[CYSemester]                 AS  [Calendar Year Semester]
      ,[CYSemesterName]             AS  [Calendar Year Semester Name]
      ,[CYQuarter]                  AS  [Calendar Year Quarter]
      ,[CYQuarterName]              AS  [Calendar Year Quarter Name]
      ,[CYSemesterQuarter]          AS  [Calendar Year Semester Quarter]
      ,[CYMonth]                    AS  [Calendar Year Month]
      ,[CYMonthName]                AS  [Calendar Year Month Name]
      ,[CYSemesterMonth]            AS  [Calendar Year Semester Month]
      ,[CYQuarterMonth]             AS  [Calendar Year Quarter Month]
      ,[CYWeek]                     AS  [Calendar Year Week]
      ,[CYWeekName]                 AS  [Calendar Year Week Name]
      ,[CYSemesterWeek]             AS  [Calendar Year Semester Week]
      ,[CYQuarterWeek]              AS  [Calendar Year Quarter Week]
      ,[CYMonthWeek]                AS  [Calendar Year Month Week]
      ,[CYDay]                      AS  [Calendar Year Day Number]
      ,[CYSemesterDay]              AS  [Calendar Year Semester Day Number]
      ,[CYQuarterDay]               AS  [Calendar Year Quarter Day Number]
      ,[CYMonthDay]                 AS  [Calendar Year Month Day Number]
      ,[CYWeekDay]                  AS  [Calendar Year Week Day Number]
      ,[CYFirstDate]                AS  [Calendar Year First Date]
      ,[CYLastDate]                 AS  [Calendar Year Last Date]
      ,[FYYear]                     AS  [Fiscal Year]
      ,[FYYearName]                 AS  [Fiscal Year Name]
      ,[FYSemester]                 AS  [Fiscal Year Semester]
      ,[FYSemesterName]             AS  [Fiscal Year Semester Name]
      ,[FYQuarter]                  AS  [Fiscal Year Quarter]
      ,[FYSemesterQuarter]          AS  [Fiscal Year Quarter Name]
      ,[FYMonth]                    AS  [Fiscal Year Month]
      ,[FYMonthName]                AS  [Fiscal Year Month Name] 
      ,[FYSemesterMonth]            AS  [Fiscal Year Semester Month]
      ,[FYQuarterMonth]             AS  [Fiscal Year Quarter Month]
      ,[FYWeek]                     AS  [Fiscal Year Week]
      ,[FYWeekName]                 AS  [Fiscal Year Week Name]
      ,[FYSemesterWeek]             AS  [Fiscal Year Semester Week]
      ,[FYQuarterWeek]              AS  [Fiscal Year Quarter Week]
      ,[FYMonthWeek]                AS  [Fiscal Year Month Week]
      ,[FYDay]                      AS  [Fiscal Year Day Number]
      ,[FYSemesterDay]              AS  [Fiscal Year Semester Day Number]
      ,[FYQuarterDay]               AS  [Fiscal Year Quarter Day Number]
      ,[FYMonthDay]                 AS  [Fiscal Year Month Day Number]
      ,[FYWeekDay]                  AS  [Fiscal Year Week Day Number]
      ,[FYFirstDate]                AS  [Fiscal Year First Date]
      ,[FYLastDate]                 AS  [Fiscal Year Last Date]
      ,[ISOYear]                    AS  [ISO Year]
      ,[ISOYearName]                AS  [ISO Year Name]
      ,[ISOSemester]                AS  [ISO Year Semester]
      ,[ISOSemesterName]            AS  [ISO Year Semester Name]
      ,[ISOQuarter]                 AS  [ISO Year Quarter]
      ,[ISOQuarterName]             AS  [ISO Year Quarter Name]
      ,[ISOSemesterQuarter]         AS  [ISO Year Semester Quarter]
      ,[ISOMonth]                   AS  [ISO Year Month]
      ,[ISOMonthName]               AS  [ISO Year Month Name]
      ,[ISOSemesterMonth]           AS  [ISO Year Semester Month]
      ,[ISOQuarterMonth]            AS  [ISO Year Quarter Month]
      ,[ISOWeek]                    AS  [ISO Year Week]
      ,[ISOWeekName]                AS  [ISO Year Week Name]
      ,[ISOSemesterWeek]            AS  [ISO Year Semester Week]
      ,[ISOQuarterWeek]             AS  [ISO Year Quarter Week]
      ,[ISOMonthWeek]               AS  [ISO Year Month Week]
      ,[ISODay]                     AS  [ISO Year Day Number]
      ,[ISOSemesterDay]             AS  [ISO Year Semester Day Number]
      ,[ISOQuarterDay]              AS  [ISO Year Quarter Day Number]
      ,[ISOMonthDay]                AS  [ISO Year Month Day Number]
      ,[ISOWeekDay]                 AS  [ISO Year Week Day Number]
      ,[ISODayName]                 AS  [ISO Year Day Name]
      ,[ISOFirstDate]               AS  [ISO Year First Date]
      ,[ISOLastDate]                AS  [ISO Year Last Date]
      ,[IsNationalHoliday]          AS  [Is National Holiday]
      ,[IsLocalHoliday]             AS  [Is Greek Holiday]
      ,[HolidayName]                AS  [Holiday Name]
  FROM [dbo].[DimCalendar]
GO


SELECT * FROM dbo.DimCalendarView;

//Antonios Chatzipavlis


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.