For best performance, should we install Analysis Service and the cubes as the same server as the production database?
We are currently running a transactional database using SQL Server. We want to set up Analysis Services and several cubes in order to get the reporting burden off the transactional database in order to boost performance. For best performance, should we install Analysis Service and the cubes as the same server, or on another server?
As you mentioned in your question, your current transactional server is already burdened by the current reporting that is going on. This tells me right away that this server is already too busy and should not take on any more work. For best performance, Analysis Services and your cubes should be located on a separate server. This will reduce the current load on the transactional server, boosting its performance. In addition, the new server should be devoted to Analysis Services, and not shared with any other software. This way, Analysis Services will have all the power it needs to produce reports on a timely basis.