Database Reference
In-Depth Information
Figure 6-14.
Documenting your ETL transformations
■
when creating your ETL code, updating your documents to define the transformations required may be
the most convenient place to start. Although we do not believe that this is a replacement for formal documentation,
it can allow developers to track their updates and provide you with accurate information about those changes. Later
this document can be turned into formal documentation. we show you examples of this in Chapter 19.
Note
Building an ETL Script
As you work with the ETL process, it is a good idea to organize all the ETL code you generate. This can easily be done by
opening up SQL Server Management Studio to create a new query, typing in the necessary code, and saving the code
as a SQL script file. The advantages to this are that you can later add script to your SSIS project as a miscellaneous file,
and it can also be reused in the future for other projects that are similar. In the next exercise, you do just that.
eXercISe 6-1. creatING aN etL ScrIpt
in this exercise, you create the ETL code needed to fill the Publication industries data warehouse. You can
choose either to type the sQL code presented here or to use the Query Designer tool to accomplish your
goal. we recommend using both.
Important:
You are practicing administrator-level tasks in this topic, so you need administrator-level
privileges. The easiest way to achieve this when opening a program is to remember to always right-click a
menu item, select Run as Administrator, and then answer Yes to access administrator-level privileges while
running this program. in windows 7 and Vista, logging in with an administrator account is not enough. For
more information, search the web on the keywords “windows 7 True Administrator and User Access Control.”
1.
open Excel from the start menu using the Run as Administrator option.
2.
Locate and open the following Excel spreadsheet:
C:\_BookFiles\Chapter06Files\BISolutionWorksheets.xlsx
. You can do so by
clicking the File tab in Microsoft Excel and choosing the open option from the menu.
