How to know last cube updated time in TM1

Post Reply
amit_hhh
Posts: 20
Joined: Wed Jan 18, 2017 3:27 pm
OLAP Product: Cognos TM1
Version: 10.2.2, 11
Excel Version: Excel2010
Location: Bangalore, India

How to know last cube updated time in TM1

Post by amit_hhh » Thu Aug 16, 2018 5:37 am

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
Regards,
Amit Saxena
India

User avatar
orlando
Posts: 52
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: How to know last cube updated time in TM1

Post by orlando » Thu Aug 16, 2018 1:28 pm

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

Edward Stuart
Community Contributor
Posts: 222
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: How to know last cube updated time in TM1

Post by Edward Stuart » Thu Aug 16, 2018 2:48 pm

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 ; 

User avatar
tomok
MVP
Posts: 2494
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to know last cube updated time in TM1

Post by tomok » Thu Aug 16, 2018 2:53 pm

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Mark RMBC
Regular Participant
Posts: 164
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: How to know last cube updated time in TM1

Post by Mark RMBC » Fri Aug 17, 2018 9:40 am

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

lotsaram
MVP
Posts: 3140
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How to know last cube updated time in TM1

Post by lotsaram » Fri Aug 17, 2018 12:50 pm

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?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
paulsimon
MVP
Posts: 648
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: How to know last cube updated time in TM1

Post by paulsimon » Sat Aug 18, 2018 4:37 pm

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

User avatar
macsir
Community Contributor
Posts: 552
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: How to know last cube updated time in TM1

Post by macsir » Mon Aug 20, 2018 9:34 pm

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",
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Post Reply