Database Reference
In-Depth Information
Figure 6-10.
Selecting the columns in the Query Designer
We need to extract data from the original Author's source table. Therefore, we write a SQL
SELECT
statement
to retrieve this data. If you check the checkboxes on each column you want to use, the Query Designer will add
them to the
SELECT
statement you are building (Figure
6-10
).
The Authors and DimAuthors tables contain several differences. As a result, we need to apply a number of
transformations to the basic query. These include combining data from multiple columns into one column of
data, renaming the existing columns, and converting column data types.
Common transformations are concatenating two columns to form a single name and converting source
data into a different data type. In our example, we want to combine an author's first and last name into a single
column. Keeping the names separate in an OLTP database is common, but it is unlikely to be of use in our sales
data warehouse, that is, unless you think that reports will be made that will aggregate the measures on a person's
first or last name.
In the DWPubsSales data warehouse, we are going to assume that a sales report detailing the sales quantity
based on an author's last name is not needed. Therefore, we are going to combine an authors' first and last names
to keep the reporting process as simple as possible. To combine this data, we can add a simple concatenation to
the query, as shown in Figure
6-11
.
