| The solution we are going to examine builds a data warehouse designed with the ability to correctly represent historical information. We will use Integration Services to manage the loading and updating of slowly changing dimension tables and Analysis Services to analyze the facts in the context of the time it was captured. Business Requirements The high-level requirements to support the business objectives are as follows: Ability to track client history. Some changes to a client's information are interesting and useful for analysis, other changes are not. Credit rating is interesting in analysis, phone number changes are not. The credit union needs the ability to know the state of their clients at any point in time, so that any analysis carried out in the client's context applies at that point in time. Any significant changes in a client's state should not overwrite existing values but should be added to the client's history. This means, for example, that when a client's credit rating changes, the periods of time when the old ratings are in effect and when the new rating is in effect are treated independently. Conversely, if the client's phone number changes, it makes no difference to any analysis, and there is no need to track old phone numbers. Multidimensional transaction analysis. The solution must allow the users to analyze the transactions using the dimension values from the CRM system that were in effect at the time of the transaction, as well as by other dimensions such as the type of transaction (withdrawal, payroll deposit, and so on) and channel (ATM, Branch, Internet Banking, and so forth).
High-Level Architecture We will build a data warehouse based on a standard dimensional data model and periodically load the information from the CRM and other systems (see Figure 8-1). In common with most real-world solutions, many of the descriptive dimension attributes such as channel names or customers' credit rating can change over time, but usually these changes to a record will not happen frequently. Figure 8-1. High-level architecture 
The dimension tables and ETL processes will be designed to support these changes, including detecting when records have been updated and making appropriate adjustments to the data warehouse records. Because ETL processes to support SCDs can be complex to implement properly, we will take advantage of the Integration Services Slowly Changing Dimension transformation to correctly process inserts and updates to the dimension tables. Business Benefits The solution will deliver the following benefits to the client: The effectiveness of planning and forecasting activities will be improved by having access to accurate data for all time periods, including historical data that was previously unavailable. The ability to track changes in customer attributes will provide accurate models of customer behavior that can be used to develop more profitable products and services.
|
