SSAS: Custom Date Dimension Design

The SSAS Date dimension is basically present in all Cube design processes, and it is difficult to see an OLAP database with no time dimension. However, depending on the project requirements, the Date dimension may be designed differently, so there are a few issues to be aware of when designing the Time dimension:

What is the minimum level of detail involved in your business? For example, view the report by quarter or by month, or by week, or by day. This level of detail needs to be made clear, for example in some sales statistics, and sometimes more quarterly or monthly to view the report. But in some of the monitoring of some machine running data statistics, may be based on hours or minutes to view the report.

The format of the time that your report needs to display, such as in the English system, the customer wants to display the full name of the month January or short, or only like to see the number 1,2,3 to 12 this type. Include showing specific days when you want to see a 10/1/2005 or 2005/10/01 format.

Are there any special time information, such as not just the natural year, but also the financial year, and whether it's a leap years, weekends, etc.

The most important point is that customers like which or how many hierarchies to view the report, such as the first layer is the year, through the year navigation to the month and then navigate to the date, or through the year direct to the week to the specific date.

Figure out these aspects of the above, the heart probably know the scope of the time attribute, to what level, by which special fields need to be added in this design phase to complete.

In general, you can write your own Data Warehouse dimension table that creates a time date, where you can customize some special date formats as needed.

-----------------------------------------------------------------------Biwork dimdate and Vdimdate Demo--http://  
Www.cnblogs.com/biwork----------------------------------------------------------------------Use biwork_ssis go SET NOCOUNT on IF object_id (' dimdate ', ' U ') are not NULL DROP TABLE dimdate go CREATE table dimdate (datekey INT PRIMARY KEY, fulldate DATE not NULL, [Datename] NVARCHAR (m), Daynumberofweek TINYINT not NULL, Daynam  Eofweek NVARCHAR (a) not NULL, daynumberofmonth TINYINT NOT NULL, daynumberofyear SMALLINT NOT NULL, isweekend Bit not NULL, isleapyear bit isn't null, weeknumberofyear TINYINT NOT NULL, EnglishMonthName NVARCHAR (Ten) not N
    ull, monthnumberofyear TINYINT not NULL, calendarquarter TINYINT NOT NULL, calendarsemester TINYINT NOT NULL, CalendarYear SMALLINT NOT NULL, fiscalquarter TINYINT NOT NULL, fiscalsemester TINYINT NOT NULL, Fiscalye AR SMALLINT not NULL) DECLARE @StartDate datetime DECLARE @EndDate datetime SELECT @StartDate = ' 2005-01-01 ', @EndDate = ' 2013-12-31 ' while (@StartDate <= @EndDate) BEGIN inserts into Dimdate (DateKey, fulldate, [Datenam
        E], Daynumberofweek, Daynameofweek, Daynumberofmonth, Daynumberofyear, Isweekend, Isleapyear, Weeknumberofyear, EnglishMonthName, Monthnumberofyear, Calendarquarte 
    R, CalendarSemester, CalendarYear, Fiscalquarter, Fiscalsemester, fiscalyear SELECT CAST (CONVERT (VARCHAR (8), @StartDate, 112) as INT) as DateKey, convert (VARCHAR, @StartDate,) A S fulldate, CONVERT (VARCHAR, @StartDate,) as [Datename], DATEPART (DW, @StartDate) as Daynumber
           Ofweek, Datename (DW, @StartDate) as Daynameofweek, Datename (DD, @StartDate) as [DayOfMonth], Datename (DY, @StartDateAs [DayOfYear], case when DATEPART (DW, @StartDate) in (1,7) THEN 1 ELSE

0 End as Isweekend (the year (@StartDate)% 4 = 0) and (@StartDate)%!= 0 OR year (@StartDate)% = 0) THEN 1 ELSE 0 end as Isleapyear, Datep ART (WW, @StartDate) as Weeknumberofyear, Datename (mm, @StartDate) as EnglishMonthName, DATEPART (mm, @St Artdate as Monthnumberofyear, DATEPART (QQ, @StartDate) as CalendarQuarter, case when DATEPART (MM, @St
           Artdate) BETWEEN 1 and 6 THEN 1 ELSE 2 end as CalendarSemester,
                     DATEPART (YY, @StartDate) as calendaryear, case when DATEPART (MM, @StartDate) BETWEEN 1 and 6
           THEN DATEPART (QQ, @StartDate) + 2 ELSE DATEPART (QQ, @StartDate)-2 end as Fiscalquarter, Case when DATEPART(MM, @StartDate) BETWEEN 1 and 6 THEN 2 ELSE 1 End as Fiscalsemester Atepart (MM, @StartDate) BETWEEN 1 and 6 THEN DATEPART (yy, @StartDate) ELSE DATEPART (yy, @StartDate) + 1 End as fiscalyear SET @StartDate = @StartDate + 1 End Go------------ -----------------------------------------------------------plus the view is because in actual project development, all table objects in the data source view of SSAS-should all refer to the view,
This allows you to modify the dimension table or fact table in the data warehouse directly in the view, and avoid modifying the SSAS project. This is also true in SSIS development, where all SQL statements configured in SSIS are encapsulated in the storage-process, and the tables are encapsulated in the view.
The logical modifications are directly embodied in the stored procedure without modifying SSIS. ----------------------------------------------------------------------IF object_id (' vdimdate ', ' V ') is not NULL DROP
View vdimdate go CREATE VIEW vdimdate as-you can implement some computed columns as needed, and these computed columns can often be added to the SSAS view. SELECT DateKey as ' DateKey ', fulldate as ' fulldate ', [datename] as ' datename ', CONVERT (VARCHAR (2), day Numberofmonth) + ' + englishmonthName + ' + CONVERT (CHAR (4), calendaryear) as ' fulldatename ',--1 July daynumberofweek as ' Daynumberofwee K ', Daynameofweek as ' Daynameofweek ', daynumberofmonth as ' Daynumberofmonth ', daynumberofyear as ' Day
       Numberofyear ' isweekend = 1 THEN ' Weekend ' ELSE ' weekday ' end as ' weekdayweekend ', Isleapyear as ' isleapyear ', weeknumberofyear as ' weeknumberofyear ', englishmonthname as ' Englishmonthna  Me ', EnglishMonthName + ' + CONVERT (CHAR (4), calendaryear) as ' MonthName ',--July 100 + monthnumberofyear as ' Monthkey ',--200507 monthnumberofyear as ' monthnumberofyear ', calendarquarter as ' CalendarQuarter ', calendarsemester as ' CalendarSemester ', calendaryear as ' calendaryear ', calendary Ear * + calendarquarter as ' Calendarquarterkey ',--200503 ' CY ' + CONVERT (CHAR (4), calendaryear) as ' calendary
   Earname ',--CY 2005    ' CY ' + convert (char (4), calendaryear) + ' Qtr ' + convert (char (1), calendarquarter) as ' Cal Endarquartername ',--CY Qtr 3 fiscalquarter as ' Fiscalquarter ', fiscalsemester as ' Fiscalsemester ' , fiscalyear as ' fiscalyear ', fiscalyear * + fiscalquarter as ' Fiscalquarterkey ',-200601 ' FY ' + CONVERT (char (4), fiscalyear) as ' fiscalyearname ',--FY 2006 ' FY ' + CONVERT (char (4), fiscalyear) + ' Qtr ' + Convert (Char (1), fiscalquarter) as ' fiscalquartername '--FY 2006 QTR 1 from Dimdate go