sas detail data store for banking 2 - dartmouth...

SAS® Detail Data Store for Banking 2.5 Implementation and Administration Guide

SAS® Documentation

The correct bibliographic citation for this manual is as follows: SAS Institute Inc. 2007. SAS® Detail Data Store for Banking 2.5: Implementation and Administration Guide. Cary, NC: SAS Institute Inc.

SAS® Detail Data Store for Banking 2.5: Implementation and Administration Guide

Copyright © 2007, SAS Institute Inc., Cary, NC, USA

All rights reserved. Produced in the United States of America.

For a Web download or e-book: Your use of this publication shall be governed by the terms established by the vendor at the time you acquire this publication.

U.S. Government Restricted Rights Notice: Use, duplication, or disclosure of this software and related documentation by the U.S. government is subject to the Agreement with SAS Institute and the restrictions set forth in FAR 52.227-19, Commercial Computer Software-Restricted Rights (June 1987).

SAS Institute Inc., SAS Campus Drive, Cary, North Carolina 27513.

1st printing, November 2007

SAS® Publishing provides a complete selection of books and electronic products to help customers use SAS software to its fullest potential. For more information about our e-books, e-learning products, CDs, and hard-copy books, visit the SAS Publishing Web site at support.sas.com/pubs or call 1-800-727-3228.

SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Other brand and product names are registered trademarks or trademarks of their respective companies.

iii

Contents

Chapter 1 Introduction to the SAS Detail Data Store for Banking 1 Introduction to This Guide 1 What’s New in the SAS Detail Data Store for Banking 2.5 2 What Is the SAS Detail Data Store for Banking? 3 Benefits of Implementing the SAS Detail Data Store for Banking 3 Industry Versions of the Banking DDS 4 Customizing the Banking DDS 7

Chapter 2 Physical Design of the SAS Detail Data Store for Banking 9 Overview 9 Populating the Retained Key 9 Tracking Historical Data over Time/Point in Time Data 10 Using Effective and Expiration Dates 11 Using Processed Date/Time 11 Using Natural/Business Keys 12

Chapter 3 Organization of Tables in the SAS Detail Data Store for Banking 13 Overview 13 More Information 17

Chapter 4 Creating and Registering Tables in the SAS Detail Data Store for Banking 19 Overview 19 Prerequisites 19 Install Process Strategy 20 Creating the Physical Tables 20 Importing the Metadata 21 Verification Process 30 Updating the Metadata 30

Chapter 5 Loading the SAS Detail Data Store for Banking 33 Identifying Data Sources 33 Guidelines for Loading Data into the Banking DDS 34 Understanding Initial and Periodic Data Loads 36 Archiving Data 37

Chapter 6 Logical Model Report 39 Overview 39 Viewing the Logical Model 39

Chapter 7 Support Resources 41

iv Contents

Appendix 1 Table-Loading Sequence 43

Appendix 2 Table Types in the SAS Detail Data Store for Banking 61 Transactional Tables 61 Master Tables 61 Reference Tables 62 Intersection Tables 62 Association Tables 62 Supertype and Subtype Tables 63

Appendix 3 Standard Data Types and Naming Conventions 65

Appendix 4 Data Model Notation 67 Reading the Relationship Notation 67 Understanding a One-to-One Relationship 68 Understanding a One-to-Many Relationship 68 Understanding a Supertype and Subtype Relationship 69

Appendix 5 Banking DDS FAQ 71

Appendix 6 Defining Role-Based Users and Groups for the Banking DDS 73 Overview 73 Planning User Groups and Roles 73 Sample User Groups and Roles 74

Appendix 7 Customizing the SAS Detail Data Store for Banking 77 Overview 77 Supported Customizations 78 Types of Customizations 78 Scope of Customizations 79 Steps for Customization 80 Modifying the Banking DDS 80 Using Caution 81 Avoiding Trouble 81 Things to Keep in Mind 81 Tracking Customizations 82

Appendix 8 Deploying the SAS Detail Data Store for Banking in SAS Scalable Performance Data Server 83 Overview 83 Prerequisites 83 Creating the Physical Tables 84 Importing the Metadata 85 Verification Process 95 Updating the Metadata 95

Contents v

Appendix 9 Deploying the SAS Detail Data Store for Banking in Oracle 97 Overview 97 Prerequisites 97 Creating the Physical Tables 98 Importing the Metadata 99 Verification Process 109 Updating the Metadata 110

Appendix 10 Deploying the SAS Detail Data Store for Banking in DB2 113 Overview 113 Prerequisites 113 Creating the Physical Tables 114 Importing the Metadata 115 Verification Process 123 Updating the Metadata 124

Glossary 127

vi Contents

1

1 Introduction to the SAS Detail Data Store for Banking

Introduction to This Guide ............................................................................................................................... 1 Audience ..................................................................................................................................................... 1

What’s New in the SAS Detail Data Store for Banking 2.5 ............................................................................ 2 Data Model Coverage................................................................................................................................. 2 ETL Coverage............................................................................................................................................. 2

What Is the SAS Detail Data Store for Banking?............................................................................................ 3 Benefits of Implementing the SAS Detail Data Store for Banking................................................................. 3 Industry Versions of the Banking DDS............................................................................................................ 4 Customizing the Banking DDS ........................................................................................................................ 7

Introduction to This Guide

This guide provides information to help the on-site SAS support personnel and customer system administrator install, configure, and administer the SAS Detail Data Store for Banking. The guide contains:

a high-level introduction to the banking detail data store (DDS) a description of the physical design and table structure of the banking DDS information about defining metadata user roles and their role-based setup information about deploying and loading the banking DDS information about maintaining the banking DDS information about using the banking DDS with various SAS Banking Intelligence

Solutions

Audience Users of this guide should be familiar with general database technology, data warehousing, and data modeling concepts. Users must have administrative and programming experience with Base SAS software and SAS Data Integration Studio.

C H A P T E R

2 What’s New in the SAS Detail Data Store for Banking 2.5 Chapter 1

What’s New in the SAS Detail Data Store for Banking 2.5 The SAS Detail Data Store for Banking 2.5 contains several new features and enhancements.

Expanded data model coverage with additional tables and fields. The majority of the additional data is for additional functionality in the latest versions of SAS Credit Risk Management for Banking and SAS Credit Scoring for Banking.

Enhanced logical model that includes subject-area breakdowns and drill-down capabilities.

DDL and metadata for a DB2 version of the banking DDS (Base SAS software, SAS Scalable Performance Data Server, and Oracle continue to be included in the banking DDS 2.5).

See the SAS Detail Data Store for Banking 2.5: Upgrade Guide for specific changes to the tables and columns within the banking DDS. This guide can be downloaded from the SAS Customer Support site at http://support.sas.com. From this page, under Knowledge Base, select Product Documentation, select SAS Detail Data Store (located under D) from the SAS Product Documentation index, and then select SAS Detail Data Store for Banking [HTML]. If you are upgrading from banking DDS 2.0 to banking DDS 2.5, request the upgrade package from your on-site SAS support personnel.

Data Model Coverage The data model has been expanded to include data elements for the following SAS solutions:

SAS Credit Scoring for Banking 4.3 SAS Credit Risk Management for Banking 4.5

The SAS Detail Data Store for Banking 2.5 continues to include coverage for data elements used by the following SAS solutions:

SAS Anti-Money Laundering SAS OpRisk VaR SAS Campaign Management for Banking SAS Cross-Sell and Up-Sell for Banking SAS Customer Segmentation for Banking SAS Customer Retention for Banking

ETL Coverage ETL jobs from the banking DDS to the data marts are available for the following SAS solutions:

SAS Credit Scoring for Banking 4.3 SAS Credit Risk Management for Banking 4.5 SAS Cross-Sell and Up-Sell for Banking 4.3 SAS Customer Segmentation for Banking 4.3 SAS Customer Retention for Banking 4.3

Introduction to the SAS Detail Data Store for Banking Benefits of Implementing the SAS Detail Data Store for Banking 3

What Is the SAS Detail Data Store for Banking? The banking DDS is a data store that serves as the single version of the truth for the SAS Banking Intelligence Solutions. It contains the atomic-level data and historical information that is needed to populate the solution data marts. Figure 1.1 illustrates the high-level role of the SAS Detail Data Store for Banking within the Integrated Solutions Data Architecture.

Figure 1.1 Integrated Solutions Data Architecture

Benefits of Implementing the SAS Detail Data Store for Banking Implementing the banking DDS at a customer site provides several benefits:

The banking DDS provides a single data target for loading data. For example, a customer first implements SAS Credit Risk Management for Banking. During the implementation, the customer populates the Financial_Account table. After the SAS Credit Risk Management for Banking implementation, the customer implements SAS Credit Scoring for Banking. Because the Financial_Account table has already been populated with cleansed data, the customer can use this same table when loading the SAS Credit Scoring for Banking data mart.

Solution Data MartsTransformations

Enterprise Operational

Systems

Solutions

(not all depicted)

SAS Anti-Money Laundering

SAS Credit Scoring for Banking

SAS Detail Data Store

for Banking

SAS Credit Risk Management

4 Industry Versions of the Banking DDS Chapter 1

Because the definition of the banking DDS table is known, the extract, transform, and load (ETL) process from the banking DDS to the solution data marts will be pre-built.

SAS Banking Intelligence Solutions can more easily share system data with each other. For example, the definition of a customer table is the same for SAS Credit Scoring for Banking as it is for SAS Credit Risk Management for Banking. Therefore, populating a single definition of a customer table ensures that both of these solutions have a single version of the truth.

Data that is created from SAS Banking Intelligence Solutions can be stored in a central location and shared with other solutions. For example, the credit scores from SAS Credit Scoring for Banking are written back to the banking DDS and shared with SAS Credit Risk Management for Banking.

Industry Versions of the Banking DDS The banking DDS is part of a larger group of industry data models. Although it would be convenient to have a single data model that covers all industries, in reality, different industries have different data needs. However, as illustrated in Figure 1.2, there is much commonality in data across industries. For example, customer, supplier, product, and segment tables share similar data attributes across industry.

Introduction to the SAS Detail Data Store for Banking Industry Versions of the Banking DDS 5

Figure 1.2 Common Tables across Industry

Data more likely to differ considerably across industry is the customer-facing or front-office data. For example, in banking, there are accounts; in retail, there are transactions; and in insurance, there are premiums and claims. Because of this difference in data, the industry versions of the banking DDS contain tables that are part of the base, cross-industry data model, and contain tables that are part of the industry-specific data model.

6 Industry Versions of the Banking DDS Chapter 1

To further illustrate the concept of sharing data, Figure 1.3 shows a simplified, table-level view of a small section of the banking data model. One set of common tables is in yellow and one set is in dark gray. The base data model (yellow set) was extended by adding some banking-specific tables (dark gray set). This same concept applies to other industry data models, such as insurance, telecommunications, manufacturing, and retail. Having common data allows data models to be easily integrated across industry and functional areas as needed, which is important because companies often operate in multiple industries.

Figure 1.3 Sample of a Base Data Model with Industry-Specific Tables

INTERNAL_ORG

EMPLOYEE

CUSTOMER

EXTERNAL_ORG

SUPPLIER

INTERNAL_ORG_ASSOC

INTERNAL_ORG_ASSOC_TYPE

FINANCIAL_ACCOUNT

INVESTMENT_ACCOUNT

SUPPLIER

Introduction to the SAS Detail Data Store for Banking Customizing the Banking DDS 7

Customizing the Banking DDS No predesigned data model will meet all of an organization’s needs. Customizations need to be applied to the banking DDS in almost every implementation. For example, Figure1.4 shows how the Competitors table was added to customize the banking DDS. Appendix 7, “Customizing the SAS Detail Data Store for Banking,” provides more information about customizing the banking DDS.

Figure 1.4 Customization of the Banking DDS

EXTERNAL_ORG

SUPPLIER

INTERNAL_ORG INTERNAL_ORG_ASSOC_TYPE

COMPETITORS

INTERNAL_ORG_ASSOCCUSTOMER

8 Customizing the Banking DDS Chapter 1

9

2 Physical Design of the SAS Detail Data Store for Banking

Overview............................................................................................................................................................. 9 Populating the Retained Key ............................................................................................................................ 9 Tracking Historical Data over Time/Point in Time Data ............................................................................ 10 Using Effective and Expiration Dates............................................................................................................ 11 Using Processed Date/Time ........................................................................................................................... 11 Using Natural/Business Keys ........................................................................................................................ 12

Overview The banking DDS is designed as an integration and storage layer for operational (or source) systems. As such, the banking DDS is a lightly denormalized, relational data model that is flexible for storage. A key difference between a source system and the banking DDS is that the banking DDS captures current and historical data. Capturing historical data includes temporal data history (event data that occurs at a particular date and time, such as an account inquiry) and non-temporal data history (non-event data, such as a customer account or a financial account). Many of the banking DDS design decisions are based on the need for this historical data.

Populating the Retained Key A retained key is a generated surrogate key that uniquely identifies a record at that point in time. Because data is coming from multiple source systems, the business key is not sufficient to identify the record. The retained key (_RK) field, which is part of the primary key, is populated with a retained surrogate (generated) key. This numeric key does not change for the different versions of the record. For example, in the INTERNAL_ORG table in Figure 2.1, the ORGANIZATION_NM column changes from Marketing to World Wide Marketing, but the retained key (shown in the first column) stays as 100. The only change in the old record is in the VALID_TO_DTTM column, which now correctly shows the changed date.

C H A P T E R

10 Tracking Historical Data over Time/Point in Time Data Chapter 2

Figure 2.1 Example of Using a Retained Key

INTERNAL_ORG - Old record

INTERNAL_ORG_RK VALID_FROM_DTTM VALID_TO_DTTM ORGANIZATION_NM 100 01JAN1999 12:00:00 01JAN5999 00:00:00 Marketing

INTERNAL_ORG – record updated, and new record populated with retained key

INTERNAL_ORG_RK VALID_FROM_DTTM VALID_TO_DTTM ORGANIZATION_NM 100 01JAN1999 12:00:00 31DEC2000

23:59:59 Marketing

100 01JAN2001 00:00:00 01JAN5999 00:00:00

World Wide Marketing

SAS Data Integration Studio has a Slowly Changing Dimension (SCD) transformation, SCD Type2 Loader, which can be used for generating a retained key when the value of an attribute changes in a record.

Tracking Historical Data over Time/Point in Time Data In Figure 2.1, the VALID_FROM_DTTM and VALID_TO_DTTM values are used with the _RK value to track historical data over time. These values define the time period during which the contents of the row are valid. You should set the VALID_TO_DTTM value to a date far into the future for ease of joins. If the source system does not track historical data for records, the VALID_FROM_DTTM and VALID_TO_DTTM values would correspond to the date and time that the DDS was loaded. However, if the source system does track historical data (for example, multiple changes of a row between load times of the DDS), there could be more than one row for the same retained key for a given load of the DDS. The design decision about how the date and time values are managed is related to the deployment of the source system. The date and time values can be tied to business system dates if they are provided by the business system, and if they do not conflict with their primary purpose in tracking the different versions of the system. VALID_FROM_DTTM values can be created from the following:

banking DDS load date and time ETL date and time business system record timestamps

