The first concern of any report developer—as far as report development is concerned—is how to implement a reporting solution without negatively impacting the existing data infrastructure. Reporting requires significant resources, process and infrastructure-wise. You cannot just build a SQL Server Reporting Services (SSRS) system and slap it in your primary production SQL Server OLTP. It just doesn't work that way.
As a developer, your highest concern is to steer clear of any possible conflict with the performance of any production databases. You should be wary of the implications of building a reporting system in your environment.
An ideal reporting delivery project usually involves the whole array of Microsoft Business Intelligence (BI) stack. The process would look something like this:
An infrastructure, as illustrated, previously would be too expensive to implement. Data cleansing and integration in the Extract, Transform, and Load (ETL) phase can take a huge amount of time and resources to complete. Implementing an Online Analytical Processing (OLAP) system is rigorous and expensive as well, probably more expensive than ETL/Integration. Plus, the hardware requirements for either of the two (ETL and OLAP) can blow your whole department budget.
In an economy where businesses are cautious with their finances, it would be next to impossible to convince the management to embark on such project.
It's really hard to justify the cost of implementing a project of this magnitude to the stakeholders. And there is a reason for that: A reporting system is seen by most as a matter only of housekeeping—data tracking, bookkeeping, and analysis—and that it is not worth the overhead it incurs on the finance aspect of the business.
Another concern that a developer should consider, therefore, is the political aspect of the reporting project. But, that is a topic better addressed in some other books.
Let's just say that a full stack BI solution is not an option in your SSRS 2012 project implementation. We agree that it's too expensive for most reporting system requirements. We could simplify the structure into something like the following diagram:
In the preceding illustration, we eliminate the most expensive part of the infrastructure, the OLAP. We cut a great portion of the project cost but we probably eliminate the most important segment of our BI. But, that's just fine for the most part because our data marts might just be adequate for what we need.
But still, a reporting system that requires data cleansing and integration is not cheap either. Let's just say that the management still couldn't afford such a system. So, we might end up with something like the following diagram:
Now that we eliminate the ETL integration portion, we are left with a big gap between our data store and reporting system. We have a backend (data store) and frontend (reporting system) but we don't have a middle process that would bridge the two.
The worst possible thing that you would resort to is eliminating the in-between process:
You cannot afford to put your SQL Server Reporting Services inside your primary SQL Server boxes. You could probably save some money but it could cost you more in the long run.
Our solution then is to put some process between our data stores and reporting system.
One option that could close that gap is to build a separate SQL Server box, say SQL Box 2, with similar database structures as our original data source, say SQL Box 1, and use SQL Box 2 as the data source for our reporting system. We then implement some merge mechanism to transfer data from SQL Box 1 to SQL Box 2. In this scenario, we could use T-SQL scripts with a joining clause such as MERGE.
The whole process could look something like the following diagram:
Again, this setup looks decent. It's practical and economical to implement. You could probably run the scripts through a SQL Server Agent Job and run them at schedule. But, there's a little problem with this.
The performance of the merging process still depends on so many things such as hardware, joins, object dependencies, indexes, and many others. There are just too many points of failure in this setup.
Performance is not just the main concern in the merge setup. Performance is just one side of the coin. The other side is time. There is high latency between when the merge occurs and the completion of the merge. Although you can automate the merge process, it could still take some time to complete the merging.