Re: Suggestion

To reduce the processing time of your non-OLAP reports, you may want to
implement summary tables with the records aggregated based on the usage
needs of your queries. For example:

insert into SALES_SUMMARY
select
period,
quarter,
region,
category,
sum(sales) as sum_sales
from
Server1.SALES..SALES
union all
Server2.SALES..SALES
union all
Server3.SALES..SALES
group by
period,
quarter,
region,
category


"Ajay" <Ajay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1DF2A234-A6B0-4B7E-AE25-48506EA58B4A@xxxxxxxxxxxxxxxx
> i think around 50% of the time the historcal server are sittng idle.
>
> Anyways tell me this if i have to propose a new architecture for ths
> requirement then how should it be done. One of my suggeston s lets
seperate
> OLTP and OLAP and the have reports run on OLAP. What do you thin? What do
you
> suggest?
>
> How are big databases designed? What are the fundamentals for arhitecting
> such database?
>
> apprecate your help
>
> thans
> "JT" wrote:
>
> > Keeping the historical vs. current data partitioned by database is
probably
> > a good idea, because it reduces the seek time for the most frequently
used
> > data, allows more flexibility in placement of data files on disks, and
> > reduces the size of full database backups, if records for historical
years
> > are static. However, if historical 2004 - 1999 data is rarely used, then
> > would there be a cost / maintenace justification to keep it hosted on
> > seperate servers? What percentage of the time would Server1 be sitting
idle
> > waiting for a user to request a report that includes historical data?
> >
> > As for spanning historical and current records into one query, read up
on
> > "partitioned views" in SQL Server Books Online. For example, the
following
> > implements a view of the Customers table, which is split across 3
servers.
> >
> > --Partitioned view as defined on Server1
> > CREATE VIEW Customers
> > AS
> > --Select from local member table
> > SELECT *
> > FROM CompanyData.dbo.Customers_33
> > UNION ALL
> > --Select from member table on Server2
> > SELECT *
> > FROM Server2.CompanyData.dbo.Customers_66
> > UNION ALL
> > --Select from mmeber table on Server3
> > SELECT *
> > FROM Server3.CompanyData.dbo.Customers_99
> >
> >
> >
> > "Ajay" <Ajay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:026E7607-C818-4855-AC9C-9FEF90C623BF@xxxxxxxxxxxxxxxx
> > > 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: SYSTEMS ENGINEER/ VAX-VMS/ CAREER POSITION
    ... System Administration and database admin from NIIT, ... salary details and generate reports and enclosures,and calculates ... Responsibilities: ... Write New programs from user requirements, Programming Changes ...
    (comp.os.vms)
  • Re: Advice needed for a growing Access 2000 project
    ... However, it turned out that quite a few of those were "leftovers" from previous releases, no longer accessible from anywhere but the database window, and, thus, no longer used. ... But that certainly isn't the _norm_ -- without any 'heroic' measures, there are routine reports of split Access DBs ... Finally, in my opinion, for "Windows apps", that is, individual-user applications, modest-sized multiuser applications, and client-server applications of any size, Dot Net does NOT "help along" any of these issues. ... The post I reference was in reference its self to the MS Access Help file under "Microsoft Access database general specifications" ...
    (comp.databases.ms-access)
  • Re: running report cause fatal error- on Win98, not XP
    ... It is actually not that difficult to crash JET with complex queries, ... another query. ... only the more complex reports cause the crash- the others do ... Then compact the database: ...
    (microsoft.public.access.reports)
  • Re: running report cause fatal error- on Win98, not XP
    ... another query. ... only the more complex reports cause the crash- the others do ... Then compact the database: ... Still in the code window, choose Compile from the Debug menu. ...
    (microsoft.public.access.reports)
  • Re: WHY
    ... As most database processing doesn't take place on Windows systems, ... >spreadsheet and copy if every month.. ... prejudiced to learn that reports aren't the only thing spreadsheets can do. ... since the number of periods over which loan payments could be made is ...
    (microsoft.public.excel)
Loading