MSBI # 28 – SSAS # 9 – Modifying Measures, Attributes and Hierarchies in SSAS

Continuing from my last post on SSAS Cube deployment :

MSBI # 30 – SSAS # 8 – Deploying an Analysis Services Project « (B)usiness (I)ntelligence Mentalist

In this article we are going to see lot of explanation in SSAS covering in all following topics :

  • How to modify the measures of the cube ?
  • Modifying the Customer Dimension !
  • Modifying User-Defined Hierarchy and Attribute Properties !
  • How to Add a Named Calculation ?
  • How to use the Named Calculation for Member Names ?
  • Where to add an Attribute and Defining Display Folders ?
  • How to Modify the Time Dimension ?
  • How to provide unique dimension member names?
  • How to specify composite key values ?
  • To modify the composite key member order ?
  • How to Modify the Product Dimension ?
  • How to define named calculations, modify name property values, and define a user hierarchy

After defining, deploying, and processing your initial cube, and then reviewing dimension and cube data in Business Intelligence Development Studio, you are ready to improve the usefulness and friendliness of the cube.

Modifying Measures 

You can increase the user-friendliness of the measures in a cube by using the FormatString property for each measure to define formatting settings that control how the measures are displayed to users. In this task, you specify formatting properties for the currency and percentage measures in the Analysis Services Tutorial cube.

To modify the measures of the cube

1.Switch to the Cube Structure tab of Cube Designer for the Analysis Services Tutorial cube, expand the Internet Sales measure group in the Measures pane, right-click Order Quantity, and then click Properties.

2.In window, click the Properties Auto Hide to pin the Properties window open.

It is easier to change properties for several items in the cube when the Properties window remains open.

3.In the Properties window, in the FormatString list, select #,#.

4.On the toolbar of the Cube Structure tab, click Show Measures Grid.

Changing to the grid view lets you select multiple measures at the same time.

5.Select the following measures, by holding down the CTRL key to select multiple measures:

  • Unit Price
  • Extended Amount
  • Discount Amount
  • Product Standard
  • Cost Total
  • Product Cost
  • Sales Amount
  • Tax Amt
  • Freight

The following image shows the Cube Structure tab with these measures selected.

clip_image001

6.In the Properties window, in the FormatString list, select Currency.

7.In the drop-down list box at the top of the Properties window, select the measure Unit Price Discount Pct, and then select Percent in the FormatString list.

8.In the Properties window, change the Name property for the Unit Price Discount Pct measure to Unit Price Discount Percentage.

9.In the Measures pane, click Tax Amt and change the name of this measure to Tax Amount.

Modifying the name of a measure in this manner is the same as changing the Name property for the measure in the Properties window.

10. In the Properties window, click Auto Hide to hide the Properties window, and then click Show Measures Tree on the toolbar of the Cube Structure tab.

11. On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Service Tutorial.

Because you have configured this project to deploy incrementally, only the changes you have made in this project since the previous deployment are deployed to the instance of Analysis Services.

12. When deployment has successfully completed, click the Browser tab of Cube Designer.

BI Development Studio detects that the cube has been updated and prompts you to reconnect to show the updated cube.

13. Click Reconnect on the toolbar of the Browser tab.

The dollar values for each Sales Amount measure are now displayed in the Data pane as currency amounts with the appropriate formatting for your locale.

14. In the Metadata pane, expand Measures, expand Internet Sales, right-click Order Quantity, and then click Add to Data Area.

Notice that this new measure is formatted to a whole number, as shown in the following image.

clip_image002

15. On the File menu, click Save All.

This saves the changes you have made to this point in the Analysis Services Tutorial project, so that you can stop the tutorial here if you want and resume it later.

Modifying the Customer Dimension

There are many different ways that you can increase the user-friendliness and functionality of the dimensions in a cube. In the tasks in this topic, you modify the Customer dimension by deleting unnecessary attributes changing attribute and user-defined hierarchy names, changing user-defined hierarchy properties, and defining user friendly attribute names based on new named calculations in the data source view. You then deploy these changes, process the modified objects, and browse the dimension to view the changes.

Deleting Unused Attributes

Some attributes in the Customer dimension will not be used in the initial Analysis Services Tutorial cube, and can be deleted.

To delete unused attributes

1.Switch to Dimension Designer for the Customer dimension in Business Intelligence Development Studio, and then select the Dimension Structure tab.

