Database Reference
In-Depth Information
➤
Source system data changes:
The key question here is, will your system produce the same
data after a full reload? That may or may not be the case, depending on the behavior of your
source system. Keep in mind that every source system has some data that is overwritten. You
have to check your data elements and identify those attributes that cannot be overwritten
and avoid doing full reloads on those attributes.
It is not uncommon for analytics systems to have two different scripts — one for incremental load
and one for full reload. Take these issues into consideration in your design to avoid painful surprises.
Set up logging and data validation
To make your analytics system robust, you must add some logging and validation capabilities to it.
We go over some simple approaches related to these concepts in this section. Think of this as an
auditing and quality assurance function.
➤
Logging:
The purpose of logging is to keep track of your system runs. There are several com-
ponents to logging in your analytics system. Keep a simple table of your system runs and add
initial and modified dates on your tables. Logging can help you look at your system and
understand:
When a run occurred
●
How long it took
●
What it updated
●
➤
Validation:
A key factor in the success of your analytics system is reliability. If you have
reports going to various users, including top management, the last thing you want to do is to
send them bad numbers. It is in your best interest to do everything you can to increase the
reliability of your output. Adding validation steps can help you catch mistakes before they go
out for general consumption. Validation involves adding queries to check for the reasonable-
ness of your output. That could take several forms, including:
Comparing a value against a running average
●
Checking a summary of update counts manually or automatically
●
Trapping and flagging bad data (data that falls outside a certain range or that violates a
certain rule)
●
Working with SQL Scripts
SQL scripts are automated procedures you develop to tell SQL Server to perform changes to your
system. They can be saved in files or stored on the server in the form of stored procedures. These
scripts can then be executed on demand or according to a schedule.
We give you an overview of SQL scripting in this section, including outlining scripting patterns that
are most commonly used in analytics systems.
Search WWH ::
Custom Search