Page 1 of 1
Storage Location Of Cube's Dimension Order
Posted: Thu Oct 27, 2016 3:19 pm
by bplaia
Hey guys, I was wondering if anybody here had some insight into where TM1 stores a cube's dimension order. I would assume that this information is stored in the individual .cub files for each cube, however the reason I'm asking is because we have changed the dimension order in our UAT environment for a cube that is 180 GB in size and saw it drop to 105 GB after the re-order. Our plan is to test each dimension as the last dimension in order to find the most optimal order.
After the first re-order which resulted in a cube size of 105 GB we stopped the TM1 service, and restored the original .cub and .feeders files back into the Data directory and restarted the service. When the TM1 server came up however, the cube was only using 70 GB instead of the expected 180 GB (or even a less expected 105 GB, the same size as the re-ordered cube). If you try to re-order the dimensions now, TM1 is showing them as being in the original order, and all of the data is present when viewed in cube viewer...so my question is where did the extra 110 GB go?
Could TM1 possibly have held onto the modified dimension order after the restart but is not displaying it?
Side note: the "Test" function in the Cube Optimizer does not seem to give reliable results, as it had reported that the dimension order which actually gave us a 75 GB reduction would have resulted in a 200% increase. Anyone else have experience with the Cube Optimizer not returning accurate results?
Re: Storage Location Of Cube's Dimension Order
Posted: Thu Oct 27, 2016 3:57 pm
by tomok
To answer your first question, all the information about a cube, metadata and data, is indeed stored in the .cub file. For the second question, concerning the size of the .cub file and the amount of memory the cube uses in TM1 there is no direct correlation between the two. The size of the .cub file is directly related to the amount of stored data. If you have rules on the cube, which would indeed consume RAM, they are not in the .cub file. A cube that is 1MB on disk could expand to multiple GBs in memory if you have enough rules and feeders on the cube. I believe the reason you see changes in the .cub file at various times when re-ordering is that TM1 does not do a complete re-write of the .cub file every time data is stored, this would greatly slow down the system when saving. It does an incremental save some of the time which saves just changes, resulting in a larger and larger .cub file At some point a complete re-write of the .cub file is done but I'm not exactly sure what triggers that but I think re-ordering would do that.
Re: Storage Location Of Cube's Dimension Order
Posted: Thu Oct 27, 2016 4:17 pm
by BrianL
tomok wrote:I believe the reason you see changes in the .cub file at various times when re-ordering is that TM1 does not do a complete re-write of the .cub file every time data is stored, this would greatly slow down the system when saving. It does an incremental save some of the time which saves just changes, resulting in a larger and larger .cub file At some point a complete re-write of the .cub file is done but I'm not exactly sure what triggers that but I think re-ordering would do that.
You can test this out easily enough. Change a single cell value, then use CubeSaveData to save the cube to disk. Now do a large spread of non-zero data to *existing data* in the cube, and re-run the CubeSaveData. You'll see both saves took the same amount of time and wrote out the same size file (might not be exact if value lengths have changed).
Unfortunately, this shows that TM1 does in fact write out the entire cube every time.
bplaia, how are you measuring cube size? Size of .cub on disk? }StatsByCube?
Since you mentioned .feeder files, TM1 does some file timestamp sanity checking on feeder files and there's a chance it might have thrown out your saved .feeder file and reprocessed feeders at startup which could change some measures if you're including feeder data too. If this was done you'd see it in the message log.
Re: Storage Location Of Cube's Dimension Order
Posted: Thu Oct 27, 2016 4:30 pm
by bplaia
BrianL wrote:bplaia, how are you measuring cube size? Size of .cub on disk? }StatsByCube?
Since you mentioned .feeder files, TM1 does some file timestamp sanity checking on feeder files and there's a chance it might have thrown out your saved .feeder file and reprocessed feeders at startup which could change some measures if you're including feeder data too. If this was done you'd see it in the message log.
The numbers I'm giving are coming from }StatsByCube. The .cub and .feeders were both re-created when the dimension re-order completed. On server startup there was no reprocessing of persistent feeders, it simply loaded them as usual.
Then, to test another dimension as the last dimension from scratch, we put the original .cub and .feeders files from a PROD backup into the Data directory and started up to find the 180 GB cube only consuming 70 GB with exactly the same rules/feeders declarations.
What's interesting is deleting the entire Data directory in UAT and restoring from the same PROD backup causes it to load up at full size (180 GB), whereas simply overwriting the affected cube's .cub and .feeders file and starting the service gave us this weird issue. The .rux files are identical between PROD and UAT.
Re: Storage Location Of Cube's Dimension Order
Posted: Thu Oct 27, 2016 4:37 pm
by bplaia
tomok wrote:To answer your first question, all the information about a cube, metadata and data, is indeed stored in the .cub file
That's what I had suspected, which is consistent with the Dimension Re-Order wizard telling me that it was currently in the original order when we loaded the .cub file from backup.
tomok wrote:For the second question, concerning the size of the .cub file and the amount of memory the cube uses in TM1 there is no direct correlation between the two. The size of the .cub file is directly related to the amount of stored data. If you have rules on the cube, which would indeed consume RAM, they are not in the .cub file. A cube that is 1MB on disk could expand to multiple GBs in memory if you have enough rules and feeders on the cube.
The change in size was seen in }StatsByCube and the amount of physical memory that the tm1sd.exe process was taking, not the .cub files themselves. Keep in mind, this is after the dimension re-order finished and we shut down the instance and replaced the new .cub and .feeders files with ones from a PROD backup. The .cub and .feeders files on UAT and PROD were identical except for the fact that the live PROD cube was consuming 180 GB via }StatsByCube while UAT was consuming 70 GB and all aspects of it were reduced (i.e., memory for populated cells and feeders).
Re: Storage Location Of Cube's Dimension Order
Posted: Thu Oct 27, 2016 6:00 pm
by gtonkin
bplaia wrote:Hey guys, I was wondering if anybody here had some insight into where TM1 stores a cube's dimension order.
Per Tomok's response-all in the cube. Not that you are going to have hundreds of backup versions of this cube file but if you have a backup and want to check the dimension order offline, use a hex dump/editor - I have a command line hex dump (hd.exe) that I use to check "things" - The cubes always start with the dimension e.g:
Code: Select all
0000000: 78 05 0c 00 70 6c 61 6e 5f 76 65 72 73 69 6f 6e 'x...plan_version'
0000010: 05 00 00 00 12 00 70 6c 61 6e 5f 62 75 73 69 6e '......plan_busin'
0000020: 65 73 73 5f 75 6e 69 74 09 00 00 00 0f 00 70 6c 'ess_unit......pl'
0000030: 61 6e 5f 64 65 70 61 72 74 6d 65 6e 74 0b 00 00 'an_department...'
0000040: 00 16 00 70 6c 61 6e 5f 63 68 61 72 74 5f 6f 66 '...plan_chart_of'
0000050: 5f 61 63 63 6f 75 6e 74 73 2a 00 00 00 13 00 70 '_accounts*.....p'
0000060: 6c 61 6e 5f 65 78 63 68 61 6e 67 65 5f 72 61 74 'lan_exchange_rat'
0000070: 65 73 06 00 00 00 0b 00 70 6c 61 6e 5f 73 6f 75 'es......plan_sou'
0000080: 72 63 65 0a 00 00 00 09 00 70 6c 61 6e 5f 74 69 'rce......plan_ti'
0000090: 6d 65 34 00 00 00 00 00 01 00 02 00 03 00 04 00 'me4.............'
00000a0: 05 00 06 00 07 00 0e 00 46 59 20 32 30 30 33 20 '........FY 2003 '
Hope this helps with respect to your initial question.
In terms of re-ordering, there are some threads on the forum that will give you some pointers in terms of small vs big dimensions, dense vs sparse and best practice (based on empirical evidence)