2.In the Attributes pane, select the following attributes and delete them:

o Address Line1

o Address Line2

o Country Region Code

o Customer Alternate Key

o First Name

o French Country Region Name

o French Education

o French Occupation

o Last Name

o Middle Name

o Name Style

o Sales Territory Key

o Spanish Country Region Name

o Spanish Education

o Spanish Occupation

o State Province Code

o Suffix

o Title

Modifying User-Defined Hierarchy and Attribute Properties

In addition to deleting unnecessary attributes in a dimension, you can also change attribute names and add or remove attributes from a user-defined hierarchy. By default, levels in a user-defined hierarchy have the same names as the attributes on which they are based. However, you can change the name of a hierarchy level without changing the underlying attribute name.

To modify user-defined hierarchy and attribute properties

1.In the Attributes pane, right-click English Country Region Name and select Rename. Change the name of the attribute to Country-Region.

In Lesson 9 you will learn how to define translation values for cube and dimension metadata. For example, you can change the value that displays for each metadata element so that the value appears in the language that is specified in the client application.

2.Change the names of the following attributes in the same manner:

o English Education attribute — change to Education

o English Occupation attribute — change to Occupation

o State Province Name attribute — change to State-Province

3.In the Hierarchies and Levels pane of the Dimension Structure tab, select the State Province Name – Geography hierarchy. In the Properties window, change the Name property for this user hierarchy to Customer Geography.

The name of the user hierarchy is now Customer Geography.

4.Drag the Country-Region attribute from the Attributes pane into the Customer Geography user hierarchy above the State Province Name level.

The Customer Geography hierarchy now has a Country-Region level.

5.In the Customer Geography user hierarchy, change the name of the State Province Name level to State-Province.

6.Drag the City attribute from the Attributes pane into the Customer Geography user hierarchy above the Customer level.

The Customer Geography user hierarchy now has a City level.

7.Delete Geography in the Customer Geography user-defined hierarchy.

The following image shows the resulting attributes, hierarchies, and levels after you have made the changes specified in this task.

clip_image004

Adding a Named Calculation

You can add a named calculation, which is a SQL expression that is represented as a calculated column, to a table in a data source view. The expression appears and behaves as a column in the table. When you create a named calculation, you specify a name and the SQL expression. Named calculations let you extend the relational schema of existing tables in a data source view without modifying the table in the underlying data source.

To add a named calculation

1. In BI Development Studio, switch to Data Source View Designer to view the Adventure Works DW data source view. (If this data source view is not open as one of the tabs in BI Development Studio, open it by double-clicking the data source view in the Data Source Views folder in Solution Explorer.)

2.In the Tables pane, right-click Customer, and then click New Named Calculation.

3.In the Create Named Calculation dialog box, type FullName in the Column name box, and then type the following CASE statement in the Expression box:

CASE WHEN MiddleName IS NULL THEN FirstName + ‘ ‘ + LastName ELSE FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName END

The CASE statement concatenates the FirstName, MiddleName, and LastName columns into a single column that you will use in the Customer dimension as the displayed name for the Customer attribute.

The following image shows the Create Named Calculation dialog box.

clip_image005

4.Click OK, and then expand Customer in the Tables pane.

The FullName named calculation appears in the list of columns in the Customer table, with an icon that denotes that it is a named calculation.

5.In the Tables pane, right-click Customer (dbo.DimCustomer), and select Explore Data.

6.Review the last column in the Explore DimCustomer Table view.

Notice that the FullName column appears in the data source view, correctly concatenating data from several columns from the underlying data source and without modifying the original data source.

7.Close the Explore DimCustomer Table view.

Using the Named Calculation for Member Names

After you have created a named calculation in the data source view, you can use the named calculation as a property of an attribute, such as the NameColumn property to increase the usability of the attribute by making its name more user-friendly.

To use the named calculation for member names

1.Switch to Dimension Designer for the Customer dimension, and then click the Customer attribute hierarchy in the Attributes pane of the Dimension Structure tab.

Make sure you click the Customer attribute hierarchy and not the Customer dimension object in the Attributes pane.

2.In the Properties window, change the Name property to Full Name.

3.Expand the NameColumn property collection, expand the Source property collection, and then change the ColumnID property from EmailAddress to FullName.

