Database Reference
In-Depth Information
3.
we have added a column called Transformations to the spreadsheet you used
in Chapter 5. it shows the transformations we need. Review the transformations
defined on the data warehouse worksheet.
4.
open sQL server Management studio 2012. You can do so by clicking the start
button and navigating to All Programs ➤ Microsoft sQL server ➤ sQL server
Management studio. Right-click sQL server Management studio 2012 and click
the Run as Administrator menu item. if the UAC message box appears asking, “Do
you want the following program to make changes to this computer?” click Yes (or
Continue depending upon your operating system) to accept this request.
5.
when sQL server Management studio opens, choose to connect to the database
engine by selecting this option in the server Type dropdown box. Then click the
Connect button to connect to the database engine. (For more information on
connecting to your database, see Chapter 5.)
6.
Decide on a method for creating the sQL script either by clicking the new Query
button and typing the code by hand, using the Query Designer, or both. Then, type
the following code in Listing 6-20 to create the script.
Tip:
This code can be found in the file
C:\_ BookFiles\Chapter06Files
from the downloadable book files.
we recommend that you type the following code yourself, but it is nice to know that you don't have to.
Listing 6-20.
The ELT Script for DWPubsSales
/*******************************************************************************
The code in this file is used to create an ETL process for the
Publication Industries data warehouse.
INPORTANT: You must run the "Creating the Publication Industries Data Warehouse.sql"
file before you can use this code. This file is in the Chapter05 folder.
*******************************************************************************/
-- Step 1) Code used to Clear tables (Will be used with SSIS Execute SQL Tasks)
Use DWPubsSales
-- 1a) Drop Foreign Keys
Alter Table [dbo].[DimTitles] Drop Constraint [FK_DimTitles_DimPublishers]
Alter Table [dbo].[FactTitlesAuthors] Drop Constraint [FK_FactTitlesAuthors_DimAuthors]
Alter Table [dbo].[FactTitlesAuthors] Drop Constraint [FK_FactTitlesAuthors_DimTitles]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimStores]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimTitles]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimDates]
Alter Table [dbo].[DimTitles] Drop Constraint [FK_DimTitles_DimDates]
-- You will add Foreign Keys back (At the End of the ETL Process)
Go
--1b) Clear all tables data warehouse tables and reset their Identity Auto Number
Truncate Table dbo.FactSales
Truncate Table dbo.FactTitlesAuthors
Truncate Table dbo.DimTitles
Truncate Table dbo.DimPublishers
Truncate Table dbo.DimStores
Truncate Table dbo.DimAuthors
