Page 1 of 1

How to know last cube updated time in TM1

Posted: Thu Aug 16, 2018 5:37 am
by amit_hhh
Hi All,

I have a scenario in which there is a cube export TI which executes every 15 min and places the file at the specific location. Now I need to enhance this logic in a way that the export TI should execute only when there is data change in the cube, not every 15 min as the former way is taking unnccessary disk storage when data is same in the export file.

How can I achieve this? Is there a way I can get the last update time of the cube other than .cub file update time? If .cub file update time is the only way, I am not sure how I can use that time in TI logic to implement the condition for TI execution.

ADMIN : Please post in the correct forum, this is not an enhancement request

Re: How to know last cube updated time in TM1

Posted: Thu Aug 16, 2018 1:28 pm
by orlando
Hi,

i think the only way is to read the TM1s.log (when logging for the cube is turned on)
If you find an entry with a timestamp in your specific timeframe for the copy, you know, that figures are changed.

The .cub file update time is only useable, when you make a saveDataAll before you copy, otherwise you don't know if there are changes.

Best regards,
orlando

Re: How to know last cube updated time in TM1

Posted: Thu Aug 16, 2018 2:48 pm
by Edward Stuart
How is your source cube updated? From ODBC? User Input?

You could write out the total value in the cube each 15 minutes to a control cube (for example) and validate the export based on the total value matching every 15 minutes or not

Code: Select all

sExistingTotal = cellGetN ('myControlCube', 'ValueEvery15Minutes', 'Value');
cTotalVal = cellGetN ('mySourceCube', 'TotalConsolDim1', 'TopConsolDim2', 'TopConsolDimN', 'Value' );

if ( cTotalVal <> sExistingTotal ) ;

Export File Logic

endif ; 

Re: How to know last cube updated time in TM1

Posted: Thu Aug 16, 2018 2:53 pm
by tomok
orlando wrote: Thu Aug 16, 2018 1:28 pm The .cub file update time is only useable, when you make a saveDataAll before you copy, otherwise you don't know if there are changes.
Actually, you only need to save that cube, not all cubes. If you include in your process(es) one that does a CubeSaveData command for the cube in question you can then use the date and time stamp to check and see if it has been updated since the last time the process has been run.

Re: How to know last cube updated time in TM1

Posted: Fri Aug 17, 2018 9:40 am
by Mark RMBC
I wondered if there would be something simpler than this, but I would have followed Tom by doing a CubesaveData on the cube in question and then running a batch script within a TI process to get the last modified date, so a command like:

wmic datafile where Name="FilePath\\FileName.cub" get LastModified >> "FilePath\\FileName.txt"

I would hold this date in a control cube and the next time it is run check this control cube date with the new date and if they were different run the process.

Not sure if I have overcomplicated this though!

cheers, Mark

Re: How to know last cube updated time in TM1

Posted: Fri Aug 17, 2018 12:50 pm
by lotsaram
amit_hhh wrote: Thu Aug 16, 2018 5:37 am I have a scenario in which there is a cube export TI which executes every 15 min and places the file at the specific location. Now I need to enhance this logic in a way that the export TI should execute only when there is data change in the cube, not every 15 min as the former way is taking unnccessary disk storage when data is same in the export file.

How can I achieve this? Is there a way I can get the last update time of the cube other than .cub file update time? If .cub file update time is the only way, I am not sure how I can use that time in TI logic to implement the condition for TI execution.
Take a step back. What is the actual requirement? Is the current technical solution fulfilling the requirement? Is modifying the current solution actually a good solution or is there a better way?

If you are on a modern enough version that supports delta transaction log queries via Rest then you can just initialize a delta query and then monitor the cube in question for changes at a defined interval. (Advancing on this you can even stream changes with small delay (practically live) from one instance to another.

Or you could read the transaction log itself and load those records somewhere else as opposed to the whole cube file ...

If the cube is small then probably no problem with frequent CubeSaveData but if the cube is many GB in size then this could/will definitely cause performance issues.

The best solution all comes back to what needs to be done and why?

Re: How to know last cube updated time in TM1

Posted: Sat Aug 18, 2018 4:37 pm
by paulsimon
Hi

You can get the time the cube was last updated via the VB/C API and probably by the Rest API too but anyway the information is available in the underlying TM1 model even if it not surfaced in any easily accessible way. The VB/C API method works for any change to the cube, so if the cube is updated directly by a user, eg in a budgeting/forecasting scenario, rather than by a TI, then it still gets the latest time updated.

I can't remember exactly how I did it now, since I tend to use the wrapper that I wrote for the TM1 API, rather than using the API directly. In my wrapper the code is just eg

Code: Select all

Sub CubeTimeLastUpdated()
    Call StartTM1EasyAPI
    Debug.Print oTM11EasyAPISserverConnection.Cubes("PNLCube").LastTimeUpdated
    Call CloseTM1EasyAPI
End Sub
I would not go for the approach of CubeSaveData as frequent flushing of data to disk could cause problems.

The approach of just checking some control totals to see if they have changed has some merit. However, it can be difficult to choose the correct control totals correctly, eg someone transferring budget from one Dept to another may not change the control total, but there has still been a change.

However, I tend to agree with other people who have replied. I would keep it simple and just run a chore every 15 minutes. If you have people updating the cube frequently then writing out to SQL every time a change is made could create a bottleneck on the SQL side. If you do go for the option of monitoring the Cube Time Last Updated, then I would still only write out every 15 minutes or so. I am guessing that your concern is more to avoid tying up the cube and the SQL database while data is being read and written and you therefore only want to do this when the cube has actually been updated?

I am sure that the RestAPI will have an equivalent of the VB/C API call to get this. Check some of the example for the Rest API on this forum and query the available properties on it.

Regards

Paul Simon

Re: How to know last cube updated time in TM1

Posted: Mon Aug 20, 2018 9:34 pm
by macsir
In rest api: {{protocol}}://{{serverName}}:{{httpPortNumber}}/api/v1/Cubes
You can see it:
"Name": "XYZ",
"Rules": "",
"LastSchemaUpdate": "2018-08-08T04:58:01.499Z",
"LastDataUpdate": "2018-08-08T04:58:01.265Z",