The name of each member of the Customer attribute hierarchy and the name of each member of the Customer level in the Customer Geography user hierarchy will be the customer’s full name instead of the customer’s e-mail address. This change will be visible after you deploy these changes and process the dimension and the cube.

The following image shows the Customer attribute hierarchy and the ColumnID changed to FullName.

clip_image007

4.In the Customer Geography user hierarchy, change the name of the lowest level from Customer to Full Name.

Changing the name of an attribute does not change the name of a level in the hierarchy that is based on the attribute, nor does changing the name of a level in the Hierarchies and Levels pane change the name of the underlying attribute.

Adding an Attribute and Defining Display Folders

You can use display folders to group user and attribute hierarchies into folder structures to increase the user-friendliness of user and attribute hierarchies when users browse the dimension and the cube. Display folders can contain both user and attribute hierarchies.

To add an attribute and define display folders

1.On the Dimension Structure tab for the Customer dimension, drag the EmailAddress column from the Customer table of the Data Source View pane into the Attributes pane.

The Email Address attribute hierarchy is created.

2.In the Attributes pane, select the following attributes by holding down the CTRL key to select multiple attributes, and then in the Properties window, set the AttributeHierarchyDisplayFolder property for the selected attributes to Location:

o City

o Country-Region

o Postal Code

o State-Province

3.In the Hierarchies and Levels pane, click Customer Geography, and then select Location as the value of the DisplayFolder property in the Properties window.

4.In the Attributes pane, select the following attributes by holding down the CTRL key to select multiple attributes, and then set the AttributeHierarchyDisplayFolder for the selected attributes to Demographic:

o Commute Distance

o Education

o Gender

o House Owner Flag

o Marital Status

o Number Cars Owned

o Number Children At Home

o Occupation

o Total Children

o Yearly Income

The following image shows these attributes selected in the Attributes pane.

clip_image009

5.In the Attributes pane, select the following attributes by holding down the CTRL key to select multiple attributes, and then set the AttributeHierarchyDisplayFolder for the selected attributes to Contacts:

o Email Address

o Phone

Deploying Changes, Processing the Objects, and Viewing the Changes

After you have changed attributes and hierarchies, you must deploy the changes and reprocess the related objects before you can view the changes.

To deploy the changes, process the objects, and view the changes

1.On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial.

2.When deployment has successfully completed, click the Browser tab of Dimension Designer for the Customer dimension and then click Reconnect on the toolbar.

3.Verify that Customer Geography is selected in the Hierarchy list, and then in the browser pane expand All, expand Australia, expand New South Wales, and then expand Coffs Harbour.

As shown in the following image, the hierarchy now has a Country-Region level and a City level, and displays the full name of each customer at the Customer level, instead of displaying the e-mail address for each customer.

clip_image011

4.Switch to Cube Designer for the Analysis Services Tutorial cube, click the Browser tab and then click Reconnect on the toolbar.

5.In the Metadata pane, expand Customer.

Notice that instead of a long list of attribute and user hierarchies, only the display folders and the hierarchies that do not have display folder values appear beneath Customer.

Notice that four attribute hierarchies and one user hierarchy appear in this display folder, as shown in the following image.

clip_image013

6.Expand the Location display folder.

7.On the File menu, or on the toolbar of BI Development Studio, click Save All so that you can stop the tutorial here if you want and resume it later.

Modifying the Time Dimension

You can define composite keys for attributes to control the members that are returned by the SELECT DISTINCT processing query. When you use composite keys, the sort order of the attribute members is determined by the order of the members of the composite key. In the tasks in this topic, you change the user-defined hierarchy name, change the member names that are displayed for the Date, Month, Calendar Quarter, and Calendar Semester attributes, define composite keys for the EnglishMonthName, CalendarQuarter, and CalendarSemester attributes, and then modify the order of the composite key members to control the sort order of dimension members.

To modify the user-defined hierarchy

1.Switch to Dimension Designer for the Time dimension in Business Intelligence Development Studio, and then click the Dimension Structure tab.

2.In the Hierarchies and Levels pane, click the CalendarYear – CalendarSemester – CalendarQuarter – EnglishMonthName – FullDateAlternateKey hierarchy, and then change the Name property to Calendar Time in the Properties window.

The name of this user-defined hierarchy is now Calendar Time.

3.In the Calendar Time user-defined hierarchy, change the English Month Name level to Calendar Month and the Full Date Alternate Key level to Date.

