Should I suggest a new piece for the captions copy over as well to make it full clone?
Original Message:
Sent: Mon May 27, 2024 11:13 AM
From: Scott Wiltshire
Subject: How to: dimension 'Save as'
Or you could download bedrock.dim.clone or bedrock.hier.clone from GitHub. These processes dynamically change the target hierarchy name to the dimension name if cloning the same named hierarchy plus the ability to:
- select which hierarchies to clone (all, wildcard, delimited list, single hierarchy)
- select whether to copy attributes and attribute values or hierarchy structure only
- also copy the settings for dimension sort order (for v11 only, see below).
- re-copy to an existing dimension or hierarchy without destroy and recreate
- option to additively copy in new members only (if target already exists) versus pure clone
For v11 or PAoC you can simply download the .pro files and place in the data directory and restart the instance (database).
For PAaaS you would need to copy the code using a text/code editor and paste into the TI editor. Also note for v12/PA Engine you would need to delete references to the }DimensionProperties cube since this no longer exists and the DimensionSortOrder function since there is no way in TI code to retrieve this property from the source object (due to deprication of }DimensionProperties cube.)
------------------------------
Scott Wiltshire
Original Message:
Sent: Mon May 27, 2024 10:14 AM
From: STUART KING
Subject: How to: dimension 'Save as'
One of the remaining gaps between modeling in Architect and Planning Analytics Workspace is the ability to copy a dimension. Although the TM1 database includes a REST API endpoint to 'Save as' a dimension, we have not exposed this as a feature in Workspace modeling. The impact on hierarchies when using the dimension 'save as' endpoint should be carefully understood.
What many TM1 modelers might not know is that dimensions in TM1 do have a default hierarchy. The default hierarchy does not need to have the same name as the dimension. When a dimension is copied using the REST API endpoint the same name hierarchy is not renamed. This can cause the copy of the dimension to be missing a hierarchy with the same name as the dimension. This will cause issues with any client looking for hierarchies with the same name instead of the default hierarchy. Planning Analytics Workspace currently looks for the hierarchy with the same name as the dimension instead of the default hierarchy. This behaviour will need to be adjusted (this is on our roadmap) before we introduce a dimension 'save as' option in the Workspace.
In the mean time, its completly possible to duplicate a dimension with a basic Turbo Intergrator scripts. The following code can be used in the prolog section of a TI process to copy a dimension. The following code will check for a hierarchy with the same name as the dimension and cause the copy of the dimension to include a hierarchy with the same name as the copy of the dimension This TI script also copies attributes and attribute values. It does not touch security but could be easily extended to also copy element security.
vSourceDimension = 'plan_business_unit';
vNewDimension = 'copy_plan_business_unit';
IF ( DimensionExists( vNewDimension ) = 1);
DimensionDestroy( vNewDimension );
ENDIF;
DimensionCreate( vNewDimension );
vSourceAttributeCubeName = '}ElementAttributes_' | vSourceDimension;
vNewAttributeCubeName = '}ElementAttributes_' | vNewDimension;
### Attributes
vAttributeDimensionName = '}ElementAttributes_' | vSourceDimension;
elementAttributeIndex = DIMSIZ ( vAttributeDimensionName );
vElementAttribute = DIMNM ( vAttributeDimensionName, elementAttributeIndex );
WHILE ( elementAttributeIndex > 0 );
vAttributeType = DTYPE ( vAttributeDimensionName, vElementAttribute );
AttrInsert ( vNewDimension, '', vElementAttribute, SUBST( vAttributeType, 2, 1 ) );
elementAttributeIndex = elementAttributeIndex - 1;
vElementAttribute = DIMNM( vAttributeDimensionName, elementAttributeIndex );
END;
### Leaves
dimIndex = 1;
vDimension = DIMNM ( '}Dimensions', dimIndex );
WHILE ( vDimension @<> '');
IF ( SCAN ( LOWER( vSourceDimension ) | ':', LOWER ( vDimension ) ) = 1 );
vDimNameLength = LONG( vSourceDimension );
vHierarchy = SUBST ( vDimension, vDimNameLength + 2, LONG( vSourceDimension ) - 1 );
IF ( LOWER ( vHierarchy ) @= 'leaves');
vElementIndex = 1;
vElementName = ElementName( vSourceDimension, vHierarchy, vElementIndex );
WHILE ( vElementName @<> '' );
vElementType = ElementType( vSourceDimension, vHierarchy, vElementName );
IF ( vElementType @= 'N' % vElementType @= 'S' );
DimensionElementInsertDirect ( vNewDimension, '', vElementName, vElementType );
# Attribute values
vAttributeDimensionName = '}ElementAttributes_' | vSourceDimension;
elementAttributeIndex = DIMSIZ ( vAttributeDimensionName );
WHILE ( elementAttributeIndex > 0 );
vElementAttribute = DIMNM ( vAttributeDimensionName, elementAttributeIndex );
ASCIIOutput( './model_upload/debug.txt', vElementAttribute, CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ) );
CellPutS ( CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ), vNewAttributeCubeName, vElementName, vElementAttribute );
elementAttributeIndex = elementAttributeIndex - 1;
END;
ENDIF;
vElementIndex = vElementIndex + 1;
vElementName = ElementName( vSourceDimension, vSourceDimension, vElementIndex );
END;
ENDIF;
ENDIF;
dimIndex = dimIndex + 1;
vDimension = DIMNM ( '}Dimensions', dimIndex );
END;
### Other hierarchies
dimIndex = 1;
vDimension = DIMNM ( '}Dimensions', dimIndex );
WHILE ( vDimension @<> '');
#ASCIIOutput( 'dimHierarchies.txt', 'checking for ' | vSourceDimension | ': in ' | vDimension );
IF ( SCAN ( LOWER( vSourceDimension ) | ':', LOWER ( vDimension ) ) = 1 );
vDimNameLength = LONG( vSourceDimension );
vHierarchy = SUBST ( vDimension, vDimNameLength + 2, LONG( vSourceDimension ) - 1 );
#ASCIIOutput( 'dimHierarchies.txt',' -> ' | vHierarchy );
IF ( LOWER ( vHierarchy ) @<> 'leaves');
HierarchyCreate( vNewDimension, vHierarchy );
vElementIndex = 1;
vElementName = ElementName( vSourceDimension, vHierarchy, vElementIndex );
WHILE ( vElementName @<> '' );
vElementType = ElementType( vSourceDimension, vHierarchy, vElementName );
IF ( vElementType @= 'C');
HierarchyElementInsertDirect ( vNewDimension, vHierarchy, '', vElementName, vElementType );
ENDIF;
vParentIndex = 1;
WHILE ( vParentIndex <= ElementParentCount ( vSourceDimension, vHierarchy, vElementName) );
vElementParentName = ElementParent ( vSourceDimension, vHierarchy, vElementName, vParentIndex );
vElementWeight = ElementWeight ( vSourceDimension, vHierarchy, vElementParentName, vElementName );
#HierarchyElementInsertDirect ( vNewDimension, vHierarchy, '', vElementParentName, 'C' );
HierarchyElementComponentAddDirect ( vNewDimension, vHierarchy, vElementParentName, vElementName, vElementWeight );
# Attribute values
vAttributeDimensionName = '}ElementAttributes_' | vSourceDimension;
elementAttributeIndex = DIMSIZ ( vAttributeDimensionName );
WHILE ( elementAttributeIndex > 0 );
vElementAttribute = DIMNM ( vAttributeDimensionName, elementAttributeIndex );
ASCIIOutput( './model_upload/debug.txt', vElementAttribute, CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ) );
CellPutS ( CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ), vNewAttributeCubeName, vElementName, vElementAttribute );
elementAttributeIndex = elementAttributeIndex - 1;
END;
vParentIndex = vParentIndex + 1;
vElementParentName = ElementParent ( vSourceDimension, vHierarchy, vElementName, vParentIndex );
END;
# Attribute values
vAttributeDimensionName = '}ElementAttributes_' | vSourceDimension;
elementAttributeIndex = DIMSIZ ( vAttributeDimensionName );
WHILE ( elementAttributeIndex > 0 );
vElementAttribute = DIMNM ( vAttributeDimensionName, elementAttributeIndex );
ASCIIOutput( './model_upload/debug.txt', vElementAttribute, CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ) );
CellPutS ( CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ), vNewAttributeCubeName, vElementName, vElementAttribute );
elementAttributeIndex = elementAttributeIndex - 1;
END;
vElementIndex = vElementIndex + 1;
vElementName = ElementName( vSourceDimension, vSourceDimension, vElementIndex );
END;
ENDIF;
ELSEIF ( LOWER ( vSourceDimension ) @= LOWER ( vDimension ) );
#ASCIIOutput( 'dimHierarchies.txt', vSourceDimension );
vElementIndex = 1;
vElementName = ElementName( vSourceDimension, vSourceDimension, vElementIndex );
WHILE ( vElementName @<> '' );
vElementType = ElementType( vSourceDimension, vSourceDimension, vElementName );
IF ( vElementType @= 'C');
HierarchyElementInsertDirect ( vNewDimension, vNewDimension, '', vElementName, vElementType );
ENDIF;
vParentIndex = 1;
WHILE ( vParentIndex <= ElementParentCount ( vSourceDimension, vSourceDimension, vElementName) );
vElementParentName = ElementParent ( vSourceDimension, vSourceDimension, vElementName, vParentIndex );
vElementWeight = ElementWeight ( vSourceDimension, vSourceDimension, vElementParentName, vElementName );
#HierarchyElementInsertDirect ( vNewDimension, vNewDimension, '', vElementParentName, 'C' );
HierarchyElementComponentAddDirect ( vNewDimension, vNewDimension, vElementParentName, vElementName, vElementWeight );
vParentIndex = vParentIndex + 1;
vElementParentName = ElementParent ( vSourceDimension, vSourceDimension, vElementName, vParentIndex );
END;
# Attribute values
vAttributeDimensionName = '}ElementAttributes_' | vSourceDimension;
elementAttributeIndex = DIMSIZ ( vAttributeDimensionName );
WHILE ( elementAttributeIndex > 0 );
vElementAttribute = DIMNM ( vAttributeDimensionName, elementAttributeIndex );
ASCIIOutput( './model_upload/debug.txt', vElementAttribute, CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ) );
CellPutS ( CellGetS ( vSourceAttributeCubeName, vElementName, vElementAttribute ), vNewAttributeCubeName, vElementName, vElementAttribute );
elementAttributeIndex = elementAttributeIndex - 1;
END;
vElementIndex = vElementIndex + 1;
vElementName = ElementName( vSourceDimension, vSourceDimension, vElementIndex );
END;
ENDIF;
dimIndex = dimIndex + 1;
vDimension = DIMNM ( '}Dimensions', dimIndex );
END;
------------------------------
Stuart King
IBM Planning Analytics Offering Manager
------------------------------