Success with replication

[cbeycwright]

I've been poking around the topics here and found several that comment on replication - nothing specific to what I want though. The comments seem mixed to me. I'm trying to do something in particular (not exotic), so I thought I'd see if anyone has created a similar scheme/architecture/version to what I want.

I'm on 9.5.1 (64). Right now I have one server only. We are frequently running processes to load incremental data from various databases and sporadically do other TI-related operations that can be expensive (five minutes here, five minutes there). The result is a lot of locking. Since we have few people that update data and a lot of people that report on data, it makes sense to me to have a write server with the full model and a read server with a subset of the write server.

A few questions I haven't found concrete answers to that perhaps someone can comment on:

• Does TM1 limit updates to only changed cells or, in the case of a data refresh from a DB, would all cells that have been written be sent to the mirror(s)? For example, writing a value of 5 to a cell that currently contains the value 5 is still a write to the cell, but it's not changing the value. My guess is it sends it on a change since that would be in the transaction log.
• Is there no concept of a read-only mirror? Can updates be unidirectional only - source to mirror? If my budget/driver submitters had reserve perms and never wrote to a mirror, then I'd effectively have a read-only relationship. However, it would be nice to simply disable all writes to mirrors by anyone other than the source.
• I've seen comments on hung replications. Is this common? Easily resolved (depends on the cause probably)? Preventable? They don't sound fun to me.
• Loosely related here, but does anyone have experience with load balancing TM1 servers? In this case, I mean create a cname, point clients to that, then use a basic round robin scheme to direct clients to TM1 boxes behind the top-level name. It's probably a question of session state ... whether there is any between the client and server. I don't know - thoughts? (Searching for "load balancing" only got me one result on here!)

Apologies if any of this has already been answered here or in the operations guide.

Thanks for reading.


Moving data between TM1 servers
http://forums.olapforums.com/viewtopic. ... eplication

Replication
http://forums.olapforums.com/search.php?fid[]=3

[Steve Vincent]

I've been poking around the topics here and found several that comment on replication - nothing specific to what I want though. The comments seem mixed to me. I'm trying to do something in particular (not exotic), so I thought I'd see if anyone has created a similar scheme/architecture/version to what I want.

I'm on 9.5.1 (64). Right now I have one server only. We are frequently running processes to load incremental data from various databases and sporadically do other TI-related operations that can be expensive (five minutes here, five minutes there). The result is a lot of locking. Since we have few people that update data and a lot of people that report on data, it makes sense to me to have a write server with the full model and a read server with a subset of the write server.

A few questions I haven't found concrete answers to that perhaps someone can comment on:

• Does TM1 limit updates to only changed cells or, in the case of a data refresh from a DB, would all cells that have been written be sent to the mirror(s)? For example, writing a value of 5 to a cell that currently contains the value 5 is still a write to the cell, but it's not changing the value. My guess is it sends it on a change since that would be in the transaction log.
• Is there no concept of a read-only mirror? Can updates be unidirectional only - source to mirror? If my budget/driver submitters had reserve perms and never wrote to a mirror, then I'd effectively have a read-only relationship. However, it would be nice to simply disable all writes to mirrors by anyone other than the source.
• I've seen comments on hung replications. Is this common? Easily resolved (depends on the cause probably)? Preventable? They don't sound fun to me.
• Loosely related here, but does anyone have experience with load balancing TM1 servers? In this case, I mean create a cname, point clients to that, then use a basic round robin scheme to direct clients to TM1 boxes behind the top-level name. It's probably a question of session state ... whether there is any between the client and server. I don't know - thoughts? (Searching for "load balancing" only got me one result on here!)

Apologies if any of this has already been answered here or in the operations guide.

Thanks for reading.


Moving data between TM1 servers
http://forums.olapforums.com/viewtopic. ... eplication

Replication
http://forums.olapforums.com/search.php?fid[]=3

I can answer some of these for you;

1> Replication works from log files and log files alone. It records when it last replicated, checks all logs since that date and any data changes logged since then will be copied across. If your sporadic updates are not logging changes then replication will likely be slower than your current setup. The only advantage it'll give is the ability to update during working hours but replicate to a reporting server over night. Fine if you don't need immediate view of the changes, but in my experience that is rarely the customers prefered option.

2> No, replication is always bi-directional. If security is set on the read only server so no one can change them it will behave one directional, but the replication process itself will still check both sides for changes. If you have big log files that will mean a long replication time no matter what.