The primary purpose of the VALID_FROM_DTTM and VALID_TO_DTTM values is tracking versions, which is the only use of these values that is guaranteed. If the values are used for another purpose (such as data extract date or business system entry date), then this usage must not compromise the primary purpose.

Physical Design of the SAS Detail Data Store for Banking Using Processed Date/Time 11

Using Effective and Expiration Dates Some types of data have effective and expiration dates that indicate when a business contract or policy is in effect. These date values are different from the date values that are used to track versions. For example, a physical asset’s value remains effective for a certain period of time–the period for which the bank has estimated this value. This estimation changes for future effective and expiration dates for the new versions of the physical asset’s value rows. In Figure 2.2, a bank evaluates the physical asset value of a property on 01MAR2003 that takes effect 01APR2003 (as seen in the EFFECTIVE_FROM_DTTM date, which is the business date). On April 1, 2004, the bank re-evaluates the physical asset value and increases the value, which now supersedes the original value. In Figure 2.2, there are two distinct and valid coverage periods, and a changing business effective period.

Figure 2.2 Example of Effective and Expiration Dates, Before and After an Increase in Coverage

Before the Increase in Coverage

PHYSICAL_ASSET_RK VALID_FROM_DTTMVALID_TO_DTTM ASSET_VALUE_AMT EFFECTIVE_FROM_DTTM

EFFECTIVE_TO_DTTM

1001 01MAR2003 00:00:00

01JAN5999 00:00:00

$100,000 01APR2003 00:00:00

31MAR2004 00:00:00

After the Increase in Coverage

PHYSICAL_ASSET_RK VALID_FROM_DTTMVALID_TO_DTTM ASSET_VALUE_AMT EFFECTIVE_FROM_DTTM

EFFECTIVE_TO_DTTM

1001 01MAR2003 00:00:00

31JAN2003 23:59:59

$100,000 01APR2003 00:00:00

31MAR2004 00:00:00

1001 01APR2004 00:00:00

01JAN5999 00:00:00

$110,000 01APR2004 00:00:00

31MAR2005 00:00:00

The EFFECTIVE_FROM_DTTM and EFFECTIVE_TO_DTTM values provide effective business periods for the data in this version of the row. If these values do not provide enough historical data with the VALID_FROM_DTTM and VALID_TO_DTTM values, then you can add business-system-related dates, which are subject to naming standards and other standards.

Using Processed Date/Time Knowing the last time that a row was processed in the banking DDS is useful. Processing can include initially creating a row, or updating a row, such as adding the VALID_TO_DTTM value to an existing row. The PROCESSED_DTTM value determines which rows have changed since they were loaded into the data mart by determining the last time that the row was touched by an ETL process or by the data administrator, which includes unusual updates that do not change the row (such as error-correction data-patching).

12 Using Natural/Business Keys Chapter 2

You can populate the PROCESSED_DDTM value by using the Load Time Column option that is available in the SCD Type2 Loader transformation of SAS Data Integration Studio.

Using Natural/Business Keys In the banking DDS, capturing the primary source system identifier (also known as the natural key or business key) and the retained keys in the rows of the tables is useful. The standard for capturing the natural/business key in the banking DDS is <table_name>_ID. Figure 2.3 shows the retained key and natural/business key for a financial account. These natural/business keys originate from the source systems and typically contain long alphanumeric strings. Capturing all keys is useful if you need to go back to the source systems to investigate data.

Figure 2.3 Capturing the Natural/Business Key

FINANCIAL_ACCOUNT_RK VALID_FROM_DTTM VALID_TO_DTTM FINANCIAL_ ACCOUNT_ID

1001 01JAN2002 00:00:00

01JAN5999 00:00:00

23086549C

13

3 Organization of Tables in the SAS Detail Data Store for Banking

Overview........................................................................................................................................................... 13 More Information ............................................................................................................................................ 17

Overview At a high level, the banking DDS can be grouped into subject areas. In Figure 3.1, the logical data model consists of the following subject areas:

Parties

This subject area includes information on the parties that are involved in banking, such as customers and counterparties. Customer information includes details of individuals, organizations, and corporate customers, associated addresses and contact information, household information for individuals, organization information for corporate customers, and information about the segments to which a household or customer belongs. Counterparty is the opposite party in a bilateral agreement, contract, or transaction. Counterparty information includes details of customers, internal/external organizations, or individuals that are involved in the transactions.

Loss Events

This subject area includes internal loss events and related entities. A loss event is an occurrence of an operational failure, such as a power blackout or computer infrastructure outage. Usually a financial impact is a consequence of a loss event. Loss events are categorized into general risk categories and fall within certain business lines. Loss event data is particularly relevant to operational risk calculation.

Analytics and Scoring

This subject area includes analytical results, such as cross-sell, up-sell, and customer-retention and credit scores, as well as the details of the analytical models. Analytical results are generated by SAS solutions and written to the banking DDS for sharing with other applications.

Financial Accounts

This subject area includes all of the types of financial accounts. The information includes attributes that are common to all financial accounts, such as date opened and account balance. There are also specific types of accounts. These accounts include loan, mortgage, core banking, credit card, investment, life insurance, auto insurance, property insurance, protection insurance, travel insurance, and retirement.

C H A P T E R

14 Overview Chapter 3

Banking Accounts

This subject area includes information that is specific to certain bank accounts, including:

mortgage account, including information such as the account branch

loan account, including information such as the interest rate and the amount of the loan

core banking account, including information such as the overdraft charge amount and the interest rate

credit card account, including information such as the payment protection status and the payoff date

retirement account, including information such as the projected retirement age and the tax status

investment account, including information such as the tax withholding information and the dividend frequency

Banking Transactions

This subject area includes transactions that are related to traditional banking accounts, such as withdrawals and deposits. It also includes information that is related to the nature of the transaction such as the transaction amount or the channel of transaction.

Exposures and Risks

This subject area includes the entities that describe the exposure of the financial institution, which include:

liability or financial loss through an account

exposure through financial position holdings

credit that is obtained through a credit facility

Risk Mitigants

This subject area includes the various types of credit risk mitigants that are used to counterbalance exposure. The general types of risk mitigants in the banking DDS are financial and physical assets, credit facilities, guarantees, and receivables.

Products and Instruments

This subject area includes typical retail banking products, such as certificates of deposit, and typical corporate banking products, such as currency swaps. It includes product details, such as product categories and types.

Customer Intelligence

This subject area includes information that is directly related to marketing, such as campaigns and communications, and customer contacts and responses. It also includes entities that are directly related to customers, such as:

surveys that are conducted by the bank or financial institution

customer-segmentation information

Organization of Tables in the SAS Detail Data Store for Banking Overview 15

event information, such as address changes, marriage, job changes, which is actively tracked for a customer and available from account management and transactional systems

Insurance Accounts

This subject area includes information about insurance accounts, such as life, travel, auto, property, and protection insurance.

From the context of the banking DDS, insurance coverage assumes that the bank is a third party offering insurance coverage. Details of benefits and adjudication are not stored in the banking DDS.

Insurance Transactions

This subject area includes information about the premiums and claims that are related to the insurance accounts.

Financial Reporting

This subject area includes tables that relate to the financial reporting area of an organization. The financial reporting area contains critical data, including information on annual revenues, net sales revenue, annual interest charges, profit before and after tax, extraordinary income, and other financial measures.

To learn more about the banking DDS organization, review the logical model report that is included on the media. The logical model report provides a drill-down feature. For more information, see Chapter 6, “Logical Model Report.”

16 Overview Chapter 3

Figure 3.1 High-Level View of the Banking DDS 2.5 Logical Model Banking

Organization of Tables in the SAS Detail Data Store for Banking More Information 17

More Information

The following appendixes include more information about banking DDS table organization:

Appendix 1, “Table-Loading Sequence,” describes the proper sequence of loading the banking DDS tables.

Appendix 2, “Table Types in the SAS Detail Data Store for Banking Table Types,” describes the six types of tables that are used in the banking DDS.

Appendix 3, “Standard Data Types and Naming Conventions,” describes the standard data types and naming conventions that are used in the banking DDS.

Appendix 4, “Data Model Notation,” describes the special notations that are used in the banking DDS data model.

Appendix 5, “Banking DDS FAQ,” lists frequently asked questions, problems, answers, and solutions that are related to the banking DDS.

18 More Information Chapter 3

19

4 Creating and Registering Tables in the SAS Detail Data Store for Banking

Overview......................................................................................................................................19 Prerequisites ...............................................................................................................................19 Install Process Strategy..............................................................................................................20 Creating the Physical Tables .....................................................................................................20 Importing the Metadata .............................................................................................................21 Verification Process ....................................................................................................................30 Updating the Metadata..............................................................................................................30

Overview This chapter describes how to create and configure the SAS banking DDS. This process includes the steps to create the physical tables and to register the metadata in an existing metadata repository. Refer to Appendix 8, “Deploying the SAS Detail Data Store for Banking in SAS Scalable Performance Data Server,” Appendix 9, “Deploying the SAS Detail Data Store for Banking in Oracle,” and Appendix 10, “Deploying the SAS Detail Data Store for Banking in DB2,” for specific installation processes.

Prerequisites SAS 9.1.3 Foundation or later is required. All SAS products and solutions must be installed and configured properly. SAS Data Integration Studio 3.4 or later must be installed and functional. Many

administrative tasks, such as setting up the servers that are used to execute jobs, are performed outside of the SAS Data Integration Studio interface. Such administrative tasks are described in the SAS Intelligence Platform documentation, which can be found at http://support.sas.com/913administration. These tasks are required before loading some of the components of the banking DDS. Additionally, all SAS Data Integration Studio hot fixes should be applied. Hot fixes can be downloaded from http://support.sas.com/techsup.

A SAS Metadata Server and a metadata repository must be operational.

C H A P T E R

20 Creating the Physical Tables Chapter 4

Install Process Strategy

Creating the Physical Tables DDL CREATE TABLE statements are provided to create the banking DDS physical data structure. A macro named %DDLDDS executes the table definitions by using %INCLUDE statements that reference <tablename>.sas files, which contain the CREATE TABLE statements. The %DDLDDS macro and the <tablename>.sas files are installed on Windows and located at C:\Program Files\SAS\SASBankingDDS\2.5\DDL\SAS. A default DATE9 informat/format is applied to date fields in the %DDLDDS macro. For customer sites with different date requirements, the macro variable DTFMT can be changed to represent the locale (such as EURDFDEw.). The DTFMT macro variable is referenced in the invocation string for the %DDLDDS macro.

Creating and Registering Tables in the SAS Detail Data Store for Banking Importing the Metadata 21

To create the physical tables:

1. Modify and submit the following invocation string: /* Macro variables to be assigned: Fileloc: Assign fileloc macro parameter to point to the directory which contains the DDL create table statements contained in <tablename>.sas files.

DTFMT: (Optional: Defaults to DATE9.) If international date values are needed, assign a new DTFMT= value, such as dtfmt=EURDFDEw.

DTTMFMT: (Optional: Defaults to NLDATM21.) If international datetime values are needed, assign a new DTTMFMT= value, such as dttmfmt=EURDFDTw.

FMTRK: (Optional: Defaults to 12.)*/

NUMW_D: (Optional: Defaults to 18.5) If currency or amount (_amt) fields require a display format, assign a new NUMW_D= value, such as NUMW_D=22.2 to display a width of 22 with 2 decimal places.

TMFMT: (Optional: Defaults to NLTIMAP10.) If time fields are needed to be displayed in a format other than NLTIMAP10., assign TMFMT= value.

%include "<location_of_provided_macro>/ddldds.sas"; LIBNAME DDS “<location_to_store_tables>”; %ddldds(fileloc=<location_of_SAS_DDL>, dttmfmt=<International_Datetime_Value>,dtfmt=<International_ Date_Value>,fmtrk=<default is 12. Use the appropriate length if you made any customizations to the _RK column>);

Your invocation string might look like:

%include "(c:\bankingdds_25_ddl_location\ddldds.sas”); LIBNAME DDS “c:\bankingdds_25\ddl_physical_tables”;

%ddldds(LIBREF=DDS, DTTMFMT=eurdfdt20., TMFMT=NLTIME10., DTFMT=DDMMYY8., FMTRK=10., NUMW_D=15.3, fileloc=”c:\bankingdds_25_ddl_location”);

Note: The %DDLDDS macro code is provided as part of the banking DDS installation, and is located in the Windows default installation folder C:\Program Files\SAS\SASBankingDDS\2.5.

2. Verify that the tables were created correctly by submitting the following code:

proc datasets lib=DDS; quit;

The DATASETS procedure output can be compared with the banking DDS physical data structure.

Importing the Metadata Importing the banking DDS metadata can be performed using either the Import Wizard in SAS Data Integration Studio or the BI Manager plug-in in the SAS Management Console. In this guide, the focus is on using SAS Data Integration Studio to import the banking DDS metadata.

22 Importing the Metadata Chapter 4

There are two other resources that describe how to register metadata using the import feature in SAS Data Integration Studio:

The online Help in the main menu of SAS Data Integration Studio provides information in the “What’s New” section (refer to the section “Enhanced Import and Export of SAS Metadata”). The online Help contains basic information about the import feature in SAS Data Integration Studio.

The “Importing, Exporting, and Copying Metadata” chapter in the SAS Data Integration Studio 3.4: User’s Guide contains detailed information about importing metadata.

Note: If you have installed the banking DDS on a non-Windows platform, ensure that the Windows client on which SAS Data Integration Studio or SAS Management Console is installed has access to the SAS package (.spk) file. Or, you can copy the SAS package file to the local Windows client machine. The SAS package file–bankingdds_25_sas.spk–contains the banking DDS library metadata, custom folder structure, table metadata, table notes metadata, and column notes metadata for the respective data store that are needed for the banking DDS metadata registration. To register your banking DDS metadata using the SAS Data Integration Studio Import Wizard, perform the following steps:

1. Log on as a restricted user to the metadata repository in which you will import the banking DDS metadata using SAS Data Integration Studio. An unrestricted user, such as the Administrator User, cannot be used for the import process.

2. To invoke the Import Wizard, select the Custom tab and expand Repositories Foundation (or Custom) SAS Data Integration Studio Custom Tree. Right-click SAS Data Integration Studio Custom Tree and select Import to invoke the Import Wizard.

3. In the Import Wizard, click Browse to locate the SAS package file, installed on Windows and located at C:\Program Files\SAS\SASBankingDDS\2.5\Metadata\bankingdds_25_sas.spk. Verify that


Include access controls is not selected, and that All Objects is selected. Click Next to continue.


4. All objects are selected for import. Click Next.

5. The next page (not shown) displays the collection of objects that you are importing, application servers, and other servers. Click Next.


6. Select your Target application server. Click Next.

Note: In this example, SASMain is the application server. Select your specific application server.


7. Enter the Target physical location of the banking DDS. Change the default location of “Location of your banking DDS” to the location of your physical banking DDS tables. Click Next.


8. Review the summary and select Import to begin the import process.

Note: In this example, the values for the number of objects being created are valid for this release of the banking DDS.

When the import process is completed, a page states whether it completed successfully. Click the View Log button to view a detailed log of the import process. Click Finish.


9. After a successful import process, the SAS Data Integration Studio Custom Tree folder will contain the DETAIL_DATA_STORE folder, and the following subfolders and library:

