Recommendations for 700 users and high volumes

[Mike88]

Hi Guys,

Could anyone advise me on what the best architecture and recommendations would be for 700 users using TM1 for reading and writing (spreading and excel DBSW) in different countries. Also, we have a huge amount of data, cube size on disk is 12 Gb.

Thanks to all for your contribution.

Mike.

[paulsimon]

Mike

I can tell you that there are some systems with this number of users out there. I think that you need to consider the number of concurrent users rather than potential users when you do your sizing. However, I think I would be betraying confidences to tell you how they achieved this. If your are contemplating building a system of that size, then I would suggest that you contact some of the TM1 consultancies and IBM Cognos, to get some advice.

Regards

Paul Simon

[Mike88]

Thanks Paul for the advise but the Cognos chaps I am discussing with seem to be new to TM1 and they do not show any confidence on this matter...

Any starting point like:
- setting up a server in each country for budget inputs
- replication with consolidated server
- consider TI to transfer data from "country" budget cubes to "all regions" budget cube instead of rules
- ...

Also how to avoid lock issues when concurrent users write/spread in same cube at same time?

Thanks again.

Mike

[ScottW]

Mike,

Generally I would say 700 users is not a problem but it is really going to come down to the number of concurrent users writing data at any one time. If you went with a split out / distributed / regionalised model then any concurrency issues are likely to fade away but you will have rep and sync issues to contend with instead. I think your key issue is not the number of users or scale / volume of data but the fact that you want "to do reading and writing in different countries." Whether to go centralised or distributed is going to come down to the quality (or lack of) of your WAN and choice of data input interface as Excel, websheet and EV all have vastly different levels of performance over WAN. If you have a decent WAN then a single centralised server approach is definitely possible for EV and definately possible also for websheet provided whoever designs the system has experience in optimising TM1 web and in particular websheet performance, it would also still be possible with Excel if you use a Citrix or Terminal Server connection. Depending on number of timezones / spread of countries you might find batch windows problematic with a single server approach but this can generally be managed. With 9.1.4 or 9.4.1 concurrent write activity isn't a problem provided the correct design is applied to take advantage of granular cube locking, you really need TM1 consultants who have worked on budget entry models of similar size and scope to help you on this. TM1 architecture / design choices are going to guided or influenced by intent and purpose of the system, the model itself, quality of infrastructure, and user base. Good TM1 consultants should be able to not only assist you in design and scoping but also offer guidance as to what is best practice given an assessment of these factors.

I hope that helps somewhat. If that all sounds a little vague, that's because it is deliberately so but I make no apologies for that. With limited information it simply isn't possible to be much more specific. I think you would get the same or similar response from any TM1 consultant worth their salt. Would certainly be happy to assist you in scoping and planning, but without a detailed assessment including knowledge of your current infrastructure, systems, processes and budget model (current and envisaged) it really is difficult or impossible to offer concrete and definitive advice.

[Martin Erlmoser]

use in every region one tm1server or a terminalservice environment like citrix.
i wouldn't think about connecting 700 users over wan directly to the tm1server or over tm1web.
the other point is you will maybe need more tm1servers out of security reasons, i can't imagine that there are ~only 3 overall admins which are allowed to create processes, edit rules for 700 users. so you maybe have to seperate them by department?

only a few thoughts, i don't know your environment.

[mattgoff]

As someone who has built and runs a system of nine servers and 200ish users, I strongly recommend that you consider hiring a consultant who has specific experience with replication.

Since replication is pull, many design/arch errors can only be corrected by going server-by-server and re-establishing the replication. Replicating security objects and attributes multiplies the number of cubes to be redone, and WAN performance makes this a slow process. Also, replication timing is critical-- overlapping replications and/or processes running on the star can cause data to become unsynchronized, requiring a lot of manual tricks to make right.

I guess I'm saying that, even if you or others on the team have lots of TM1 experience, replication is a special case with many pitfalls. It's possible to work through them yourself if you have the time and inclination, but you can save yourself a lot of heartache if you get someone in that's done it before and can build it with you. I can't tell you how many times I rebuilt all eight replications over remote desktop on a Sunday..... But, touch wood, once you have got it set up correctly things do seem to run well.

Matt

[paulsimon]

Mike

I am pleased that Scott has come forth since I know that Cubewise have experience in this area.

