Deleting Dimensions

[MSidat]

Hello TM1 Guru's..

I am having an issue deleting Dimensions in 9.5.2. I have a VBA led process which kicks off 2 Tis. The first one builds up a mapping Dimension from a flat file and a Temporary Cube and the Second Ti populate this temporary cube with data and flat files out certain consolidation points for the vba to pick up and populate a report and in the epilog the temp cube and mapping dimension is deleted.

So far so good no temp objects are visible through perspectives however when i look in the data directory the .dim files are still there. No real issue as I always create a unique user/time stamp for the temp dimension but I can slowly see my Data file expanding without good reason.

Any ideas why the dim file isnt removed from the data directory, its as if all the markers are removed so that it does not appear in server explorer but remains in the data folder.

thanks in advance

Regards

[qml]

Have you tried restarting the TM1 service? Normally TM1 should pick up all *.dim files during startup and load them. Is that what happens in your model or are these files being ignored?

[Alan Kirk]

In addition to qml's suggestion, have you tried doing a data save? If the objects are gone from memory it's possible that the server is waiting to sync the .dim files with what resides in memory on the next save to disk. I haven't tested that but it's a possibility worth checking.

[MSidat]

Hi,

On a restart all the Temp Dims appear again in server explorer.

[Alan Kirk]

Hi,

On a restart all the Temp Dims appear again in server explorer.

Are you sure that they were ever really deleted? You said that you didn't see them in Server Explorer but was that after pressing [F5] to refresh the object list?

[MSidat]

Alan,

Just tried the save data, to no avail. Still in the Data Directory, done a F5 on TM1 server explorer and its not visible.

[qml]

How exactly is your TI deleting the dimension? Are you using the DimensionDestroy() function? Maybe you can post that piece of your code here, including what's directly before and after?
Also, is there any difference in behaviour if you kick off the TI manually and not from VBA?

[MSidat]

Ok.

heres the process.

VBA kicks of TI No 1 passing it relevent params to build up a Temp Dimension using a user defined text file as source.
In Epilog of same TI a Temp Cube is built using this dimension and another fixed dimension.

VBA Kicks of Ti No 2 by passing it relevant params to executes the TM1 RunTi.exe to kick off another TI (Had to do this as needed control back to the VBA for user interaction, the exe only seems to work if its kicked off from the server location, so used a TI to kick it off rather than a shell command)

This TI builds up:
A datasource view in the prolog from a existing cube.
In the metadata any elements are added into the temp dimension if necessary,
In the data tab the data is mapped to the temp cube.
In the epilog relevant data is exported out to a flat file by using a loop through the temp dimension structure. After everything is flat filed out the datasoure view is deleted, temp subsets are deleted, the temp cube is deleted and the temp dimension is deleted all using the standard TM1 DimensionDestroy CubeDestroy etc functions.

[Alan Kirk]

This TI builds up:

I think QML meant the actual code, not just a description of it. The typical reason for a TI failing to work is the gap between what we believe it to be doing, and what it actually is doing.

[MSidat]

Alan, QML

Code from the epilog of the second TI is attached. This is basically where i dump out the data into a flat file to create a report and tidy all the cubes/views/subsets/dimensions up

Code: Select all

PCExportFile = SUBST(pExportFile,1, long(pExportFile) - 4) | 'PC.txt';
DetailExportFile = SUBST(pExportFile,1, long(pExportFile) - 4) | 'Detail.txt';

TotalElements = DIMSIZ(pMasterDim) - 1;

#MDX Returns the Current Approved MWE Parent Child Structure.  Everything Except Liquidated Damages and Warranty Lines.
MDXString = '{FILTER({ORDER({TM1DRILLDOWNMEMBER({[Major Work Elements].[' | pCSR | ']},ALL,RECURSIVE)},[MWE Mapping].([Period].[' | pPeriod | '],[Vers
ion].[' | pVersion | '],[MWE Mapping Hierarchy].[Order]) ,BASC)},[MWE Mapping].([Period].[' | pPeriod | '],[Version].[' | pVersion | '],[MWE Mapping H
ierarchy].[Order])<>"")}';
MDXName = 'Sub' | pMasterDim;

SubsetCreatebyMDX(MDXName, MDXString);

TotalMWEPC = SubsetGetSize('Major Work Elements', MDXName) + 3;

# +3 as need LD, Warranty and CNA

IF(SubsetExists('Major Work Elements',MDXName) = 1);
      SubsetDestroy('Major Work Elements',MDXName);
ENDIF;


#We need this for the Excel text import method
ASCIIOUTPUT(DetailExportFile,  numbertostring(TotalElements));
ASCIIOUTPUT(PCExportFile,  numbertostring(TotalMWEPC));

