Databases Reference
In-Depth Information
performance issues. Reporting on changing data can also be risky because the report can only capture a
single moment in time, while this data continues to change.
Decision-support database systems are typically designed to be exclusively read-only and use data struc-
tures much simpler than those used by equivalent transactional databases. This optimizes report perfor-
mance and keeps the data consistent for a set period of time. Users understand that they aren't looking at
the most current data, but they know it should be accurate as of the end of business on the previous day.
One of the challenges when reporting on data in an existing database is that the database may not have
been designed with your reporting requirements in mind. Even the simplest reporting requirements can
often be difficult to meet without writing very complex queries. This can slow performance and only
support a certain amount of data. For small and simple database systems, reporting on the same tables
in a transactional database as the rest of the applications may be the easiest choice.
Building an End-to-End Reporting Solution
Using Reporting Services you can create an entire user experience by prompting for input parameters,
customizing query operations, filtering, sorting, and using report item actions and navigation features.
However, this all assumes that the data sources contain the necessary data in a form that is both accessi-
ble and scalable to meet future demands. If this isn't the case, what might have looked like a simple
reporting project can take on a whole new dimension. Decision-support systems often involve a separate
database that is populated at regular intervals from one or more transactional sources. Typically, scripts
run during off hours to copy new data from the main database into a set of simplified tables designed
especially for reporting. A
data mart
is a decision-support database used within a department or business
unit to serve up report data to meet a set of specific business requirements. It could be as simple as a
small set of denormalized tables in a relational database, or it could be a set of OLAP cubes in a hierar-
chal database system like Microsoft Analysis Services. Queries for OLAP reporting will be written in a
hierarchal expression language, like MDX, rather than T-SQL.
Data warehouses and data marts are similar in principle but different in scale. A
data warehouse
is typi-
cally a large-scale, enterprise-wide system that meets the reporting needs of many business groups, and
will nearly always be deployed as a specialized OLAP system.
An effective ETL process involves not only copying new data from one system to another but also trans-
forming many rows from many source tables into preaggregated rows that describe specific facts. In the
end, the decision-support data, populated by the ETL process, helps meet reporting business require-
ments as effectively as possible. Tools such as SQL Server Data Integration Services (formerly Data
Transformation Services, or DTS) are often used to implement ETL.
Sample and Test Data
During report design, it is very important to work with data similar to that of report users. Sample or
“mocked-up” data is often meaningless in a business context and doesn't exhibit the same characteristics
as the real thing. This data should represent variety and should adhere to the same business rules as
real data so that data grouping, sorting, and filtering features can be designed with predictable results.
Where possible, production data (or at the very least, production-like data) should be used for report
design and testing. Sensitive information can be scrubbed by using search-and-replace iterations and cal-
culations to modify numeric and currency values. Report designers shouldn't be expected to enter their
own data because they're too focused on the process to create effective test data. This is like expecting
application developers to test their own code.
Search WWH ::
Custom Search