Any bad records might be placed in a temporary table and the
control flow could email the data warehouse administrator that some records failed
to load properly. An example of a simple flow can be seen in Figure 3-5.
Figure 3-5 A simple SSIS package that empties the warehouse and reloads it from
scratch.
46 B u s i n e s s I n t e l l i g e n c e w i t h M i c r o s o f t O f f i c e P e r f o r m a n c e P o i n t S e r v e r 2 0 0 7
Data Quality
Unfortunately, no company has completely clean data. The old saying is that as soon
as you make something idiot proof, the universe creates a bigger idiot. While this is
likely true, it??™s also a fact that many applications allow bad data into the system in
the first place. Having bad data then makes analysis much more difficult.
Here is a real world example: A company had a large presence in the city of Ann
Arbor, Michigan. One of the systems asked customers for their address and the input
box for the city was a free-form text field. People living in the city of Ann Arbor
found (at last count) fourteen different ways to spell Ann Arbor. These included
Anne Arbor, AnnArbor, AnneArbor, Ann Arbour, and so forth. The easy solution
going forward would be to fix the application so that the city was chosen from a
drop-down box. Unfortunately, the owners of that application were busy and didn??™t
see it as a priority. Even if it had been fixed, however, this did nothing to handle the
existing data that was incorrect.
Pages:
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88