Exporting changed data only - flag?

Post Reply
HyunaHyuna
Posts: 5
Joined: Thu Jun 16, 2016 7:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Exporting changed data only - flag?

Post by HyunaHyuna » Wed Feb 28, 2018 10:09 am

Hi! Here is an issue we have been facing pretty much since we started our project: We are currently loading all data from our TM1 10.2.2 to our DB every night (couple dimensions with 50+ elements), because we cannot narrow our subset of what to export down. Users might change any data at any time, basically.

This is taking hours, as well as prompting us to turn off logging, because there is massive amounts of data. Does anyone have any useful tips on whether TM1 has any functions to flag data that has been changed since a given time? (Is this accessible in Planning Analytics?). I have researched using transaction log, but it is generally not considered reliable enough. Does anyone have any experience saying otherwise?

How do other developers tackle this problem?

kangkc
Community Contributor
Posts: 179
Joined: Fri Oct 17, 2008 2:40 am
OLAP Product: TM1
Version: 9.x
Excel Version: 200x
Location: Singapore

Re: Exporting changed data only - flag?

Post by kangkc » Wed Feb 28, 2018 10:53 am

Did you attempt to isolate where the bottleneck is ?

In many cases this is at the DB (SQL?) end as ODBCOuput is not really an optimum way to upload into RDBMS.

Also how do you clear the current data in table before insert ? Delete or Drop table ? All this will have impact to the overall run time.

User avatar
tomok
MVP
Posts: 2412
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: Exporting changed data only - flag?

Post by tomok » Wed Feb 28, 2018 12:00 pm

HyunaHyuna wrote:
Wed Feb 28, 2018 10:09 am
This is taking hours, as well as prompting us to turn off logging, because there is massive amounts of data.
Why do you think turning off logging is going to help you export data faster??? Transactions that write to cubes are logged, not those that read.
HyunaHyuna wrote:
Wed Feb 28, 2018 10:09 am
I have researched using transaction log, but it is generally not considered reliable enough.
Generally considered by who to be unreliable? I've never heard anyone say that before but you. If you have logging turned on for all the cubes that users can write to (assuming these are the changes you want to export) then I have no reason to believe that those changes won't show up in the logs. If they didn't that would be a major bug in the tool and we would know about it.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
Steve Rowe
Site Admin
Posts: 1742
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Exporting changed data only - flag?

Post by Steve Rowe » Wed Feb 28, 2018 12:13 pm

As kangkc implies but doesn't actually say a better approach to this would be to output a flat file that can be imported by the DB.

I'd also support tomok, if you wanted to just move data changes the transaction log should be fine, assuming logging is on for the cubes concerned. It would be a pretty unusual approach though. At some point you will have to sync the DB up with the data in the cube, so you'll need two sets of code.

tm123
Posts: 95
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Exporting changed data only - flag?

Post by tm123 » Wed Feb 28, 2018 1:13 pm

HyunaHyuna wrote:
Wed Feb 28, 2018 10:09 am
Hi! Here is an issue we have been facing pretty much since we started our project: We are currently loading all data from our TM1 10.2.2 to our DB every night (couple dimensions with 50+ elements), because we cannot narrow our subset of what to export down. Users might change any data at any time, basically.

This is taking hours, as well as prompting us to turn off logging, because there is massive amounts of data. Does anyone have any useful tips on whether TM1 has any functions to flag data that has been changed since a given time? (Is this accessible in Planning Analytics?). I have researched using transaction log, but it is generally not considered reliable enough. Does anyone have any experience saying otherwise?

How do other developers tackle this problem?
There a couple of workarounds or potential solutions to that.
1. You can use transaction log file and build a mechanism to find what you need there, but you have to make sure that Logging is turned on in your input cubes. If you have TI processes that load data into your input cubes, and if you turn off the logging before the data load start (in Prolog or in a master process), then make sure logging is turned back on after data load is finished (in Epilog or in a master process).
2. For all your Input Measures, you create another measure like in example below for FTE:
FTE_Delta.GIF
FTE_Delta.GIF (4.75 KiB) Viewed 428 times
Delta FTE will have a non-zero value only if FTE value is different from Previous FTE Value, and then after you run the export process, set Previous FTE = FTE

But I do agree with others than if you use ODBCOutput to write directly to your database, that is slow so you can export to a Text File first and then use an ETL ( or even a Bulk Load statement) to load your database table from the Text File

HyunaHyuna
Posts: 5
Joined: Thu Jun 16, 2016 7:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Re: Exporting changed data only - flag?

