Sunday 26 March 2017

Learning SSAS with Me – Working with Time Dimension

Introduction
As we all know that the Time Dimension is a special type of Dimension and almost all multi-dimensional models have the Time Dimension exist. Here we are not going to describe about the Time Dimension in details as we assume that the purpose of it has known by all.
Here in this article we are going to describe about some important property of Time Dimension that makes a normal Dimension to Time Dimension and how to use Business Intelligence to set those Property.

How we Create a Time Dimension
There is two options to create time dimension, by using Existing Table or Generating Time Dimension.





If we choose the Option -2 SSAS take care of all and nothing to worry.
But if we chose the Option-2 means By Using Existing table we have to set something for that others it teat as Normal Dimension.
Two understand it lets take an example.


Create Time Dimension using Existing Table
We have an existing table

SELECT * FROM [dbo].[DIMTime1];

Sample:

PK_Date                 Date_Name                          Year                    Year_Name                                         
----------------------- -------------------------------------------------- -----------------------
2014-01-01 00:00:00.000 Wednesday, January 01 2014         2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-02 00:00:00.000 Thursday, January 02 2014          2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-03 00:00:00.000 Friday, January 03 2014            2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-04 00:00:00.000 Saturday, January 04 2014          2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-05 00:00:00.000 Sunday, January 05 2014            2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-06 00:00:00.000 Monday, January 06 2014            2014-01-01 00:00:00.000 Calendar 2014                                      
2014-01-07 00:00:00.000 Tuesday, January 07 2014           2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-08 00:00:00.000 Wednesday, January 08 2014         2014-01-01 00:00:00.000 Calendar 2014                                     
2014-01-09 00:00:00.000 Thursday, January 09 2014          2014-01-01 00:00:00.000 Calendar 2014                                      
2014-01-10 00:00:00.000 Friday, January 10 2014            2014-01-01 00:00:00.000 Calendar 2014



When we create a Time Dimension from the existing table




The Dimension property named Type is showing Regular and it need to be change first.

Dimension Property Type = Time

Now it is treated as Time Dimension.

What Next ?
Now we are going to add all the attributes and choosing some attributes we are going make hierarchy of Time Dimension. Named it as Calendar-Hierarchy




If we look at the Calendar-Hierarchy, we find that there is a warning message comes and saying
Attribute relationship does not exists in one or more level of hierarchy. They may result of degrade of performance …etc.
This can be fixing by using attribute relationship (Natural- Hierarchy).

Now it’s Time to Add Business Intelligence










Browsing the Dimension
Process the Dimension





Browse the Dimension




Date Dimension Attribute Type

