Wednesday, September 10, 2014

SSAS TUTORIAL : Modifying the Date Dimension

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:
DATENAME(mm, FullDateAlternateKey) + ' ' +
DATENAME(dd, FullDateAlternateKey) + ', ' +
DATENAME(yy, FullDateAlternateKey)

 
 




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:
EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)

 
 


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:
'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY ' +
CONVERT(CHAR (4), CalendarYear)

 
 



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.      
CASE
WHEN CalendarSemester = 1 THEN 'H1' + ' ' + 'CY' + ' ' 
       + CONVERT(CHAR(4), CalendarYear)
ELSE
'H2' + ' ' + 'CY' + ' ' + CONVERT(CHAR(4), CalendarYear)
END

 

In the Create Named Calculation dialog box, type CalendarSemesterDesc in theColumn name box, and then type or copy and paste the following SQL script in theExpression box:








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