Post by HyunaHyuna » Wed Feb 28, 2018 3:11 pm

Thank you for your responses! I apologize for some of the confusion I caused, I did mix up a few things and wasn't clear enough.

The process imports data from DB, does a lot of data load between cubes, and then exports. The actual issue (again, sorry for not being clear enough at first, this is my first post) is that the data loads between cubes, the import, the export, all of it takes a lot of time, because we also transfer a large amount of data that has not changed, so we overwrite cells with the same values.
kangkc wrote:
Wed Feb 28, 2018 10:53 am
Also how do you clear the current data in table before insert ? Delete or Drop table ? All this will have impact to the overall run time.
We use viewzeroout, because we need to load separate countries (found in a dimension) at different times of the day.
tomok wrote:
Wed Feb 28, 2018 12:00 pm
Why do you think turning off logging is going to help you export data faster??? Transactions that write to cubes are logged, not those that read.
We don't do this to speed up transfer, we do this so that the transaction log doesn't grow by 100 gigs every day :) But I would very much prefer a solution that would let us have transaction logging on, it would be really helpful with debugging.
tomok wrote:
Wed Feb 28, 2018 12:00 pm
Generally considered by who to be unreliable? I've never heard anyone say that before but you. If you have logging turned on for all the cubes that users can write to (assuming these are the changes you want to export) then I have no reason to believe that those changes won't show up in the logs. If they didn't that would be a major bug in the tool and we would know about it.
I looked into this option a couple of months ago, on this forum and on other sites, and there were enough people saying that this solution (loading info into DB from transaction log) would be unreliable. Not strictly because the tool is buggy, if I remember correctly, but because it is too easy to turn off. There were other reasons that, I admin, I have forgotten. Again, if you have ever tried exporting daily data into DB from TM1 using its transaction log, please tell me how you did it, I would be forever grateful.
Steve Rowe wrote:
Wed Feb 28, 2018 12:13 pm
As kangkc implies but doesn't actually say a better approach to this would be to output a flat file that can be imported by the DB.
We actually do this, I apologize for not being clear enough. We export to flat files that DB picks up. But I want to only export (and load internally) changed data.
Steve Rowe wrote:
Wed Feb 28, 2018 12:13 pm
I'd also support tomok, if you wanted to just move data changes the transaction log should be fine, assuming logging is on for the cubes concerned. It would be a pretty unusual approach though. At some point you will have to sync the DB up with the data in the cube, so you'll need two sets of code.
How often do you think I would need to sync? Would weekly syncs be enough? We could keep current code for longer loads during the weekend, and have a faster approach during weekdays, when users use the application... :ugeek:
tm123 wrote:
Wed Feb 28, 2018 1:13 pm
2. For all your Input Measures, you create another measure like in example below for FTE:
FTE_Delta.GIF
Delta FTE will have a non-zero value only if FTE value is different from Previous FTE Value, and then after you run the export process, set Previous FTE = FTE
Thank you for this idea, I think we might be able to use (something like) this! :) :)

User avatar
tomok
MVP
Posts: 2412
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: Exporting changed data only - flag?

Post by tomok » Wed Feb 28, 2018 3:44 pm

Your original post should be a poster child for why you should provide all relevant details when asking for help. Your original request was like asking "where can I get a hamburger for lunch" when what you really wanted to know was "where can a I get a grass-fed, under 25 grams of fat, within 5 miles of my current location, where they have a drive-thru, and they sell milkshakes too, hamburger for lunch".
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

HyunaHyuna
Posts: 5
Joined: Thu Jun 16, 2016 7:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016

Re: Exporting changed data only - flag?

Post by HyunaHyuna » Thu Mar 01, 2018 8:52 am

I get where you are coming from, but believe me when I say that I tried to verbalize my bundle of issues as best as I could. Please cut me some slack, I am a newbie, I will do better in the future.
While we're talking about this, your replies were more about shouting at me (multiple question marks) instead of helping me with my question or giving me constructive criticism on how I should post in the future.

User avatar
Steve Rowe
Site Admin
Posts: 1742
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Exporting changed data only - flag?

Post by Steve Rowe » Thu Mar 01, 2018 9:54 am

HyunaHyuna and tomok,
Just to head off a flame war, I'd just like to say that both your last two posts contain valid points.
tomok, you'll know that we have a policy and standard to follow if you are not happy with the approach a poster is taking.

I'll lock this thread if there are any further off-topic comments.

Thanks,
Steve

Post Reply