Replication may be the answer for you. However, it has its pitfalls. TM1 does not replicate everything. For replication to work you need to have logging on, when it is generally turned off during bulk loads. It can be more efficient to do bulk loads at each site rather than replicate. A centralised server with TM1 Web or Terminal Services servers around it may be a better answer. It depends on the degree of interaction between the different groups of users and their cubes. Another alternative is to issue Budget Templates, and have them emailed back in. I have written VB to generate them with SUMIFs to give the interactive effect on consolidations. With a little VB is is possible to strip off the attachment and load it in by adding DBS. The upload could be automated overnight, leaving the server free for querying during the day. The latest version of the template can then be populated with the figures and emailed back out. The best approach will depend on your requirements.

Your cube is very large. I wonder whether all your querying needs data at that level of granularity? An approach I have used in the past is to create summary cubes at different levels of granularity. The summarisation needs to be done via TI, rather than rules for performance. The summary cube can be populated in the same pass over the source data as your larger cube. You may find that the 80:20 rule applies with 80% of users' queries being met from the summary cube(s) which can have a dramatic effect on response time.

You should also consider whether you need all that granularity in a cube and whether drill thru to transactional data, or perhaps a data warehouse style staging area, would give the users what they need.

Regards


Paul Simon

[Mike88]

Thanks to all for your thoughts.

This is a new project coming soon and my objective is to get helpful information and directions when it comes to such size of applications. Business requirements have not been gathered yet and the information I mentionned is provided from the client. We have not done any assessment of the WAN yet, I will certainly include this in my to do list!

The version of TM1 in use would probably be 9.4MR1, DEV Server Windows 64bits with 4 CPUs / 128 Gb RAM, Executive Viewer, read / write (budget) by the 700 users in 42 countries.

The client would be interested in using the workflow from Planning Manager... would anyone recommend it?

Any other suggestions are welcome!

Mike

[ScottW]

128 Gb RAM, ... what exactly are you planning on building?

TM1 Workflow probably warrants it's own discussion, uptake has generally been pretty low so you might not get a whole lot of responses.

In my opinion TM1 Workflow can be a very good tool, especially for a budget contributor model of this scope. It's only really going to be applicable though if the delivery UI is TM1 web. Workflow and TM1 web is a nice combination and very functional. There is a lack of process overview tools a la EP Contributor but it is not hard to build your own report. The Excel workflow client is rather clunky though and that goes doubly for the workflow admin client. If task dimensions are large the manual admin interface is a bit cumbersome to say the least. However if the workflow can be built from other existing dimensions or another source db then building/maintaining with TI is a breeze once set up and very very fast (but there is zero documentation so this needs to be figured out from scratch.)

[Mike88]

Thanks Scott for your comments on TM1 Workflow, that is exactly what I thought about manual admin vs automated TI admin.

About 128 Gb RAM on the DEV server, we need to plan and figure out the best architecture for high volumes of data (20 million rows daily loads), 700 users, business rules associated feeders and so forth... we need a strong server to simulate several architecture type, centralised, distributed, replication or not, virtualisation with VMWare... by the way, RAM is not that expensive nowadays from a global client point of view!

Anyone experienced capacity/load increase testing with tools such as loadrunner on TM1?

Thanks gurus.


Mike

[Harvey]

Mike,

It seems a lot more projects like this are coming up, and my company has certainly come up against several in the past year or so.

My experience backs up what Scott is saying, which is there are realistically only two workable approaches:

1) Distributed servers for each country/group, with consolidation on a central server (via replication or other mechanisms)
2) A beast of a central server with users connecting through farms of Citrix or Terminal Servers.

It is not practical to even consider WAN connections from multiple countries. You would need to consider the lowest common denominator when it comes to WAN speed, and TM1's chatty protocol simply does not perform well enough for high latency inter-country connections.

TM1Web is not a very good contribution tool for large amounts of data, as there is no cut-and-paste, excel formulas, etc. Users generally prefer creating offline Excel or CSV files and sending them in for upload.

Maybe one day we'll have Cognos Contributor integrated with TM1 which could well be the best of both worlds!

Cheers,
Harvey.

[Mike88]

Thanks for your inputs Lazarus. What about using EV for contributions?

I see a risk with centralised architecture as every country would depend on this only one "box". Distributed servers seems to be a better approach to me but I am not keen on using TM1 replication. Has anyone experienced Replication with high volumes and lots of countries?

Thanks again.

