Database Reference
In-Depth Information
Figure 2-2.
Documenting the plan
During the planning phase, researching how to accomplish the types of transformations you need during
the ETL process helps us estimate what needs to be done during the ETL process. It also lets us contact the client
earlier if we discover a problem. Although you do not actually create the ETL process yet, you do want to feel
confident that you can accomplish the task when the time comes.
Listing 2-1 shows SQL code that takes a date as a string of 11 characters like those found in the text file and
converts them into datetime data. One of the transformations listed in the Excel file in Figure
2-2
requires this
change; thus, we can test how this is accomplished and whether this data will be clean enough to use for the ETL
process we perform later.
Listing 2-1.
Sample ETL Code
-- Convert the string to datetime
Declare @Date Char(11)
Set @Date='1/23/2011'
Select @Date;
-- Outcome = 1/23/2011
Select Convert(datetime, @Date)
-- Outcome = 2011-01-23 00:00:00.0
Adding Documents to Visual Studio
At this point, we have two documents that outline the BI solution: the original file and our Excel workbook. We
should now think about organizing our work by grouping the documents in some manner. As we mentioned
earlier, we are placing the documents into a folder that will be added to a Visual Studio solution.
If you are not familiar with Visual Studio already, you should know that it organizes projects and code files
under a structure Microsoft calls a
solution
. These Visual Studio solutions consist of a folder with a set of XML
files that identify which projects and files are part of the solution.
Creating Visual Studio Solutions and Projects
You can create a Visual Studio solution in a couple of ways. For example, if you create a Visual Studio project, a
Visual Studio solution will automatically be created for you. If you are not ready to make a project yet, you can
also create a blank solution and add projects to it later. In both cases, you can add documentation and script files
to the solution folder at any time.
Each project you make in Visual Studio uses a predefined template. These templates are part of various plug-
ins to Visual Studio. Once a project plug-in installs, it becomes part of Visual Studio, similar to how the Adobe's
Flash plug-in becomes part of your web browser.