DDS_MAIN (contains the DDS Main tables) DDS_REFERENCE (contains the DDS Reference tables) DDS_NOTES (contains the DDS Table and Column Notes) Detail Data Store library (refers to the banking DDS 2.5 library)

10. Log on to SAS Management Console as a restricted user and select the repository

in which you have just imported your banking DDS metadata. Expand Data Library Manager and SAS Libraries.

Creating and Registering Tables in the SAS Detail Data Store for Banking Verification Process 29

11. Right-click on Detail Data Store. Select Properties from the menu. Select the Options tab, and then select a new Path Specification. Click OK.

The banking DDS metadata is now fully imported. You have completed all of the tasks that are associated with metadata registration.

30 Updating the Metadata Chapter 4

Verification Process Log on to SAS Data Integration Studio to perform verification. The following steps help you verify that the metadata was imported successfully:

Review the log that was produced by the import process. On Windows platforms, logs are written to the user’s folder at a location such as Documents and Settings\<username>\My Documents. The log file is named import_date/time.log.

Select the SAS Data Integration Studio Custom Tree folder and expand the DETAIL_DATA_STORE folder. This folder should contain the following subfolders and their associated metadata: DDS_MAIN (contains the DDS Main tables) DDS_REFERENCE (contains the DDS Reference tables) DDS_NOTES (contains the DDS Table and Column Notes) Detail Data Store library (refers to the banking DDS 2.5 library)

Right-click on Detail Data Store and click Properties. Verify that the library and path specification are correct.

Updating the Metadata If you made any changes to the DDL CREATE TABLE statements, you must update the table metadata to synchronize it with the physical tables. If no changes were made to the DDL CREATE TABLE statements when the physical tables were created, there is no need to update the table metadata. To update the table metadata, log on to SAS Data Integration Studio and select all of the tables that need to be updated from the DDS_MAIN and

Creating and Registering Tables in the SAS Detail Data Store for Banking Updating the Metadata 31

DDS_REFERENCE folders, which are located under the SAS Data Integration Studio Custom Tree folder. Select Update Table Metadata in the Tools menu. The table metadata is updated to synchronize it with the physical tables.

Note: A report is produced by the Update Table Metadata option that shows a table-by-table listing of each modification.

32 Updating the Metadata Chapter 4

33

5 Loading the SAS Detail Data Store for Banking

Identifying Data Sources ................................................................................................................................ 33 Expected Data Source Systems ............................................................................................................... 33

Guidelines for Loading Data into the Banking DDS .................................................................................... 34 Recommended Process ............................................................................................................................. 34

Step 1: Define the precise data to be loaded into the banking DDS. ............................................. 34 Step 2: Identify and extract the data from the data source systems.............................................. 34 Step 3: Clean and consolidate the data from the data source systems. ........................................ 35 Step 4: Load the reference tables. .................................................................................................... 35 Step 5: Load data into the banking DDS and validate. ................................................................ 35

Understanding Initial and Periodic Data Loads .......................................................................................... 36 Archiving Data ................................................................................................................................................ 37

Identifying Data Sources Because banks and financial institutions are among the oldest and most mature users of information technology, the number, variety, and application of IT systems are extremely diverse. Quality data must be loaded into the banking DDS to benefit from a SAS Banking Intelligence solution. Therefore, it is critical to identify correct data source systems. Customers with an existing data warehouse can obtain all of the required data from the data warehouse itself. However, other customers might need to obtain data from diverse data source systems, external data stores, and multiple data feeds.

Expected Data Source Systems The following categories of data and IT systems are typical in banks and financial institutions:

customer and account management systems financial account and application management systems collateral management systems marketing and customer intelligence systems product and pricing management systems transactional systems, such as teller systems, ATMs, credit card transaction feeds external data feeds, such as bureau information business and analytical intelligence systems other systems, such as human resources and general ledger

C H A P T E R

34 Guidelines for Loading Data into the Banking DDS Chapter 5

These categories could be available from a single IT system or from multiple IT systems. Historical information could be available from data archival systems. Or, all of the data could be available in an existing data warehouse.

Guidelines for Loading Data into the Banking DDS After you have identified the banking DDS data sources, you must load the data into the banking DDS. Both the bank or financial institution and the data warehouse consultants need to choose the method to load data into the banking DDS. This choice depends mostly on the structure and diversity of the data source systems. For example, if the number of data source systems is large, and the data source systems are uncoordinated, then consolidating and cleaning data are major considerations. However, if the data source systems are coordinated, or if there is an existing data warehouse, then consolidating and cleaning data are already an integral part of the existing IT system.

Recommended Process The recommended process for loading data into the banking DDS (as shown in Figure 5.1) includes five steps.

Step 1: Define the precise data to be loaded into the banking DDS. The banking DDS is designed to support multiple solutions, although not all of the solutions are implemented simultaneously. Therefore, you must identify the precise data that needs to be loaded into the banking DDS to support the solutions that are to be implemented. This is often an iterative process. A detailed listing of the banking DDS tables and columns that are needed to support specific banking solutions is provided in the Matrix: SAS Detail Data Store for Banking 2.5, Solutions to DDS Mapping spreadsheet. This spreadsheet can be requested from your on-site SAS support personnel. This detailed listing identifies data that needs to be loaded into the banking DDS for a particular SAS solution. The listing might need to be customized based on the specific requirements of the bank or financial institution, or based on the availability of data in the source data systems.

Step 2: Identify and extract the data from the data source systems. Identify the data source systems and the data structures within the data source systems. To accomplish this step, map the banking DDS data to data from the data source systems. For each data source system, a format is defined for the data extract. This format represents how the data is stored in the data source system and optimizes extracting data. Data is extracted from the data source system into an extract file of a defined format.

Loading the SAS Detail Data Store for Banking Guidelines for Loading Data into the Banking DDS 35

Step 3: Clean and consolidate the data from the data source systems. Data that has been extracted from the data source systems and into different formats is cleaned and consolidated into the formats that are suitable for loading the banking DDS. The time that is needed to clean and consolidate the data depends on the condition of the data and the number of data source systems.

Step 4: Load the reference tables. Reference tables that store code values that are required in the banking DDS must be loaded before the banking DDS is loaded because of the following reasons:

Different data source systems might use different codes to represent the same code value.

Codes that are required by the banking DDS might not be defined. The column widths of existing code columns might not match the column widths

that are defined in the banking DDS. Ensure that code values are stored correctly and are available to the solutions that use the banking DDS. As you carefully check the code values for accuracy and availability, include business users, IT staff of the bank or financial institution, and banking solution consultants. After this check is performed, load code manually into the banking DDS reference tables, or extract code from the existing data source systems and load it into the banking DDS reference tables.

Step 5: Load data into the banking DDS and validate. Ensure that data is loaded in the correct sequence. For example, an account transaction cannot be loaded unless the account in which the transaction occurred is first loaded into the banking DDS. Appendix 1, “Table-Loading Sequence,” contains the correct sequence for loading the banking DDS. Because the banking DDS can have data from multiple data source systems, you need to generate the primary key for data in the banking DDS. The column name of the banking DDS primary source identifier has a _RK suffix. The online data dictionary includes details of the primary source identifier (also known as the natural key or business key) for each banking DDS table. When loading data into the banking DDS, the natural key or business key of the source data is matched with data that is available in the banking DDS. If the natural key or business key exists in the banking DDS, the existing banking DDS record is expired, and a new record is inserted with the updated information. The natural key or business key in the banking DDS retains the same value, but the validity dates are changed. If the natural key or business key does not exist in the banking DDS, a new record with a new natural key or business key (calculated as maximum + 1) is inserted.

Note: The business key is typically the primary key from the data source system table and the data source system code. The load time for the banking DDS varies, depending on the volume of data and the number of necessary transformations.

36 Understanding Initial and Periodic Data Loads Chapter 5

Figure 5.1 Loading Data into the Banking DDS

Understanding Initial and Periodic Data Loads The initial data load loads all of the available cleansed, consolidated, and validated data from the data source systems and the associated data archives into the banking DDS. The required history to be loaded depends on the following factors:

the amount of historical data that is available the historical data that is required by the solutions that will be implemented

Loading the SAS Detail Data Store for Banking Archiving Data 37

After the initial data load, the banking DDS and solutions are designed to support any periodic load frequency (daily, weekly, or monthly). The banking DDS and solutions support ad hoc loads and partial loads, as long as the consistency of the data is maintained. For example, an account transaction cannot be loaded unless the account in which the transaction occurred is first loaded into the banking DDS. The actual load frequency depends on a solution’s requirements and the business processes of the bank or financial institution. Periodic loads are generally incremental, and only new or changed data is extracted from the data source systems for loading.

Archiving Data Whether to archive data is based on the answers to the following questions:

How much data can you keep online? When is data no longer needed? What category of data can be archived (for example, transactions or aggregates

only, old customers, old reference data, any data that is older than a certain date)? Is the archived data expected to be reintegrated on short notice? Do you have DBMS-level archival mechanisms, such as partition-level archival or

rollout? What drives archival decisions (for example, space management, regulatory

constraints)? The simplest archival approach with the largest benefit is to archive transactions and aggregates as they age beyond a site-defined threshold. However, you should consider the following facts when you make decisions about archiving.

Associated reference data of a relatively low data volume might be too unimportant.

Age should not be the only factor. For example, an active customer’s record might have been created years ago and have never changed; in this case, you would want to archive the data.

Note: Archiving transactions or aggregates might have an effect on calculated data within the banking DDS, which means the data will have to be recalculated. Archival should be a simple and regularly scheduled task. Test the archival process in a test environment before archiving data from a production environment. In addition, consider the archival-process window, archival tape or disk allocation and storage, recovery procedures, and user notification.

38 Archiving Data Chapter 5

39

6 Logical Model Report

Overview......................................................................................................................................39 Viewing the Logical Model.........................................................................................................39

Overview This chapter describes how to set up and view the banking DDS logical model report in a Web browser. This report provides a business subject area view of the banking DDS, which includes the entities, attributes, key groups, and relationships that make up the banking DDS.

Viewing the Logical Model HTML files and other supporting logical model files are provided as a part of the banking DDS installation process. The installation process creates the files on Windows in the directory C:\Program Files\SAS\SASBankingDDS\2.5\Supporting_Documents\Logical_Model. The Logical_Model folder and Images subfolder, including all of the files in both of these folders, should be loaded to your Web server, maintaining the source directory’s structure. After the directories and files are loaded, invoke the logical model report by opening the 01_bankingdds_25_logical_report.htm file, which is located in C:\Program Files\SAS\SASBankingDDS\2.5\Supporting_Documents\Logical_Model. The logical model report will open in a separate Web browser. Business subject area views are provided for the following:

Analytics and Scoring Banking Accounts Banking Transactions Customer Intelligence Exposures and Risks Financial Accounts Financial Reporting Insurance Accounts Insurance Transactions Loss Events Parties

C H A P T E R

40 Viewing the Logical Model Chapter 6

Products and Instruments Risk Mitigants

The Picture section provides drill-down capabilities to the selected business subject area. Each table can be double-clicked to show a definition of the table. Each relationship line can be double-clicked to show the Parent to Child Phrase and the Parent Entity definition. The Entity section provides a listing of tables, their definitions, and links to the child relationships for each table.

41

Support Resources

For solution-specific technical support and documentation, visit the SAS Customer Support site at http://support.sas.com. For other types of support, SAS Professional Services provides support in the following areas:

consulting services solution assessment, including feasibility and methodology for implementing SAS

Banking Intelligence Solutions training DDS customization and implementation project management

The data model for the banking DDS is available in the Computer Associates AllFusion ERwin Data Modeler format. The ERwin file is available by request from your on-site SAS support personnel.

C H A P T E R

7

43

1 Table-Loading Sequence

The banking DDS table-loading sequence is listed in the following table. Tables in Wave 1 should be loaded before tables in Wave 2, and so on.

Tables Loaded in Wave 1

ACCOUNT_BLOCKING_REASON

ACCOUNT_CLOSE_REASON

ACCOUNT_LIFECYCLE_STAGE

ACCOUNT_REGISTRATION_TYPE

ACCOUNT_RENEWAL_TYPE

ACCOUNT_RESTRICTION_TYPE

ACCOUNT_STATUS

ACCOUNT_USAGE_TYPE

ADDL_BORROWING_PURPOSE

ADDRESS_QUALITY

ADDRESS_TYPE

ADD_ON_SET_TYPE

AGENCY_TYPE

ALARMED

ANALYTICAL_MODEL_CATEGORY

ANALYTICAL_MODEL_PACKAGE

ANNUAL_INCREASE

ANTI_LOCK_BRAKING

APPENDED_DATA_MEASURE

APPENDED_DATA_SOURCE

APPROACH_TYPE

APR_RT_CHANGE_REASON

APR_TYPE

AREA_COVERED

ASSESSMENT_CHANGE_REASON

ASSESSMENT_RESULT_TYPE

ASSESSMENT_TYPE

ASSESSMENT_VALUE_TYPE

ASSET_CLLTRL_RLN_TYPE

ASSET_TYPE

ASSOCIATE_ACCOUNT_ROLE

ASSOCIATE_STATUS

A P P E N D I X

44 Appendix 1

Tables Loaded in Wave 1 AUTO_DEBIT_ACCOUNT_TYPE

BANKRUPTCY_STATUS

BANK_CARD_TYPE

BARRIER_TYPE

BENEFICIARY_RELATIONSHIP

BLDG_VOLUNTARY_EXCESS

BOND_INSTRUMENT_TYPE

BRANCH_FREQUENCY_REASON

BREAKDOWN_COVER

BROKERAGE_ACCOUNT_STATUS

BUILDING_STATUS_TYPE

BUILD_ERA

BUREAU

BUREAU_CLASS

BURGLAR_ALARM_TYPE

BUSINESS_LINE_ASSOC_TYPE

BUSINESS_NATURE

CAL_DATE

CAMPAIGN_TYPE

CARD_CANCEL_REASON

CARD_OTHER_TERMS

CARD_PAYMENT_ACCOUNT_TYPE

CARD_PAYMENT_TYPE

CARD_PROTECTION_INS

CARD_PROTECTION_STATUS

CARD_PROTECTION_TYPE

CASHFLOW_INSTRUMENT_TYPE

CASH_FLOW_SOURCE

CASH_FLOW_TYPE

CENTRALIZATION_OF_DECISIONS

CHANNEL

CLAIM_REASON

CLAIM_STATUS

CLASS_OF_BUSINESS

CLEAN_UP_CALL_TYPE

CLIENT_TYPE

CODE_LANGUAGE

COLLATERAL

COLLECTIONS_STATUS

COLOR

COMMISSION_EXCL_REASON

COMMITMENT_TYPE

COMMODITY_INSTRUMENT_TYPE

COMMODITY_TYPE

COMMUNICATION_STATUS

Table-Loading Sequence 45

Tables Loaded in Wave 1 COMPOUNDING

CONSTRUCTION

CONTACT_ACTION

CONTACT_REASON_TYPE

CONTACT_TYPE

CONTENTS_VOLUNTARY_EXCESS

CONTRIBUTION_TYPE

CONVICTIONS

CORE_ACCOUNT_STATUS

CORE_ACCOUNT_TYPE

CORE_ACCT_REGULAR_DIRECTION

CORE_ACCT_REGULAR_METHOD

CORE_ACCT_REGULAR_STATUS

CORE_BANKING_ACCOUNT_TYPE

