Creating and Managing SQL Scripts - Microsoft Business Intelligence Tools for Excel Analysts - page 199

Listing 10-1: Data Extraction
Declare @FromDateTime as datetime,
@ToDateTime as datetime

--You must set the @FromDateTime and @ToDateTime from the logging table

Truncate Table Extract.Customer

Insert Into Extract.Customer
Select CustomerKey, CustomerFirstName, CustomerLastName, CustomerEmail
From Orders.dbo.Customer
Where ModifyDate Between @FromDateTime and @ToDateTime

Listing 10-2 shows a more complicated extract script for ReoccuringPayment. In order to produce the
required analytics related to the table in this example, you need the OrderDate and the full payment his-
tory for an order. To accomplish this, you make two passes against the ReoccurringPayment table — first
to get all the orders that had new or changed payments, and second to grab all of those orders payments.

Listing 10-2: A Complex Data Extraction
Declare @FromDateTime as datetime,
@ToDateTime as datetime

--You must set the @FromDateTime and @ToDateTime from the logging table

Truncate Table Extract.ReoccurringPayment

Insert Into Extract.ReoccurringPayment
Select p1.ProductKey, p1.OrderKey, o.OrderDate, o.CustomerKey,
p1.PaymentDate, p1.PaymentAmount
From Orders.dbo.Order o
Join Billing.dbo.ReoccurringPayment p1
On o.OrderKey=p.OrderKey
Join (
Select Distinct OrderKey
Join Billing.dbo.ReoccurringPayment
Where ModifyDate Between @FromDateTime and @ToDateTime
) p2
On p1.OrderKey=p2.OrderKey

This example makes the extract script more complex in order to handle the required
business rules. An alternative approach would be to keep the extract layer simpler, but
expand the prepare layer or add a new layer to the system to keep a permanent copy of
the source data that you can use to perform the required calculations. In this case, that
approach works well because the source systems are replicated to the analytics server
and there is no risk of overloading the transactional system.

Note