Database Reference
In-Depth Information
Figure 2-23.
Solution Explorer at the end of Exercise 2-2
4.
Leave visual Studio open for now because we continue working with it in the next
exercise.
in this exercise, you executed a SQL script that created a data warehouse. You then placed this script into
a new solution folder using visual Studio. Soon, you will be adding additional projects to the solution. Your
ultimate goal is to place all the code and projects you need for the weatherTracker Bi solution into this one
visual Studio solution. we continue this process in the next exercise by adding a SQL Server integration
Services project.
Create the ETL Process
With the data warehouse created, it is time to start the extract, transform, and load (ETL) process. During this
phase of a BI solution, just as the title of the process states, you must first extract the source data, then transform
it as necessary, and finally load it into the data warehouse tables. In the WeatherTracker project, the text file
called
WeatherHistory.txt
is the source of the data. The destinations are the tables you created in Exercise 2-2.
The transformations needed for the WeatherTracker ETL process are listed in the Excel spreadsheet we created
earlier (Figure
2-2
). To create the ETL process using SSIS, we examine this spreadsheet making special note of
column names, data types, and transformations listed. Let's review an SSIS project based on our recorded plan.
ETL with an SSIS Project
SSIS represents Microsoft's premier ETL tool. It is one of Microsoft's business intelligence servers, and it is
one of the project types available in Visual Studio. To create an SSIS project, start Visual Studio and select
File
➤
Add
➤
New Project from its main menu, as shown in Figure
2-24
. Doing so forces the Add New Project
dialog window to appear (Figure
2-25
).