4.Switch to Data Source View Designer for the Adventure Works DW data source view, right-click Time (dbo.DimTime) in the Tables pane, and then click New Named Calculation.

5.In the Create Named Calculation dialog box, type SimpleDate in the Column name box, and then type the following SQL script in the Expression box:

DATENAME(mm, FullDateAlternateKey) + ‘ ‘ + DATENAME(dd, FullDateAlternateKey) + ‘, ‘ + DATENAME(yy, FullDateAlternateKey)

This SQL script extracts the year, month, and day values from the FullDateAlternateKey column. You will modify the Date level in the Calendar Time hierarchy in the Time dimension to display the value of this new column instead of the original value that is stored in the FullDateAlternateKey column of the DimTime table.

6.Click OK, and then switch to Dimension Designer for the Time dimension.

7.Click Date in the Calendar Time hierarchy, and then review the properties of the Date level in the Properties window.

Notice that the Date level in the hierarchy is based on the FullDateAlternateKey attribute. In the next steps, you will change the attribute on which this level is based to the TimeKey attribute, and then will modify the properties of the TimeKey attribute so that the names of its members are based on the values in the SimpleDate column that you just defined. You could also leave FullDateAlternateKey as the attribute on which the Date level is based and change the attribute so that its member names are based on the SimpleDate column. However, the first method is more efficient; the TimeKey attribute and the FullDateAlternateKey attribute are not both needed, so you will therefore delete the FullDateAlternateKey attribute.

8.In the Calendar Time user-defined hierarchy, change the value for the SourceAttribute property of the Date level to TimeKey, and then delete the FullDateAlternateKey attribute from the Attributes pane.

9.Select TimeKey in the Attributes pane, expand the NameColumn property collection, and then expand the Source property collection in the Properties window. Change the value for the ColumnID property to SimpleDate.

Notice that the OrderBy property for the TimeKey attribute specifies that the members of the attribute hierarchy are ordered by their key values.

10. On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension and then click Reconnect on the toolbar.

11. Expand the levels in the Calendar Time hierarchy to review the members of the Date level.

Notice that the members of the Date level are more user-friendly than they were before. However, notice also that the semester, quarter, and month members do not indicate their parent year.

The following image shows the members of the Date level.

clip_image015

12. In the Hierarchy list, select EnglishMonthName, and then expand the All member.

Notice that each month appears only once, instead of once for each year in the Time dimension. In the next task in this topic you will generate unique names for each month in each year.

13. Select CalendarQuarter in the Hierarchy list, and then expand the All member.

Notice that each quarter appears only once, instead of once for each quarter in the Time dimension. Notice also that the months are sorted alphabetically. In the next task in this topic you will generate unique names for each calendar quarter in each year. In Lesson 4, you will configure the months to sort chronologically.

To provide unique dimension member names

1.Switch to Data Source View Designer for the Adventure Works DW data source view, right-click Time (dbo.DimTime) in the Tables pane, and then click New Named Calculation.

2.In the Create Named Calculation dialog box, type MonthName in the Column name box, and then type the following SQL script in the Expression box:

EnglishMonthName+’ ‘+ CONVERT(CHAR (4), CalendarYear)

This SQL script concatenates the month and year for each month in the DimTime table into a new column.

3.Click OK.

4.Right-click Time (dbo.DimTime) in the Tables pane, and then click New Named Calculation.

5.In the Create Named Calculation dialog box, type CalendarQuarterDesc in the Column name box, and then type the following SQL script in the Expression 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 DimTime table into a new column.

6.Click OK.

7.Right-click Time in the Tables pane, and then click New Named Calculation.

8.In the Create Named Calculation dialog box, type CalendarSemesterDesc in the Column name box, and then type the following SQL script in the Expression box:

CASE WHEN CalendarSemester = 1 THEN ‘H1’ + ‘ ‘ + ‘CY’ + ‘ ‘ + CONVERT(CHAR(4), CalendarYear) ELSE ‘H2’ + ‘ ‘ + ‘CY’ + ‘ ‘ + CONVERT(CHAR(4), CalendarYear) END

This SQL script concatenates the calendar semester and year for each semester in the DimTime table into a new column.

9.Click OK, and then switch to Dimension Designer for the Time dimension and click the Dimension Structure tab.

