Database Reference
In-Depth Information
The majority of companies start by designing or purchasing an OLTP system that supports the operational
activities of the business. Reporting and analysis is initially accomplished based on OLTP data; however, as the
business grows, that approach becomes more and more problematic. Database schema in OLTP systems rarely suit
reporting purposes. Reporting activity adds load to the server and degrades the performance of and the customer
experience in the system.
Data partitioning can help address some of these issues; however, there are limits on what can be achieved with
such an approach. At some point, the separation of operational and analysis data becomes the only option that can
guarantee the acceptable performance of both solutions.
■
While you can
technically
separate OLTP and Data Warehouse data into different tables in the same database
or in different databases running on the same sQL server, it is rarely enough. The Data Warehouse workload is usually
processing a large amount of data, and this adds a heavy load on the I/O subsystem and constantly flushes the content of
the buffer pool. all of this negatively affects the performance of an OLTP system. It is better to deploy the Data Warehouse
database to a separate server.
Note
OLTP systems usually become the source of the data for Data Warehouses. The data from OLTP systems is
transformed and loaded into a Data Warehouse with
ETL(Extract Transform and Load) processes
. This transformation
is key here; that is, database schemas in OLTP and Data Warehouse systems do not and should not match.
A typical Data Warehouse database consists of several dimensions tables and one or a few facts tables.
Facts
tables
store facts or measures of the business, while
dimensions tables
store the attributes or properties of facts. In our
Point-of-Sale
system, the information relating to sales becomes facts while the list of articles, customers, and branch
offices become dimensions in the model.
Large facts tables can store millions or even billions of rows and use terabytes of disk space. Dimensions, on the
other hand, are significantly smaller.
A typical Data Warehouse database design follows either a
star
or
snowflake
schema. A
star schema
consists of a
facts table and a single layer of dimensions tables. A snowflake schema, on the other hand, normalizes dimensions
tables even further.
Figure
34-1
shows an example of a star schema.
