Our development team has been tasked with creating "Executive Dashboards" for our Executives as well as some of our client's Executives. We need to include data from 3 separate apps (ASP 3.0, ASP.NET 2.0, ASP.NET 3.5 SP1) with data across 2 servers (older application on a SQL Server 2000 machine, 2 newer applications running on a SQL Server 2005 machine). The data needed from the apps is similar including orders, products, logins, page views, revenue. But the data is organized totally different in each app's database(s).
We are looking for ideas on getting "close to real-time data" from all 3 apps (possibly more later) available from a single web-based dashboard. We aren't looking for dashboard solutions, but ideas on getting the data together or ways of pulling the data. We are also very worried about the performance of both the existing systems and the dashboard.
We have thought about using triggers in the different systems, but ran into a problem with having to setup the Distributed Transaction Coordinator since they would have to execute across servers. We don't have an in-house DBA, so we'd rather not use DTC.
Any ideas would be very helpful! Thanks in advance for any help or advice.
Order Data (on SQL1) from App1 includes Price, Name, Qty, TotalCost
Order Data (on SQL2) from App2 includes Price, Name, Qty, TotalCost, CountryCode, CurrencyCode
Order Data (on SQL2) from App3 includes Price, Name, Qty, TotalCost # of Recipients
Overall Revenue for last week
Drill into Overall Revenue by day
Drill into Overall Revenue by hour