Attribute Type Value
Description
Date
Represents a date. This attribute 
type is  typically applied to the key 
attribute of a time dimension or 
server time dimension.
DayOfHalfYear
Represents the day ordinal of a half-
year.
DayOfMonth
Represents the day ordinal of a 
month.
DayOfQuarter
Represents the day ordinal of a 
quarter.
DayOfTenDays
Represents the day ordinal of a ten-
day period.
DayOfTrimester
Represents the day ordinal of a 
trimester.
DayOfWeek
Represents the day ordinal of a 
week.
DayOfYear
Represents the day ordinal of a year.
Days
Represents days.
FiscalDate
Represents a date in a fiscal 
calendar.
FiscalDayOfHalfYear
Represents the day ordinal of a half-
year in a fiscal calendar.
FiscalDayOfMonth
Represents the day ordinal of a 
month in a fiscal calendar.
FiscalDayOfQuarter
Represents the day ordinal of a 
quarter in a fiscal calendar.
FiscalDayOfTrimester
Represents the day ordinal of a 
trimester in a fiscal calendar.
FiscalDayOfWeek
Represents the day ordinal of a 
week in a fiscal calendar.
FiscalDayOfYear
Represents the day ordinal of a year 
in a fiscal calendar.
FiscalHalfYears
Represents half-years in a fiscal 
calendar.
FiscalHalfYearOfYear
Represents the half-year ordinal of 
year in a fiscal calendar.
FiscalMonths
Represents months in a fiscal 
calendar.
FiscalMonthOfHalfYear
Represents the month ordinal of a 
half-year in a fiscal calendar.
FiscalMonthOfQuarter
Represents the month ordinal of a 
quarter in a fiscal calendar.
FiscalMonthOfTrimester
Represents the month ordinal of a 
trimester in a fiscal calendar.
FiscalMonthOfYear
Represents the month ordinal of a 
year in a fiscal calendar.
FiscalQuarters
Represents quarters in a fiscal 
calendar.
FiscalQuarterOfHalfYear
Represents the quarter ordinal of a 
half-year in a fiscal calendar.
FiscalQuarterOfYear
Represents the quarter ordinal of a 
year in a fiscal calendar.
FiscalTrimesters
Represents trimesters in a fiscal 
calendar.
FiscalTrimesterOfYear
Represents the trimester ordinal of 
year in a fiscal calendar.
FiscalWeeks
Represents weeks in a fiscal 
calendar.
FiscalWeekOfHalfYear
Represents the week ordinal of a 
half-year in a fiscal calendar.
FiscalWeekOfMonth
Represents the week ordinal of a 
month in a fiscal calendar.
FiscalWeekOfQuarter
Represents the week ordinal of a 
quarter in a fiscal calendar.
FiscalWeekOfTrimester
Represents the week ordinal of a 
trimester in a fiscal calendar.
FiscalWeekOfYear
Represents the week ordinal of a 
year in a fiscal calendar.
FiscalYears
Represents years in a fiscal calendar.
HalfYears
Represents half-years.
HalfYearOfYear
Represents the half-year ordinal of 
year.
Hours
Represents hours.
IsHoliday
Indicates whether a date is a 
holiday.
ISO8601Date
Represents a date in an ISO 8601 
calendar.
ISO8601DayOfWeek
Represents the day ordinal of a 
week in an ISO 8601 calendar.
ISO8601DayOfYear
Represents the day ordinal of a year 
in an ISO 8601 calendar.
ISO8601Weeks
Represents weeks in an ISO 8601 
calendar.
ISO8601WeekOfYear
Represents the week ordinal of a 
year in an ISO 8601 calendar.
ISO8601Years
Represents years in an ISO 8601 
calendar.
IsPeakDay
Indicates whether a date is a peak 
day.
IsWeekDay
Indicates whether a date is a 
weekday.
IsWorkingDay
Indicates whether a date is a 
working day.
ManufacturingDate
Represents a date in a 
manufacturing calendar.
ManufacturingDayOfHalfYear
Represents the day ordinal of a half-
year in a manufacturing calendar.
ManufacturingDayOfMonth
Represents the day ordinal of a 
month in a manufacturing calendar.
ManufacturingDayOfQuarter
Represents the day ordinal of a 
quarter in a manufacturing calendar.
ManufacturingDayOfTrimester
Represents the day ordinal of a 
trimester in a manufacturing 
calendar.
ManufacturingDayOfWeek
Represents the day ordinal of a 
week in a manufacturing calendar.
ManufacturingDayOfYear
Represents the day ordinal of a year 
in a manufacturing calendar.
ManufacturingHalfYears
Represents half-years in a 
manufacturing calendar.
ManufacturingHalfYearOfYear
Represents the half-year ordinal of 
year in a manufacturing calendar.
ManufacturingMonths
Represents months in a 
manufacturing calendar.
ManufacturingMonthOfHalfYear
Represents the month ordinal of a 
half-year in a manufacturing 
calendar.
ManufacturingMonthOfQuarter
Represents the month ordinal of a 
quarter in a manufacturing calendar.
ManufacturingMonthOfTrimester
Represents the month ordinal of a 
trimester in a manufacturing 
calendar.
ManufacturingMonthOfYear
Represents the month ordinal of a 
year in a manufacturing calendar.
ManufacturingQuarters
Represents quarters in a 
manufacturing calendar.
ManufacturingQuarterOfHalfYear
Represents the quarter ordinal of a 
half-year in a manufacturing 
calendar.
ManufacturingQuarterOfYear
Represents the quarter ordinal of a 
year in a manufacturing calendar.
ManufacturingWeeks
Represents weeks in a 
manufacturing calendar.
ManufacturingWeekOfHalfYear
Represents the week ordinal of a 
half-year in a manufacturing 
calendar.
ManufacturingWeekOfMonth
Represents the week ordinal of a 
month in a manufacturing calendar.
ManufacturingWeekOfQuarter
Represents the week ordinal of a 
quarter in a manufacturing calendar.
ManufacturingWeekOfTrimester
Represents the week ordinal of a 
trimester in a manufacturing 
calendar.
ManufacturingWeekOfYear
Represents the week ordinal of a 
year in a manufacturing calendar.
ManufacturingYears
Represents years in a 
manufacturing 
calendar.
Minutes
Represents minutes.
Months
Represents months.
MonthOfHalfYear
Represents the month ordinal of a 
half-year.
MonthOfQuarter
Represents the month ordinal of a 
quarter.
MonthOfTrimester
Represents the month ordinal of a 
trimester.
MonthOfYear
Represents the month ordinal of a 
year.
Quarters
Represents quarters.
QuarterOfHalfYear
Represents the quarter ordinal of a 
half-year.
QuarterOfYear
Represents the quarter ordinal of a 
year.
ReportingDate
Represents a date in a reporting 
calendar.
ReportingDayOfHalfYear
Represents the day ordinal of a half-
year in a reporting calendar.
ReportingDayOfMonth
Represents the day ordinal of a 
month in a reporting calendar.
ReportingDayOfQuarter
Represents the day ordinal of a 
quarter in a reporting calendar.
ReportingDayOfTrimester
Represents the day ordinal of a 
trimester in a reporting calendar.
ReportingDayOfWeek
Represents the day ordinal of a 
week in a reporting calendar.
ReportingDayOfYear
Represents the day ordinal of a year 
in a reporting calendar.
ReportingHalfYears
Represents half-years in a reporting 
calendar.
ReportingHalfYearOfYear
Represents the half-year ordinal of 
year in a reporting calendar.
ReportingMonths
Represents months in a reporting 
calendar.
ReportingMonthOfHalfYear
Represents the month ordinal of a 
half-year in a reporting calendar.
ReportingMonthOfQuarter
Represents the month ordinal of a 
quarter in a reporting calendar.
ReportingMonthOfTrimester
Represents the month ordinal of a 
trimester in a reporting calendar.
ReportingMonthOfYear
Represents the month ordinal of a 
year in a reporting calendar.
ReportingQuarters
Represents quarters in a reporting 
calendar.
ReportingQuarterOfHalfYear
Represents the quarter ordinal of a 
half-year in a reporting calendar.
ReportingQuarterOfYear
Represents the quarter ordinal of a  
year in a reporting calendar.
ReportingTrimesters
Represents trimesters in a reporting 
calendar.
ReportingTrimesterOfYear
Represents the trimester ordinal of 
year in a reporting calendar.
ReportingWeeks
Represents weeks in a reporting 
calendar.
ReportingWeekOfHalfYear
Represents the week ordinal of a 
half-year in a reporting calendar.
ReportingWeekOfMonth
Represents the week ordinal of a   
month in a reporting calendar.
ReportingWeekOfQuarter
Represents the week ordinal of a 
quarter in a reporting calendar.
ReportingWeekOfTrimester
Represents the week ordinal of a 
trimester in a reporting calendar.
ReportingWeekOfYear
Represents the week ordinal of a 
year in a reporting calendar.
ReportingYears
Represents years in a reporting 
calendar.
Seconds
Represents seconds.
TenDayOfHalfYear
Represents the ten-day period 
ordinal of a half-year.
TenDayOfMonth
Represents the ten-day period   
ordinal of a month.
TenDayOfQuarter
Represents the ten-day period   
ordinal of a quarter.
TenDayOfTrimester
Represents the ten-day period   
ordinal of a trimester.
TenDayOfYear
Represents the ten-day period     
ordinal of a year.
TenDays
Represents ten-day periods.
Trimesters
Represents trimesters.
TrimesterOfYear
Represents the trimester ordinal of a     year.
UndefinedTime
Represents an undefined time     
period.
WeekOfYear
Represents the week ordinal of a           year.
Weeks
Represents weeks.
WinterSummerSeason
Indicates whether the date is part of    the winter/summer season.
Years
Represents years.


Source of the Chart: MSDN




Hope you like it.






Posted by: MR. JOYDEEP DAS