question on handling unassigned records for type 2 SCD for Organization Hierarchy

View previous topic View next topic Go down

question on handling unassigned records for type 2 SCD for Organization Hierarchy

Post  obiapps on Tue Sep 28, 2010 8:57 pm

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.

obiapps

Posts : 21
Join date : 2010-09-28

View user profile

Back to top Go down

Re: question on handling unassigned records for type 2 SCD for Organization Hierarchy

Post  ngalemmo on Wed Sep 29, 2010 12:42 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: question on handling unassigned records for type 2 SCD for Organization Hierarchy

Post  obiapps on Fri Oct 01, 2010 1:48 am

Thank you.

obiapps

Posts : 21
Join date : 2010-09-28

View user profile

Back to top Go down

Re: question on handling unassigned records for type 2 SCD for Organization Hierarchy

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum