|
|
You're in the
sql server data warehouse
group:Suggestion
sql server data warehouse:
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:
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 [quoted text, click to view] "Ajay" <Ajay@discussions.microsoft.com> wrote in message news:026E7607-C818-4855-AC9C-9FEF90C623BF@microsoft.com... > 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,
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 [quoted text, click to view] "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@discussions.microsoft.com> wrote in message > news:026E7607-C818-4855-AC9C-9FEF90C623BF@microsoft.com... > > 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, > >
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 [quoted text, click to view] "Ajay" <Ajay@discussions.microsoft.com> wrote in message news:1DF2A234-A6B0-4B7E-AE25-48506EA58B4A@microsoft.com... > 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@discussions.microsoft.com> wrote in message > > news:026E7607-C818-4855-AC9C-9FEF90C623BF@microsoft.com... > > > 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, > > > > > >
you are right,
you have to separate the OLTP system from the OLAP/Reporting system. you can create some datamarts to solve your queries. To improve the loading process (and query process), keep historical data into separate tables, but you can do this on 1 server (histo + current data). Datamarts allow you to create summarized data and improve the query response time. and/or use OLAP cubes to have sub-second response time. You have to choose the better source regarding the report you have to generate. if your most requested report need to summarized information about a specific customer (total sales, total complaints, total email sent...), so create a summarized table with preaggregated information then 1 simple query return all your information. Detailed information can query your OLTP data sources. There is many way to create a good responsive solution with and without a data warehouse. This depends of your knowledge, the budget and time you have. [quoted text, click to view] "Ajay" <Ajay@discussions.microsoft.com> wrote in message news:1DF2A234-A6B0-4B7E-AE25-48506EA58B4A@microsoft.com... >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@discussions.microsoft.com> wrote in message >> news:026E7607-C818-4855-AC9C-9FEF90C623BF@microsoft.com... >> > 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, >> >> >>
Hi Ajay,
"How are big databases designed? What are the fundamentals for arhitecting such database?" Well, what you are dealing with is not a 'big' database any more and would not have been called so for a while...... Producing reports off operational systems has always been a 'dead end' path for all but the very smallest of organisations....and maybe not even then..... There are plenty of materials around for 'newbies' now. Lots of books, web sites etc...... I have made an effort to link some of the better sources I know about on my web site so feel free to go to my web site www.peternolan.com. I have also published a lot of papers, presentations, code etc...and I have also linked a lot of information for 'newbies'..... I am building my site as a 'useful place' for people new to BI/DW to go......feel free to comment as well... :-) Best Regards Peter
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |