Page 1 of 1
Replace an element with same name and different case
Posted: Fri Jul 26, 2019 6:43 pm
by manu0521
Hi Gurus,
I have an issue wher emy current dimension element for region levels are Region 001 ,Region 002 etc.
Now in the source we had update some hierarchy changes and we first unwind all the higher levels and then reupdate in ti .
The source is having value REGION 001 ... But TM1 since is case unsensitive , its not replacing the element , it still says Region 001 . If there were any new regions which was not part of Existing dimension it would show up in upper case as from Source REGION C01..
How can i get this fixed .
Re: Replace an element with same name and different case
Posted: Fri Jul 26, 2019 6:50 pm
by tomok
manu0521 wrote: ↑Fri Jul 26, 2019 6:43 pm
How can i get this fixed .
The only way would be to delete the element, save the changes and then add it back spelled correctly. Of course this means you'll have to backup the data for that element beforehand and add it back too. Not ideal.
Re: Replace an element with same name and different case
Posted: Fri Jul 26, 2019 7:00 pm
by manu0521
If that element is not at the lowest level then should i back up the data . I am renaming Region 1. Will I be able to delete Region1 with out deleting the customer data under it ?
So hierarchy is like All Regions-> Region 1 ->Agency ->Customer
Re: Replace an element with same name and different case
Posted: Fri Jul 26, 2019 7:38 pm
by declanr
manu0521 wrote: ↑Fri Jul 26, 2019 7:00 pm
If that element is not at the lowest level then should i back up the data . I am renaming Region 1. Will I be able to delete Region1 with out deleting the customer data under it ?
So hierarchy is like All Regions-> Region 1 ->Agency ->Customer
Numeric data is stored at the lowest level and consolidated up so there would be no need to back it up before doing so.
String data (and all attribute types and various security assignments) can be stored directly against the C-Level so you might want to back up and export them.
If there is any uncertainty; the safest method is to just loop every single cube (including control cubes) and if it contains the dimension in question - run a sub process that exports all data against that element (you would need to make sure consolidations aren’t skipped in the view) to CSV files with the cube name as the title.
After your element is recreated; run a similar process that loops your files and does a cellputn or cellputs inside an if cellisupdateable check. If the cell can’t be updated then it doesn’t matter; if it can - it will be.
It also means you have some CSV files you can check against to give you confidence nothing has changed (this shouldn’t be necessary though.)
Also make sure to check whether you have any rules/feeders that explicitly mention the element name as they will cause issues if the element is deleted (probably easiest to hash them out first and remove the hash after.)
EDIT - a possible easier potential solution that I think will work but have never tested so can’t confirm.
Create a temporary alias and in the element attributes cube manually put the capitalised name against that element only. Then run a TI with swapaliaswithprincipalname - afterwards delete the alias.
Re: Replace an element with same name and different case
Posted: Fri Jul 26, 2019 8:12 pm
by paulsimon
Hi Declan
I was thinking along the same lines with swapping the alias. However, a potential problem is that TM1 will not store the capitalised alias because it will think that it is the same as the element name. Therefore it will probably be necessary to swap the alias to a completely different name, then clear the alias which will now be the original element name, then swap the completely different name with the capitalised alias.
However, if the original poster only wanted to rename a consolidated element, then there is less of a problem.
Regards
Paul
Re: Replace an element with same name and different case
Posted: Sat Jul 27, 2019 11:21 am
by gtonkin
Here's another way:
Create a new process and use a subset as a datasource, set to any dimension and subset.
Create two string paramaters:
pDim
pElement
In prolog have:
Code: Select all
IF(Dimix('}Dimensions',pDim)=0);
LogOutput('Error','Error: Invalid Dimension: '|pDim);
ProcessQuit;
ENDIF;
IF(Dimix(pDim, pElement)=0);
LogOutput('Error','Error: Element '|pElement|' does not exist in Dimension: '|pDim);
ProcessQuit;
ENDIF;
IF(DType(pDim, pElement)@<>'N');
LogOutput('Error','Error: Element '|pElement|' is not type N');
ProcessQuit;
ENDIF;
sSubset='_S-Change Case';
IF(SubsetExists(pDim,sSubset)=1);
SubsetDeleteAllElements(pDim,sSubset);
ELSE;
SubsetCreate(pDim,sSubset);
ENDIF;
sMDX='{['|pDim|'].['|pElement|']}';
SubsetMDXSet(pDim,sSubset,sMDX);
SubsetMDXSet(pDim,sSubset,'');
DatasourceNameForServer=pDim;
DatasourceDimensionSubset=sSubset;
DimensionElementDelete(pDim,pElement);
In Metadata have:
Code: Select all
DimensionElementInsert(pDim,'',pElement,'N');
In epilog have:
Code: Select all
IF(SubsetExists(pDim,sSubset)=1);
SubsetDestroy(pDim,sSubset);
ENDIF;
Save and run the process.
When providing the name of the element to change case on, input the desired format
e.g. if existing element is SALes Revenue, enter Sales Revenue.
Adding or removing space will also work e.g. existing element is SALesRevenue, enter Sales Revenue to correct it.
Never lost data doing it this way (yet)
HTH
EDIT: Added some validation per Wim's post.
Re: Replace an element with same name and different case
Posted: Sun Jul 28, 2019 11:23 pm
by Wim Gielis
Hi George,
Good code !
Just in case the user enters a wrong element (or an element that is TM1-wise not
equal to an existing element):
rather than getting an error with an MDX statement, I would add an IF statement in the Prolog tab to check for existence:
Code: Select all
If( Dimix( pDim, pElement ) = 0 );
ProcessQuit;
EndIf;
Re: Replace an element with same name and different case
Posted: Mon Jul 29, 2019 4:23 am
by gtonkin
Wim Gielis wrote: ↑Sun Jul 28, 2019 11:23 pm
Hi George,
Good code !
Just in case the user enters a wrong element (or an element that is TM1-wise not
equal to an existing element):
...
Thanks Wim, additional validation added to make it a bit more robust.