CORE_PRODUCT_TYPE

COST_CENTER_ASSOC_TYPE

COUNTERPARTY_ASSOC_TYPE

COUNTERPARTY_LEGAL_TYPE

COUNTERPARTY_RLN_TYPE

COUNTERPARTY_TYPE

COUNTRY

COVERAGE

CREDIT_CARD_ACCOUNT_TYPE

CREDIT_CARD_PRODUCT_TYPE

CREDIT_DERIVATIVE_TYPE

CREDIT_FACILITY_TYPE

CREDIT_LINE_USED_RANGE

CREDIT_PAYMENT_PROTECTION

CREDIT_RATING

CREDIT_RISK_MITIGANT_TYPE

CREDIT_STATUS

CR_MITIGANT_REL_TYPE

CURRENCY

CURRENT_CARD_ORGANIZATION

CUSTOMER_ACTIVE

CUSTOMER_CLASS

CUSTOMER_LIFECYCLE

CUSTOMER_RISK_FACTOR

CUSTOMER_TYPE

DAY_BASIS

DECISION

DEFAULT_REASON

DEFAULT_STATUS

DEFAULT_TYPE

DELIVERY_POINT_SUFFIX

46 Appendix 1

Tables Loaded in Wave 1 DERIVATIVE_INSTRUMENT_TYPE

DISBURSEMENT_TYPE

DIVIDEND_PAYMENT

DIVISION_TYPE

DOCUMENTATION_TYPE

DRIVE_SIDE

EARLY_AMORTIZATION_TYPE

ECONOMIC_ENTITY_TYPE

ECONOMIC_SECTOR

EDUCATION_LEVEL

ELIGIBLE_CR_MITIGANT_TYPE

EMPLOYEE_ASSOC_TYPE

EMPLOYEE_INVOLVEMENT_TYPE

EMPLOYEE_UNION

EMPLOYMENT_POSITION_STATUS

EMPLOYMENT_STATUS

ENGINE_LOCATION

EQUITY_INSTRUMENT_TYPE

EQUITY_POSITION_TYPE

ETHNICITY

EVENT_CATEGORY

EVENT_STATUS

EVENT_TYPE

EXCESS_SPREAD_BAND

EXPENSE

EXTERNAL_CREDIT_RATING

EXTERNAL_ORG_ASSOC_TYPE

FEE_REASON

FINANCIAL_ACCOUNT_TYPE

FINANCIAL_ASSOCIATE_TYPE

FINANCIAL_BOOK_TYPE

FINANCIAL_EXCHANGE

FINANCIAL_INSTRUMENT_CLASS

FINANCIAL_POSITION_STATUS

FINANCIAL_POSITION_TYPE

FINANCIAL_PRODUCT_TYPE

FINANCIAL_UNIT_TYPE

FIN_COLLATERAL_SUBTYPE

FLOAT_RATE_INDEX

FRA_INSTRUMENT_TYPE

FUND_INSTRUMENT_TYPE

FX_INSTRUMENT_TYPE

GENDER

GENERAL_LEDGER

GEO_DEMOGRAPHIC


Tables Loaded in Wave 1 GL_ACCOUNT_TYPE

GUARANTEE_CODE

GUARANTEE_TYPE

HHOLD_TYPE

HOME_INSURANCE_TYPE

HOME_REASON_LAST_CLAIM

HOME_STATUS

HOME_TYPE

ID_VERIFICATION_TYPE

IMMOBILIZER

INCENTIVE_TYPE

INCOME

INCOME_CATEGORY

INCOME_TYPE

INCORPORATION_TYPE

INDIVIDUAL_ORGANIZATION

INDUSTRY

INDUSTRY_CODE_TYPE

INFORMATION_SOURCE

INQUIRY_STATUS

INQUIRY_TYPE

INSURANCE_COVER

INSURANCE_TYPE

INSURED_ITEM_TYPE

INTEREST

INTERNAL_CREDIT_RATING

INTERNAL_ORG_ASSOC_TYPE

INTERNAL_PRODUCT_CATEGORY

INTRODUCER

INVESTMENT_METHOD

INVESTMENT_OBJECTIVE

INVESTMENT_PRODUCT_TYPE

IRB_ALT_TREAT_ELIGIBLE_TYPE

ISSUE_TYPE

LAST_CLAIM_REASON

LAST_CLAIM_STATUS

LATE_PAYMENT_STATUS

LEASE_TYPE

LEGAL_ENTITY_TYPE

LICENSE_STATUS

LIFESTAGE

LIFE_INSURANCE_STATUS

LIFE_INSURANCE_TYPE

LIFE_OF_LOAN_CAP

LIMIT_TYPE

48 Appendix 1

Tables Loaded in Wave 1 LINE_OF_BUSINESS

LOAN_PAYMENT_TYPE

LOAN_PRODUCT_TYPE

LOAN_SECURITY_TYPE

LOAN_STATUS

LOAN_TRANS_STATUS

LOAN_TYPE

LOSS_EVENT_FIN_STATUS

MARGIN_AGREEMENT

MARITAL_STATUS

MARKET

MARKETING_SOURCE

MARKET_INDEX

MARKET_SEGMENT

MATURITY_BAND

MATURITY_CHANGE_REASON

MEDIUM

MEDIUM_TYPE

MED_EXPENSES

MERCHANT_CATEGORY_TYPE

MODEL_DEPLOYMENT

MODEL_RANK

MORTGAGE_ADDITIONAL_STATUS

MORTGAGE_PRODUCT_TYPE

MORTGAGE_STATUS

MORTGAGE_TYPE

MOTOR_INS_COVERAGE_TYPE

MOTOR_MANUFACTURER

MOTOR_REASON_LAST_CLAIM

MOTOR_STATUS

MOTOR_TYPE

NET_WORTH

OFF_BALANCE_NETTING_TYPE

OFF_BALANCE_SHEET_TYPE

OPTION_TYPE

OP_RISK_CAUSE

ORG_TYPE

OUTBOUND_COMMUNICATION_TYPE

OUTCOME

OVERRIDE_REASON

OWNERSHIP

OWNER_TYPE

PAYMENT_INTERVAL

PAYMENT_LEG

PAYMENT_METHOD


Tables Loaded in Wave 1 PAYMENT_PROTECT_STATUS

PAYMENT_STATUS

PHONE_TYPE

PHYSICAL_ASSET_TYPE

PHYSICAL_COLLATERAL_SUBTYPE

PPI_REASON_LAST_CLAIM

PPI_STATUS_LAST_CLAIM

PPI_TERMS_CONDITIONS

PREMIUM_PAYMENT_STATUS

PREMIUM_PAYMENT_TYPE

PRE_CREDIT

PRIMARY_ECONOMIC_ACTIVITY

PROCESS

PROCESS_ASSOC_TYPE

PRODUCT_CATEGORY_ASSOC_TYPE

PROJECTION_METHOD

PROPERTY_CONSTRUCTION_TYPE

PROPERTY_INS_STATUS

PROPERTY_OWNERSHIP

PROPERTY_TYPE

PROPOSER

PROPOSER_RLNSHP

PROTECTION_CLAIM

PROTECTION_CONDITION

PROTECTION_INS_STATUS

PROTECTION_SPECIAL_TERMS

PROTECTION_STATUS

PROTECTION_TYPE

PROT_TERM_CONDITION

PROVISION_TYPE

PURPOSE

PUT_CALL_TYPE

RADIO_REGION

RECEIVABLES_INSTRUMENT_TYPE

RECEIVABLES_TYPE

RECOVERY_FROM_TYPE

REDEMPTION_CHARGES

REGLTRY_COUNTERPARTY_TYPE

REGULATORY_LGD_SET

REGULATORY_PD_SET

REGULATORY_PRODUCT

RELATIONSHIP

RELATIONSHIP_TO_ACCOUNT

REPO_INSTRUMENT_TYPE

RESIDENT_STATUS

50 Appendix 1

Tables Loaded in Wave 1 RESPONSE_RULE

RESPONSE_TYPE

RETIREMENT_PLAN_TYPE

RETIREMENT_STATUS

RIGHTS_TYPE

RISK_CATEGORY_ASSOC_TYPE

RISK_CLASS

RISK_FACTOR_MEASURE_TYPE

RISK_FACTOR_ROLE

RISK_FACTOR_VARIABLE

RISK_PROFILE

ROOF_CONSTRUCTION

SALARY_RANGE

SCALE_FACTOR

SCALE_FACTOR_TYPE

SCORE_RANK

SCORE_SEGMENT

SCORE_TYPE

SECURED_STATUS

SECURITIZ_INSTRUMENT_TYPE

SECURITIZ_PRIM_BANK_ROLE

SECURITIZ_STRUC_SUBTYPE

SECURITIZ_STRUC_TYPE

SEGMENT_STATUS

SEGMENT_TYPE

SENIORITY

SERVICE_COST

SERVICING_ARRANGEMENT

SHARED_LOSS_GROUP

SHAREHOLDER_PATTERN

SOCIAL_AIM

SOURCE_SYSTEM

SPECIALIZED_LENDING

SPECIAL_NEEDS

SPECIAL_RATE_TYPE

SPECIAL_TERMS

SPOUSE_BENEFIT

STATE_REGION

STATISTICAL_AREA

STATUS_LAST_CLAIM

STD_OCCUPATION

STRUCTURE_TYPE

SURVEY_SOURCE

SUSPENSIONS

SWAP_INSTRUMENT_TYPE


Tables Loaded in Wave 1 TAX_BRACKET

TAX_DEFERRED_TYPE

TAX_ID_TYPE

TAX_STATUS

TAX_WITHHOLDING

TERMINATION_PROVISION

TIME_FREQUENCY

TIME_OF_DAY_TO_CONTACT

TIME_UNIT_OF_MEASURE

TRANSACTION_METHOD

TRANSACTION_STATUS

TRANSACTION_STATUS_REASON

TRANSACTION_TYPE

TRANSFER_TYPE

TRAVEL_REASON

TRAVEL_STATUS

TV_REGION

UNDERLYING_TERM

UNDERWRITING_AREA

UNIT_OF_MEASURE

USED_TO_COVER_RISK_TYPE

VALUATION_TYPE

VARIABLE_RATE_PLAN_TYPE

VOLUNTARY_EXCESS

WINTER_SPORTS

WITHDRAWAL_RESTRICTION

52 Appendix 1

Tables Loaded in Wave 2 ADD_ON_SET

ANALYTICAL_MODEL_TYPE

ASSESSMENT_AGENCY

BUSINESS_LINE

COMMODITY_CODE

COST_CENTER

COUNTRY_EXTERNAL_DATA

COUNTY

CREDIT_FACILITY_GROUP

CR_MITIGANT_VALUATION_TYPE

DEBIT_CREDIT_CODE

ECONOMIC_ENTITY

EVENT_OCCURENCE

EXTERNAL_FINANCIAL_ACCOUNT

EXTERNAL_ORG

FINANCIAL_BOOK

FINANCIAL_CALENDAR

FINANCIAL_INSTRUMENT_TYPE

FINANCIAL_PRODUCT_CATEGORY

FIRM_RISK_FACTOR_VALUE

FIRM_SCALE_FACTOR_VALUE

GENERAL_HEDGING_SET

HOUSEHOLD

ISSUE_CODE

MARKETING_CAMPAIGN

MINIMUM_LGD_IRB_COLLATERAL

PHYSICAL_COLLATERAL

POSTAL_CD_EXTERNAL_DATA

PROCESS_ASSOC

RECEIVABLES_POOL

REGULATORY_LGD

REGULATORY_OPTION_SET

REGULATORY_PARAMETER_SET

REGULATORY_PD

REGULATORY_RISK_WEIGHT_SET

RESOLUTION_CODE

RISK_CATEGORY

SECURITIZATION_POOL

SEGMENT

SOURCE_MEASURE

SURVEY

TERM

TRADE


Tables Loaded in Wave 3 ADD_ON

ANALYTICAL_MODEL

BL_RISK_FACTOR_VALUE

BL_SCALE_FACTOR_VALUE

BUSINESS_LINE_ASSOC

CAMPAIGN_COMMUNICATION

COST_CENTER_ASSOC

EMPLOYEE

EXTERNAL_ORG_ADDRESS

EXTERNAL_ORG_ASSOC

EXTERNAL_ORG_CONTACT

EXTERNAL_ORG_FINANCIAL_DATA

EXTERNAL_ORG_INDUSTRY

FINANCIAL_CAL_DATE

FINANCIAL_INSTITUTION

FINANCIAL_PRODUCT

HOUSEHOLD_MEASURE

HOUSEHOLD_X_SEGMENT

INSURANCE_COVERAGE

INTERNAL_LOSS_EVENT

PRODUCT_CATEGORY_ASSOC

PROPERTY

REGULATORY_OPTION

REGULATORY_PARAMETER

REGULATORY_RISK_WEIGHT

RISK_CATEGORY_ASSOC

RISK_FACTOR

SOURCE_MEASURE_ARGUMENT

STD_INTERNAL_BL_ASSOC

STD_INTERNAL_RISK_CAT_ASSOC

SURVEY_QUESTION

54 Appendix 1

Tables Loaded in Wave 4 ANALYTICAL_MODEL_TRGT_PROD

ANALYTICAL_MODEL_X_RISK_FACTOR

ASSESSMENT_RATING_GRADE

BOND_QUOTE

CCF_AMORTIZATION_SET

CCF_SET

COMMODITY_QUOTE

CORE_PRODUCT

CREDIT_CARD_PRODUCT

EMPLOYEE_ASSOC

EQUITY_QUOTE

EQUITY_VOLATILITY_QUOTE

EXTERNAL_INDIVIDUAL

FINANCIAL_PRODUCT_X_BL

FX_FORWARD_QUOTE

FX_QUOTE

FX_VOLATILITY_QUOTE

HAIRCUT_SET

INTEREST_RATE_QUOTE

INTERNAL_ORG

INT_LOSS_EVENT_X_CAUSE

INT_LOSS_EVENT_X_INSURANCE

INT_RATE_VOLATILITY_QUOTE

LOAN_PRODUCT

MARKET_DATA

MORTGAGE_PRODUCT

PHYSICAL_ASSET

PROFIT_CENTER


Tables Loaded in Wave 5 ASSESSMENT_VALUES

ASSET_X_PHYSICAL_COLLATERAL

BUSINESS_ENTITY

BUSINESS_ENTITY_X_INTERNAL_ORG

CCF

CCF_AMORTIZATION

CONFIGURATION

CREDIT_SPREAD_QUOTE

EMPLOYEE_X_INTERNAL_ORG

EXTERNAL_INDIVIDUAL_ADDRESS

FINANCIAL_REPORTING_DATA

FINANCIAL_UNIT

GENERAL_PROVISION

GL_ACCOUNT

HAIRCUT_FX

INTERNAL_ORG_ADDRESS

INTERNAL_ORG_ASSOC

INTERNAL_ORG_FINANCIAL_DATA

INTERNAL_ORG_X_COST_CENTER

PHYSICAL_ASSET_CREDIT_ASSESS

RATING_GRADE_BAND

REGULATORY_CAPITAL

RISK_LIMITS

Tables Loaded in Wave 6 CONFIGURATION_X_INTERNAL_ORG

FINANCIAL_ASSOCIATE

GL_ACCOUNT_ASSOC_TYPE

