Parent Department ID from DEPARTMENTS table is not available in DWH_FIN_DEPARTMENT table

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

The DWH table DWH_FIN_DEPARTMENT doesn't contain the column for Parent Department. How to get this column into DWH as it can't be DWH enabled.

Environment

Release : 16.1.1

Resolution

We can't include the Parent Department into DWH from the Department object. You can however create a lookup attribute with the following query.

SELECT @SELECT:q.ID:ID@,
@SELECT:q.parent:parent@,
@SELECT:q.last_updated_date:last_updated_date@
FROM
(WITH RECURSIVE DEPT AS(
    SELECT ID,departcode, null::varchar as parent,last_updated_date from departments where parent_department_id is null
    union
    select d.id,d.departcode,d1.departcode,d.last_updated_date from departments d
    join DEPT d1 on d.parent_department_id=d1.id
) Select id,parent,last_updated_date from Dept) Q

Once the attribute is included into DWH and full load is run, a lookup table such DWH_LKP_<ATTRIBUTE_CODE> is created which has the Parent associated with a particular department from DWH_FIN_DEPARTMENT table.