Database Reference
In-Depth Information
•
The same data structure can be represented multiple ways without having to create
duplicate data structures.
You can probably think of more advantages than those we have listed, but these are enough to make one
consider utilizing them in your ETL solutions. In fact, Microsoft has long recommended it as a best practice to do so.
Views
One of the most common tools used in a database is a SQL view. A SQL view consists of a named select statement
that is saved internally in the database. The view is used as if it were a table. Indeed, it is sometimes called a
virtual table
. This is a misnomer, however, because it gives the impression that the table and the view are more
similar than they truly are.
One of the biggest differences between a table and a view is that table data has a physical representation on
your hard drive, whereas the view is just a saved select statement. Yet views still act as if they were tables, and you
can select against the view exactly as you would against a table.
Although there are some restrictions about which SQL SELECT clauses are allowed in a view, they are
still useful for many ETL processing tasks. For example, the code in Listing 6-21 creates a view around a SQL
statement that extracts data required for a sales fact table. Note that the view transforms the column names
using column aliases. It also transforms data found in multiple tables into a single logical table using SQL
JOIN
statements.
Listing 6-21.
Creating a View for ETL Processing
Create View vEtlFactSalesData
as
Select
[OrderNumber]=ord_num
, [OrderDateKey]=DateKey
, [TitleKey]=DimTitles.TitleKey
, [StoreKey]=DimStores.StoreKey
, [SalesQuantity]=qty
From pubs.dbo.sales
JOIN DWPubsSales.dbo.DimDates
On pubs.dbo.sales.ord_date=DWPubsSales.dbo.DimDates.date
JOIN DWPubsSales.dbo.DimTitles
On pubs.dbo.sales.Title_id=DWPubsSales.dbo.DimTitles.TitleId
JOIN DWPubsSales.dbo.DimStores
On pubs.dbo.sales.Stor_id=DWPubsSales.dbo.DimStores.StoreId
Using column aliases and SQL Joins in a query may be rather simple for experienced SQL programmers,
but it is considered advanced by most novices. Once the view is created, however, the complexity of this query is
masked by the view. You can then query the view using a simple SQL statement, such as the one in Listing 6-22,
that is understood by everyone.
Listing 6-22.
Querying the View
Select * from vEtlFactSalesData
