Jun
11
PeopleSoft EPM Dimension NULL records
After successfully loading compensation mart I had to help bring up the rest of the marts up to speed. The most common data issue the rest of the facts were experiencing was dimensions that weren’t being implemented like PS_D_GOV_POI.
The PS_POSITION_DATA.GVT_POI and PS_POSITION_DATA.GVT_SUB_AGENCY columns in HCM are being defaulted to a blank space by our conversion programs which is kind of fine since there’s no relational integrity constraints at the database level, so if the position conversion program defaults the fields to a blank space or a value that does not exist on PS_GVT_POI_TBL or PS_GVT_POI_TBL no warnings or errors are raised.
Now this is not fine on the warehouse, the blank spaces are converted to dashes (‘-’) by the dimension load programs and since PS_GVT_POI_TBL contains no rows PS_D_GOV_POI is an empty dimension.
The solution to all these issues is very simple, all dimensions that are NOT being implemented should contain what I’m going to call “NULL Rows” for lack of a better term. These NULL Rows have code values in the dimension that match the default values used for the conversion program so that joins like the following would work:
TRANSACTION:
SELECT * FROM PS_POSITION_DATA, PS_GVT_SUBAGCY_TBL WHERE PS_POSITION_DATA.GVT_SUB_AGENCY=PS_GVT_SUBAGCY_TBL.GVT_SUB_AGENCY
WAREHOUSE:
SELECT *
FROM PS_GVT_SUBAGCY_TBL,PS_D_GOV_SUBAG
WHERE PS_D_GOV_SUBAG.GOV_SUBAG_CD=PS_GVT_SUBAGCY_TBL.GVT_SUB_AGENCY
AND PS_GVT_SUBAGCY_TBL.COMPANY=’<your company code here>’
