replicating data from multiple sources

[JamiseBondi]

Hi guys,

I'm researching how I would go about creating a consolidated report from multiple separate instances to get an overview of all of them in another (higher level) report. So if we had Company 1, Company 2 and Company 3 each being a standalone separate TM1 instance and then another instance (all running on the same TM1 server) called Group. I'm wanting to replicate data from companies 1,2 and 3 into the Group instance and consolidate this for reporting. The cubes will be an identical design with the group company having one extra dimension to differentiate the 'lower level' companies data. I know this isn't the ideal environment to do this in and it should be done in BI, but this is an interim step that will be replaced in a year or so once the new BI environment is ready to overhaul but it's a requirement for now.

The question is, have you guys done this, is it complex, will I run the risk of overwriting data in the same cube? I plan to have a dimension in the Group instance called 'Company' that will allow each individual company instance to write into so that the intersection is unique.

Any feedback would be appreciated, thanks.

[declanr]

As long as you have the separate "company" dimension that is really just an indicator for which tm1 service it is sourced from then you shouldn't have any issues with overwriting as you just zero out the relevant elements only and write to the relevant elements only.

I don't think there is anything wrong with the approach at all; it is reasonably common to have tm1 servers set up as a planet/start configuration to handle a distributed environment. You obviously need to think about how best to transfer data across (personally I would always try and use an instance of SQL etc as the medium to give a few days traceability at least).

I know this isn't the ideal environment to do this in and it should be done in BI, but this is an interim step that will be replaced in a year or so once the new BI environment is ready to overhaul but it's a requirement for now.

I wouldn't necessarily say its the wrong tool... I certainly wouldn't jump at the idea of BI being a better place to consolidate. Personally I try and avoid doing any calculations on TM1 data in BI if the calculation can just be handled in TM1... in fact I try avoid doing calculations in BI altogether as I find it much more quicker to do your calculations in source systems and then you can guarantee that its "one source of the truth" and everyone gets the same output regardless of which system they look in.

[Mems]

Totally agree with declanr with his answer.
Just to add what we did in terms of a data load.

Instead of using the SQL (which when reading this might have been have been better)
We added a nightly chore kicking out a asciioutput from each tm1 instances and and the being picked up by the consolidated instance.

The process was pretty straight forward. Especially since your reporting cubes sounds like it will have similar dimensions.

[JamiseBondi]

thanks guys, much appreciated