In the tasks in this topic, you
create a user-defined hierarchy and change the member names that are displayed
for the Date, Month, Calendar Quarter, and Calendar Semester attributes. You
also define composite keys for attributes, control the sort order of dimension
members, and define attribute relationships.
1.
To open the Adventure Works DW 2012 data source view, double-click it in
theData Source Views folder in Solution Explorer.
2.
Near the bottom of the Tables pane,
right-click Date,
and then click New
Named Calculation.
3.
In the Create Named Calculation dialog box, type SimpleDate in the Column name box, and then type or copy and paste
the following DATENAME statement in the Expression box:
|
The DATENAME statement extracts the year, month,
and day values from the FullDateAlternateKey column. You will use this new
column as the displayed name for the FullDateAlternateKey attribute.
4.
Click OK, and then expand Date in
the Tables pane.
The SimpleDate named calculation appears in the list
of columns in the Date table, with an icon that indicates that it is a named
calculation.
5.
On the File menu,
click Save
All.
6.
In the Tables pane,
right-click Date,
and then click Explore
Data.
7.
Scroll to the right to review the last column in the Explore Date
Table view.
Notice
that the SimpleDate column appears in the data source
view, correctly concatenating data from several columns from the underlying
data source, without modifying the original data source.
8.
Close the Explore Date Table view.
1.
Open Dimension Designer for
the Date dimension in SQL Server Data Tools (SSDT). To do this, double-click
the Date dimension in the Dimensions node of Solution Explorer.
2.
In the Attributes pane
of the Dimension
Structure tab, click
the Date Key attribute.
3.
If the Properties window is not open, open the Properties
window, and then click theAuto Hide button on the title bar so that it
stays open.
4.
Click the NameColumn property
field near the bottom of the window, and then click the ellipsis browse (…) button to open the Name Column dialog box.
5.
Select SimpleDate at
the bottom of the Source column list,
and then click OK.
6.
On the File menu,
click Save
All.
1.
In Dimension Structure tab
of the Dimension Designer for the Date dimension,
drag the Calendar
Year attribute from
the Attributes pane into the Hierarchiespane.
2.
Drag the Calendar Semester attribute
from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the Calendar Year level.
3.
Drag the Calendar Quarter attribute
from the Attributes pane into the <new level>cell
in the Hierarchies pane, underneath the Calendar Semester level.
4.
Drag the English Month Name attribute
from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the Calendar Quarter level.
5.
Drag the Date Key attribute
from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the English Month
Name level.
6.
In the Hierarchies pane,
right-click the title bar of the Hierarchy hierarchy,
cickRename, and then type Calendar Date.
7.
By using the right-click context menu, in the Calendar Date hierarchy, rename theEnglish Month Name level
to Calendar
Month, and then rename the Date Keylevel to Date.
8.
Delete the Full Date Alternate Key attribute from the Attributes pane because you will not be using it.
Click OK in the Delete Objects confirmation window.
9.
On the File menu,
click Save
All.
1.
In the Dimension Designer for
the Date dimension, click the Attribute Relationships tab.
2.
In the diagram, right-click the English Month
Name attribute, and
then click New
Attribute Relationship.
3.
In the Create Attribute Relationship dialog box, the Source Attribute is English Month Name. Set the Related Attribute to Calendar Quarter.
4.
In the Relationship type list,
set the relationship type to Rigid.
The
relationship type is Rigid because
relationships between the members will not change over time.
5.
Click OK.
6.
In the diagram, right-click the Calendar Quarter attribute, and then click New Attribute
Relationship.
7.
In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Quarter. Set the Related Attribute to Calendar Semester.
8.
In the Relationship type list,
set the relationship type to Rigid.
9.
Click OK.
10.
In the diagram, right-click the Calendar Semester attribute, and then click New Attribute
Relationship.
11.
In the Create Attribute Relationship dialog box, the Source Attribute is Calendar Semester. Set the Related Attribute to Calendar Year.
12.
In the Relationship type list,
set the relationship type to Rigid.
13.
Click OK.
14.
On the File menu,
click Save
All.
15.
1.
To switch to the Adventure Works DW 2012 data source view, double-click it in
theData Source Views folder in Solution Explorer.
2.
In the Tables pane,
right-click Date,
and then click New
Named Calculation.
3.
In the Create Named Calculation dialog box, type MonthName in the Column name box, and then type or copy and paste
the following statement in theExpression box:
|
The
statement concatenates the month and year for each month in the table into a
new column.
4.
Click OK.
5.
In the Tables pane,
right-click Date,
and then click New
Named Calculation.
6.
In the Create Named Calculation dialog box, type CalendarQuarterDesc in theColumn name box, and then type or copy and paste
the following SQL script in theExpression box:
|
This
SQL script concatenates the calendar quarter and year for each quarter in the
table into a new column.
7.
Click OK.
8.
In the Tables pane,
right-click Date,
and then click New
Named Calculation.
9.
|
This
SQL script concatenates the calendar semester and year for each semester in the
table into a new column.
10.
Click OK.
11.
On the File menu,
click Save
All.
1.
Open the Dimension Structure tab
for the Date dimension.
2.
In the Attributes pane,
click the English
Month Name attribute.
3.
In the Properties window,
click the KeyColumns field, and then click the browse (...) button.
4.
In the Key Columns dialog
box, in the Available
Columns list, select
the columnCalendarYear, and then click the > button.
5.
The EnglishMonthName and CalendarYear columns are now displayed in the Key Columns list.
6.
Click OK.
7.
To set the NameColumn property
of the EnglishMonthName attribute, click theNameColumn field in the Properties window, and
then click the browse (...) button.
8.
In the Name Column dialog
box, in the Source
Column list, select MonthName,
and then click OK.
9.
On the File menu,
click Save
All.
To define composite KeyColumns for the
Calendar Quarter attribute
1.
In the Attributes pane,
click the Calendar
Quarter attribute.
2.
In the Properties window,
click the KeyColumns field, and then click the browse (...) button.
3.
In the Key Columns dialog
box, in the Available
Columns list, select
the columnCalendarYear, and then click the > button.
The CalendarQuarter and CalendarYear columns
are now displayed in the Key Columns list.
4.
Click OK.
5.
To set the NameColumn property
of the Calendar
Quarter attribute,
click theNameColumn field in the Properties window, and
then click the browse (...) button.
6.
In the Name Column dialog
box, in the Source
Column list, selectCalendarQuarterDesc, and then click OK.
7.
On the File menu,
click Save
All.
To define composite KeyColumns for the
Calendar Semester attribute
1.
In the Attributes pane,
click the Calendar
Semester attribute.
2.
In the Properties window,
click the KeyColumns field, and then click the browse (...) button.
3.
In the Key Columns dialog
box, in the Available
Columns list, select
the column,CalendarYear, and then click the > button.
The CalendarSemester and CalendarYear columns
are now displayed in the Key Columns list.
4.
Click OK.
5.
To set the NameColumn property
of the Calendar
Semester attribute,
click theNameColumn field in the property window, and then
click the browse (...) button.
6.
In the Name Column dialog
box, in the Source
Column list, selectCalendarSemesterDesc, and then click OK.
7.
On the File menu,
click Save
All.
1.
On the Build menu
of SQL Server Data Tools, click Deploy Analysis Services Tutorial.
2.
After you have received the Deployment Completed Successfully message, click the Browser tab of Dimension
Designer for the Date dimension, and then click the
Reconnect button on the toolbar of the designer.
3.
Select Calendar Quarter from
the Hierarchy list. Review the members in theCalendar Quarter attribute
hierarchy.
Notice
that the names of the members of the Calendar Quarter attribute
hierarchy are clearer and easier to use because you created a named calculation
to use as the name. Members now exist in the Calendar Quarter attribute
hierarchy for each quarter in each year. The members are not sorted in
chronological order. Instead they are sorted by quarter and then by year. In
the next task in this topic, you will modify this behavior to sort the members
of this attribute hierarchy by year and then by quarter.
4.
Review the members of the English Month Name and Calendar Semester attribute hierarchies.
Notice
that the members of these hierarchies are also not sorted in chronological
order. Instead, they are sorted by month or semester, respectively, and then by
year. In the next task in this topic, you will modify this behavior to change
this sort order.
In this task, you will change
the sort order by changing the order of the keys that make up the composite
key.
To modify the composite key member order
1.
Open the Dimension Structure tab
of Dimension Designer for the Date dimension,
and then select Calendar Semester in
the Attributes pane.
2.
In the Properties window, review the value for the OrderBy property. It is set to Key.
The
members of the Calendar
Semester attribute
hierarchy are sorted by their key value. With a composite key, the ordering of
the member keys is based first on the value of the first member key, and then
on the value of the second member key. In other words, the members of the Calendar Semester attribute hierarchy are sorted first
by semester and then by year.
3.
In the Properties window, click the ellipsis browse button (...) to change theKeyColumns property value.
4.
In the Key Columns list
of the Key
Columns dialog box,
verify thatCalendarSemester is selected, and then click the down
arrow to reverse the order of the members of this composite key. Click OK.
The
members of the attribute hierarchy are now sorted first by year and then by
semester.
5.
Select Calendar Quarter in
the Attributes pane, and then click the ellipsis
browse button (...) for the KeyColumns property in the Properties window.
6.
In the Key Columns list
of the Key
Columns dialog box,
verify that CalendarQuarteris
selected, and then click the down arrow to reverse the order of the members of
this composite key. Click OK.
The
members of the attribute hierarchy are now sorted first by year and then by quarter.
7.
Select English Month Name in
the Attributes pane, and then click the ellipsis
button (...) for the KeyColumns property in the Properties window.
8.
In the Key Columns list
of the Key
Columns dialog box,
verify thatEnglishMonthName is selected, and then click the
down arrow to reverse the order of the members of this composite key. Click OK.
The
members of the attribute hierarchy are now sorted first by year and then by
month.
9.
On the Build menu
of SQL Server Data Tools, click Deploy Analysis Services Tutorial. When deployment
has successfully completed, click the Browser tab
in Dimension Designer for the Date dimension.
10.
On the toolbar of the Browser tab,
click the Reconnect button.
11.
Review the members of the Calendar Quarter and Calendar Semester attribute hierarchies.
Notice
that the members of these hierarchies are now sorted in chronological order, by
year and then by quarter or semester, respectively.
12.
Review the members of the English Month Name attribute
hierarchy.
Notice
that the members of the hierarchy are now sorted first by year and then
alphabetically by month. This is because the data type for the
EnglishCalendarMonth column in the data source view is a string column, based
on the nvarchar data type in the underlying relational database
No comments:
Post a Comment