3> "Hung" and "Taking a long time" are 2 different and subjective things. As everything is done from logs it has to use disk rather than memory to handle data. Couple that with large log files since the last replication, maybe slow network connections between the servers and it all adds up. As with everything on TM1 its subjective to your own models and the only real way to find out is to try it. Ours aren't bad but they all sit in the same data centre - try it across continents and i'm sure the responses will be different.

4> I think i understand the concept you are referring to and no, you can't do that. Client points to admin server, admin server then knows the host its registered with and directs requests that way. You could point 1 TM1 service to multiple admin hosts and connect from either, but its not the admin host doing the work so it'd make no difference. TM1 is very basic in some of its IT concepts <cough> user passwords </cough> but this one would be pretty complicated to manage.

[tomok]

2> No, replication is always bi-directional. If security is set on the read only server so no one can change them it will behave one directional, but the replication process itself will still check both sides for changes. If you have big log files that will mean a long replication time no matter what.

Although replication is bi-directional, with proper configuration of your cube logging, you can effectively make it uni-directional. If the planet server is is truly only a reporting server I would turn off all logging on it, except maybe on the ClientProperties cube. Your log file on the planet server would be very tiny and almost all the transaction checking, updating, would be star to planet. For what this user wants to do, replication could be a workable option, as long as he doesn't try to replicate too many times during the day or have too much data to replicate on each cycle or both servers will be occupied for too long and users will have no clue why TM1 isn't responding.

[mattgoff]

I'm on 9.5.1 (64). Right now I have one server only. We are frequently running processes to load incremental data from various databases and sporadically do other TI-related operations that can be expensive (five minutes here, five minutes there). The result is a lot of locking. Since we have few people that update data and a lot of people that report on data, it makes sense to me to have a write server with the full model and a read server with a subset of the write server.

Steve did a good job of directly addressing your questions, so I won't repeat. What I will do is offer that, if your problem is locking during data loads for large amounts of data and/or a slow source server, you could improve your end-user performance by establishing a hidden data source cube.

I had a similar problem where our Oracle GL reporting server slowed to a crawl during close. As a result, I had major lock problems a few days a month. To resolve this, I created a hidden data cube with no rules linking to it and now pull from the GL reporting server into this hidden cube. Since there are no rules, the visible cubes remain available during the data load. After the load is complete, a second TM1 process updates the visible cube using the hidden cube as a datasource-- the local copy for us means at least two orders of magnitude less lock time for end-users.

Separately, I also did a lot of work to tighten up the wipe/refresh to a limited number of periods. Whereas a scorched earth policy was cleaner and easier, by limiting the frequent refreshes to a single period, I really improved performance (at the slim risk that if accounting re-opened an old period, TM1 would not see the change-- something we addressed with a process/policy mechanism).

If you read my other posts on here, you'll see that I'm not a huge fan of replication. It's a fragile, admin-intensive tool, and I really don't recommend it to anyone for any reason. There's almost always a way to work around needing it, but if you do have to implement it be prepared for a lot more work than you were expecting. You will need to design your own detection system to monitor servers for desynchronization and your own recovery mechanisms to fix it-- happens to us a few times a year. Replication has it's own locking issues for the planet server too.

Matt

[cbeycwright]

This is all really solid input, guys. I appreciate it.

Verdict: replication doesn't sound like something I want to tangle with right now. Besides, the problem I'm having - locking - may best be remedied with a solution like mattgoff suggested. We actually did that (a no-frills, no-rules staging cube) with a headcount model we recently built. We did it that way to trace the changes in an employee over time, not really to avoid any locking issue. But staging the data is essentially what we did and it has worked out well. We sync the data to another cube and while it involves a lot of TI-based lookups, the performance is fine. Applying this to our incoming financials makes a lot of sense. (Shouldn't be any lookup business with staging financials either.)

Thanks for all the replies. Hopefully future lurkers will find this thread as valuable as I have.

[Steve Vincent]

Glad it has helped

The other item to content with it architecture and the size of your models. The staging idea is great if you have plenty of RAM to play with, 64bit will likely never be an issue but for those of us stuck on 32bit it can be. If the cubes are quite chunky and RAM limits are a factor then repeating a cube might not be viable. It is one of the reasons i enjoy using this software, so many paths you can take to achieve the same end goal and it might not always be the most obvious ones that turn out best.