Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

Go down

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

Post  obiapps 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  obiapps Fri Oct 01, 2010 1:48 am

Thank you.

obiapps

Posts : 21
Join date : 2010-09-28

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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