Parent-Child Dimension usage

Parent-child dimension is characterized by self-reference, data hierarchy, table schema is characterized by the existence of a field and its own foreign key relationship, such as employee table emph (Id,parentid, .... )。

Is AdventureWorksDW2012 in the DimEmployee, semantics is an employee's leader, is also employee.

1, create a dimemployee dimension, create only two properties

View attribute Usage,employeekey is key, and Parentemployeekey is parent.

2, parent-child dimensions automatically create Parentemployeekey as a user-defined hierarchy, rather than an attribute hierarchy.

3, an icon that adds three properties of an attribute Title,dimemployee dimension is different, in browser, you can see the title hierarchy, and the hierarchy created by SSAS for title is an attribute hierarchy.

4, to see the properties of title, there are three important attributes in advanced

Attributehierarchyenable: Is the attribute hierarchy that enables the property title, and if set to False, the attribute hierarchy is not created.

AttributeHierarchyVisible: Indicates whether the attribute hierarchy is visible, if the attribute hierarchy of the property title is enabled, but Attributehierarchyvisible=false, The attribute hierarchy for this property is not seen in Borwser.

DefaultMember: The default member, if DefaultMember is not set, and Isaggregatable=true, then the system automatically generates an all member as the default member of hierarchy. If Isaggregatable=false, then the system does not automatically generate an all member, the system will hierarchy the first member as the default member of hierarchy

IsAggregatable: Whether it is aggregatable. If it is possible to aggregate, SSAS automatically generates a member named All, which represents the aggregation of all values for that property. If Isaggregatable=false, the first value of the property is used as the query condition to be aggregated.

Understanding of 5,isaggregatable Properties

The isaggregatable of title is set to Ture, and by Borwser view, there are all members and all level.

Set the isaggregatable of title to False, viewing through borwser, without generating the all member and All level.

5,attributehierarchyvisible Property

The reason that the EmployeeKey attribute hierarchy is not visible in Borwser is Attributehierarchyvisible=false

6,value column displays the value of the member, if not set, the default value is Name column.

KeyColumns is generally designed as an int value, only as primary Key, which plays a unique role in identification.

NameColumn easy to read

ValueColumn is the value of a member and is capable of doing certain operations.

How to understand Keycolumns,namecolumn and valuecolumn? A row of records have a different understanding, but are the same row of records, for the type of int, occupy a small storage space, easy to aggregate, suitable for doing primary key, but difficult to read, char type of data, easy to read, suitable as the name Column, for a specific type of data, Its value must be able to do some operations, such as the date type, to perform operations such as year (), DATEADD (), which can be done through valuecolumn.

Example, a table record (Id,name,value) with one row of data (1,20150201,1/2/2015)

So id=1 is keycolumn,name=20150201 is namecolumn,value=1/2/2015 is ValueColumn

7, parent-child dimension-specific attributes that are unique to the dimension attribute of attribute usage=parent.

7.1 Property RootMemberIf

Determine whether a node is the basis of the root node, the value of the property is assigned to ParentIsBlankSelfOrMissing, that is, when a member of the parent property value is empty, is self, or does not exist, this member is the root node.

7.2 MembersWithData whether the non-leaf nodes are displayed in the child nodes.

The default value is NonLeafDataVisible, which has a self in the list of child nodes for each node.

By assigning the value of the MembersWithData property to NonLeafDataHidden, the non-leaf node is not displayed.

7.3 Memberwithdatacaption

Under a non-leaf node, add a caption to the node to differentiate the non-leaf nodes.

For example, memberwithdatacaption=senior_*,* Represents the value of the current member

7.4 NamingTemplate, actually level naming Template

Define a named template for the level of the non-leaf node, the first leval is named All, and cannot be changed, starting with Level2, the default is level + number.

The level naming template can be used to rewrite the named templates for level, such as Level2 named Boss_level,,,,

Parent-Child Dimension usage