RE: Warehouse Server Requirements

Hello Clint,

Dataware house usually is organized to archive history data of OLTP Server.
If data is simply archived for preservation, it is not available or
organized for use by analysts and decision makers. If data is allowed to
accumulate in the OLTP so it can be used for analysis, the OLTP database
continues to grow in size and requires more indexes to service analytical
and report queries. These queries access and process large portions of the
continually growing historical data and add a substantial load to the
database. The large indexes needed to support these queries also tax the
OLTP transactions with additional index maintenance. These queries can also
be complicated to develop due to the typically complex OLTP database
schema.

A data warehouse offloads the historical data from the OLTP, allowing the
OLTP to operate at peak transaction efficiency. High volume analytical and
reporting queries are handled by the data warehouse and do not load the
OLTP, which does not need additional indexes for their support. As data is
moved to the data warehouse, it is also reorganized and consolidated so
that analytical queries are simpler and more efficient.

Online analytical processing (OLAP) is a technology designed to provide
superior performance for ad hoc business intelligence queries. OLAP is
designed to operate efficiently with data organized in accordance with the
common dimensional model used in data warehouses.

A data warehouse provides a multidimensional view of data in an intuitive
model designed to match the types of queries posed by analysts and decision
makers. OLAP organizes data warehouse data into multidimensional cubes
based on this dimensional model, and then preprocesses these cubes to
provide maximum performance for queries that summarize data in various
ways. For example, a query that requests the total sales income and
quantity sold for a range of products in a specific geographical region for
a specific time period can typically be answered in a few seconds or less
regardless of how many hundreds of millions of rows of data are stored in
the data warehouse database.

It's usually recommended that you use different boxes for OLTP and OLAP
(datawarhouse) for performance and manintenance purpose. You may want to
refer to the following articles for more related information:

Data Warehouse Design Considerations
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part5/c1761.msp
x?mfr=true

Microsoft SQL Server 2005 Analysis Services Performance Guide
http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b
2011c/SSAS2005PerfGuide.doc

MICROSOFT SQL SERVER SQL Server OLTP vs. data warehouse performance tuning
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1083557,00.html?
bucket=ETA&topic=301333

Hope this information is helpful. Please feel free to let's know if you
have further questions or comments on this. Thank you.
Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

.

Relevant Pages

  • Re: reporting advice
    ... Having views from your staging into the OLTP system ... So I suggest replicating the OLTP to a Reports server. ... I am looking at creating a data warehouse where our OLTP and Archival ... thinking of using transactional replication to feed from the OLTP ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: New Database System Overview
    ... useful when you cannot afford a query on OLTP during daily operations. ... I you want to design a "classical" relational data warehouse, ... technique between the OLTP transaction and the ODS update: ... terminals feed our OLTP servers in near real time. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: New datawarehouse
    ... Adhoc queries are contending with transactional inserts/updates ... The OLTP is slowly down due to volume of data and the OLTP doesn't need the ... Queries are to hard for users to create against the complex OLTP schema. ... The usual system development life cycle and operational techniques and ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Create star schema in SQL Server 2005?
    ... real-world example taking a simple transactional table, ... "data warehouse" on it. ... tblEmployee in the OLTP database... ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: SQL Server newbie has basic questions...
    ... clear picture of what OLTP is, but from what I gather I think that will ... but must all be available for queries as any time. ... > hardware to buy except storage space. ... > Data Warehouse type system etc. ...
    (microsoft.public.sqlserver.setup)
Loading