Database Reference
In-Depth Information
This is not meant to imply that the results of the query are materialized in a physical
structure, although that is an option if caching is configured.
Caching the result set of a query may seem wasteful. However, when
viewed from a more holistic perspective, the benefits can be substantial.
Given the nature of standard reporting—where the same reports are
being run by large numbers of users—the ability to cache the data
retrieved from the source system can provide considerable performance
gains and greatly reduce the load placed on the source system. We'll
cover this topic in more detail later in the chapter.
While creating a dataset, the developer first chooses the data source, which can be
a shared data source referenced by the report or an embedded data source created
and contained within the report. Once the data source has been selected, a query
is created—the results of which will be made available as a two-dimensional
structure—like the one in the preceding screenshot, which can then be consumed
by the various report items.
It is important to understand that the use of the word "query" in the preceding
paragraph is in the most general sense. The type of query is completely dependent
on the data source. For example, if the data source is a relational database, the query
could be a standard SQL select statement, or it could be the result set from a stored
procedure. On the other hand, if the data source is an Analysis Services database,
the query could be an MDX query (for a multidimensional cube), a DMX query (for
a data mining model), or even a DAX query (for a tabular model that is new to SQL
Server 2012).
Dataset queries can be entered manually as text or constructed using the graphical
query designer shown in the following screenshot. The graphical query designer
is helpful when the report author is not very familiar with the query language.
However, report authors who are more proficient in the query language are
likely to prefer the manual entry method or a combination of the two.