GL_TRANSACTION_SUM

HAIRCUT

HEDGING_SET_TYPE

Tables Loaded in Wave 7 CUSTOMER

GL_ACCOUNT_ASSOC

HEDGING_SET_TYPE_X_MAT_BAND

56 Appendix 1

Tables Loaded in Wave 8 CAMPAIGN_COMM_SUPPRESSED

CORPORATE_CUSTOMER

COUNTERPARTY

CUSTOMER_MODEL_SCORE

CUSTOMER_SURVEY

CUSTOMER_X_EVENT

CUSTOMER_X_SEGMENT

FINANCIAL_ACCOUNT_APPLICATION

HOUSEHOLD_MODEL_SCORE

INDIVIDUAL_CUSTOMER

SURVEY_ANSWER

Tables Loaded in Wave 9 APPLICATION_SCORE

CONTACT

CORPORATE_CUSTOMER_OWNER

CORPORATE_CUST_MEASURE

COUNTERPARTY_ASSOC

COUNTERPARTY_CREDIT_ASSESSMENT

COUNTERPARTY_CREDIT_BEHAVIOR

COUNTERPARTY_INSURANCE

COUNTERPARTY_X_CUSTOMER

COUNTERPARTY_X_EXTERNAL_IND

COUNTERPARTY_X_EXTERNAL_ORG

COUNTERPARTY_X_INTERNAL_ORG

COUNTERPARTY_X_SEGMENT

FINANCIAL_ACCOUNT_APPLICANT

FINANCIAL_INSTRUMENT

INDIVIDUAL_CUSTOMER_ADDRESS

INDIVIDUAL_CUST_MEASURE

INDIVIDUAL_CUST_X_INTEREST

IND_CUSTOMER_CASH_FLOW

NETTING_SET


Tables Loaded in Wave 10 APPLICANT_CASH_FLOW

BOND_INSTRUMENT

CASHFLOW_INSTRUMENT

COMMODITY_INSTRUMENT

CONTACT_HISTORY

CREDIT_BUREAU_INFO

CREDIT_DERIVATIVE_INSTRUMENT

CREDIT_FACILITY

DERIVATIVE_INSTRUMENT

EQUITY_INSTRUMENT

FINANCIAL_COLLATERAL

FINANCIAL_INSTRUMENT_ISSUE

FINANCIAL_INSTRUMENT_VAR

FINANCIAL_INST_CREDIT_ASSESS

FRA_INSTRUMENT

FUND_INSTRUMENT

FX_INSTRUMENT

GUARANTEE

INVESTMENT_PRODUCT

OPTION_INSTRUMENT

RECEIVABLES_INSTRUMENT

REPO_INSTRUMENT

RESPONSE

RISK_FACTOR_X_FINANCIAL_INST

RISK_POSITION

SECURITIZATION_INSTRUMENT

SWAP_IRS_CCS_INSTRUMENT

Tables Loaded in Wave 11

CASHFLOW_PAYMENT

CASHFLOW_RESETS

CREDIT_FACILITY_CREDIT_ASSESS

FINANCIAL_ACCOUNT

FINANCIAL_POSITION

RECEIVABLES

RECEIVABLES_ASSET

58 Appendix 1

Tables Loaded in Wave 12 ACCOUNT_CREDIT_ASSESSMENT

ACCOUNT_EVENT

CORE_BANKING_ACCOUNT

CREDIT_CARD_ACCOUNT

CREDIT_RISK_MITIGANT

CUSTOMER_ACCOUNT_SCORE

CUSTOMER_X_FINANCIAL_ACCOUNT

FINANCIAL_ACCOUNT_ADDRESS

FINANCIAL_ACCOUNT_CHNG

FINANCIAL_ACCOUNT_INQUIRY

FINANCIAL_ACCOUNT_PAYMENTS

FINANCIAL_ACCOUNT_RESETS

FINANCIAL_ACCOUNT_RESTRICTION

FINANCIAL_ACCOUNT_ROLE

FINANCIAL_ASSOCIATE_X_ACCOUNT

FINANCIAL_PRODUCT_ACCOUNT

HEDGE

INVESTMENT_ACCOUNT

LOAN_ACCOUNT

MORTGAGE_ACCOUNT

PROTECTION_INSURANCE_ACCOUNT

RETIREMENT_ACCOUNT

SPECIFIC_PROVISION

TRAVEL_INSURANCE_ACCOUNT


Tables Loaded in Wave 13 ACCOUNT_CREDIT_RISK_MITIGANT

BANK_CARD

CORE_ACCT_REGULAR_PAYMENT

CORE_ACCT_TRANSACTION

CORE_BANKING_ACCOUNT_CHNG

COUNTERPARTY_X_CR_MITIGANT

CREDIT_CARD_ACCOUNT_CHNG

CREDIT_CARD_PROTECTION

CREDIT_CARD_STMT

CREDIT_CARD_TRANSACTIONS

CREDIT_DERIV_CR_MITIGANT

DEFAULT_EVENT

EXPOSURE_CR_MITIGANT_RANK

FINANCIAL_ACCOUNT_ROLE_ADDRESS

FINANCIAL_POSITION_CR_MITIGANT

INVESTMENT_TRANSACTION

LIFE_INSURANCE_ACCOUNT

LOAN_ACCOUNT_CHNG

LOAN_TRANSACTION

MORTGAGE_ACCOUNT_CHNG

MORTGAGE_ADDITIONAL_BORROWING

MORTGAGE_TRANSACTION

MOTOR_INSURANCE_ACCOUNT

PROPERTY_INSURANCE_ACCOUNT

PROTECTION_INSURANCE_ACCT_CHNG

PROTECTION_INSURANCE_CLAIM

PROTECTION_PREMIUM_PAYMENT

RETIREMENT_ACCOUNT_TRANSACTION

TRAVEL_CLAIM

TRAVEL_INSURANCE_ACCOUNT_CHNG

TRAVEL_PREMIUM_PAYMENT

WIRE_TRANSFER

60 Appendix 1

Tables Loaded in Wave 14 EXPOSURE_RECOVERY

LIFE_CLAIM

LIFE_PREMIUM_PAYMENT

MOTOR_CLAIM

MOTOR_INSURANCE_ACCOUNT_CHNG

MOTOR_INSURANCE_COVERAGE

MOTOR_PREMIUM_PAYMENT

MOTOR_VEHICLE

OUTBOUND_COMMUNICATION

PROPERTY_CLAIM

PROPERTY_INSURANCE_ACCT_CHNG

PROPERTY_INSURED_ITEM

PROPERTY_PREMIUM_PAYMENT

Tables Loaded in Wave 15 LEASE_ACCOUNT

Tables Loaded in Wave 16 LEASE_TRANSACTION

61

2 Table Types in the SAS Detail Data Store for Banking

Transactional Tables ...................................................................................................................................... 61 Master Tables .................................................................................................................................................. 61 Reference Tables .............................................................................................................................................. 62 Intersection Tables .......................................................................................................................................... 62 Association Tables ........................................................................................................................................... 62 Supertype and Subtype Tables ....................................................................................................................... 63

Transactional Tables Transactional tables capture events that occur at a particular point in time, or have a beginning and ending time. Examples of transactional tables include:

LOAN_TRANSACTION COMMODITY_QUOTE CUSTOMER_ACCOUNT_SCORE

Master Tables Master tables are reference tables that contain a significant number of rows (for example, over 100 rows) and are frequently updated. Master tables differ from reference tables, which contain a limited number of rows and are relatively static. Master tables typically include the following columns:

an _RK column for a retained key that remains the same for different versions of the same instance of an entity

a VALID_FROM_DTTM column and a VALID_TO_DTTM column that distinguish different versions of the same instance of an entity

an _ID column that identifies the source ID a SOURCE_SYSTEM_CD column for the source system code that identifies where

the _ID column comes from Examples of master tables include the following:

FINANCIAL_PRODUCT CUSTOMER COUNTERPARTY

A P P E N D I X

62 Association Tables Appendix 2

Reference Tables Reference tables contain code value definitions. Reference tables are nonvolatile and have a restricted set of values. Reference tables include the following columns:

a _CD column for the code a _DESC column for the code description a VALID_FROM_DTTM column to track changes a VALID_TO_DTTM column to track changes

Examples of reference tables include the following:

ACCOUNT_STATUS ASSET_TYPE

Intersection Tables Intersection tables resolve many-to-many relationships between tables. For example, an employee might be associated with more than one internal organization, and an internal organization contains multiple employees. Examples of intersection tables include the following:

EMPLOYEE_X_INTERNAL_ORG CUSTOMER_X_FINANCIAL_ACCOUNT

Association Tables Association tables establish hierarchy relationships or other relationships between rows in a table. Association tables have an _ASSOC suffix. Association tables include a column that refers to the base rows and a column that refers to the parent row. For example, the INTERNAL_ORG_ASSOC table includes the INTERNAL_ORG_RK column and the PARENT_INTERNAL_ORG_RK column. The INTERNAL_ORG_ASSOC_TYPE_CD column indicates the association type that this row represents. For example, the TYPE code could indicate that the hierarchy is an employee-reporting relationship, such as departments rolling up into divisions. Or, the TYPE code could indicate departments rolling up into different divisions for financial reporting purposes. All association tables have an ASSOC_TYPE table to define the relationship between parent and child rows in the table. Examples of association tables include the following:

INTERNAL_ORG_ASSOC PRODUCT_CATEGORY_ASSOC

Table Types in the SAS Detail Data Store for Banking Supertype and Subtype Tables 63

Supertype and Subtype Tables A supertype table occurs when a parent entity of a category consists of other child entities that exist at a lower level of abstraction and that have their own attributes. For example, CORPORATE_CUSTOMER and INDIVIDUAL_CUSTOMER are subtype tables of the supertype CUSTOMER table. One attribute in the supertype table is used as a category discriminator. This attribute’s value in the physical database indicates which subtype table each record in the supertype table is associated with. A special reference data table stores these values, and contains one record for each value that can be used as a category discriminator. An example of a supertype table and two subtype tables are the following:

CUSTOMER INDIVIDUAL_CUSTOMER CORPORATE_CUSTOMER

64 Supertype and Subtype Tables Appendix 2

65

3 Standard Data Types and Naming Conventions

The banking DDS data models use a consistent naming convention that includes an abbreviation formula and the use of class codes on the columns, as shown in the table. The class code indicates the type of data that is contained in that field (such as code, indicator flag, and so on). The banking DDS data models use a set of domains to define consistency in data types and lengths.

Table 3.1 Standard Data Types

Domain Data Type

Length Class Codes Comment/Example

Identifier Character 32 ID identifier from the data source system

Small Code Character 3 CD short length codes, such as ADDRESS_TYPE_CD

Medium Code Character 10 CD medium length codes, such as EXCHANGE_SYMBOL_CD

Large Code Character 20 CD long length codes, such as POSTAL_CD

Standard Count Code

Numeric 6 CNT standard counts, such as AUTHORIZED_USERS_CNT

Name Character 40 NM proper name, such as LAST_NM, FIRST_NM

Short Length Text

Character 20 TXT short, free-form text

Medium Length Text

Character 100 TXT, DESC long, free-form text and descriptions that are associated with code tables

Indicator Field Character 1 FLG binary indicatory flag (Y or N)

Surrogate Key Numeric 10 RK, SK generated surrogate keys

Currency Amount

Numeric 18,5 AMT standard currency amount

Rates and Percentages

Numeric 9,4 PCT, RT exchange rates, for example

DateTime Date DT, DTTM dates and date/time

A P P E N D I X

67

4 Data Model Notation

Reading the Relationship Notation ................................................................................................................ 67 Things to Keep in Mind ........................................................................................................................... 67

Understanding a One-to-One Relationship ................................................................................................... 68 Understanding a One-to-Many Relationship................................................................................................. 68 Understanding a Supertype and Subtype Relationship................................................................................ 69

Reading the Relationship Notation Two tables can have different types of relationships between them. The notation between the tables in the displayed or printed data model indicates how they are related. This appendix describes how to read this notation.

Note: The entire banking DDS data dictionary for the SAS Detail Data Store for Banking is available in the installation package.

Things to Keep in Mind The banking DDS data model uses Information Engineering notation as it is

represented in the Computer Associates AllFusion ERwin Data Modeler. The ellipses indicate that additional columns exist, but that the additional

columns are not necessary to explain the notation. The key sign indicates that the column represents the primary key of the table.

This type of column is needed to uniquely identify rows in the table. For example, EMPLOYEE_ID is the primary key of the EMPLOYEE table.

The letters “FK” indicate that the column serves as the foreign key. A foreign key is a column, or combination of columns, that refers to a primary key in another table. However, in the case of a recursive relationship, a foreign key could refer to a primary key in the same table. For example, DEPARTMENT_ID in the EMPLOYEE table serves as a foreign key in the DEPARTMENT table.

A P P E N D I X

68 Understanding a One-to-Many Relationship Appendix 4

Understanding a One-to-One Relationship The first example illustrates a one-to-one relationship between two tables; the relationship is a non-identifying relationship, which is optional in both directions. The same notation applies when you specify an optional relationship and when you identify relationships in a one-to-one relationship.

Understanding a One-to-Many Relationship The following examples illustrate a one-to-many relationship between a department and an employee. Each department can contain multiple employees; however, subtle differences exist in these relationships. In the first example, an optional relationship exists between a department and employee in both directions. An EMPLOYEE table does not have to have a DEPARTMENT_ID and a DEPARTMENT table does not have to have an EMPLOYEE_ID. This optional relationship is indicated by the circles on both sides.

In the next example, a DEPARTMENT table must contain one or more EMPLOYEE_IDs. This requirement is indicated by the hash mark on the EMPLOYEE side of the relationship.

Data Model Notation Understanding a Supertype and Subtype Relationship 69

In the next example, the relationship is the same as the previous example, except that an EMPLOYEE table must contain a DEPARTMENT_ID. This requirement is indicated by the hash mark on the DEPARTMENT side of the relationship.

In the next example, an identifying relationship exists between the EMPLOYEE and DEPARTMENT tables. This is indicated by the solid line between the DEPARTMENT table and the EMPLOYEE table. This identifying relationship means that an EMPLOYEE table cannot exist outside the context of a DEPARTMENT table. In identifying relationships, the primary key of the parent table becomes part of the primary key of the child table.

Understanding a Supertype and Subtype Relationship A supertype entity is involved in an inheritance relationship with one or more subtypes. The key attributes or all attributes from this supertype entity can be inherited by each of the subtypes, or the subtype attributes can be consolidated into the parent table. A subtype entity is involved in an inheritance relationship with a parent supertype. The resulting table can inherit attributes from the parent supertype, or attributes can be consolidated with other subtypes into the parent table.

70 Understanding a Supertype and Subtype Relationship Appendix 4

For example, a supertype entity named CUSTOMER has been created to represent the information that is common for different types of customers. The supertype CUSTOMER includes a primary key of customer_id. The subtype entities CORPORATE_CUSTOMER and INDIVIDUAL_CUSTOMER are added as dependent entities that are related to CUSTOMER and contain specific information.

71

5 Banking DDS FAQ

Question: On what databases can the banking DDS be implemented?

Answer: Out of the box, the banking DDS 2.5 can be implemented on SAS, SAS Scalable Performance Data Server, Oracle, and DB2. Tune the specific databases for optimal performance. For example, add indexes and partitions as needed.

