I'm a little stumped with this issue, where some element attributes in a dimension would not be populated. This issue appears to occur randomly and sporadically throughout the hierarchy, as I can't seem to find a definitive pattern that would cause this. [1_wo_missing_attibute.JPG]
The weird thing is, the source data has all the available data, and the TI process that refresh the dimension completes without errors. [2_wo_db_source.JPG]
Did a few tests with the TI queries without success, so I updated the TI process' source query to only bring back the subset of elements with the issue, and viola the dimension refreshed with all attributes populated for this subset!
Weirder still, after doing the above and reverting the TI process back to the original SQL and reran the process, the elements with missing attributes are now fixed! But there are still elements with missing attributes further down the hierarchy. [3_wo_fixed.JPG]
The dimension itself has about 230k+ elements, which shouldn't really be an issue? As there are other dimensions with 1m+ elements and TM1 populated the dimension attributes OK.
I've also attached the TI process, the Data tab that populate the attributes is fairly standard.
Has anyone had similar issue like this before? I couldn't find anything in the forum...so any help or pointers would be greatly appreciated!
Cheers.
Ray
Prolog:
Code: Select all
#########################
# Declare Variables
#########################
cDimName='Work Order';
UnkWO = 'Unknown WO';
vALL='ALL WORK ORDERS';
#########################
# Unwind the dimension
#########################
ExecuteProcess('_System - Unwind Dimension', 'pDimName', cDimName);
#########################
# Insert top parent and unknown element
#########################
IF(DIMIX(cDimName, vALL)=0);
DIMENSIONELEMENTINSERT(cDimName,'',vALL,'C');
ENDIF;
IF(DIMIX(cDimName, UnkWO)=0);
DIMENSIONELEMENTINSERT(cDimName, '', UnkWO, 'N');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vALL, UnkWO, 1);
Code: Select all
IF(DIMIX(cDimName, vWONUM)=0);
DIMENSIONELEMENTINSERT(cDimName,'', vWONUM, 'N');
ENDIF;
##############################################
# RO hierarchy
# BU > Category > Work Type > Region > Service > Suburb
##############################################
IF(vBU@='RO');
IF(DIMIX(cDimName, vMaintDeptSuburb)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vMaintDeptSuburb, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vMaintDeptSuburb, vWONUM, 1);
IF(DIMIX(cDimName, vHierarchyMaintDept)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyMaintDept, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyMaintDept, vMaintDeptSuburb, 1);
IF(DIMIX(cDimName, vHierarchyRegion)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyRegion, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyRegion, vHierarchyMaintDept, 1);
IF(DIMIX(cDimName, vHierarchyType )=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyType, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyType, vHierarchyRegion, 1);
IF(DIMIX(cDimName, vHierarchyCategory)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyCategory, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyCategory, vHierarchyType, 1);
IF(DIMIX(cDimName, vBU)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vBU, 'C');
ENDIF;
ENDIF;
##############################################
# Remaining BU hierarchy
# BU > Category > Work Type > Region > Service > Suburb
##############################################
IF(vBU@<>'RO');
IF(DIMIX(cDimName, vHierarchySuburb)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchySuburb, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchySuburb, vWONUM, 1);
IF(DIMIX(cDimName, vHierarchyNetwork)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyNetwork, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyNetwork, vHierarchySuburb, 1);
IF(DIMIX(cDimName, vHierarchyService)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyService, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyService, vHierarchyNetwork, 1);
IF(DIMIX(cDimName, vHierarchyRegion)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyRegion, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyRegion, vHierarchyService, 1);
IF(DIMIX(cDimName, vHierarchyType )=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyType, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyType, vHierarchyRegion, 1);
IF(DIMIX(cDimName, vHierarchyCategory)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vHierarchyCategory, 'C');
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vHierarchyCategory, vHierarchyType, 1);
IF(DIMIX(cDimName, vBU)=0);
DIMENSIONELEMENTINSERT(cDimName, '', vBU, 'C');
ENDIF;
ENDIF;
DIMENSIONELEMENTCOMPONENTADD(cDimName, vBU, vHierarchyCategory, 1);
DIMENSIONELEMENTCOMPONENTADD(cDimName, vALL, vBU, 1);
Code: Select all
vAlias = vWONUM |' : '| vDESCRIPTION;
#########################
# Update dimension attributes
#########################
ATTRPUTS(vAlias, cDimName, vWONUM, 'Code and Description' );
ATTRPUTS(vLOCATION, cDimName, vWONUM, 'Location');
ATTRPUTS(vASSET, cDimName, vWONUM, 'Asset Number');
ATTRPUTS(vREGION, cDimName, vWONUM, 'Region');
ATTRPUTS(vSTATUS, cDimName, vWONUM, 'Status');
ATTRPUTS(vGL, cDimName, vWONUM, 'GL Account');
ATTRPUTS(vAssetGL, cDimName, vWONUM, 'Asset GL Account');
ATTRPUTS(vBU, cDimName, vWONUM, 'Business Unit');
ATTRPUTS(vService, cDimName, vWONUM, 'Service');
ATTRPUTS(vSuburb, cDimName, vWONUM, 'Suburb');
ATTRPUTS(vNetwork, cDimName, vWONUM, 'Network Service');
