Database Reference
In-Depth Information
As you can see, using the
CONVERT()
function allows you to change datetime data types into a more readable
character format, but because we are not interested in storing the dates in a character format, it is irrelevant in
our current ETL process. It is nice to know that it exists, however, and that it provides additional options beyond
what the
CAST()
function provides. A word of caution: keep in mind that
CAST()
is a standard ANSI function and
CONVERT()
is not. As a result, if you need to pull source data from tables in an Oracle or MySQL database,
CAST()
is still the better option.
Looking Up Surrogate Key Values
In Chapter 5, we mentioned adding surrogate keys to your dimension tables as a best practice. These artificial
columns allow you to merge data from different sources and record data changes more effectively. Therefore, it is
no surprise that we use surrogate keys in addition to natural keys in all our dimension tables in this topic.
This necessitates that the foreign key relationships between our data warehouse tables are based on the
surrogate keys rather than original natural columns. These are different from the relationships defined in the
source database. As a result, we need to identify a way to look up the new surrogate key value based on the
natural key value in the original tables.
To help you more thoroughly understand this issue, take a look at Figure
6-4
. In this figure you can see that
the original link between the titles and publishers table in the OLTP database was defined in the pub_id column.
This is no longer true in the data warehouse where the link is provided based on the PublisherKey column.
Figure 6-4.
Comparing tables with and without surrogate keys