You will modify the EnglishMonthName, CalendarQuarter, and CalendarSemester attributes in the Time dimension to use the values in the new columns as member values.

10. In the Attributes pane, select EnglishMonthName. In the Properties window, expand the NameColumn property and then the Source property, and then change the value of the ColumnID property to MonthName.

11. In the same way, change the ColumnID property for the Calendar Quarter attribute and the Calendar Semester attribute to the following:

o Calendar Quarter — change the ColumnID property to CalendarQuarterDesc

o Calendar Semester — change the ColumnID property to CalendarSemesterDesc

12. On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension.

13. On the toolbar of the Browser tab, click Reconnect, and then review the members of the CalendarQuarter attribute hierarchy in the Calendar Quarter attribute hierarchy.

Notice that while the names of the members of the CalendarQuarter attribute hierarchy are more user-friendly, there are still only four members in the attribute hierarchy, instead of a member for each year and quarter combination. If you review the EnglishMonthName or CalendarSemester attribute hierarchies, you will notice similar behavior.

SOLUTION: In the next task in this topic you will modify this behavior by specifying a composite key for these attributes.

To specify composite key values

1.Select the Dimension Structure tab of Dimension Designer for the Time dimension, select the EnglishMonthName attribute, and then click the ellipsis button () in the KeyColumns property cell in the Properties window to modify the value of the attribute.

The DataItem Collection Editor opens to display the member key column for this attribute. When Analysis Services processes a dimension, it issues a SELECT DISTINCT query to determine the members of the dimension. In the DataItem Collection Editor, you can specify a composite key instead of a single column key for the attribute, to return additional dimension members.

2.Click Add to define a second member key for the dimension attribute.

A new member key appears in the members list. You must define the properties of this new key member.

3.Under New Binding (WChar) properties, click the ellipsis button () in the Source property cell to change its value.

The Object Binding dialog box appears. In this dialog box, you either specify an existing column in a specified table or specify the type of column you want to create if you are generating the relational schema by using the top-down design method.

4.In the Binding type list, select Column binding.

5.Verify that Time is selected in the Source table list.

6.In the Source column list, select CalendarYear.

The following image shows the Object Binding dialog box.

clip_image017

7.Click OK to close the Object Binding dialog box, and then click OK again to close the DataItem Collection Editor.

Notice that the value for the KeyColumns property is now set to (Collection).

8.Select the CalendarQuarter attribute, and then click the ellipsis button () in the KeyColumns property cell in the Properties window to modify the value of the attribute.

The DataItem Collection Editor appears.

9.Click Add to define a second member for the KeyColumns property.

10. Under New Binding (WChar) properties, click the ellipsis button () in the Source property cell to change its value.

The Object Binding dialog box appears.

11. In the Binding type list, select Column binding.

12. In the Source table list, verify that Time is selected.

13. In the Source column list, select CalendarYear.

14. Click OK, and then click OK again.

15. Select the CalendarSemester attribute, and then click the ellipsis button () in the KeyColumns property cell in the Properties window to modify the value of the attribute.

The DataItem Collection Editor dialog box appears.

16. Click Add to define a second member for the KeyColumns property.

17. Under New Binding (WChar) properties, click the ellipsis button () in the Source property cell to change its value.

The Object Binding dialog box appears.

18. In the Binding type list, select Column binding.

19. In the Source table list, verify that Time is selected.

20. In the Source column list, select CalendarYear.

21. Click OK, and then click OK again.

22. On the Build menu of BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension.

23. On the toolbar of the Browser tab, click Reconnect, and then review the members of the CalendarQuarter attribute hierarchy in the Calendar Quarter attribute hierarchy.

Notice that while members now exist in the CalendarQuarter 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.

The following image shows the current structure of the CalendarQuarter attribute hierarchy.

clip_image019

24. Review the members of the EnglishMonthName and CalendarSemester 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.

Changing the Sort Order by Modifying Composite Key Member Order

To modify the composite key member order

1.Select the Dimension Structure tab of Dimension Designer for the Time dimension, and then select CalendarSemester in the Attributes pane.

2.In the Properties window, review the value for the OrderBy property.

The members of the CalendarSemester 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 order words, the members of the CalendarSemester attribute hierarchy are sorted first by semester and then by year.

3.In the Properties window, click the ellipsis button () to change the KeyColumns property value.

The DataItem Collection Editor opens.