Question: Where is the ERwin file for the banking DDS?

Answer: The ERwin file is not included with the banking DDS. It can be requested through an authorized on-site SAS support personnel.

Question: How do I know what banking DDS data items I need for the solution that I am implementing?

Answer: After installing the banking DDS, a directory named supporting documents is available. In this directory, a .pdf file contains data model diagrams of the SAS solutions that are supported by the banking DDS. In addition, you can request a DDS solutions matrix spreadsheet that maps the banking DDS data items to the various SAS solutions.

Question: How can I determine how the banking DDS data model is organized?

Answer: The .html logical model that is provided with the banking DDS is an excellent resource for understanding how the banking DDS is organized. The logical model provides a high-level subject area model with drill-down capabilities into the entities that comprise each subject area. The logical model is in the logical_model folder.

Question: How is historical data stored in the banking DDS without incrementing the surrogate key value?

Answer: The VALID_FROM_DTTM and VALID_TO_DTTM values are used with the _RK value to track historical data over time. For more details, see the “Tracking Historical Data over Time/Point in Time Data” section in Chapter 2, “Physical Design of the SAS Detail Data Store for Banking.”

Question: How are different hierarchies managed in the banking DDS?

Answer: Hierarchies in the banking DDS are maintained through respective association tables (the ASSOC and ASSOC_TYPE tables). For more details, see the “Association Tables” section in Appendix 2, “Table Types in the SAS Detail Data Store for Banking.”

A P P E N D I X

72 Appendix 5

Question: Is there a hierarchy for the FINANCIAL_UNIT table?

Answer: No. The FINANCIAL_UNIT table has a relationship with either the INTERNAL_ORG or EXTERNAL_ORG table, and its hierarchy is available through the respective ASSOC and ASSOC_TYPE tables on the ORG tables.

Question: In the banking DDS, the FINANCIAL_PRODUCT_ACCOUNT table that shows the product and account relationship is not in the history. It has no VALID_FROM and VALID_TO attributes. In this case, how can I handle history? How can I see which product was associated to the account two years ago? Should it be managed by the start and end dates? If yes, how can it be populated? Should we use the SCD Type2 Loader as well?

Answer: For the FINANCIAL_PRODUCT_ACCOUNT table, you can use START_DT and END_DT (part of the primary key in this table) for maintaining history. Verify that the SCD Type2 Loader options use DATE as a means of tracking history, instead of DATETIME.

Question: What is the proper use of EFFECTIVE_FROM_DTTM and EFFECTIVE_TO_DTTM, versus VALID_FROM_DTTM and VALID_TO_DTTM?

Answer: VALID_FROM_DTTM and VALID_TO_DTTM handle the SCD Type2 attributes in a particular row and indicate which record is valid for a given time period. EFFECTIVE_FROM_DTTM and EFFECTIVE_TO_DTTM represent the business date that is associated with the row and can remain constant for multiple rows for a given attribute. For example, an analytical model might be designed to be effective for a certain period of time (for example, a future date). That effective period of time might end at a certain date. However, even if the analytical model is no longer in effect, the data itself is still valid.

Question: Why does the banking DDS specify Numeric(18,5) for currency amounts?

Answer: The banking DDS typically uses Numeric(18,5) as the specification for currency amounts. However, if the banking DDS is implemented in SAS, the upper limit on the floating-point precision is 15 decimal places. Although this could lead to a loss of decimal-place precision, the likelihood of this issue being encountered at a customer site is low. Should this issue occur, you could limit the input to a width of 15 with a precision of 3 decimal places – Informat(15,3). Because you would lose decimal-place precision, this solution should be weighed against the likelihood of encountering a problem with the data that is being loaded into the banking DDS.

73

6 Defining Role-Based Users and Groups for the Banking DDS

Overview........................................................................................................................................................... 73 Planning User Groups and Roles ................................................................................................................... 73 Sample User Groups and Roles ...................................................................................................................... 74

Metadata Administrator ......................................................................................................................... 75 DDS Administrator ................................................................................................................................. 75 ETL Administrator.................................................................................................................................. 75 Information Architect .............................................................................................................................. 75 Power Users.............................................................................................................................................. 76

Overview The tasks in this appendix describe how to define metadata user roles, set up appropriate user IDs, and assign user IDs to groups. This appendix provides a recommended role-based setup guide for banking DDS metadata users. Although the appendix provides recommendations on potential roles, each business should define its own roles that are based on the specialized functions that are required by the business.

Planning User Groups and Roles The steps in planning user groups and roles are:

1 Define user roles that are based on business and functional needs.

2 Determine which user accounts you must establish in the metadata for the business and functional needs.

3 Decide how to organize users into groups.

When setting up user roles, analyze the business and functional needs that pertain to the banking DDS setup and usage. User roles should be robust for the administrative roles, and limited, but not limiting, for the power user roles. User roles should have the correct balance between deployability, usability, and maintainability for your metadata. Consider the various business and functional tasks that need to be accomplished to set up the proper user environment for the banking DDS. For example, some required business and functional tasks are:

setting up the SAS Metadata Server environment registering and administering the metadata maintaining, scheduling, and running the ETL jobs

A P P E N D I X

74 Sample User Groups and Roles Appendix 6

creating information maps creating, running, and viewing reports

After defining the business and functional tasks, create the user IDs and permissions that are necessary to perform the tasks. It is a best practice to organize users into groups because it simplifies the process of establishing and managing the users. Assigning permissions to users on an individual basis can be cumbersome. After defining the groups, assign permissions to the groups rather than to individual users.

Note: Two user groups are automatically created in the Foundation repository: PUBLIC and SASUSERS. Membership in the PUBLIC and SASUSERS groups is implicit. If you can access the SAS Metadata Server, then you are automatically a member of the PUBLIC group. If you can access the SAS Metadata Server and you have your own metadata identity, then you are automatically a member of both the PUBLIC group and the SASUSERS group. Define the user roles and create the groups that are necessary to assist with the administration of the banking DDS.

Sample User Groups and Roles This section suggests user roles and groups. There are various other ways to establish metadata user IDs. For example, there could be a developer group and many types of power users. The sample user roles are derived from the perspective of a production environment. Metadata Administrator

This role is the unrestricted user. It acts as overall administrator for the metadata repository. It is not given access to the directories that contain data.

DDS Administrator This role is the administrator for the banking DDS. It is responsible for managing the metadata for the banking DDS and owns directories that contain banking DDS and any other critical customer data.

ETL Administrator This role updates and runs ETL jobs and provides production support.

Information Architect This role creates information maps using the banking DDS data files. It coordinates with the DDS Administrator to set up privileges for Power Users.

Power Users This role queries the information maps that have been set up. Access to the information maps is assigned by the DDS Administrator in consultation with the Information Architect.

Users and groups are defined around these functional tasks and user roles.

Defining Role-Based Users and Groups for the Banking DDS Sample User Groups and Roles 75

Metadata Administrator The Metadata Administrator is the unrestricted user for your SAS installation. For a default SAS installation, this user is sasadm. This user has unrestricted privileges to the Foundation repository and can make changes to the SAS installation. The Metadata Administrator is responsible for the following:

installing the SAS Metadata Server and other SAS software setting up scripts and services to start and stop the servers creating the Foundation repository setting up and registering the servers within the Foundation repository creating administrative users (for example, the DDS Administrator)

DDS Administrator The DDS Administrator deploys the banking DDS and ETL jobs. The DDS Administrator is created by the Metadata Administrator. The DDS Administrator is responsible for the following:

creating the banking DDS physical data files creating the library and registering the banking DDS metadata creating other libraries that are needed by the deployment importing ETL jobs and EFI (External File Interface) objects creating other users and groups, such as the ETL Administrator and Power Users assigning permissions to the user accounts creating deployment directories

ETL Administrator The ETL Administrator develops, schedules, manages, and deploys ETL jobs. The ETL Administrator is responsible for the following:

verifying that production deployment has worked properly deploying ETL jobs making changes to ETL jobs using SAS Data Integration Studio scheduling ETL jobs using LSF Scheduler or any other third-party scheduler providing production support to resolve any problems in the data reporting on job status by directly accessing the data sets that are created for this

purpose

Information Architect The Information Architect designs, creates, and deploys information maps for Power Users using SAS Information Map Studio. The Information Architect is responsible for the following:

creating and maintaining information maps working with DDS Administrators to set up privileges for Power Users

76 Sample User Groups and Roles Appendix 6

Power Users The Power Users consume the information maps that are generated by the Information Architects. Power Users query the banking DDS based on the information maps. There can be many types of power users. You might need different groups of power users to focus on different business areas. For example, you might set up a credit card power user to focus on credit card accounts, and an investment power user to focus on investment information. Access for power users is restricted to the information maps that they need. The Power User is responsible for the following:

running information maps that have been assigned to the Power User working with the Information Architect to update or request information maps

77

7 Customizing the SAS Detail Data Store for Banking

Overview........................................................................................................................................................... 77 Supported Customizations.............................................................................................................................. 78 Types of Customizations.................................................................................................................................. 78 Scope of Customizations.................................................................................................................................. 79 Steps for Customization .................................................................................................................................. 80 Modifying the Banking DDS .......................................................................................................................... 80 Using Caution.................................................................................................................................................. 81 Avoiding Trouble ............................................................................................................................................. 81 Things to Keep in Mind................................................................................................................................... 81 Tracking Customizations ................................................................................................................................ 82

Overview Initially, the scope of the banking DDS can be larger than the scope of the data that is to be subsequently loaded. New data might be required or a new SAS solution might be needed that requires data that was not previously loaded into the banking DDS. You might need to populate tables and fields in the banking DDS that exist, but were not previously required. An issue to consider is time consistency. If new data will not be loaded to the same historical point in time as the current banking DDS, then that fact must be known to the application that is accessing the data. New data table, column, or field-level requirements mean the following:

a change to data source system extract files a change to SAS Data Integration Studio jobs a possible change to dependencies, DDS tables, and indices the need for new SAS Data Integration Studio jobs for downstream applications

(for example, the solution’s applications that created the new data requirements) Understanding the data flow from the data source to the banking DDS means understanding the impact of adding new data requirements. Identify each affected component, starting with data source system extract files, and modify each component accordingly in a test environment. All customizations should follow the customization guidelines, and all additions should have a defined prefix (for example, X_), which ensures that the upgraded version of the banking DDS does not have a table or column of the same name. Additions should have a meaningful and accurate description.

A P P E N D I X

78 Types of Customizations Appendix 7

Supported Customizations Adding Tables You can add tables to support a new business area or to enhance the functionality of an existing business area that is not in the current banking DDS. New tables should be added with a prefix (for example, X_) to distinguish them from tables that are provided in the current banking DDS. Adding Columns You can add columns to existing tables in the banking DDS to support a new business area or to enhance the functionality of an existing business area that is not in the current banking DDS. New columns should be added with a prefix (for example, X_) to distinguish them from columns that are provided in the current banking DDS. Expanding Column Length You can expand the column length to accommodate the data from upstream applications. You should perform an impact analysis before expanding the length of a column to avoid truncation in tables in downstream applications. Changing Formats and Informats You can change the formats and informats to address the local language requirements. Storing Descriptions in Different Languages Reference tables allow descriptions to be stored in multiple languages. Use the LANGUAGE_CD column in the reference table to specify the language for the description. Remember the downstream processing impact if you store a description in more than one language.

Types of Customizations Customizations can be classified as:

Simple changes: No effect on data structure, logic, or process in the data warehouse (for example, character column length increase, label or format for a column, extra descriptive columns not referenced by supplied applications)

Moderate changes: Localized and clear and simple effect on data structure, logic, or process in the data warehouse (for example, extra data columns for a few specific tables)

Complex changes: Widespread effect with potential complex impact on data structure, logic, or process in the data warehouse (for example, addition of a new table)

The degree of complexity is based on how much manual effort is required and how many components are affected. You should avoid making complex changes because they add to the cost and make future upgrades more difficult.

Customizing the SAS Detail Data Store for Banking Scope of Customizations 79

Scope of Customizations The following figure shows areas of a typical solution and the dependencies that need to be considered when you are working on customizations.

The arrows show the directions of the cascading effects of changes that are made in components. Cascading effects should be considered when deciding whether to build a new data structure or modify an existing one. Based on the figure, the following areas are candidates for customization: Data Model Customization

Detail Data Store Data Delivery ETL Process Customization Reporting Customization

Information Maps Report Definitions

Data Management Processes

Note: Only guidelines for the banking DDS are covered in this document. For guidelines for other business subject areas, refer to the customization guidelines of the respective solutions.

80 Modifying the Banking DDS Appendix 7

Steps for Customization Customizations should be performed by taking a step-by-step approach. Step 1: Identify the customization requirements.

a. Identify the gaps between requirements and the solution’s predefined data mart.

b. For each additional variable that is required in the target data mart, determine whether the required data is available in the banking DDS.

Note: In certain cases, the gaps might not be apparent. If aggregated or derived variables are available in source systems or existing data warehouses, the variables might be directly added to the banking DDS tables to avoid duplicating business rules or aggregations in solutions. Step 2: Map the requirements to identify the banking DDS customizations. Based on the banking DDS gap analysis, specific additions to the banking DDS are identified in this step.

a. First, identify the columns that need to be added to the existing tables.

b. In case the gaps between requirements and the solution are not filled by adding new columns, identify additional tables that need to be added to fill gaps. Also, identify the relationships of the new tables with the existing tables.

Step 3: Implement the identified customizations.

Note: Any changes that are made to the predefined banking DDS environment should be logged. Details of tracking changes are mentioned in a subsequent section.

Modifying the Banking DDS Typically, you can expect to make the following modifications during an implementation:

adding local language or value format requirements adding columns in the tables because of business requirements adding an entity or table because of a new area of business adding new data formats customizing the language code (LANGUAGE_CD) to be specific to a country customizing most of the amount values that use currencies (CURRENCY_CD) to

be specific to a country changing column labels (without any impact) expanding column lengths when data is expected to be longer

This will not have any impact on existing data, but processes that refer to a column whose length has been expanded will need to be updated.

Customizing the SAS Detail Data Store for Banking Things to Keep in Mind 81

Using Caution You can add columns to tables; however, if you use these columns in downstream

applications that have already been implemented, you will need to customize the downstream applications.

You cannot reduce the length of columns because doing so might impact downstream processing.

You can expand the length of columns; however, do so carefully because this can lead to truncation at the solution level.

You can add tables; however, if you use these tables in downstream applications that have already been implemented, you will need to customize the downstream applications. If you add a new business subject area, you should add new processes instead of modifying existing processes.

Avoiding Trouble Do not change column names because doing so can significantly impact

downstream applications that have already been implemented. Do not change data types for columns because doing so can significantly impact

downstream applications that have already been implemented. Do not delete columns. Columns that are not required by the client can hold

missing values. Do not reduce the length of columns because doing so can significantly impact

downstream processing.

Things to Keep in Mind Develop and test modifications to the banking DDS in a controlled development or

test environment before you migrate the modifications to a production environment.

You can add tables or columns or modify the length of columns within the SAS Data Integration Studio environment. See the SAS Data Integration Studio: User’s Guide for more information.

If you change the provided DDL files, back up or copy the original DDL files in case you need to get back to a known starting point.

