Microsoft® SQL Server® 2012 Bible

1143

CHAPTER

51

Business Intelligence

Database Design

IN THIS CHAPTER

Understanding Data Warehousing Concepts

Understanding Warehouse Structure and Relationships

Loading Facts and Dimensions

Managing Different Types of Dimensions

The majority of this book discusses Online Transactional Processing (OLTP) databases, which are

typically used for operational purposes, such as Sales, Human Resource and Payroll management,

Business Process Management, Student Information Systems, and so on. In recent years there has

been a slow evolution by a large number of organizations to analyze their data for trends in an

attempt to make more informed decisions about a company’s direction, which is often referred to

as Business Intelligence (BI). When these queries and analytics are run directly against the OLTP

database, often confl icts and contention occur that generally affect performance.

To mitigate these problems, organizations usually copy the OLTP database to another server

and offl oad all the reporting to that database. Although this approach may solve some of the

problems, it does not completely solve them all. This is because OLTP systems are not optimized

for large summary queries that return large volumes of data. In addition, OLTP systems usu-

ally don’t persist historical data values. Finally, OLTP systems usually have a primary focus as

mentioned earlier. As a result, typically, a small population of individuals has a deep under-

standing of the data.

The next step that is usually taken is to create a database that summarizes the OLTP data into new

tables. Again, this may solve some of the problems. However, because it is still a refi ned set of data,

it is often insuffi cient in performing analytics that are holistic to the entire organization.

So how do you completely solve this problem? To solve the problem you must provide a data source

that contains a centralized consistent view of the data available to the organization as a whole.

Now users can perform OLAP without contending with the operations system. Over the years OLAP

has become synonymous with data warehousing. OLAP provides business with the ability to quickly

analyze large data sets in different ways. The data warehouse is typically the database structure

c51.indd 1143c51.indd 1143 7/31/2012 10:28:40 AM7/31/2012 10:28:40 AM

http://www.it-ebooks.info