4.In the Members table of the DataItem Collection Editor, verify that DimTime.CalendarSemester (UnsignedTinyInt) 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 CalendarQuarter in the Attributes pane, and then click the ellipsis button () for the KeyColumns property in the Properties window.

6.In the Members table of DataItem Collection Editor, verify that DimTime.CalendarQuarter (UnsignedTinyInt) 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 quarter.

7.Select EnglishMonthName in the Attributes pane, and then click the ellipsis button () for the KeyColumns property in the Properties window.

8.In the Members table of DataItem Collection Editor, verify that DimTime.EnglishMonthName (WChar) 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 BI Development Studio, click Deploy Analysis Services Tutorial. When deployment has successfully completed, click the Browser tab in Dimension Designer for the Time dimension.

10. On the toolbar of the Browser tab, click Reconnect, and then review the members of the CalendarQuarter and CalendarSemester attribute hierarchies.

Notice that the members of these hierarchies are now sorted in chronological order, by year and then by quarter or semester, respectively.

11. Review the members of the EnglishMonthName 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). However, it would be more useful for the months to be sorted chronologically within each year. You will make this change in Lesson 4.

The following image shows the EnglishMonthName attribute hierarchy.

clip_image021

12. On the File menu of BI Development Studio, click Save All to save the changes you have made to this point in the Analysis Services Tutorial project, so that you can stop the tutorial here if you want and resume it later.

Modifying the Product Dimension

You can improve the user-friendliness of a dimension by using a named calculation to generate descriptive dimension member names, by defining additional user hierarchies, and by specifying the name of the member of the (All) level. You specify the name of the All member for attribute hierarchies separately from the All member name for each user hierarchy. In the tasks in this topic, you define a user hierarchy in the Product dimension, use a named calculation to spell out the product line names, and specify the (All) member name for the attribute hierarchies and for the new user hierarchy. You also delete unnecessary dimension attributes, and group attributes into display folders.

To delete unnecessary attributes

1.In Solution Explorer, double-click Product in the Dimensions node to open Dimension Designer for the Product dimension.

2.In the Attributes pane, select and delete the following attributes:

Arabic Description

Chinese Description

English Description

English Product Name

Finished Goods Flag

French Description

French Product Name

Hebrew Description

Product Alternate Key

Product Subcategory Key

Size Unit Measure Code

Spanish Product Name

Thai Description

Weight Unit Measure Code

These attributes will not be used in the initial Analysis Services Tutorial cube.

To define named calculations, modify name property values, and define a user hierarchy

1.Switch to Data Source View Designer for the Adventure Works DW data source view.

2.In the Diagram pane, right-click the Product table, and then click New Named Calculation.

The Create Named Calculation dialog box opens. You will use this dialog box to create a named calculation that you will use to display the full product line name instead of the cryptic name.

3.In the Create Named Calculation dialog box, type ProductLineName in the Column name box.

4.In the Expression box, type the following SQL script:

CASE ProductLine WHEN ‘M’ THEN ‘Mountain’ WHEN ‘R’ THEN ‘Road’ WHEN ‘S’ THEN ‘Accessory’ WHEN ‘T’ THEN ‘Touring’ ELSE ‘Components’ END

This SQL script creates user-friendly names for each product line in the cube.

5.Click OK.

The ProductLineName named calculation is created.

6.Switch to Dimension Designer for the Product dimension, select Product Line in the Attributes pane of the Dimension Structure tab, and then change the value of the NameColumn property in the Properties window to DimProduct.ProductLineName (WChar), and then click OK.

The members of the Product Line attribute hierarchy will now display the full name of the product line instead of an abbreviated product line name, after you deploy these changes.

7.In the Attributes pane, select the Product attribute hierarchy, and then change the value of the NameColumn property in the Properties window to DimProduct.EnglishProductName (WChar).

8.Change the value of the Name property for the Product attribute to Product Name.

9.Drag the Product Line attribute from the Attributes pane to the Hierarchies and Levels pane.

A new user hierarchy is created that has a default name of Hierarchy.

10. In the Hierarchies and Levels pane, select Hierarchy, and then change the Name property in the Properties window to Product Model Lines.

11. Drag the Model Name attribute from the Attributes pane into the Product Model Lines hierarchy, underneath the Product Line level.

You have defined a second level in the Product Model Lines user hierarchy.