Identify the job dependencies and job order for new jobs and schedule the jobs accordingly.

When creating user-defined tables and columns, include meaningful and accurate descriptions that are clear and concise without circular reasoning.

82 Tracking Customizations Appendix 7

Tracking Customizations You should track all customizations. Ongoing maintenance and support requires a log of customizations (with the reason for each change and authorization). The following is a sample of ‘useful data to capture’ in the log:

No. Area Customized Item

Date Proposed

Identified By

Customization Decision By

Effort Assigned To

Progress Date Closed

83

8 Deploying the SAS Detail Data Store for Banking in SAS Scalable Performance Data Server

Overview........................................................................................................................................................... 83 Prerequisites .................................................................................................................................................... 83 Creating the Physical Tables .......................................................................................................................... 84 Importing the Metadata .................................................................................................................................. 85 Verification Process ......................................................................................................................................... 95 Updating the Metadata................................................................................................................................... 95

Overview This appendix describes how to set up the SAS Detail Data Store for Banking in a SAS Scalable Performance Data Server environment. It provides step-by-step instructions to help the on-site SAS support personnel and customer system administrator create the physical tables and to register the metadata in an existing metadata repository.

Note: The instructions in this appendix have been tested on a Windows NT 5.2 Service Pack 1 machine with a SAS Scalable Performance Data Server 4.4 client and on a Windows NT 5.2 Service Pack 1 server machine with an SAS Scalable Performance Data Server 4.4 server.

Prerequisites

SAS Scalable Performance Data Server environment deployment requires the SAS Scalable Performance Data Server DDL and the SAS Scalable Performance Data Server package file.

The SAS Scalable Performance Data Server client must be properly installed with access to a server environment in SAS Scalable Performance Data Server.

SAS Data Integration Studio 3.4 or later must be installed and functional. Many administrative tasks, such as setting up the servers that are used to execute jobs, are performed outside of the SAS Data Integration Studio interface. Such administrative tasks are described in the SAS Intelligence Platform documentation, which can be found at http://support.sas.com/913administration. These tasks are required before loading some of the components of the banking DDS. Additionally, all SAS Data Integration Studio hot fixes should be applied. Hot fixes can be downloaded from http://support.sas.com/techsup.

A P P E N D I X

84 Creating the Physical Tables Appendix 8

A SAS Metadata Server and a metadata repository must be operational. Connectivity to the server in SAS Scalable Performance Data Server must be set up in the Foundation repository in which the banking DDS metadata will be registered.

Contact the metadata administrator for the SAS Intelligence Platform to request the server name in SAS Scalable Performance Data Server in the metadata repository. SAS Scalable Performance Data Server connectivity is explained in the “Connecting to Common Data Sources” section in SAS 9.1.3 Intelligence Platform: Data Administration Guide. The SAS Data Integration Studio user ID must be set up for the Authentication Domain of the server.

The server name and schema in SAS Scalable Performance Data Server must exist for the SAS Scalable Performance Data Server package file import process during the metadata registration process.

Creating the Physical Tables DDL CREATE TABLE statements are provided to create the banking DDS physical data structure. A macro named %SPDDLDDS executes the table definitions by using %INCLUDE statements that reference <tablename>.sas files, which contain the CREATE TABLE statements. A default DATE9 informat/format is applied to date fields in the %SPDDLDDS macro. For customer sites with different date requirements, the macro variable DTFMT can be changed to represent the locale (such as EURDFDEw.). The DTFMT macro variable is referenced in the invocation string for the %SPDDLDDS macro. To create the physical tables:

1. Set up the DDS LIBNAME using the SAS Scalable Performance Data Server library engine.

LIBNAME DDS SASSPDS ’<SPDS-data-library>’ SERVER=’<node_name.servname>’ USER=’<user_id>’ PASSWORD=’Password’;

For example, suppose your server LIBNAME domain is spdslib, the SAS Scalable Performance Data Server host machine is spdhost, the SAS Scalable Performance Data Server user ID is user1, the password is Pass1, and the SAS Scalable Performance Data Server named service is spdsname. Your LIBNAME statement would look like the following:

LIBNAME DDS SASSPDS ’spdslib’ SERVER=’spdhost.spdsname’ user=’user1’ password=’Pass1’;

2. Modify and submit the following invocation string:

/* Macro variables to be assigned: Fileloc: Assign fileloc macro parameter to point to the directory which contains the DDL create table statements contained in <tablename>.sas files.



FMTRK: (Optional: Defaults to 12.) Assign a display format for retained surrogate keys such as fmtrk=10.

Deploying the SAS Detail Data Store for Banking in SAS Scalable Performance Data Server Importing the Metadata 85

NUMW_D: (Optional: Defaults to 18.5 If currency or amount (_amt) fields require a display format, assign a new NUMW_D= value, such as NUMW_D=22.2 to display width of 22 with 2 decimal places.

TMFMT: (Optional: Defaults to: NLTIMAP10.) If Time fields are needed to be displayed in a format other than NLTIMAP10., assign TMFMT= value.

%include "<location_of_provided_macro>/spddldds.sas";

LIBNAME DDS “<location_to_store_tables>”;

%spddldds(USER=,PASS=,PATH=,fileloc=<location_of_SPDS_DDL_folder>),

dtfmt=<International_Date_Value>, fmtrk=<default is 12. Use the

appropriate length if you made any customizations to the _RK column>);

%include "<location_of_provided_macro>/spddldds.sas"; %spddldds(USER=,PASS=,PATH=,fileloc=<location_of_SPDS_DDL_folder>);

*/

Note: When invoking the %SPDDDLDDS macro, the FILELOC macro variable has to be set to the folder where the SPDS DDL files are located. For example, if the SPDS DDL files are located in the C:\BankingDDS\DDL\SPDS folder, then FILELOC has to be set to C:\BankingDDS\DDL. For example, using the previous settings, the macro invocation code would look like this:

%include “C:\BankingDDS\DDL\SPDS\spddldds.sas”;

%spddldds(fileloc= C:\BankingDDS\DDL);




Importing the Metadata Importing the banking DDS metadata can be performed using either the Import Wizard in SAS Data Integration Studio or the BI Manager plug-in in the SAS Management Console. In this guide, the focus is on using SAS Data Integration Studio to import banking DDS metadata. There are two other resources that describe how to register metadata using the import feature in SAS Data Integration Studio:


The “Importing, Exporting, and Copying Metadata” chapter in the SAS Data Integration Studio 3.4 User’s Guide contains detailed information about importing metadata.

86 Importing the Metadata Appendix 8

The SAS package file for SAS Scalable Performance Data Server–bankingdds_25_spds.spk–contains the banking DDS library metadata, custom folder structure, table metadata, table notes metadata, and column notes metadata that are needed for the banking DDS metadata registration.

Note: If you have installed the banking DDS on a non-Windows platform, the Windows clients such as SAS Data Integration Studio and SAS Management Console require access to the package files. Accessing the UNIX file system from Windows, or transferring these files from the UNIX file system to the Windows file system, is necessary because these files are required as input to the Windows-based clients. To register your banking DDS metadata using the SAS Data Integration Studio Import Wizard, perform the following steps:




3. In the Import Wizard, click Browse to locate the SAS package file for SAS Scalable Performance Data Server, installed on Windows and located at C:\Program Files\SAS\SASBankingDDS\2.5\Metadata\ bankingdds_25_spds.spk. Verify that Include access controls is not selected, and that All Objects is selected. Click Next to continue.


7. Enter the Scalable Performance Data Server Target server where your banking DDS will be installed. Click Next.


8. Select the Scalable Performance Data Server Target library using the elliptical button. Click Next.


Review the summary and select Import to begin the import process.


When the import is completed, a page states whether it completed successfully. Click the View Log button to view a detailed log of the import process. Click Finish.


9. After a successful import process, the SAS Data Integration Studio Custom Tree folder will contain the DETAIL_DATA_STORE folder, and the following subfolders and library: DDS_MAIN (contains the DDS Main tables) DDS_REFERENCE (contains the DDS Reference tables) DDS_NOTES (contains the DDS Table and Column Notes) Detail Data Store library (refers to the banking DDS 2.5 library)

10. Log on to SAS Management Console as a restricted user.

11. Select the repository in which you have just imported your banking DDS metadata. Expand Data Library Manager, SAS Libraries, and Database Schemas.


12. Right-click on Detail Data Store. Select Properties from the menu. Select the Data Server tab.

13. Using the Default Login menu, select the correct SAS Scalable Performance Data Server user name, or select more options to invoke the search functionality.

14. Click OK to apply the user name to the DDS library.

15. Right-click on the Detail Data Store library, and then select Display Libname. Verify that the library and connection information is correct.


Deploying the SAS Detail Data Store for Banking in SAS Scalable Performance Data Server Updating the Metadata 95


Review the log that was produced by the import process. On Windows platforms, logs are written to the user’s folder at a location such as Documents and Settings\<username>\My Documents. The log file is named import_date/time.log.


Right-click on Detail Data Store and click Properties. Verify that the library and server connection information are correct.

Updating the Metadata If you made any changes to the DDL CREATE TABLE statements, you must update the table metadata to synchronize it with the physical tables. If no changes were made to the DDL CREATE TABLE statements when the physical tables were created, there is no need to update the table metadata.

96 Updating the Metadata Appendix 8

To update the table metadata, log on to SAS Data Integration Studio and select all of the tables that need to be updated from the DDS_MAIN and DDS_REFERENCE folders, which are located under the SAS Data Integration Studio Custom Tree folder. Select Update Table Metadata in the Tools menu. The table metadata is updated to synchronize it with the physical tables.


97

9 Deploying the SAS Detail Data Store for Banking in Oracle

Overview........................................................................................................................................................... 97 Prerequisites .................................................................................................................................................... 97 Creating the Physical Tables .......................................................................................................................... 98 Importing the Metadata .................................................................................................................................. 99 Verification Process ....................................................................................................................................... 109 Updating the Metadata................................................................................................................................. 109

Overview This appendix describes how to set up the SAS Detail Data Store for Banking in an Oracle environment. It provides step-by-step instructions to help the on-site SAS support personnel and customer system administrator create the physical tables and to register the metadata in an existing metadata repository.

Note: The instructions in this appendix have been tested on a Windows XP Service Pack 2 machine with an Oracle Client (9.2.0.1.0), and on a Windows 2000 Service Pack 4 server machine with an Oracle Database 10g Enterprise Edition Release (10.2.0.1.0).

Prerequisites

Oracle environment deployment requires the Oracle DDL and the Oracle package file.

SAS/ACCESS Interface to Oracle must be installed. For more information on Oracle installations, see “Establishing Connectivity to an Oracle Database,” in the SAS Intelligence Plaform: Data Administration Guide.

Oracle clients must be installed and available for accessing the Oracle tables. Ensure that your Oracle tnsnames.ora file contains the proper client-side network configuration parameters.

SAS Data Integration Studio 3.4 or later must be installed and functional. Many administrative tasks, such as setting up the servers that are used to execute jobs, are performed outside of the SAS Data Integration Studio interface. Such administrative tasks are described in the SAS Intelligence Platform documentation, which can be found at http://support.sas.com/913administration. These tasks are required before loading some of the components of the banking DDS. Additionally, all SAS Data Integration Studio hot fixes should be applied. Hot fixes can be downloaded from http://support.sas.com/techsup.

A P P E N D I X


A SAS Metadata Server and a metadata repository must be operational. Connectivity to the Oracle server must be set up in the Foundation repository in which the banking DDS metadata will be registered.

The server name and schema in Oracle must exist for the Oracle package file import process during the metadata registration process.

Creating the Physical Tables DDL CREATE TABLE statements are provided to create the banking DDS physical data structure. A macro named %ORDDLDDS executes the table definitions by using %INCLUDE statements that reference <tablename>.sas files, which contain the CREATE TABLE statements. The %ORDDLDDS macro and the <tablename>.sas files are installed on Windows and located at the default install location of C:\Program Files\SAS\SASBankingDDS\2.5\DDL\Oracle. A default DATE9 informat/format is applied to date fields in the %ORDDLDDS macro. For customer sites with different date requirements, the macro variable DTFMT can be changed to represent the locale (such as EURDFDEw.). The DTFMT macro variable is referenced in the invocation string for the %ORDDLDDS macro. To create the physical tables:

1. Set up the DDS LIBNAME using the Oracle library engine.

LIBNAME DDS ORACLE PATH=’<Oracle TNS Entry>’ USER=’<user name>’ PASSWORD=’<Password>’;

For example, suppose your Oracle user ID is scott, the password is tiger, and the Oracle TNS network configuration points to bankdb.acme.com. Your LIBNAME statement would look like the following:

LIBNAME DDS ORACLE PATH=’bankdb.acme.com’ USER=’scott’ PASSWORD=’tiger’;






