Database Reference
In-Depth Information
Creating parent-child dimensions
Business models often include entities that have a large number of levels. If you work
for a large corporation, you don't have to think very long to come up with an example
of a drawn-out hierarchy, simply refer to your organizational chart. You're likely to have
entry-level employees who report to first-level managers, who in turn report to several
other levels of managers, followed by directors, senior directors, vice presidents, and
so on. The exact titles and number of levels in the food chain might vary, but you get
the idea. Such structures are by no means limited to human resources; many organ-
izations could have buildings that roll up to facilities that in turn roll up to networks and
so on. The main challenge is that of modeling the large and unpredictable number of
levels in a single dimension. If the number of levels in a hierarchy varies, the object is
known as a ragged hierarchy . For example, in a given enterprise, there might be five
levels of managers between John and the Chief Executive Officer ( CEO ) but only
four levels between Jane and the CEO; even though both Jane and John are entry-
level associates. If the number of levels is unknown or if it could change over time, it's
best to use the Analysis Services parent-child hierarchy feature.
We build parent-child hierarchies upon self-referencing entities, meaning the dimen-
sion table will contain a key column and another column that references the key
column. For example, the Adventure Works 2012 sample database has the DimEm-
ployee table, which contains EmployeeKey and ParentEmployeeKey columns.
The ParentEmployeeKey column refers to the EmployeeKey column because the
parent employee (manager) is also employed by the same company and unless she
is a CEO, she's also likely to have a manager. When we add this table to the DSV,
Analysis Services automatically detects that the entity is self-referencing—the Par-
entEmployeeKey column refers to EmployeeKey .
How to do it...
Let's learn how to create a parent-child dimension using the following steps:
1. Invoke the Dimension Wizard as you would with a regular dimension. Select
the Use Existing Table option.
2. Specify DimEmployee as the source table, EmployeeKey as the key column,
and FullName as the name column. Refer to the Creating named calculations
Search WWH ::




Custom Search