Modifying
the Product Dimension
In the tasks in this topic, you use a named calculation to
provide more descriptive names for the product lines, define a hierarchy in the
Product dimension, and specify the (All) member name for the hierarchy. You
also group attributes into display folders.
1.
To open the Adventure Works DW 2012 data source view, double-click Adventure Works
DW 2012 in the Data Source Views folder in Solution Explorer.
2.
In the bottom of the diagram pane, right-click the Product table header, and then click New Named
Calculation.
3.
In the Create Named Calculation dialog box, type ProductLineName in theColumn name box.
4.
In the Expression box,
type or copy and paste the following CASE statement:
|
This CASE statement
creates user-friendly names for each product line in the cube.
5.
Click OK to
create the ProductLineName named calculation. You might need to
wait.
6.
On the File menu,
click Save
All.
1.
Switch to Dimension Designer for the Product
dimension. To do this, double-click theProduct dimension in the Dimensions node of Solution Explorer.
2.
In the Attributes pane
of the Dimension
Structure tab, select Product Line.
3.
In the Properties window on the right side of the
screen, click the NameColumnproperty field at the bottom of the window,
and then click the browse (…) button to open
the Name
Column dialog box.
(You might need to click the Properties tab
on the right side of the screen to open the Properties window.
4.
Select ProductLineName at
the bottom of the Source column list,
and then clickOK.
The NameColumn field now contains the text, Product.ProductLineName
(WChar). The members of the Product Line attribute
hierarchy now display the full name of the product line instead of an
abbreviated product line name.
5.
In the Attributes pane
of the Dimension
Structure tab, select Product
Key.
6.
In the Properties window, click the NameColumn property field, and then click the
ellipsis browse (…) button to open the Name Column dialog box.
7.
Select EnglishProductName in
the Source
column list, and then
click OK.
The NameColumn field now contains the text, Product.EnglishProductName
(WChar).
8.
In the Properties window, scroll up, click the Name property field, and then typeProduct Name.
1.
Drag the Product Line attribute
from the Attributes pane into the Hierarchiespane.
2.
Drag the Model Name attribute
from the Attributes pane into the <new level> cell in the Hierarchies pane, underneath the Product Line level.
3.
Drag the Product Name attribute
from the Attributes pane into the <new level>cell
in the Hierarchies pane, underneath the Model Name level. (You renamed Product Key to
Product Name in the previous section.)
4.
In the Hierarchies pane
of the Dimension
Structure tab,
right-click the title bar of the Hierarchy hierarchy,
click Rename,
and then type Product
Model Lines.
The name of the hierarchy is now Product Model
Lines.
5.
On the File menu,
click Save
All.
1.
In
the Attributes pane, select the following attributes
by holding down the CTRL key while clicking each of them:
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 field 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, typeFinancial.
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, typeHistory.
You have now grouped
these attributes into a third display folder.
7.
Select
the Product
Model Lines hierarchy
in the Hierarchies pane, and then change the AllMemberName property in the Properties window to All Products.
8.
Click
an open area of the Hierarchies pane,
and then change theAttributeAllMemberName property at the top of the Properties
window to All
Products.
Clicking an open area
lets you modify properties of the Product dimension itself. You could also
click Product at the top of the attributes list in
the Attributes pane.
9.
On
the File menu, click Save All.
If the underlying data supports it, you should define attribute
relationships between attributes. Defining attribute relationships speeds up
dimension, partition, and query processing.
1.
In
the Dimension
Designer for the
Product dimension, click the Attribute Relationships tab.
2.
In
the diagram, right-click the Model Name attribute,
and then click New
Attribute Relationship.
3.
In
the Create
Attribute Relationship dialog
box, the Source
Attribute is Model Name.
Set the Related
Attribute to Product Line.
In the Relationship type list, leave the relationship type set
to Flexible because relationships between the
members might change over time. For example, a product model might eventually
be moved to a different product line.
4.
Click OK.
5.
On
the File menu, click Save All.
1.
On
the Build menu of SQL Server Data Tools (SSDT),
click Deploy
Analysis Services Tutorial.
2.
After
you have received the Deployment Completed Successfully message, click the Browser tab of Dimension
Designer for the Product dimension, and then click the
Reconnect button on the toolbar of the designer.
3.
Verify
that Product
Model Lines is
selected in the Hierarchy list,
and then expandAll Products.
Notice that the name of
the All member appears as All Products.
This is because you changed the AllMemberName property
for the hierarchy to All Productsearlier in the lesson. Also, the members
of the Product
Line level now have
user-friendly names, instead of single-letter abbreviations.
No comments:
Post a Comment