Mike

[glambert]

Mike,
I'm a consultant who built (with others) a global TM1 budget model for a company with a similar but smaller requirement (7 countries, 150 users). After trying budgetting "online" with TM1 & finding issues with input performance (some TM1 locking related, many from the latency issues of international WAN links), we ended up architecting a solution using offline Excel templates & VBA to "pick & download" relevant cost centre data, allow the creation of the budget nearly entirely offline in Excel, and then "upload" data back to TM1. To overcome latency issues on upload/download, we used a couple of regional TM1 replicated servers.

The outcome has been good, but the road to get there wasn't pretty. Note that I've never tried EV for the input option so I can't comment on that, but some of the lessons learned along the way included;

a. Minimise replication - over international links at 100ms+ latency, a nightly sync could take a couple of hours per planet server. Because the sync is initiated at each planet, they can't tell if any other planet has finished before they start - which can lead to conflict on the syncs. Times to complete can be unpredictable (due to other traffic on the links) & hence you need to give each one a "plentiful" time window. Hard to do that for more than a few servers with only 24 hours in the day on the star. Syncing between two servers on the same LAN (for example to allow for one server to do data loads/remote sync whilst another one handles the users at the main site is fast). Note the issue to watch in the WAN is far more latency related than bandwidth (TM1 does too much chatting, not enough sending). In the end we pulled out two of the servers we tried in smaller remote sites as they just gave us more trouble than the "local performance" benefits they were supposed to deliver.

b. The cube sizes in the budget data were less data volume related & more driven by architecture & feeders - despite several years data in them now, they're still quite similar in size to where they started (in fact some Rules, feeder & cube design tuning a year ago meant they got smaller).

c. Whilst the offline Excel templates took a long time to "get right & robust", the users like them as they can copy & paste, work "on the road", & it avoid TM1 locking problems. It also kept concurrency of users on TM1 very low. In order to make them reliable though, we had to lock their structure & validate entry very tightly - you have to know exactly where the data will be if you're going to send the right value to to the right account, month, cost centre, currency, etc. Duplicating a small amount of cost logic calculation - local taxes, currency conversions, etc in both Excel as well as in TM1 rules was a bit of a pain though.

d. Finally, find a good TM1 Partner. Besides the development resource you'll need to build the solution (on infrastructure, rules, TI, Excel & VBA), they could also help you proto type or concept stage some of what you want to do before you commit to one particular option & later find out you can't scale it adequately.

Geoff

[belair22]

Hi Mike

700 READ/WRITE users is quite a large number, 128-GIG on a 64 Bit server is overkill, I could go on, ... but I'd be making too many assumptions.

Your first port of call is to definitely contact a local consulting firm who have performed implementations within your industry verticle and have experience with this size of implementation. This might be difficult as most consulting firms will swear black-and-blue THEY have the necessary experience - ensure you get references and attend site visits. Successful consulting firms have a plethora of happy customers who are more than happy to disucss their experience with said consulting firm.

Cheers.

[Gregor Koch]

Hi

I don't see how 128-GIG is an overkill?? It probably is for a small implementation. But we are currently working on an implementation which constantly is on 90 GIG and peaked out at 120 GIG so we are happy about the 128GIG the box has. Development still going and growing. Definetely consider the size of the Test and/or Development server or at least the possible max memory to be close or same as Production Server. Not a must but helps, as it creates some issues for us.

Not sure as to what the current official recommondation is for EV, as far as I remember it was TM1Web over EV (at least for input).

I don't think an Excel Sheet Solution is recommandable unless it is to upload masses of data where there are no calculations needed. I think Lazarus mentioned this in a similar way as well.

Cheers

[Mike88]

Hi,

We are planning on deploying regional TM1 servers to reduce data volume (related to the regions only) and concurrent users on a centralised server. The centralised server will consolidate all dims and data.

Re replication, I have the same view and we might go with export/import but no final decision yet.

Any other suggestions?

Thanks All.

Mike

[rollo19]

Why not use a central TM1 server to load all versions/history for reporting & analysis using C8 or EV
AND Cognos Enterprise Planning Contributor (EP) for the distributed stuff > to collate the bottom-up submissions from your user base? Scale, concurrent sessions, network, usability, break-back etc. are no problem in EP.

You can sync the key dimensions in EP pretty easily with TM1 via FM and then you can load the published EP data back into TM1 with TI.