Database Reference
In-Depth Information
Existing Tables
Stored Procedures or Views
Separate Reporting Database
Data Warehouse with Procedures and Views
Data Warehouse with Procedures and Views and Cubes
Figure 1-5.
An example of how reporting data sources change over time
Many companies begin by selecting report data directly from OLTP relational tables. Quite often, they come
to regret this choice when performance issues occur and maintenance costs rise. It has long been considered a
poor choice to do so, yet this is still happening in businesses today.
An improvement on this design, and what is considered to be “best practice,” is to create views or stored
procedures that select data from one or more OLTP tables and use these as the source for all of your reports.
Many reports can then be created against a single view or stored procedure, which makes maintaining your
reports much easier over time. For example, consider a scenario where a decision has been made that all tables
must be renamed to start with the letters
tbl_
. All that you need to do to keep your reports working properly is
change the table names in the select statements within the view or procedure to reflect the new table names,
while maintaining the same output from the view or procedure. With this simple step, your reports will continue
to work as they always have. Chapter 13 of this topic shows how easy it is to create both views and stored
procedures.
Stored procedures and views can access data in the same database, across databases, and even across
different database servers. You will gain better performance, however, when you query data from a dedicated
reporting database, otherwise known as a
data warehouse
. These report databases are designed to provide simple
and efficient reporting. Once the data warehouse has been created, you need an ETL process to copy the data
from its original locations to the new reporting data warehouse database.
■
The term
data warehouse
can have a number of meanings. in this topic, a database designed for reporting
with one or more centralized fact tables containing measured data such as sales quantities, with zero or more sup-
porting dimension tables containing additional measured data descriptions, is considered a data warehouse. You may
hear this type of database referred to as a
data mart
,
data silo
,
data factory
, and a host of other names. However,
Microsoft documents refer to it as a data warehouse, so we do too.
Note
Additional report performance is provided by using SSAS cubes. This performance increase, however, is
at the cost of your solution becoming more complex. The most common complexity is that cube databases use
different programming languages than relational databases. We discuss the most common of these programming
languages, known as MDX, in Chapter 14.
To round out your report-building skills, we present report-building applications in Chapter 15. We work
with Microsoft's desktop-based reporting application, Excel 2010. Then, in Chapters 16 and 17, we create reports
using Microsoft's server-based reporting application, Reporting Services 2012.
