I've got a flattened employee hierarchy like this (DIM_EMP_HIERARCHY):
Hierarchy Key | AlternateKey | Node 0 | Node 1 | Node 2....Node15
1 | 1 | BigBoss | Null | Null
2 | 3 | BigBoss | Manager| Pleb
3 | 1031 | BigBoss | Manager | Another Pleb
4 | 2674 | BigBoss | Manager | NULL
When I process the Employee Dimension, I get the following error:
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DIM_EMP_HIERARCHY', Column: 'Node2', Value: ''. The attribute is 'Node2'.
I know that the value '' is given as the substitute for the nulls, and I have read the articles below, which talk about how it is null, and the suggestion is 'just avoid null'. However, I need to have a SCD, so cannot do a self referencing table.
I have also tried setting the errors parameter to not showing. However, this makes the hierarchy incomplete.
Can anyone please shed any light on how I can get my dimension processing?