Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ASP.NET Dashboards from Multiple Systems, Multiple SQL Servers

Posted on 2009-05-19
1
298 Views
Last Modified: 2013-12-07
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.
Data Example:
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
 
Dashboard Example:
Overall Revenue for last week
Drill into Overall Revenue by day
Drill into Overall Revenue by hour

Open in new window

0
Comment
Question by:perksdev
1 Comment
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 500 total points
ID: 24438641
Based on your requirements it sounds like you need to build the data in OLAP data cubes.  Basically you schedule the import of data into the cubes preferably on a different server, then use the cubes to create drill-down reports.  If memory serves me correctly, OLAP is available with the Enterprise version of SQL.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An enjoyable and seamless user experience can go a long way on an eCommerce site. While a cohesive layout and engaging copy play roles in creating a positive user experience, some sites neglect aspects that seem marginal but in actuality prove very …
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
The viewer will learn how to dynamically set the form action using jQuery.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question