Having data housed on any number of servers across a variety of locations is always a difficult burden when it comes to reporting. Many businesses know that consolidating all of that data into a single data warehouse and cube environment is essential for their reporting needs, yet they do not know how to proceed with this task. Although the Jet Data Manager (JDM) can complete this task independently, it is not the recommended best practice because of the method in which the data will traverse the distance.
The first thing that should be done in an effort to consolidate multiple databases into a solitary data warehouse is to bring all of the data to the centralized location where the JDM is installed. However, having the JDM facilitate this transfer isn't the best option because using SSIS to pull large amounts of data across large distances can be exponentially slower than standard replication measures. Therefore, it is recommended to use the SQL Server Replication processes to bring the data onto a centralized server because these processes are purpose-built for this exact scenario. Once the data has all been centralized, the JDM will work exceptionally faster because it is then simply working with local copies of the various databases from various locations.
Whenever consolidating multiple databases from multiple locations, we recommend the use of SQL Server's Replication tools prior to bringing the data into the JDM because of the speed differences between replication and SSIS transfers. Once the data is centralized, the JDM can be used to consolidate the different databases into a single data warehouse and cube environment without the performance degradation involved in moving large amounts of data over large distances. To proceed with the SQL Server Replication process, there are a variety of resources available online. Below are just a few links that you may find helpful in setting up replication within your environment.