Database Reference
In-Depth Information
Alter Table [dbo].[FactTitlesAuthors] With Check
Add Constraint [FK_FactTitlesAuthors_DimAuthors]
Foreign Key ( [AuthorKey] ) References [dbo].[DimAuthors] ( [AuthorKey] )
Alter Table [dbo].[FactTitlesAuthors] With Check
Add Constraint [FK_FactTitlesAuthors_DimTitles]
Foreign Key ( [TitleKey] ) References [dbo].[DimTitles] ( [TitleKey] )
Alter Table [dbo].[FactSales] With Check
Add Constraint [FK_FactSales_DimStores]
Foreign Key ( [StoreKey] ) References [dbo].[DimStores] ( [Storekey] )
Alter Table [dbo].[FactSales] With Check
Add Constraint [FK_FactSales_DimTitles]
Foreign Key ( [TitleKey] ) References [dbo].[DimTitles] ( [TitleKey] )
Alter Table [dbo].[FactSales] With Check
Add Constraint [FK_FactSales_DimDates]
Foreign Key ( [OrderDateKey] ) References [dbo].[DimDates] ( [DateKey] )
Alter Table [dbo].[DimTitles] With Check
Add Constraint [FK_DimTitles_DimDates]
Foreign Key ( [PublishedDateKey] ) References [dbo].[DimDates] ( [DateKey] )
when you are done, if you found any transformations that were missed, go back
7.
and finish them by adding the necessary sQL code to your script file. once that
is done, save your script file to:
C:\_BISolutions\PublicationsIndustries\
PublicationIndustriesETLCode.sql
.
in this exercise, you created the code that gathers data for the data warehouse objects. Most of these tables
are still empty because we have defined only the source of the data, but we start filling them up using a
combination of the sQL statements you have just created along with ssis tasks in the next chapter. Before
we do that, however, we need to look at three common ways of abstracting your sQL code: using views,
stored procedures, and user-defined functions.
Working in the Abstract
It is common knowledge in the programming industry that building software by defining multiple layers can
give you greater flexibility and lower maintenance costs. One of the classic ways of doing this is by providing an
abstraction layer between the objects that contain data and the software that uses that data. In Microsoft SQL
Server, this can be done with three simple database objects: views, stored procedures and functions.
The concept of abstraction basically means designing your software so that the underlying objects are always
used indirectly. In the case of a database table, you do not directly connect your SSIS applications to the table
itself, but instead utilize one of these abstraction objects.
The advantages of this design include the following:
•
It can mask the complexity of programming statements by binding these statements to a
named database object.
•
Underlying data structures can undergo changes as a normal part of maintenance, but
the abstraction objects will hide these changes.
•
Permissions can be given to the abstraction objects and not to the underlying data
structures, thus protecting the data structure from misuse.
