Integration of data from multiple TM1 servers

[IanB]

I have a number of TM1 servers distributed around the world - all running TM1 v9.1.4 (32 bit)
They run local variations of a financial planning application and although dimensions and cubes are similar, they have enjoyed a certain amount of local development.
The challenge I am now addressing is how to pull high level data from these servers into a central application

My initial experiments have involved remote TI processes to create ascii comma delimited files that I can then bring to the centre and import to a central TM1 server. The problem is that this is a bit fiddly as I need to set this up processes and chores separately on each server
Surely there is a better way - do members of the group have any experience using TM1's data retrieval capabilities to avoid use of text files
I am now investigating options to pull data to the centre
1) TI remote connection
2) TM1 Replication
3) ODBO connection to the remote servers

Does anyone have any recommendations. Any better ideas?

IanB

[David Usherwood]

1) TI remote connection
This _sounds_ like 'I would like to point a TI to a view in a cube on another TM1 server'. Brilliant idea, would be really useful. Asked for it five years ago. No sign.
2) TM1 Replication
Can't make selective - and not reliable unless your servers are completely identical
3) ODBO connection to the remote servers
Didn't scale when we last tried it, also very poorly documented.

So flat files is your (not very) best option.

We have a project just starting where we are segmenting the app into three servers and passing data around. Plan is to use a staging relational store to align dimensions and attributes (and probably cube metadata), but flat files for data, since odbcoutput('insert into....') is so much slower than asciioutput. Servers will be on the same box so passing data won't be a problem, also neatly leverages modern multi core servers with TM1's very limited use of multithreading.

[paulsimon]

Ian

You mentioned that you only want to send summary data from each of the satellite servers. Another possibility is to define a common cube to hold the summary data, which has standard dimensions, etc. Each local site would need to write a TI to populate this cube (with logging on). That then shifts the problem of any incompatibilities they have introduced in the original cubes out to them. You then just need to set up replication to pull in the data from the summary cube.

You can then process the data from the Summary cubes on the main server to merge data from the 3 or so summary cubes in to one big cube. Alternatively you could just have one summary cube, but that would then mean that you would send out the data from the other sites to theirs. However, that probably wouldn't happen except on a full replication because changes from synchronisation don't create logs.

With the file method, you could get more control, by connecting to each of the remote servers and running the export chore. That would be better than having to rely on a scheduled chore, and therefore having to check whether it had run or not.

Regards


Paul Simon

[Steve Vincent]

i'm with Paul on that as i'm doing something similar myself. My hierarchies are common in the key dimensions so i have a central "sync" server that is used to maintain them. That then uses replication on the element attribute cubes to ensure all the dims are aligned in the other services. Some data cubes are also replicated but only because they are total and complete copies - the data i am grabbing from the satellites in to a consolidated, higher level central cube is all TI based because it's the only way i can accurately control what it is doing.