I was trying to get the OBS mapping for Resource_department_key and Investment_Department_key from DWH_FIN_TRANSACTION table. But instead of OBS_UNIT_ID Resource department key and Investment Department key is mapped with ID from the DEPARTMENT table from CA PPM database. Since the ID is not in the warehouse I am not able to map the department's parent OBS. Because of this issue I am not able to aggregate actuals at resource department level. Is there any other way to get department ID in the warehouse?
Thanks Prashank , that is a good suggestion we have tried that in couple of our jasper soft reports. But performance gets affected especially if you we try to map a large dataset like the financial transaction tables.
Having said that I want to confirm that mapping in DWH_FIN_TRANSACTION table is incorrect and instead of ID we need to map that table with OBS_UNIT_ID.
You can try DWH to CA PPM Database link to access CA PPM Tables, we have used this in some of our jasper reports, see if that works for you.
Regards,
Prashank Singh