We have an anomaly where over time the hierarchy of a dimension
changes. With that, we are only allowed to have "1" hierarchy. It is
basically an organization hierarchy where people get promoted, demoted
and change ranks over time. The business requirement is to be able to
see that 1 appropriate hierarchy over time and the hierarchy changes.
I know all about slowly changing dimensions and know how to manage
those. But what about hierarchys. We would like to use a Type 2 but
really need some pointers in how to model this.

Thanks,
Rico

do you use a parent-child dimension?


<ricocali (AT) hotmail (DOT) com> wrote

Type-2 dimensions are actually modelled as inserts. The canonical example of
a type-2 is when a sales person moves from the east coast to the west
coast -- and having ot track the person through a dimension change.

1) create a surrogate key (this allows you to track the person indpendently
when they were on the east coast and the west coast -- if using SQL Server
RDBMS, I typically use an integer w/ IDENTITY turned on as the SK
2) track the SK, the business key (e.g. their badge #), and the sales rep
name in separate fields, The member key is the SK. The member name is the
sales rep name. I typically map the business key (badge# in this case) to a
member property.
3) a changed person (promoted, moved, etc.) is an insert. Same business key,
same name, same other properties other than what changed -- obviously a new
SK value.
4) in your ETL, you need to replace the business key on the incoming fact
record with the proper SK (you mgiht use an effective date to guide the
selection of one or the other SK -- dpending on if the sales is when the rep
was on the east coast or on the west coast).
5) the old sales for when the person was on the east coast are tied to the
old SK (and old member with it's properties); and new sales for the person
on the west coast are tied to the new SK.
6) when you need to see all sales of a sales rep, that is when I use the
business key kept as a member property. Just create a virtual dimension over
top of it and now you can slice by a specific Badge# and see both east coast
sales and west coast sales for the person.

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


<ricocali (AT) hotmail (DOT) com> wrote

Yes the employee dimension uses a recursive table where there is a
parentid column

Dave,

Yes, I understand that and I know how to manage that. But my
situtation is a bit more complex than that. Here's whats happening.
We have an employee dimension that uses a parent-child relationship
where the hierachy is determined by the parentid of the employee.

Here's our challenges. We have a "SALESEMPLOYEE" hierarchy where the
number of levels don't ever change through time. But the sales people
move around that hierarchy often. For example Joe Smith starts out as
a level 1 salesperson. He hits his goal for the month so now he moves
up the tree at level 2. While Joe Smith is moving up the rung, Jack
Doe moves down. Salespeople can move up the "SALESEMPLOYEE" hierarchy
at any given month.

The business requirements wants only "1" hierarchy but as I move across
time in the cube (AS) they want to see the changes in the
"SALESEMPLOYEE" hierarchy.


Rico

Dave Wickert [MSFT] wrote:

maybe you have to create 2 dimensions.
1 Type 2 dimension with historical information about the employee
(employeeA) (non hierarchical table; temporal change tracking active)
the second which is a Type 2 but only for parent/child links. (employeeB)
(not a temporal table, just hierarchical)

In this case, the employeeB table will contain only 1 row by employee /
parent combination, if an employee come back to the same parent, then you'll
use the same surrogate key.

your fact table will contain 2 keys, 1 to employeeA the second to employeeB
So John Smith could have 10 rows in the table employeeA while there is only
2 rows in employeeB

there is other options available.

<ricocali (AT) hotmail (DOT) com> wrote