TM1 9.4 introduced metadata logging but after having a look at it in test its far from what we are looking for, so I’ve created some TIs to do the job instead. We don’t plan to upgrade for a while yet, so this is a good solution for anyone not on 9.4 already.
Basis: to compare a “live†and “archive†version of a dim and report on the differences.
Setup:
Manually create a dimension that is to be the “archive†version of the dim. Decide on a naming convention to help locate them easily, I’ve used an underscore for a prefix (ie. "Resource Breakdown" and "_Resource Breakdown"). Insert any element in the dim otherwise it won’t allow you to save it.
nb: in the following code i've left my dim names in for clarity, obviously you need to change them to suit your own needs. Anything in PURPLE are values you need to alter.
TIs:
TI NAME: _Audit 1a - Resource Breakdown
No datasource, only code is in the PROLOG
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
DIMENSIONDELETEALLELEMENTS('_Resource Breakdown');
TI NAME: _Audit 1b - Resource Breakdown
Datasource: Dim Subset "All" (should be created by default) against the LIVE version of your dim
Variables: "v1" set as string / other
PROLOG
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
LiveDimName = 'Resource Breakdown';
ArcDimName = '_Resource Breakdown';
METADATA
#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****
count = 1;
IF ( ELPARN ( LiveDimName , v1 ) = 0 );
DIMENSIONELEMENTINSERT(ArcDimName ,'',v1,'n');
Else;
MaxCount = ELPARN ( LiveDimName , v1 );
While ( MaxCount >= Count );
Parent = ELPAR ( LiveDimName , v1 , count );
DIMENSIONELEMENTINSERT( ArcDimName ,'',v1,'n');
DIMENSIONELEMENTINSERT( ArcDimName ,'',parent,'c');
Heavy = ELWEIGHT( LiveDimName , Parent, v1);
DIMENSIONELEMENTCOMPONENTADD( ArcDimName , parent, v1, Heavy );
count = count + 1;
End;
EndIF;
TI NAME: _Audit 1c - Resource Breakdown
No datasource
Only code required is in the PROLOG tab
#SYSTEM DEFINED
DateStamp = DATE ( NOW , 1 );
nFile = 1;
#USER DEFINED
LiveDimName = 'Resource Breakdown';
ArcDimName = '_Resource Breakdown';
FileOut = '\\Glkas1026v\tm1data\' | LiveDimName | '_' | DateStamp | '.txt';
#START OF AUDIT CODE
#loop thru the whole archived version of the dim
dMax = DIMSIZ ( ArcDimName );
dN = 1;
WHILE ( dN <= dMax );
v1 = DIMNM ( ArcDimName , dN );
#################################################
#set headers on output file
IF ( nFile = 1 );
ASCIIOUTPUT ( FileOut , 'Source_Element' , 'Change_Type' , 'Change_Description' );
nFile = nFile + 1;
ENDIF;
#locate the index of the current element in the other dimension
xFind = DIMIX ( LiveDimName, v1);
#element has been deleted
IF ( xFind = 0 );
ASCIIOUTPUT ( FileOut , v1 , 'Deleted' , 'element ' | v1 | ' deleted from dimension ' | LiveDimName );
EndIF;
#count children of element on both versions of the dim
xChildren = ELCOMPN ( LiveDimName , v1 );
yChildren = ELCOMPN ( ArcDimName , v1 );
#report old that was nodal but new is now consolidation (you will have lost data!)
IF ( xChildren > 0 & yChildren = 0 );
ASCIIOUTPUT ( FileOut , v1 , 'To Consolidation' , 'element ' | v1 | ' changed from nodal to consolidation in ' | LiveDimName);
EndIF;
#report old that was consolidation but new is now nodal
IF ( xChildren = 0 & yChildren > 0 );
ASCIIOUTPUT ( FileOut , v1 , 'To Nodal' , 'element ' | v1 | ' changed from consolidation to nodal in ' | LiveDimName );
EndIF;
#everything else
IF ( xChildren > 0 & yChildren > 0 );
yC = 1;
#loop thru entire archived version of the dim
WHILE ( yC <= yChildren );
xC = 1;
#internal loop of the live dim for each entry in the archive dim
WHILE (xC <= xChildren );
#positive check for no change in hierarchy - do nothing and break out of loop
xChildName = ELCOMP ( LiveDimName , v1, xC );
yChildName = ELCOMP ( ArcDimName , v1 , yC );
IF ( xChildName @= yChildName );
#break out of loop and set found marker
xC = xChildren + 1;
xFound = 'T';
ELSE;
#set flag for confirming the element cannot be found
xC = xC + 1;
xFound = 'F';
ENDIF;
END;
#report elements that have been moved out of a consolidation compared to the archive version
IF ( xFound @= 'F' & DIMIX ( LiveDimName , yChildName ) > 0 );
ASCIIOUTPUT ( FileOut , yChildName , 'Move Out' , 'element ' | yChildName | ' removed from consolidation ' | v1 | ' in ' | Li
veDimName );
ENDIF;
yC = yC + 1;
END;
ENDIF;
########
dN = dN + 1;
END;
#loop thru the whole live version of the dim
dMax = DIMSIZ ( LiveDimName );
dN = 1;
WHILE ( dN <= dMax );
v1 = DIMNM ( LiveDimName , dN );
#################################################
#locate the index of the current element in the other dimension
yFind = DIMIX ( ArcDimName, v1);
#element has been added
IF ( yFind = 0 );
ASCIIOUTPUT ( FileOut , v1 , 'Added' , 'element ' | v1 | ' added to dimension ' | LiveDimName );
EndIF;
#count children of element on both versions of the dim
xChildren = ELCOMPN ( LiveDimName , v1 );
yChildren = ELCOMPN ( ArcDimName , v1 );
#everything else
IF ( xChildren > 0 & yChildren > 0 );
xC = 1;
#loop thru entire live version of the dim
WHILE ( xC <= xChildren );
yC = 1;
#internal loop of the archive dim for each entry in the live dim
WHILE (yC <= yChildren );
#positive check for no change in hierarchy - do nothing and break out of loop
xChildName = ELCOMP ( LiveDimName , v1, xC );
yChildName = ELCOMP ( ArcDimName , v1 , yC );
IF ( xChildName @= yChildName );
#break out of loop and set found marker
yC = yChildren + 1;
yFound = 'T';
ELSE;
#set flag for confirming the element cannot be found
yC = yC + 1;
yFound = 'F';
ENDIF;
END;
#report elements that have been moved in to a consolidation compared to the archive version
IF ( yFound @= 'F' & DIMIX ( ArcDimName , xChildName ) > 0 );
ASCIIOUTPUT ( FileOut , xChildName , 'Move In' , 'element ' | xChildName | ' added to consolidation ' | v1 | ' in ' | LiveDimName );
ENDIF;
xC = xC + 1;
END;
ENDIF;
########
dN = dN + 1;
END;
CHORE:
Create a chore to run once a day in the order 1c, 1a, 1b
On first creating the archive dimension, manually run TIs 1a and 1b. This copies the live dim structures in to the archive so you have a perfect copy.
Each time the chore runs, it will compare the 2 versions of the dim and report on any differences. It will output to a dim name / time stamped text file. The format of the file is element name, change type, description and I've copied an example of my test below.
- metadata_audit_example.jpg (76.02 KiB) Viewed 5484 times
To Consolidation = where a nodal element has been changed in to a consolidation (and any data you might have had against it will have gone!).
To Nodal = opposite of above, a consolidation has been changed to a nodal. Quite common when moving elements and "old" consolidations get left behind.
Added = um, easy enough!
Deleted = again, self explanatory
Move In = element X has been added to consolidation Y
Move Out = element X has been removed from consolidation Y
You should always see 2 entries for an element when its moved, one in and one out. If you only see one for the element then its been added (move in) or deleted (move out) from the consolidation, but it existed already (ie. Its not a "new" element like additions are). Its impossible to differentiate those from a move transaction so its important to understand the difference. The descriptions will give you the detail of the elements involved. What it doesn't cover right now is telling you the parent of any additions / deletions. If I added that it'd end up reporting one element twice for 2 reasons, and I'm not sure that’s of much benefit.
I've corrected it in the above post and highlighted it in green in case anyoen has grabbed it already.