Solved

ASP.NET Dashboards from Multiple Systems, Multiple SQL Servers

Posted on 2009-05-19
1
295 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:
tedbilly earned 500 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Change data in datatable 8 14
MS SQL Pagination for Complex Query 5 31
Do we need servers??? 5 114
Cordova Camera plugin fails 2 5
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Any business that wants to seriously grow needs to keep the needs and desires of an international audience of their websites in mind. Making a website friendly to international users isn’t prohibitively expensive and can provide an incredible return…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now