Database Reference
In-Depth Information
Creating Planning Documents
We created two tables (Tables 2-2 and 2-3 ) that document information about the client's data and what we know
about it so far.
Table 2-2 lists the data source combined with descriptive names in one column and the data types in the other.
Because all the data is coming from a text file rather than an existing database table, the data types are all strings.
Table 2-2. Documenting the Source
Data Source
Source Data Type
FlatFile.Date
String
FlatFile.Max TemperatureF
String
FlatFile.Min TemperatureF
String
FlatFile.Events
String
In Table 2-3 , you see a listing of the destination columns, destination data types, any transformations we can
expect to use, and an example of the outcome of those transformations. The purpose of this is to document the
design of the destination tables, so we have listed the appropriate data types.
Table 2-3. Documenting the Destination
Destination
Data Type
Data Destination
Transformations
Example
DimDates.DateName
datetime
add zero as needed
and cast to datetime
01/23/2011
FactWeather.MaxTempF
int
cast to int
48
FactWeather.MinTempF
int
cast to int
43
DimEvents.EventName
varchar(50)
n/a
Rain
We often informally record source and destination information using a Microsoft Excel spreadsheet. From
this informal evaluation, we then proceed to create more formalized documents toward the end of the solution
life cycle. The formal documents will become a part of the BI solution we deliver to a client, while the informal
spreadsheet is for development.
One advantage of using Excel is that it may be used to outline many parts of the solution using the different
worksheets within one workbook.
As an example, one of the worksheets can include the informal information we have laid out in Tables 2-2
and 2-3 , which defines the Extract Transform and Load (ETL) process in a solution. Figure 2-2 shows that we
have recorded the need to extract dates from the flat file and convert the string data into a datetime data type, on
a worksheet called ETL Planning.
 
 
Search WWH ::




Custom Search