Organization Hierarchy

Purpose

The Organization Hierarchy CDP contains current and historical information on all managerial chains within the organization, starting from the CEO and extending down through all levels of management. This CDP is especially useful for answering detailed questions about how employees are structured within divisions and managers, and it should be used in conjunction with the device and software application CDPs to provide a holistic view of group-level analytics.

Understanding the Table

In this CDP, Level 1 will always represent the CEO for every row, with all managers listed above the row’s user_id. This hierarchical structure clearly illustrates the number of managerial levels between the CEO and any given employee.

Whenever there is a change in the managerial chain, a new row is created for all affected users. This means that if a high-level manager's position changes, all employees below that manager in the hierarchy will have a new row added to the table. This approach ensures that the historical context of managerial changes is preserved, allowing for accurate tracking of the organization's evolving structure over time.

Joining to CDPs and OCSF

The row’s user_id should be used for all CDP and OCSF joins.

To identify and retrieve all current manager chains, one should use the active column in conjunction with checking if the end_time column is not null. By doing so, you can effectively filter out inactive records and focus on the relevant, up-to-date management structures.

Schema

Example Queries

Find the latest record for a given id

SELECT * FROM organization_hierarchy WHERE id={id} and active=TRUE

Find all active entities and their latest row

SELECT * FROM organization_hierarchy WHERE active=TRUE

Find all direct reports for a manager

SELECT * FROM user where manager_id ={id}

CDP Data Dictionary

Caption	Name	Requirement	Type	Description
End Time	end_time	optional	timestamp_t	User.end_time: The end time of when a particular state of the user was valid. Using the `start_time` and `end_time` together bound the time when a particular user state was valid. If there is no `end_time` it tells the analyst that this is the current state of the user as DataBee understands it. There will ever only be a single user for which the `end_time` is `null`.
History ID	id	required	integer_t	User.hid: The unique DataBee ID to identify a particular history entry in an object table. This field is the PK and should not be mapped manually as the DataBee product populates this field itself. User.id: The unique identifier used by DataBee for a specific user. This will be logged as `user_id` in activity tables to link to a particular user in this table. This field should not be mapped manually as the DataBee product populates this field itself.
Level 1 Manager ID	level_1	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 10 Manager ID	level_10	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 11 Manager ID	level_11	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 12 Manager ID	level_12	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 13 Manager ID	level_13	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 14 Manager ID	level_14	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 15 Manager ID	level_15	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 16 Manager ID	level_16	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 17 Manager ID	level_17	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 18 Manager ID	level_18	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 19 Manager ID	level_19	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 2 Manager ID	level_2	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 20 Manager ID	level_20	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 3 Manager ID	level_3	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 4 Manager ID	level_4	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 5 Manager ID	level_5	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 6 Manager ID	level_6	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 7 Manager ID	level_7	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 8 Manager ID	level_8	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Level 9 Manager ID	level_9	optional	integer_t	User.manager: The user's manager. This helps in understanding an org hierarchy. This should only ever be populated once in an event. I.e. there should not be a manager's manager in an event.
Record Created At	record_created_at	required	timestamp_t	CDPs generated timestamp when record was created.
Record Updated At	record_updated_at	required	timestamp_t	CDPs generated timestamp when record was last updated.
Start Time	start_time	optional	timestamp_t	User.start_time: The start time when a particular state of the user became valid.
User ID	user_id	required	integer_t	User.id: The unique identifier used by DataBee for a specific user. This will be logged as `user_id` in activity tables to link to a particular user in this table. This field should not be mapped manually as the DataBee product populates this field itself.