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
a 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
a 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
a 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
a 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
a 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
a 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
