04 bdw physical model

7/16/2019 04 BDW Physical Model

1/22

Industry Models And Assets Lab

July 2007 2005 IBM Corporation

BDW physical Model

Scoping the Model


2/22


04 BDW Physical Model | Isbank 2005 IBM Corporation2

What should the Warehouse contain?- Approach 1. Stand-alone Scoping.


3/22



What should the Warehouse contain?- Approach 2. FSDM Scoping


4/22



What should the Warehouse contain?

- Approach 3. BST Scoping via FSDM


5/22



What should the Warehouse contain?- Approach 4. AST Scoping


6/22



What should the Warehouse contain?

- Approach 5. BST Scoping


7/22



BDW Physical Model

History In The BDWM


8/22



History In The BDWM

One of the principle functions of a data warehouse is the storage of historical

facts. For example: The balance on an account at a given point in time

Transaction Details

What was the Total Income from a Product for a given month?

The complete list of Relationship Managers dealing with a Customer over time

The variances in prices of Financial Instruments

Rate fluctuations

BDW implementation experience has shown that several different forms of

structure are required to store different types of history:

- Invariant facts - Episodic History

- Snapshot History - Continuous Relationship History

- Periodic History - Continuous Value History

- Fixed periods

- Ad Hoc periods


9/22



History In The BDWM Invariant Facts

Name Smith, John

Sex Male

Date Of Birth 20 March 1956

Marital Status Separated

Judgement required as to

"business significance in

the context of given scope.

In a warehouse, some values will not be subject to change.

For example:A Customers Date Of Birth

The initial amount deposited for a savings account

There will also be values for which a change has no business

significance for the Financial Institution E.g. not interested in tracking marital status

For the types of value above, there is no need to track any

history. (Assuming that if an error occurs, then there is no

need to track the original wrong value)

For such values, the BDWM usually records the currentvalue and the date on which it was assigned (where

appropriate), directly on the entity concerned.

This enables a quick and easy lookup of the values.


10/22



History In The BDWM Snapshots

Some entities will require that certain values be captured at material points in

time. For example:

When a change in a Financial Instrument price occurs

When a change in general market conditions occurs

For such cases, the BDWM

uses Snapshot tables

Values in a Snapshot table

are taken at a given point-in-

time and relate only to that

point-in-time


11/22



History In The BDWM Periodic History Some entities will require that values be captured that

relate to a period. For example:

Opening and Closing values for the period

Statistical values (averages, maximums, minimums, )

Aggregations (totals, counts, )

The period may be fixed i.e. a specific month, quarter oryear

The BDWM uses Summary Tables for such values

Keyed on MEASUREMENT PERIOD.

The period may be ad hoc i.e. between any two relevantdates

The BDWM uses Profile Tables for such values

Keyed on an Effective Date and End Date pair

The Date pair may span past, present or future periods

Financial

Results

1993-94

Financial

Results

1994-95

Financial

Results

1995-96

.


12/22



History In The BDWM Episodic History

Some entities are intrinsically historical

In the BDWM, such entities are stored as

Event and its subtypes.

For example:

Transaction

Communication

These are recorded in the warehouse in a

completed non-volatile state i.e. once the

Event occurs, the details of it can not change.

No need to track changes, as changes cannot

occur

1 2

3 4 5 6 7 8 9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

2

4

2

5

2

6

2

7

2

8

2

9

3

031


13/22



History In The BDWM Continuous Relationships

Variations in relationships holding between different

concept instances will vary over time. For example:

Relationships between Involved Parties

Relationships between Involved Parties and Arrangements

Relationships between Arrangements and Resource Items

Relationships between Arrangements and Classifications

In the BDWM, such relationships are held in Associative

entities. These are principally keyed on:

The two relevant concepts

The Type of the relationship

An Effective Date / End Date pair

By convention, a NULL End Date implies a current

relationship

Gives full historical tracking of relationships


14/22



History In The BDWM Continuous Values

Certain entities in the BDWM require that

significant related numerical values aretracked fully over time

General approach is to use the generic

Accounting Unit structures

Accounting Unit defines the semantics of the

number

Accounting Unit Balance records (historical)

variations of the number

For certain entities, a specific value is so

commonly required and subject to change

that specific attributive History tables have

been provided. For example:The worth of a Resource Item

The values of a Rate

Quotes for Financial Instruments


15/22



History In The BDWM

Different types of data have different

historical storage requirements e.g.

