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
How to know last cube updated time in TM1
-
- Posts: 28
- 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
Regards,
Amit Saxena
India
Amit Saxena
India
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: How to know last cube updated time in TM1
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
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
-
- Community Contributor
- Posts: 248
- 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
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
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 ;
-
- MVP
- Posts: 2832
- 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
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.
-
- Community Contributor
- Posts: 292
- 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
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
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
-
- MVP
- Posts: 3654
- 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
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?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.
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.
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: How to know last cube updated time in TM1
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
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
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
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
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: How to know last cube updated time in TM1
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",
You can see it:
"Name": "XYZ",
"Rules": "",
"LastSchemaUpdate": "2018-08-08T04:58:01.499Z",
"LastDataUpdate": "2018-08-08T04:58:01.265Z",