Database Reference
In-Depth Information
Chapter 13
Creating Reports
with SQL Queries
I'm convinced you can combine this with reporting integrity and accuracy.
—Jack Brickhouse
With the data warehouse completed and filled with data, it is time to realize some of the fruits of your efforts by
making your first preliminary reports.
In this chapter, we take a look at the process of creating report code using the SQL programming language
and some of the decisions that you have to make regarding this code. We walk you through the process of creating
report queries from start to finish.
A data warehouse can have thousands of reports against it, and all reports have aspects in common:
•
Reports are viewed with user applications such as Excel, PowerPivot, or Reporting Server
Reports.
•
The underlying code is most often written in SQL or MDX (query languages used for
relational and OLAP databases).
•
All report code should be consistent and well formed.
•
Abstraction layers are used to keep code maintenance costs low.
Reporting queries can become complex very quickly, and complex queries can be overwhelming for
developers who do not do a lot SQL programming; therefore, we do what we can to keep them simple. And, to
make things more understandable, we break the SQL code into bite-size chunks and break down each of the
features that typically make up a standard SQL report query. Using the methods in this chapter, you will soon be
writing polished, professional, and accurate queries.
■
Examples in this chapter are SQL-based. MDX is the other common reporting language in use. We cover
MDX next, in Chapter 14.
Note