NUMW_D: (Optional: Defaults to 18.5 If currency or amount (_amt) fields require a display format, assign a new NUMW_D= value, such as NUMW_D=22.2 to display width of 22 with 2 decimal places.


%include ‘<location_of_provided_macro>/orddldds.sas’;

Deploying the SAS Detail Data Store for Banking in Oracle Importing the Metadata 99


%orddldds(USER=,PASS=,PATH=,fileloc=<location_of_Oracle_DDL_folder>),

dtfmt=<International_Date_Value>, fmtrk=<default is 12. Use the


*/

Note: When invoking the %ORDDLDDS macro, the FILELOC macro variable has to be set to the folder where the Oracle DDL files are located. For example, if the Oracle DDL files are located in the Windows install folder location of C:\Program Files\SAS\SASBankingDDS\2.5\DDL\Oracle, then FILELOC has to be set to this folder location. For example, using the previous settings, the macro invocation code would look like this:

%include “C:\Program Files\SAS\SASBankingDDS\2.5\DDL\Oracle \orddldds.sas”;

%orddldds(USER=’scott’,PASS=’tiger’,PATH=’bankdb.acme.com’,fileloc= C:\Program Files\SAS\SASBankingDDS\2.5\DDL\Oracle);







The SAS package file for Oracle–bankingdds_25_oracle.spk–contains the banking DDS library metadata, custom folder structure, table metadata, table notes metadata, and column notes metadata that are needed for the banking DDS metadata registration. To register your banking DDS metadata using the SAS Data Integration Studio Import Wizard, perform the following steps:



3. In the Import Wizard, click Browse to locate the SAS package file for Oracle, installed on Windows and located at C:\Program Files\SAS\SASBankingDDS\2.5\Metadata\bankingdds_25_oracle.spk. Verify that Include access controls is not selected, and that All Objects is selected. Click Next to continue.


7. Enter the Oracle Target server where your banking DDS will be installed. Click Next.





12. Right-click on ORA_BANKING. Select Properties from the menu. Select the

Options tab. Rename the database schema based on your specific requirements.

Note: When renaming your Oracle schema, the schema name must be in uppercase and named the same as the login. SAS/ACCESS Interface to Oracle requires an uppercase schema name.

108 Verification Process Appendix 9

Using the Default Login menu, select the correct Oracle server where the banking DDS will be installed. Click OK.


Deploying the SAS Detail Data Store for Banking in Oracle Updating the Metadata 109


Review the log that was produced by the import process. On Windows platforms, logs are written to the user’s folder at a location such as Documents and Settings\username\My Documents. The log file is named import_date/time.log.



Updating the Metadata If you made any changes to the DDL CREATE TABLE statements, you must update the table metadata to synchronize it with the physical tables. If no changes were made to the DDL CREATE TABLE statements when the physical tables were created, there is no need to update the table metadata.


To update the table metadata, log on to SAS Data Integration Studio and select all of the tables that need to be updated from the DDS_MAIN and DDS_REFERENCE folders, which are located under the SAS Data Integration Studio Custom Tree folder. Select Update Table Metadata in the Tools menu. The table metadata is updated to synchronize it with the physical tables.


113

10 Deploying the SAS Detail Data Store for Banking in DB2

Overview......................................................................................................................................................... 113 Prerequisites .................................................................................................................................................. 113 Creating the Physical Tables ........................................................................................................................ 114 Importing the Metadata ................................................................................................................................ 115 Verification Process ....................................................................................................................................... 123 Updating the Metadata................................................................................................................................. 124

Overview This appendix describes how to set up the SAS Detail Data Store for Banking in a DB2 environment. It provides step-by-step instructions to help the on-site SAS support personnel and customer system administrator create the physical tables and to register the metadata in an existing metadata repository.

Note: The instructions in this appendix have been tested on a Windows XP Service Pack 2 machine with a DB2 9.1 client, and on a Windows 2000 Service Pack 4 server machine with a DB2 9.1.0.0 database.

Prerequisites

DB2 environment deployment requires the DB2 DDL and the DB2 package file. SAS/ACCESS Interface to DB2 must be installed. For more information on DB2

installations, see “Connecting to Common Data Sources,” in the SAS Intelligence Plaform: Data Administration Guide.

DB2 clients must be installed and available for accessing the DB2 tables. SAS Data Integration Studio 3.4 or later must be installed and functional. Many

administrative tasks, such as setting up the servers that are used to execute jobs, are performed outside of the SAS Data Integration Studio interface. Such administrative tasks are described in the SAS Intelligence Platform documentation, which can be found at http://support.sas.com/913administration. These tasks are required before loading some of the components of the banking DDS. Additionally, all SAS Data Integration Studio hot fixes should be applied. Hot fixes can be downloaded from http://support.sas.com/techsup.

A SAS Metadata Server and a metadata repository must be operational. Connectivity to the DB2 server must be set up in the Foundation repository in which the banking DDS metadata will be registered.

A P P E N D I X


The server name and schema in DB2 must exist for the DB2 package file import process during the metadata registration process.

Creating the Physical Tables DDL CREATE TABLE statements are provided to create the banking DDS physical data structure. A macro named %D2DDLDDS executes the table definitions by using %INCLUDE statements that reference <tablename>.sas files, which contain the CREATE TABLE statements. The %D2DDLDDS macro and the <tablename>.sas files are installed on Windows and located at the default install location of C:\Program Files\SAS\SASBankingDDS\2.5\DDL\DB2. A default DATE9 informat/format is applied to date fields in the %D2DDLDDS macro. For customer sites with different date requirements, the macro variable DTFMT can be changed to represent the locale (such as EURDFDEw.). The DTFMT macro variable is referenced in the invocation string for the %D2DDLDDS macro. To create the physical tables:

1. Set up the DDS LIBNAME using the DB2 library engine.

LIBNAME DDS DB2 DSN=’<DB2 Data Server Name>’ USER=’<user name>’ PASSWORD=’<Password>’ SCHEMA=’<schema name>’;

For example, suppose your DB2 user ID is dba1, the password is Pass1, the DSN is sample, and the schema is BANKINGDDS. Your LIBNAME statement would look like the following:

LIBNAME DDS DB2 DSN=’sample’ USER=’dba1’ PASSWORD=’Pass1’ schema=BANKINGDDS;






NUMW_D: (Optional: Defaults to 18.5 If currency or amount (_amt) fields require a display format, assign a new NUMW_D= value, such as NUMW_D=22.2 to display width of 22 with 2 decimal places


Deploying the SAS Detail Data Store for Banking in DB2 Importing the Metadata 115

%include "<location_of_provided_macro>/ddldds.sas";


%include ‘<location_of_provided_macro>/d2ddldds.sas’;

%d2ddldds(USER=,PASS=,DSN=,SCHEMA=,fileloc=<location_of_DB2_DDL_folder>

)dtfmt=<International_Date_Value>, fmtrk=<default is 12. Use the


*/

Note: When invoking the %D2DDLDDS macro, the FILELOC macro variable has to be set to the folder where the DB2 DDL files are located. For example, if the DB2 DDL files are located in the default install location of C:\Program Files\SAS\SASBankingDDS\2.5\DDL\DB2 folder, then FILELOC has to be set to this folder location. For example, using the previous settings, the macro invocation code would look like this:

%include “C:\Program Files\SAS\SASBankingDDS\2.5\DDL\DB2\d2ddldds.sas”;

%d2ddldds(USER=’dba1’,PASS=’Pass1’,DSN=’Sample’,SCHEMA=’BANKINGDDS’, fileloc=C:\Program Files\SAS\SASBankingDDS\2.5\DDL\DB2);







The SAS package file for DB2–bankingdds_25_db2.spk–contains the banking DDS library metadata, custom folder structure, table metadata, table notes metadata, and column notes metadata that are needed for the banking DDS metadata registration.

Note: If you have installed the banking DDS on a non-Windows platform, the Windows clients such as SAS Data Integration Studio and SAS Management Console require access to the package files. Accessing the UNIX file system from Windows, or transferring these files from the UNIX file system to the Windows file system, is necessary because these files are required as input to the Windows-based clients.


To register your banking DDS metadata using the SAS Data Integration Studio Import Wizard, perform the following steps:



3. In the Import Wizard, click Browse to locate the SAS package file for DB2, installed on Windows and located at C:\Program Files\SAS\SASBankingDDS\2.5\Metadata\bankingdds_25_db2.spk. Verify that Include access controls is not selected, and that All Objects is selected. Click Next to continue.


7. Enter the DB2 Target server where your banking DDS will be installed. Click Next.


12. Right-click on DB2_BANKING. Select Properties from the menu. Select the Options tab. Rename the database schema based on your specific requirements.

Note: When renaming your DB2 schema, the schema name must be in uppercase. SAS/ACCESS Interface to DB2 requires an uppercase schema name.


Deploying the SAS Detail Data Store for Banking in DB2 Verification Process 123

14. Using the Default Login menu, select the correct DB2 server where the banking DDS will be installed. Click OK.



Review the log that was produced by the import process. On Windows platforms, logs are written to the user’s folder at a location such as Documents and Settings\username\My Documents. The log file is named import_date/time.log.




Updating the Metadata If you made any changes to the DDL CREATE TABLE statements, you must update the table metadata to synchronize it with the physical tables. If no changes were made to the DDL CREATE TABLE statements when the physical tables were created, there is no need to update the table metadata. To update the table metadata, log on to SAS Data Integration Studio and select all of the tables that need to be updated from the DDS_MAIN and DDS_REFERENCE folders, which are located under the SAS Data Integration Studio Custom Tree folder. Select Update Table Metadata in the Tools menu. The table metadata is updated to synchronize it with the physical tables.

Deploying the SAS Detail Data Store for Banking in DB2 Updating the Metadata 125


127

0 Glossary

AllFusion ERwin Data Modeler a visual data modeling tool that is used to create and maintain databases, data

warehouses, and enterprise data models. The AllFusion ERwin Data Modeler helps to visually determine the proper data structure, key elements, and optimal design for the database.

analytical result a calculation that is a result of applying an analytic process. In the context of the

banking DDS, an analytic result can be a cross-sell, up-sell, or customer-retention score, and can relate to an account, customer, or household.

attribute in entity-relationship modeling, an attribute is a property or a characteristic of an

entity that can be stored as a data fact. In subject modeling, an attribute represents any characteristic that the modeler might choose to capture about a subject.

banking solution a SAS analytic solution that addresses banking-specific issues, including

segmentation, customer retention, cross-sell, up-sell, credit scoring, campaign management, and strategic performance management.

business key also referred to as the natural key, the business key is the primary identifying data

from the source system. A business key often goes back to the transactional system to investigate the source data.

data mart a subset of the data in a data warehouse. Typically, a data mart is designed to meet

the needs of a particular department or set of users. A data mart can also be used to store the results of ad hoc queries or cross-subject analyses. A data mart is more limited in scope than a data warehouse, which typically contains information that is used by more than one department.

data source in the context of the banking DDS, a source of data for populating the DDS. The

data is extracted and might be transformed before loading it into the DDS. A data source can be in any format that SAS can read and on any supported hardware platform.

DDL (Data Definition Language) a language for describing and defining data and its relationships in a database. The

banking DDS includes DDL to create the data objects that are part of the DDS architecture. DDL syntax is specific to the target environment, but largely governed by a set of SQL standards published by ANSI.

128 Glossary

DDLDDS a macro that includes the SAS files that are necessary to create tables that are used

to instantiate the model.

denormalized a data object structure that has intentional data redundancy, most often done to

improve the performance of the data model.

detail data in general, the most granular level of data. In the context of the DDS, the data is

rarely moved into a summarized form.

detail data store (DDS) the “system of record” for SAS solutions that are served by the DDS. DDS data

contains minimal summarization. The DDS serves as a single version of the truth for the solution data marts and is the integration layer for SAS solutions.

dimension an aspect or perspective by which data can be accessed, selected, sequenced,

grouped, filtered, and presented. Dimensions offer an intuitive way of organizing and selecting data for retrieval, exploration, and analysis.

entity a topic about which the business stores or plans to store data. An entity is a person,

place, thing, concept, or event that represents a subject of business information.

ETL (extract, transform, and load) the process of (1) obtaining data from data sources (the extract step), (2) cleansing

and preparing data for import into the data warehouse (the transform step), and (3) importing the transformed data into the data warehouse (the load step).

ETL job a set of instructions that is used to specify ETL processes that are needed to create

output.

external data data taken from sources outside the business, such as purchased mailing lists,

customer profiles, competitor profiles, and marketing data. External data is also referred to as third-party data.

foreign key the primary key of one data structure that is placed into a related data structure to

represent a relationship between those data structures. A foreign key resolves relationships and supports navigation among data structures.

information engineering (IE) a methodology that is used in entity-relationship modeling that uses its own

notation for model diagrams.

intersection table a table that resolves a many-to-many relationship between two or more tables.

master table a reference table that usually contains a significant number of rows and is

frequently updated. Master tables contain key business information, which can include data about customers, products, suppliers, and so on. This data is typically non-transactional.

Glossary 129

metadata

abstractly, metadata is “data about data.” More concretely, metadata is structured data (available in known formats and through known access methods) that describes the location and structure of enterprise data.

metadata repository a container for metadata that is managed by the SAS Metadata Server. A SAS

Metadata Server can manage many SAS metadata repositories of several types. All SAS Metadata Servers have a Foundation repository that stores critical metadata that must be shared with the other types of repositories and is the primary production environment.

natural key See business key.

non-temporal data non-time-sensitive or non-event data, such as a customer or financial account.

Master data and reference data are examples of non-temporal data. Non-temporal data can become temporal data if a process starts to capture and date or time-stamp how then underlying data changes over time, such as an account name change.

normalize the process of organizing data to minimize data redundancy.

physical data model represents the detailed specifications of what is physically implemented as data

objects such as tables, views, and so on.

primary key a set of one or more attributes that uniquely identifies a single occurrence in a data

structure, such as a row in a table. In a logical model, every entity has a primary key.

reference table contains the code value definitions and their associated descriptions. A reference

table is non-volatile (rarely updated) and has a restricted set of values.

relationship an important, real-world association among entities that has meaning as business

information. In a subject model, relationships illustrate the most visible business associations among subjects. In a physical data model, relationships are implemented via foreign keys. For example, the relationship "a customer can open one or more accounts" is implemented by placing the customer primary key as a foreign key on all financial account records for that customer.

retained key a generated surrogate key that uniquely identifies a record at that point in time.

Because data is coming from multiple source systems, the business key is not sufficient to identify the record. The retained key (_RK) field, which is part of the primary key, is populated with a retained surrogate (generated) key. This numeric key does not change for the different versions of the record.

130 Glossary

slowly changing dimension (SCD) a technique for tracking changes to table values in order to maintain the history of

changes. For example, a dimension table named Customers might have columns for Customer ID, Home Address, Age, and Income. Each time the address or income changes for a customer, a new row could be created for that customer in the dimension table, and the old row could be retained. This historical record of changes could be combined with purchasing information to forecast buying trends and to direct customer marketing campaigns.

source an input to an operation. Source is often referred to as source data.

source system identifier See business key or natural key.

subject a high-level view of a topic of business interest that is equivalent to both a global

data class and a class of data entities.

subtype an entity or table that is involved in an inheritance relationship with a parent

supertype. The resulting table can inherit attributes from the parent supertype, or attributes can be consolidated with other subtypes into the parent table.

surrogate key a single-part, artificially established identifier for an entity. A surrogate key is a

special case of derived data - one where the primary key is derived. A common way of deriving surrogate key values is to assign integer values sequentially.

supertype an entity that is involved in an inheritance relationship with one or more subtypes.

The key attributes or all attributes from this supertype entity can be inherited by each of the subtypes, or the subtype attributes can be consolidated into the parent table.

target an output location of an operation.

temporal data event data that occurs at a particular date and time, such as an account inquiry.

Temporal data is often referred to as time-sensitive data.

transformation a process that performs some type of transformation on data in SAS Data

Integration Studio. Each transformation that is specified in a process flow diagram generates or retrieves SAS code. User-written code can be specified in the metadata for any transformation in the process flow diagram.

Your Turn

We want your feedback.

• If you have comments about this book, please send them to [email protected]. Include the full title and page numbers (if applicable).

• If you have comments about the software, please send them to [email protected].

SAS® Publishing gives you the tools to flourish in any environment with SAS!Whether you are new to the workforce or an experienced professional, you need to distinguish yourself in this rapidly changing and competitive job market. SAS® Publishing provides you with a wide range of resources to help you set yourself apart.

SAS® Press SeriesNeed to learn the basics? Struggling with a programming problem? You’ll fi nd the expert answers that you need in example-rich books from the SAS Press Series. Written by experienced SAS professionals from around the world, these books deliver real-world insights on a broad range of topics for all skill levels.

s u p p o r t . s a s . c o m / s a s p r e s sSAS® DocumentationTo successfully implement applications using SAS software, companies in every industry and on every continent all turn to the one source for accurate, timely, and reliable information—SAS documentation. We currently produce the following types of reference documentation: online help that is built into the software, tutorials that are integrated into the product, reference documentation delivered in HTML and PDF—free on the Web, and hard-copy books.

s u p p o r t . s a s . c o m / p u b l i s h i n gSAS® Learning Edition 4.1Get a workplace advantage, perform analytics in less time, and prepare for the SAS Base Programming exam and SAS Advanced Programming exam with SAS® Learning Edition 4.1. This inexpensive, intuitive personal learning version of SAS includes Base SAS® 9.1.3, SAS/STAT®, SAS/GRAPH®, SAS/QC®, SAS/ETS®, and SAS® Enterprise Guide® 4.1. Whether you are a professor, student, or business professional, this is a great way to learn SAS.

s u p p o r t . s a s . c o m / L E

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies. © 2007 SAS Institute Inc. All rights reserved. 428713_1US.0307