12. Drag the Product Name attribute from the Attributes pane into the Product Model Lines hierarchy, underneath the Model Name level.

You have defined a third level in the Product Model Lines user hierarchy.

To specify the folder and member names

1.In the Attributes pane, select the following attributes:

o Class

o Color

o Days To Manufacture

o Reorder Point

o Safety Stock Level

o Size

o Size Range

o Style

o Weight

2.In the AttributeHierarchyDisplayFolder property cell in the Properties window, type Stocking.

You have now grouped these attributes into a single display folder.

3.In the Attributes pane, select the following attributes:

o Dealer Price

o List Price

o Standard Cost

4.In the AttributeHierarchyDisplayFolder property cell in the Properties window, type Financial.

You have now grouped these attributes into a second display folder.

5.In the Attributes pane, select the following attributes:

o End Date

o Start Date

o Status

6.In the AttributeHierarchyDisplayFolder property cell in the Properties window, type History.

You have now grouped these attributes into a third display folder.

7.Select the Product Model Lines user hierarchy in the Hierarchies and Levels pane, and then change the AllMemberName property in the Properties window to All Products.

8.Click an open area of the Hierarchies and Levels pane, and then change the AttributeAllMemberName property to All Products.

Clicking an open area lets you modify properties of the Product dimension itself. You could also click the Product dimension icon at the top of the attributes list in the Attributes pane.

To review the Product dimension changes

1.On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Services Tutorial.

2.When deployment has successfully completed, switch to the Browser tab of Dimension Designer for the Product dimension. Verify that Product Model Lines is selected in the Hierarchy list, and then expand All Products.

Notice that the name of the All member appears as All Products and that the members of the Product Line level now have user-friendly names, instead of single letter abbreviations.

3.Select Product Line in the Hierarchy list.

Notice that the All member appears as All Products.

4.On the File menu, click Save All to save the changes you have made to this point in the Analysis Services Tutorial project, so that you can stop the tutorial here if you want and resume it later.

In the next task in this lesson, you will review the Analysis Tutorial cube to view all of the changes that you have made in this lesson.

Viewing the Changes in Cube Designer

After you change dimension attributes and hierarchies, you should review the dimensions in the cube to view the cumulative effect of the changes before you continue to develop the solution.

To view the changes in Cube Designer

1.Switch to Cube Designer for the Analysis Services Tutorial cube, select the Browser tab, and then click Reconnect.

In the left pane of the designer, the Metadata pane, expand Product to access the Product Line attribute hierarchy. Notice that the members of the Product Line attribute hierarchy now have user-friendly names.

2.Clear any existing measures and attribute hierarchies from the Data pane, and then remove any dimension members from the Filter pane.

To clear the entire data area at once, click in the data area and then click Clear Results on the toolbar.

3.Add the Sales Amount measure to the data area.

4.In the Metadata pane, expand Product.

Notice that the attribute and user hierarchies are organized into display folders in the Product metadata list.

5.Drag the Product Model Lines user hierarchy to the Drop Column Fields Here area of the Data pane, and then expand the Road member of the Product Line level of this user hierarchy.

Notice that the user hierarchy provides a path to the product name level.

6.In the Metadata pane, expand Customer, expand Location, and then drag the Customer Geography hierarchy from the Location display folder in the Customer dimension to the Drop Row Fields Here area of the Data pane.

7.On the row axis, expand United States to view the sales details by region within the United States.

8.Expand Oregon to view the sales details by city within the state of Oregon.

9.In the Metadata pane, expand Order Date and then drag the Order Date.Calendar Time hierarchy to the Drop Filter Fields Here area of the Data pane.

10. Click the arrow to the right of the Order Date.Calendar Time filter in the Data pane, clear the check box for the (All) level, expand 2002, expand H1 CY 2002, expand Q1 CY 2002, select the check box for February 2002, and then click OK.

Internet sales by region and product line for the month of February, 2002 appear as shown in the following image.

clip_image022

11. On the File menu, click Save All.

Thanks for visiting my blog !!

Hope you have understood Modifying Measures, Attributes and Hierarchies of SSAS Cube and ready to use Smile

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource # 20 : Aug 26 – Aug 28 « Dactylonomy of Web Resource

One thought on “MSBI # 28 – SSAS # 9 – Modifying Measures, Attributes and Hierarchies in SSAS

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: