2.3 Dimension Data Flow
Dimension Data in the OFS IPA Application is loaded from Staging Master Tables using the Slowly Changing Dimensions (SCD) Process. Data from Source Systems can be loaded into staging through flat file or Source System Interfaces. SCD process tracks the changes in the Dimensional Attributes and loads data into Dimension Tables. Examples of Dimension Tables that follow the SCD process are Product, Customer Type, Customer, and so on.
Some dimensions are static or maintained internally within the application and are not expected as a download from the source system. Examples of such dimensions are the Reporting Line. These dimensions are maintained through the AMHM (Attribute Member Hierarchy Maintenance) component of OFSAAI or other framework components like DEFI.
Table 2-1 OFS IPA Dimension
| Dimension Entity Name | Staging Entity Name (or names) | Loading or Maintenance Method |
|---|---|---|
| Account Status Dimension |
Stage Account Status Master |
SCD |
| Application Reject Reasons Dimension | Stage Application Reject Reason Master | SCD |
| Application Type Dimension | Stage Application Type Master | SCD |
| Attrition Dimension | Stage Attrition Reason Master | SCD |
| Account Management Dimension | Stage Account Mgmt Master | SCD |
| Country Dimension | Stage Country Master | SCD |
| Credit Center Dimension | Stage Credit Center Master | SCD |
| Credit Officer Dimension | Stage Credit Officer Master | SCD |
| Customer Dimension | Stage Customer Master | DT |
| Customer Type Dimension | Stage Customer Type Master | SCD |
| Decision Status Dimension | Stage Decision Status Master | SCD |
| Deviation Reasons Dimension | Stage Deviation Reason Master | SCD |
| Education Dimension | Stage Customer Education Master | SCD |
| Geography Dimension | Stage Geography Master | SCD |
| Industry Dimension | Stage Industry Master | SCD |
| Management Dimension | Stage Account Mgmt Master | SCD |
| Migration Reasons Dimension | Stage Migration Reason Master | SCD |
| Offer Dimension | Stage Offer Master | SCD |
| Opportunity Dimension | Stage Opportunity | SCD |
| Opportunity Activity Type Dimension | Stage Activity Type Master | SCD |
| Organization Structure Dimension | Stage Organization Structure Dimension | SCD |
| Partner Dimension | Stage Partner Master | SCD |
| Product Dimension | Stage Product Master | SCD |
| Product Feature Dimension | Stage Product Feature Master | SCD |
| Product Type Dimension | Stage Product Type Master | SCD |
| Prospect Dimension | Stage Prospect Master | SCD |
| Reason Dimension | Stage Opportunity Win-Loss Reason Master | SCD |
| Retention Offer Type Dimension | Stage Retention Offer Master | SCD |
| Sales Representative Dimension | Stage Sales Rep Master | SCD |
| Vendor Dimension | Stage Vendor Master | SCD |
| Vintage Dimension | Stage Vintage Master | SCD |
| Line of Business Dimension | Stage Line of Business Master | SCD |
| Common Chart Of Accounts Dimension |
Common COA Dimension Members, Common COA Hierarchies, Common COA Member Attributes, Common COA Member Translations |
SCD |
| General Ledger Account Dimension |
General Ledger Member Attributes, General Ledger Dimension Members, General Ledger Hierarchies, General Ledger Member Translations |
SCD |
| DIM_ORG_UNIT |
Organization Unit Member Attributes, Organization Unit Dimension Members, Organization Unit Hierarchies, Organization Unit Member Translations |
SCD |
| Product Dimension |
Product Member Attributes, Product Dimension Members, Product Hierarchies, Product Member Translations |
SCD |
| Reporting Line Dimension |
Reporting Line Dimension Members, Reporting Line Member Translation, Reporting Line Member Attributes, Reporting Line Hierarchies |
AMHM/DT |
| Region Dimension | Direct Load | |
| Acquisition Channel Dimension | Stage Sales Channel Master | SCD |
| Instrument Category Dimension | Seeded | |
| Currency Dimension | Seeded | |
| Consolidation Dimension | Seeded | |
| Calendar Dimension | DT | |
| Account Dimension | Stage LC Contracts | SCD |
|
Stage Commitment Contracts |
SCD | |
| Party Dimension | Stage Party | SCD |
| Location Dimension | Stage Location Master | SCD |
| Band Dimension |
Band Dimension Members, Band Member Translation, Band Member Attributes |
AMHM/SCD NOTE: When updating DIM_BANDS, the lower bound of one band cannot start with the upper bound of the previous band. For example, for a Customer Balance band, if the upper bound of the first band is 10,000 USD, the lower bound of the next band must start with 10,000.01 USD, if the data load convention followed is for two decimal points. For integer bands, for example, Number of Transactions; if the upper bound of a band ends with 5, the lower bound of the next band must begin with 6. |
| Account Dimension | Stage OD accounts | SCD |
| Stage TD contracts | SCD | |
| Stage Trusts | SCD | |
| Stage Loan Contracts | SCD | |
| Stage Mutual Funds | SCD | |
| Stage Bills Contracts | SCD | |
| Stage CASA Accounts | SCD | |
| Stage Guarantees | SCD | |
| Stage Leases Contracts | SCD | |
| Stage MM Contracts | SCD | |
| Stage Annuity Contracts | SCD | |
|
Stage Borrowings, Stage Card Accounts |
SCD | |
| Stage Investments | SCD |
Some of the Stage Data can also come from Master Data Management Interfaces. In such a case, data from the interface is loaded into staging interface tables and SCD is run on the interface tables. Mapping of dimensional attributes to staging can be obtained by querying SYS_STG_JOIN_MASTER and SYS_TBL_MASTER table in the Atomic Schema.