Suggestion

Hi,

We have an application that access multiple databases for generating reports
and running various queries. These databases are segregated as follows:

Server1 DB1 1999-2001
Server2 DB2 2002-2004
Server3 DB3 2005-present

Data is sync-ed up using replication. Database grows at an average 4 million
records per year. The database is not a Warehouse. Database schema is
normalized. Need to keep historical data for generating baseline reports.

This approach has various drawbacks including the major one that if any
report has to span for the entire duration than data has to retrieved from
all databases and then grouped up as summary. This takes lot of time. Also,
over a period of time, we can't just keep adding more hardware and escalate
the current problems.

So, I am looking at a new database architecture which solves the current
problem and is scalable also.

Here are the considerations:
1. New architecture should support fast report generation
2. Be scalable to support new report
3. Easy to Manage and Maintain

Looking forward to suggestions:

thanks,
.

Relevant Pages

  • RE: updated numbers in report
    ... for making an input to the database. ... textbox will be located alongside with the combobox where the belonging ... belonging query for the report. ... ElapsedTime([Date First Time Onboard], ...
    (microsoft.public.access.dataaccess.pages)
  • Re: Creating a report entirely through code
    ... In my case this is a very simple database, ... >> thus greatly increase the probability of corruption. ... >> button doesn't gray out after a compile, a control starts to ... >>>report and not a form where they have to enter data in. ...
    (microsoft.public.access.formscoding)
  • Re: Creating Access DB
    ... database and in the SQL database that your report needs. ... click the query tab. ...
    (microsoft.public.excel.programming)
  • Re: MSSQL$SBSMonitoring Database size (SBS2003)
    ... You can use the SQL Client Utilities to try and shrink the database. ... to delete over 90 day old information from the monitoring ... The server will start to collect new counter value ... Check 'View the usage report in Server Management' option. ...
    (microsoft.public.windows.server.sbs)
  • Re: Suggestion
    ... Datamarts allow you to create summarized data and improve the query response ... You have to choose the better source regarding the report you have to ... Detailed information can query your OLTP data sources. ... >> Keeping the historical vs. current data partitioned by database is ...
    (microsoft.public.sqlserver.datawarehouse)
Loading