Database Reference
In-Depth Information
The data structure we use in this section involves three SQL Server databases — Orders, Billing, and
RevenueAnalytics. Orders and Billing are two source system databases, and RevenueAnalytics is the ana-
lytics system. The tables from the source system include Customer, Order, and Product from the Orders
system and Payment from Billing, as shown in Figure 10-5. Here is how the three tables work together:
➤ Customer is related to Order via CustomerKey and Product via ProductKey.
➤ Customer and Product are the parents of Order.
➤ The keys to the three tables from the Orders system are also transferred over the
ReoccurringPayment table in the Billing system.
Figure 10-5:
Source systems data model.
This example is based on a business that sells products with reoccurring monthly billing — one order
results in multiple billings for each customer. The desired output of this system is to produce analyt-
ics on reoccurring revenue, including:
➤ Pivoting the data by OrderDate and showing a crosstab of those dates by payment period
(defined as the days' difference between OrderDate and PaymentDate divided by 30).
➤ Showing a running cumulative payment amount.
Looking at these requirements, you can outline a simple model for the Data Delivery phase that
allows you to produce the required outputs. The model is outlined in Figure 10-6.
Deliver.PaymentPeriod and Deliver.OrderDate do not have load and modify dates
because they are static tables loaded one time from Excel or a flat file.
Note
Data extraction scripting
The data extraction phase should be kept simple and pull in data changes only. All objects in this sec-
tion should be stored under the same schema in the database. The name of this schema is Extract.
Figure 10-7 outlines the tables.
Search WWH ::
Custom Search