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