Values at a point in time

Values relating to a period

Changes in relationships over time

For each different type of data, the BDWM

provides appropriate data structures to

record the relevant type of history

The sets of attributes provided can be

customized to suit the exact needs of the

Financial Institution


16/22



Logical / Physical

BDW Physical Model

I d M d l A d A L b


17/22



Logical / Physical Entities

Involved Party

Involved Party Id

Population Date (FK)

Population Time (FK)

Source System Id (FK)

Unique Id In Source System (FK)

Involved Party Type Id (FK)

Primary Relationship Type Id (FK)

Customer Life Cycle Status Type Id (FK)

Customer Life Cycle Status Effective DateFinancial Legal Status Id (FK)

Financial Legal Status Effective DateLegal Competency Status Id (FK)

Legal Competency Status Effective DateInvolved Party Credit Risk Rating Id (FK)

Involved Party Credit Risk Rating DateInvolved Party Risk Exposure Type Id (FK)

Involved Party Risk Exposure Effective DateProbability Of Default Category Id (FK)

Probability Of Default Category Effective DateOperational Risk Approach Type Id (FK)

Operational Risk Approach Type Effective DateMarket Risk Approach Type Id (FK)

Market Risk Approach Type Effective DateCounterparty Credit Risk Approach Type (FK)

Counterparty Credit Rsk Approach Effective DateInvolved Party Skill Type Id (FK)

Involved Party Name

Financial Institution Flag

Financial Institution Investor Flag

Relationships Incomplete Flag

Basic Data Incomplete Flag

Address Incomplete Flag

Advertising FlagSuspect Monitor Status Id (FK)

Suspect Monitor Status Date

Previous Refusal Flag

Unconsolidated Regulatory Capital Coverage Flag

Blacklist Flag

Fiscal Year Start DateFiscal Year End Date

Effective DateEnd Date

Description

IP

IP_ID: INTEGER

IP_TP_ID: SMALLINTPRIM_RLN_TP_ID: SMALLINTCST_LCS_TP_ID: SMALLINTCST_LCS_TP_EFF_DT: DATE

FNC_LGL_ST_ID: SMALLINTFNC_LGL_ST_EFF_DT: DATE

LGL_CMPNC_ST_ID: SMALLINTLGLCMPNC_ST_EFF_DT: DATE

IP_CR_RSK_RTG_ID: SMALLINTIP_CR_RSK_RTG_DT: DATE

IP_RSK_ESR_TP_ID: SMALLINTIP_RSK_ESR_EFF_DT: DATE

PDFT_CGY_ID: SMALLINTPDFT_CGY_EFF_DT: DATE

ORSK_APRC_TP_ID: SMALLINTOPSKAPRC_TP_EFF_DT: DATE

MKT_RSK_APRC_TP_ID: SMALLINTMKTRSKAPRCTP_EFFDT: DATE

CNTPR_CR_RISK_APRC_TP_ID: SMALLINTCNTPR_CR_RSK_APRC_EFF_DT: DATE

IP_SKL_TP_ID: SMALLINTNM: CHAR(64)

FI_F: SMALLINT

FI_IVSR_F: SMALLINT

RLTNP_INCOM_F: SMALLINT

BSC_DATA_INCOM_F: SMALLINT

ADR_INCOM_F: SMALLINT

ADVG_F: SMALLINT

SSP_MON_ST_ID: SMALLINTSSP_MON_ST_DT: DATE

PREV_RFS_F: SMALLINT

UCNSLD_RCPTL_CVR_F: SMALLINTBLIST_F: SMALLINT

FSC_YR_STRT_DT: DATE

FSC_YR_END_DT: DATE

EFF_DT: DATE

END_DT: DATE

DSC: VARCHAR(256)

I d t M d l A d A t L b


18/22



Logical / Physical Attributes


19/22



Subject Area

BDW Model

I d t M d l A d A t L b


20/22



Subject Areas


21/22



Erwin Walkthru

BDW Model



22/22



9 Major Subject Areas

01. Involved Party, Location

02. Product, Condition

03. Arrangement, Accounting Unit

04. Event

05. Resource Item

06. Classification Family

07. Associative Relationships

08. Summary And Profile Area

09. Analysis Area

04 bdw physical model

Documents

industry models

assets labjuly

ibm corporation9history

ibm corporation10history

ibm corporation8history

ibm corporation2what

ibm corporation3what

ibm corporation4what