# We Need to Loop through each Parent and Then Child and Then CCN

MWELoop = 1 ;
MaxMWELoop = ElcompN(pMasterDim, pCSR);

WHILE (MWELoop <= MaxMWELoop);
#Each Parent
   CurrentMWE = ELCOMP(pMasterDim, pCSR, MWELoop);
   HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CurrentMWE);
   CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CurrentMWE);
   CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CurrentMWE);
   CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CurrentMWE);
   HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CurrentMWE);
   CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CurrentMWE);
   CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CurrentMWE);
   CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CurrentMWE);

   HrsinMonthL = CellGetN(pMasterDim, 'Actual Labour Hours', CurrentMWE);
   HrsinMonthN = CellGetN(pMasterDim, 'Actual Non Labour Hours', CurrentMWE);
   HrstoDateL = CellGetN(pMasterDim, 'Actual Labour Hours to Date', CurrentMWE);
   HrstoDateN = CellGetN(pMasterDim, 'Actual Non Labour Hours to Date', CurrentMWE);

   IF (Scan('Warranty', CurrentMWE) > 0);
      MWEElemRef = 'Warranty';
      ASCIIOUTPUT(PCExportFile,  'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(C
stinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoDat
eT));
      ASCIIOUTPUT(DetailExportFile,  'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostri
ng(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(Cstt
oDateT));

      IF (pReport @='False');
        #Send Data into FD Core Financials Cube
      CellPutN(CstinMonthL, 'FD Core Financials', 'Costs in Month Labour' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(CstinMonthN, 'FD Core Financials', 'Costs in Month Non Labour' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(CsttoDateL, 'FD Core Financials', 'Actual Costs to Date Labour' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(CsttoDateN, 'FD Core Financials', 'Actual Costs to Date Non Labour' ,pPeriod, pVersion, CurrentMWE);

      CellPutN(HrsinMonthL, 'FD Core Financials', 'Actual Labour Hours' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(HrsinMonthN, 'FD Core Financials', 'Actual Non Labour Hours' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(HrstoDateL, 'FD Core Financials', 'Actual Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(HrstoDateN, 'FD Core Financials', 'Actual Non Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
     ENDIF;

   ELSEIF (Scan('LDs', CurrentMWE) > 0);
      MWEElemRef = 'Liquidated Damages';
      ASCIIOUTPUT(PCExportFile,  'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(C
stinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoDat
eT));
      ASCIIOUTPUT(DetailExportFile,  'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostri
ng(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(Cstt
oDateT));

      IF (pReport @='False');
        #Send Data into FD Core Financials Cube
      CellPutN(CstinMonthL, 'FD Core Financials', 'Costs in Month Labour' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(CstinMonthN, 'FD Core Financials', 'Costs in Month Non Labour' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(CsttoDateL, 'FD Core Financials', 'Actual Costs to Date Labour' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(CsttoDateN, 'FD Core Financials', 'Actual Costs to Date Non Labour' ,pPeriod, pVersion, CurrentMWE);

      CellPutN(HrsinMonthL, 'FD Core Financials', 'Actual Labour Hours' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(HrsinMonthN, 'FD Core Financials', 'Actual Non Labour Hours' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(HrstoDateL, 'FD Core Financials', 'Actual Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
      CellPutN(HrstoDateN, 'FD Core Financials', 'Actual Non Labour Hours to Date' ,pPeriod, pVersion, CurrentMWE);
     ENDIF;
   ELSEIF (Scan('Costs Not Allocated From Mapping', CurrentMWE) > 0);
      MWEElemRef = 'Costs Not Allocated From Mappings or Inputs';
      ASCIIOUTPUT(PCExportFile, 'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(Cs
tinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoDate
T));
      ASCIIOUTPUT(DetailExportFile,'P', MWEElemRef, '', '', MWEElemRef, '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring
(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoD
ateT));
   ELSE;
      MWEElemRef = CellGetS('MWE Mapping', pPeriod, pVersion, CurrentMWE, 'DisplayRef');
      ASCIIOUTPUT(PCExportFile,  'P', MWEElemRef, '', '', '', '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(CstinMont
hN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoDateT));
      ASCIIOUTPUT(DetailExportFile, 'P', MWEElemRef, '', '', '', '', '',numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(CstinM
onthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoDateT));
   ENDIF;

   ChildLoop = 1;
   MaxChildren = ElcompN(pMasterDim, CurrentMWE);
   WHILE (ChildLoop <= MaxChildren);

        IF (Scan('Warranty', CurrentMWE) > 0 % Scan('LDs', CurrentMWE) > 0 % Scan('Costs Not Allocated From Mapping', CurrentMWE) > 0);
           #These will be CCN's
           CCN = ELCOMP(pMasterDim, CurrentMWE, ChildLoop);
           CCNStatus = CellGetS('CCN Status', pPeriod, CCN, 'Status');
           HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CCN);
           CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CCN);
           CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CCN);
           CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CCN);
           HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CCN);
           CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CCN);
           CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CCN);
           CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CCN);

           ASCIIOUTPUT(DetailExportFile, 'L', '', '', '', '' , CCN, CCNStatus, numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring
(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoD
ateT));

        ELSE;
          #These will be actual MWE Children
          CurrentChild = ELCOMP(pMasterDim, CurrentMWE, ChildLoop);
          ChildElemRef = CellGetS('MWE Mapping', pPeriod, pVersion, CurrentChild, 'DisplayRef');
          HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CurrentChild);
          CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CurrentChild);
          CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CurrentChild);
          CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CurrentChild);
          HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CurrentChild);
          CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CurrentChild);
          CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CurrentChild);
          CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CurrentChild);

          HrsinMonthL = CellGetN(pMasterDim, 'Actual Labour Hours', CurrentChild);
          HrsinMonthN = CellGetN(pMasterDim, 'Actual Non Labour Hours', CurrentChild);
          HrstoDateL = CellGetN(pMasterDim, 'Actual Labour Hours to Date', CurrentChild);
          HrstoDateN = CellGetN(pMasterDim, 'Actual Non Labour Hours to Date', CurrentChild);

         ASCIIOUTPUT(PCExportFile,  'C', '', '', '', ChildElemRef , '', '', numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring(Cs
tinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoDate
T));
         ASCIIOUTPUT(DetailExportFile, 'C', '', '', '', ChildElemRef , '', '', numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbertostring
(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring(CsttoD
ateT));

           IF (pReport @='False');
          #Send Data into FD Core Financials Cube
          CellPutN(CstinMonthL, 'FD Core Financials', 'Costs in Month Labour' ,pPeriod, pVersion, CurrentChild);
          CellPutN(CstinMonthN, 'FD Core Financials', 'Costs in Month Non Labour' ,pPeriod, pVersion, CurrentChild);
          CellPutN(CsttoDateL, 'FD Core Financials', 'Actual Costs to Date Labour' ,pPeriod, pVersion, CurrentChild);
          CellPutN(CsttoDateN, 'FD Core Financials', 'Actual Costs to Date Non Labour' ,pPeriod, pVersion, CurrentChild);

          CellPutN(HrsinMonthL, 'FD Core Financials', 'Actual Labour Hours' ,pPeriod, pVersion, CurrentChild);
          CellPutN(HrsinMonthN, 'FD Core Financials', 'Actual Non Labour Hours' ,pPeriod, pVersion, CurrentChild);
          CellPutN(HrstoDateL, 'FD Core Financials', 'Actual Labour Hours to Date' ,pPeriod, pVersion, CurrentChild);
          CellPutN(HrstoDateN, 'FD Core Financials', 'Actual Non Labour Hours to Date' ,pPeriod, pVersion, CurrentChild);
          ENDIF;

             CCNLoop = 1;
             MaxCCN = ElcompN(pMasterDim, CurrentChild);
             WHILE (CCNLoop <= MaxCCN);

                 CCN = ELCOMP(pMasterDim, CurrentChild, CCNLoop);
                 CCNStatus = CellGetS('CCN Status', pPeriod, CCN, 'Status');
                 HrsinMonth = CellGetN(pMasterDim, 'Total Hours in Month', CCN);
                 CstinMonthL = CellGetN(pMasterDim, 'Costs in Month Labour', CCN);
                 CstinMonthN = CellGetN(pMasterDim, 'Costs in Month Non Labour', CCN);
                 CstinMonthT = CellGetN(pMasterDim, 'Costs in Month', CCN);
                 HrstoDate = CellGetN(pMasterDim, 'Total Hours to Date', CCN);
                 CsttoDateL = CellGetN(pMasterDim, 'Actual Costs to Date Labour', CCN);
                 CsttoDateN = CellGetN(pMasterDim, 'Actual Costs to Date Non Labour', CCN);
                 CsttoDateT = CellGetN(pMasterDim, 'Actual Costs to Date', CCN);
                 ASCIIOUTPUT(DetailExportFile,  'L', '', '', '', '' , CCN, CCNStatus, numbertostring(HrsinMonth), numbertostring(CstinMonthL), numbert
ostring(CstinMonthN), numbertostring(CstinMonthT), numbertostring(HrstoDate), numbertostring(CsttoDateL),  numbertostring(CsttoDateN),  numbertostring
(CsttoDateT));

             CCNLoop = CCNLoop + 1;
             END;

        ENDIF;

   ChildLoop = ChildLoop + 1;
   END;

MWELoop = MWELoop + 1;
END;

#Delete Temp Cube and Views/Subsets and Temp Dimension

   IF (ViewExists('FD Cost Financials CCN',pMasterDim) = 1);
      ViewDestroy('FD Cost Financials CCN',pMasterDim);
   ENDIF;

   IF (SubsetExists('Financial Variables', pMasterDim) = 1);
      SubsetDestroy('Financial Variables', pMasterDim) ;
   ENDIF;

   IF (SubsetExists('Cum Periods', pMasterDim) = 1);
      SubsetDestroy('Cum Periods', pMasterDim) ;
   ENDIF;

   IF (SubsetExists('Cost Breakdown GL', pMasterDim) = 1);
       SubsetDestroy('Cost Breakdown GL', pMasterDim) ;
   ENDIF;

   IF (SubsetExists('CCN', pMasterDim) = 1);
       SubsetDestroy('CCN', pMasterDim) ;
   ENDIF;

   IF (CubeExists(pMasterDim) = 1);
       CubeDestroy(pMasterDim);
   ENDIF;

   IF (DimensionExists(pMasterDim) = 1);
      DimensionDestroy(pMasterDim);
   ENDIF;

pMasterDim is a Unique user time stamp and is used as the temp cube name/temp dimension name, temp subsets etc.

[Alan Kirk]

Alan, QML

Code from the epilog of the second TI is attached.

I'll try to take a look at this over the weekend if someone else doesn't beat me to it; I'm curious about whether you've stumbled upon a bug.

[MSidat]

Thanks Alan,

Did some more testing to try and isolate the issue further. I wrote a simple Ti that had a series of DimensionDestory(DimName); in it for dimensions which existed in the data directory and were visible in server explorer. These executed fine and were deleted from the data directory.

I thought the issue around actually deleting the dimension may be because I was using the RunTI.exe to execute the TI outside of VBA, I changed the process for the VBA to kick off the TI in the normal way using the executeprocess function previously provided by the gurus on this forum. This again resulted in the same result i.e. After doing a save data and a F5 dimension and cube seems to have been deleted from server explorer and its not visible but look in the data directory and its still there.

I also tried putting in the DimensionDestroy statement in twice, again no luck.

If I took out the dimension destory from the TI and put it into a 3rd TI it appears to work fine i.e. its is removed from the data directory and server explorer.

It appears the process of populating data into a cube with a brand new dimension (created for the first time in the prior TI) and then destroying it in the same process causes the issue. Bizarrly though the Temp Cub file is deleted fine, its just the dimension.

[lotsaram]

Thanks Alan,

Did some more testing to try and isolate the issue further. I wrote a simple Ti that had a series of DimensionDestory(DimName); in it for dimensions which existed in the data directory and were visible in server explorer. These executed fine and were deleted from the data directory.

I thought the issue around actually deleting the dimension may be because I was using the RunTI.exe to execute the TI outside of VBA, I changed the process for the VBA to kick off the TI in the normal way using the executeprocess function previously provided by the gurus on this forum. This again resulted in the same result i.e. After doing a save data and a F5 dimension and cube seems to have been deleted from server explorer and its not visible but look in the data directory and its still there.

I also tried putting in the DimensionDestroy statement in twice, again no luck.

If I took out the dimension destory from the TI and put it into a 3rd TI it appears to work fine i.e. its is removed from the data directory and server explorer.

It appears the process of populating data into a cube with a brand new dimension (created for the first time in the prior TI) and then destroying it in the same process causes the issue. Bizarrly though the Temp Cub file is deleted fine, its just the dimension.

Hmmmm. I see from your profile that you are using 9.5.2 is that accurate or aspirational?

I raised a bug for 9.5.1 which was if a temp dimension was created in a process and then used as a data source for the process and then subsequently destroyed in the same process that this would crash the server. Local workaround fix for the issue was to move the dimension destroy to a process outside of the process/chore in which the temp dimension was being created. By the sounds of what you are describing development has "fixed" my bug but not as completely as one would like. That is the crash is gone and the dimension is removed from the in memory model but as the disk copy is not removed the dimension isn't really "destroyed" as it will be there on a restart. A case of replacing one bug with another (albet maybe not so critical as a server crash). Same local fix - move killing the temp dimension to another process.

If I were you I'd raise it as a bug as that's what it would appear to be.

[MSidat]

lotsaram,

Yes we are on 9.5.2 here. Seems like you may have hit it on the head.

As a temporary measure I am putting in an asciidelete straight after the dimensiondestroy rather than have a separate process to remove the .dim file from the data directory seeing as it is removing it from the in memory version.

Will raise a bug with IBM.