Database Reference
In-Depth Information
Analysis Services can build a special kind of hierarchy from this type of table called a
parent/child hierarchy, and will do so automatically if you run the
New Dimension
Wizard
on a table with the appropriate relationship defined in the DSV and build
attributes from the key and parent key columns. Once we've done this, we'll have a
dimension with two attribute hierarchies:
• The key attribute built from the primary key of the table, which is a flat list of
all the members in the table.
•
The attribute built from the parent key column, which is the parent/child
hierarchy and has the same members but is organized into a ragged hierarchy.
You'll see that the
Usage
property of this attribute has been set to
Parent
.
Typically, we will set the
AttributeHierarchyVisible
property of the key attribute
to
False
and only let users see the parent/child hierarchy in the dimension.
We can give the levels on a parent/child hierarchy names by setting
the
NamingTemplate
property. This property takes the form of a
semicolon delimited list of names, which are used as the names of
levels, starting from the top of the hierarchy: asterisks in the names
are replaced with the ordinal of the level. Thankfully, there is a dialog
box that allows you to construct these strings easily.
There is, unfortunately, a long list of limitations when it comes to using
parent/child hierarchies:
• You can only have one parent/child hierarchy per dimension.
• A parent/child hierarchy has to be built from the key attribute of
the dimension.
• You cannot use a parent/child hierarchy as a level in a user hierarchy.
• Dimensions with parent/child hierarchies are slower to process than
those without.
• Parent/child hierarchies can be very difficult to work with when writing
MDX calculations and queries because they do not obey the same rules as
regular hierarchies. Scoped assignments, auto-exists, and any calculation that
uses the
Currentmember
function are a particular problem.
•
The unique names of parent/child hierarchies include surrogate key values,
which as we've already noted, can be a problem if you need to reload the
dimension table.
Search WWH ::
Custom Search