question on handling unassigned records for type 2 SCD for Organization Hierarchy
2 posters
Page 1 of 1
question on handling unassigned records for type 2 SCD for Organization Hierarchy
Hi, this forum is very insightful. I am implementing a packaged BI solution for an HR system and have the following question. I created a dimensional hierarchy table for the Manager Hierarchy (this is a type 2 SCD which includes effective from/to dates as well as a CURRENT indicator). Essentially, this DImensional Hierarchy table is a flattened view of the hierarchy in the source system and joins to a separate Organization DImension table (on the column Department ID), which in turn joins to a Event based Fact table. Due to issues in the source system, there are records (events) that do not have an assigned organization (i.e they are mapped to a zero "unassigned" row in the Org Dimension table. These events are not being captured in queries that use my custom Manager Hierarchy table as these events dont have a valid Organization assigned.
What is the best model to accommodate this scenario? Ideally, I would like to use the Manager Hierarchy but still show events that do not have a valid department. From what I understand, it is not advised to have a "unassigned" row for a flattened dimension hierarchy table that is type 2 enabled. Not sure if I have clearly described the issue. Any thoughts are appreciated.
What is the best model to accommodate this scenario? Ideally, I would like to use the Manager Hierarchy but still show events that do not have a valid department. From what I understand, it is not advised to have a "unassigned" row for a flattened dimension hierarchy table that is type 2 enabled. Not sure if I have clearly described the issue. Any thoughts are appreciated.
obiapps- Posts : 21
Join date : 2010-09-28
Re: question on handling unassigned records for type 2 SCD for Organization Hierarchy
There is nothing to stop you from doing so, as long as it has meaning to the business, is labeled properly, and understood. Just include an unassigned node to the hierarchy.
obiapps- Posts : 21
Join date : 2010-09-28
Similar topics
» Modelling, level of hierarchy, employee and organization structure
» Database design for Organization hierarchy for collecting data at different level for creating metrics
» ORG HIERARCHY-TYPE 2
» How to handle SCD type 2 changes - when source records are archived?
» Handling records in Fact when dimension is Type 2
» Database design for Organization hierarchy for collecting data at different level for creating metrics
» ORG HIERARCHY-TYPE 2
» How to handle SCD type 2 changes - when source records are archived?
» Handling records in Fact when dimension is Type 2
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum