Transcription
OFSAA Data Model NamingStandards GuideVersion 4.0
Table of ContentsTABLE OF CONTENTS1OVERVIEW . 31.1Introduction .31.1.1Table Standards . 31.1.2Table Naming Conventions . 31.1.3Column Standards . 41.1.4Column Naming Convention. 51.1.5Column Data Size . 51.1.6Column Information . 61.1.7Keywords . 71.1.8Domains. 81.1.9UDP . 111.1.10PKs and FKs . 191.1.11Change Management Process . 201.1.12Maintenance . 20Oracle Financial Services Software
OFSAA Data Model Naming Standards Guide1Overview1.1IntroductionOracle Financial Services Analytical Applications (OFSAA) leverages a common underlying datamodel. This document provides an overview of the naming standards followed in the developmentprocess of the OFSAA data model. It is recommended that these standards are followed in datamodel extensions done at customer sites.1.1.1Table Standards Entities within the OFSAA application subject area will be in Uppercase. Entities within the Staging subject area will be in Lowercase. Underscores separate distinct terms used to define the Table / Column as per the standardconventions. Table names to start with the application name. E.g. FSI , REV etc. All table names will be in plural. Table names will have indications of the functional area. Table prefix and suffix will be in accordance with the table type. Table names will have only A-Z, 0-9 and underscore ( ) characters. Table names can contain multiple underscoresMore details can be inferred from Table Naming Conventions.1.1.2Table Naming ConventionsFollowing chart gives the prefix and suffix that a particular category of tables should contain.1.1.2.11.1.2.2Staging and ReportingClass of tablePrefixDimensionsDIMStageSTGReporting TableFCTSuffixApplication ProcessingOracle Financial Services Software3Contains
OFSAA Data Model Naming Standards GuideClass of tablePrefixSuffixCodesFSICDInstrument/DataFSI DOFSA legacyOFSAOutput factsFSI orFSILOG/ ERRORSAudit TrailFSIAUDITTranslated tablesFSIOtherFSITemporary tablesFSIViewFSIVTemplate TablesFSI DTEMPLATEInterface Tables1.1.3ContainsB/ TL/ CD/ MLSTEMPINTFColumn Standards1.1.3.1Generic Column names will have only A-Z, 0-9 and underscore ( ) characters. Column names can contain multiple underscores. Column name should not be too generic. Avoid term, multiplier, description, name, codeetc. Column names should not be cryptic and should be intuitive.Ideally should use longnames instead of short meaningless abbreviations.1.1.3.2Staging and Reporting Columns related to staging entities will be in Lower case. Column names should have type of the column as part of prefix or suffix. Column names of staging entities will have prefix. v for varchar, n for numeric, d fordate, f for flag columns.Oracle Financial Services Software4
OFSAA Data Model Naming Standards Guide1.1.3.3Application Processing Columns related to OFSAA application entities will be in Upper case. Column names in OFSAA application entities will have suffix. Example, CD for code typecolumns, FLAG for flag type columns, BAL for balance type columns etc. 1.1.4Column class information is suffixed to the column.Column Naming ConventionThe following table provides the suffixes / prefixes that need to be part of the column names,depending on the type of column:Class of Column1.1.5PrefixSuffixSystem Generated IdSYS ID/ IDCodeCDNameNAMEDescriptionDESCFlagFLAG/ sColumn Data SizeA column that is created in the entity can fall into various categories, and should fit into one of the belowfield type. A category may have one or more data sizes.Oracle Financial Services Software5
OFSAA Data Model Naming Standards GuideField TypeData TypeSize 1MultiplierCHAR1TermNUMBER5numberNUMBER14AMOUNT fieldsNUMBER22,3RATE R2Size 2Size lumn InformationTypical Information captured for each column is as below Column Name Erwin Domain – represents standard logical data types and maps to physical data types. Column Data Type and Length Primary Key Indicator Foreign Key Indicator Not NULL Indicator Comment or Description of the Column User Defined Properties (UDP), if anyOracle Financial Services Software6
OFSAA Data Model Naming Standards Guide1.1.7KeywordsThe below table provides the indicative list of abbreviations and names of the keywords that canbe used while naming tables and erageAVGOver ODDetailDTLSourceSRCEarnings at erationGENValue at RiskVARGeneral LedgerGLValueVALHierarchyHIERFlagFLGHistoryHISTEnd Of PeriodEOPOracle Financial Services Software7FormOfLOB
OFSAA Data Model Naming Standards Guide1.1.8DomainsDomains are Logical data types that are attached to each column within the Erwin model.Descriptions of domains used in OFSAA are given below:DomaincodeDomain NameDomain Desc1BALANCEMonetary Balance2CHARFixed-length alpha-numeric data3CODEDefined Alpha or Numeric Code Value4CODE NUMUndefined Numeric Code Value5DATEDate value6DESCRIPTIONDescription7LONG RAWVariable length binary data8FLAGTrue/False value9FREQRecurring period of time10LEAFLeaf column11IDENTITYReserved12ID NUMBERReserved13MULTMultiplier14N/ANot applicable15NUMBERNumeric Value16NUMERICNumeric Value17RATEInterest rate18SWITCHSwitch Value19SYS ID NUMIdentifies application IDs20TERMNon-recurring period of time21VARCHAR2Variable-length alpha-numeric value22UNDEFINEDColumn data type not defined23CHAR RANGECharacter RangeOracle Financial Services Software8
OFSAA Data Model Naming Standards Guide99101DEFAULTDefault Data typeLONG RATELong RateList of domains modeled within Erwin are given below along with their equivalent physical datatypes:ClassSub Class [i.e. DomainName]Data TypeSurrogateKeySurrogateKey ShortNUMBER3SurrogateKey MediumNUMBER5SurrogateKey LongNUMBER10System identifierNUMBER10System identifier Dimension memberNUMBER14System IdentifierLongNUMBER25SYS ID NUMNUMBER10LEAFNUMBER10ID NUMBERNUMBER10SurrogateKey Long Type2NUMBER15System Identifier MediumNUMBER15Code Numeric ShortNUMBER5Code Alphanumeric ShortVARCHAR25Code CurrencyVARCHAR23Code Numeric MediumNUMBER10Code Alphanumeric MediumVARCHAR210Code Numeric LongNUMBER20Code Alphanumeric LongVARCHAR220CODENUMBER5NUM CODENUMBER5Code AlphaNumericVARCHAR225Code Alphanumeric Long Type2VARCHAR240Code Alphanumeric Long Type3VARCHAR250Code Alphanumeric Medium Type2VARCHAR215Code Alphanumeric Short Type2VARCHAR26Code LongVARCHAR260CODE NUMNUMBER5Code ShortVARCHAR23DatetimeDATECodeDatetimeOracle Financial Services Software9SizePrecision
OFSAA Data Model Naming Standards GuideClassFlagSub Class [i.e. DomainName]Data TypeSizePrecisionDATEDATETimestampTIMESTAMPFlag IdentifierNUMBER2Flag BooleanNUMBER1Flag CharacterCHAR1FLAGNUMBER1Flag IdentifierNUMBER2IndicatorVARCHAR21Text CommentsVARCHAR2500Text ExpressionVARCHAR24000Text MessageVARCHAR22000Text Short DescriptionVARCHAR230Text Long ption 20Text Comments Type2VARCHAR21000Text Long Description Type2VARCHAR2120Text Medium DescriptionVARCHAR260Text Medium Description Type2VARCHAR240Number ShortNUMBER5Number MediumNUMBER10Number LongNUMBER20Number DaysNUMBER103Number TermNUMBER84Number AmountNUMBER226Number ValueNUMBER223Number RateNUMBER103Number PercentageNUMBER105Number Currency RateNUMBER104Number BER10MULT/CHARTextNumberOracle Financial Services Software10
OFSAA Data Model Naming Standards GuideClass1.1.9Sub Class [i.e. DomainName]Data MBER142PCTNUMBER84Long RateNUMBER159Amount Long Type2NUMBER226Amount LongNUMBER3011AmountNUMBER223Amount MediumNUMBER256CapitalNUMBER226Capital LongNUMBER383Capital Long Type2NUMBER386Capital Long DGER BALANCENUMBER154LONG RATENUMBER159Number GenericNUMBER(22)22PercentNUMBER(8,4)84Percent LongNUMBER(15,11)1511Phone Fax NumberNUMBER(15)15Probability 5)5Short RateNUMBER(8,4)84UDPUDP: User defined properties is a key value pair which can be assigned at both Table level andColumn level. The values to the below mentioned UDP to be always be 0/1, Yes/No, Y/NIt is recommended that customers extending the OFSAA data model start a new UDP field totrack whether a new table or column added is “Customer site-specific” or not. This will ensure thatthere is a clean way of identifying site-specific changes. Please note the following aspects withrespect to this new UDP.1. Please ensure that the name of the UDP represents the customer name.Oracle Financial Services Software11
OFSAA Data Model Naming Standards Guide2. Please use the default value for this UDP as N/No/0 to ensure you have to edit only theones that are newly added/ modified and the rest of the model has the default standardvalue.3. Please use this UDP while generating Erwin reports for identifying site-specificchanges.Table UDP NameTable UDP DefinitionALM StandardALM StandardALMALM Table UDPAsset Back SecurityAsset Back SecurityBASEL III - Credit Risk - Non Securitization STDTable UDP for Basel Credit Risk Non SecuritizationBASEL III - Market RiskTable UDP for Basel III Market RiskBASEL III USA STDTable UDP for Basel USA STDBasel III - EquityTable UDP for Basel III EquityBreak FundingBreak Funding instrument for TPCI - CustomerCustomer AnalyticsCI - InstitutionalInstitutional AnalyticsCI - RetailRetail AnalyticsCRACRA UDPCapfloorProperty to identify if the table is classified as 'Capfloor' for thepurpose of executing ALM Cash flow engine to generate cashflowsCodes Reserved (base tbl)Base table for Reserved code valuesCodes User Defined (base tbl)Base table for User Defined Code columnsCustomCustom UDPCustomization RemarksTable UDP for Cutomization RemarksData Correction ProcessingData Correction ProcessingData Quality-Correction RulesData Quality-Correction RulesDerivative InstrumentsClassification to identify derivative instrumentsEFPAUDP to identify the tables used in PFTBIESTCPEnterprise Stress testing and Capital PlanningFAHFinancial Accounting HubFinancial Accounting HubFTP Break DetectionFTP Break DetectionFTP CommitmentsFTP CommitmentsFX ContractsProperty to identify if the table is classified as 'FX Contracts' forthe purpose of executing ALM Cash flow engine to generate cashOracle Financial Services Software12
OFSAA Data Model Naming Standards GuideTable UDP NameTable UDP DefinitionflowsForward Rate AgreementsForward Rate AgreementsFutures ContractProperty to identify if the table is classified as 'Futures Cash Flow'for the purpose of generating cash flow.GLRECONGL Reconciliation FrameworkInstrument ProfitabilityInstrument ProfitabilityInstrumentSuper-type for all Instrument tablesLRM IntradayUDP created for LRM Intraday RequirementsLRM RBI CustomUDP created for RBI requirement.LRMTable UDP for LRMLedger ClassManagement or account Ledger ClassLedger StatLedger StatLoan Commitments PropagationLoan Commitments PropagationMLS Descriptions ReservedMLS Description table for Reserved Code valuesMLS Descriptions User DefinedMLS Description table for User Defined Code columnsMantas- FATCAThis table UDP indicates if the table is being used by FATCAApplication.MortgagesMortgagesOIPAUDP Indicating table usage for Oracle Insight InsuranceUDP Indicating table usage for Oracle Insight InsuranceORAThis UDP is to identify if the table is used by ORA.OREC-AnalyticsOREC AnalyticsORECThis UDP will be used for the creation of tables required for ORECORIndicates if table is used by Operation Risk ApplicationIndicates if table is used by Operation Risk ApplicationOracle BD AMLOracle BD Money LaunderingOracle BD AMLTFOracle Behavior Detection Anti-Money Laundering Trade FinanceOracle BD BCOracle BD Brokerage ComplianceOracle BD CTROracle Behavior Detection Currency Transaction ReportingOracle BD FROracle BD FraudOracle BD TCOracle BD Trade ComplianceOracle Insurance Solvency II AnalyticsTables used in solvency II AnalyticsOther Table ClassSuper-type for all Other Class TablesOracle Financial Services Software13
OFSAA Data Model Naming Standards GuideTable UDP NameTable UDP DefinitionPA Lookup TablesLookup tables used in PA Lookup Table IDPortfolioPortfolioProfitability - Other ClassClassification to identify Other Class Tables that will be used forprofitability.QMRTable Used in Solvency II QRT ReportsRM StandardRM StandardRRDFRegulatory Reporting SolutionRRS RBIRegulatory Reporting Solution - RBIRelationship PricingPrice Creation and DiscoveryReporting - Basel IIUsed in Basel II reportingReporting - Basel IIIUsed in Basel III reportingReputational RiskWhether used in OBI reports of Reputational RiskSolvency IITables used in solvency II AnalyticsSourceThis UDP contains the source system that provides data for thistableStaging - ALM AnalyticsTable UDP used for Staging - ALM AnalyticsStaging - ALMTable UDP used for Staging - ALMStaging - BSPTable UDP used for Staging - BSPStaging - Basel II AnalyticsTable UDP used for Staging - Basel II AnalyticsStaging - Basel IITable UDP used for Staging - Basel IIStaging - Basel III MainTable UDP used for Staging - Basel III MainStaging - Basel IIITable UDP used for Staging - Basel IIIStaging - CATable UDP used for Staging - CAStaging - CRATable UDP used for Staging - CRAStaging - ECTable UDP used for Staging - ECStaging - FTPTable UDP used for Staging - FTPStaging - GL RECONTable UDP used for Staging - GL RECONStaging - HMTable UDP used for Staging - HMStaging - ICAAPTable UDP used for Staging - ICAAPStaging - LLFPTable UDP used for Staging - LLFPStaging - LRMTable UDP used for Staging - LRMStaging - Market Risk AnalyticsTable UDP used for Staging - Market Risk AnalyticsStaging - Market RiskTable UDP used for Staging - Market RiskStaging - OIPATable UDP used for Staging - OIPAStaging - ORECTable UDP used for Staging - ORECOracle Financial Services Software14
OFSAA Data Model Naming Standards GuideTable UDP NameTable UDP DefinitionStaging - PFT AnalyticsTable UDP used for Staging - PFT AnalyticsStaging - PFTTable UDP used for Staging - PFTStaging - PM - Capital ChargeTable UDP used for Staging - PM - Capital ChargeStaging - PM - Transfer PricingTable UDP used for Staging - PM - Transfer PricingStaging - Retail PoolingTable UDP used for Staging - Retail PoolingStagingThis property holds whether the entity is staging or not.Strategic RiskWhether used in OBI reports of Strategic RiskSwapsProperty to identify if the table is classified as 'Swaps' for thepurpose of executing ALM Cash flow engine to generate cashflowsTP Cash FlowTP Cash FlowTP Non-Cash FlowTP Non-Cash FlowTP Option CostingTransfer Pricing Option Costing processingTransaction ProfitabilityTransaction ProfitabilityUser DefinedTable Classification with no requirementsColumn UDP NameColumn UDP DefinitionALMField applicable for ALM ApplicationALMBIThis UDP is to identify if the column is used by ALMBI.BASEL IUDP for BASEL IBASEL III BIS CAP STRUCTUDP for BASEL III BIS CAP STRUCTBASEL III BIS LEVERAGE RATIOUDP for BASEL III BIS LEVERAGE RATIOBASEL III BIS NON SEC AIRBUDP for BASEL III BIS NON SEC AIRBBASEL III BIS NON SEC FIRBUDP for BASEL III BIS NON SEC FIRBBASEL III BIS NON SEC STDUDP for BASEL III BIS NON SEC STDBASEL III BIS REPORTINGUDP for BASEL III BIS REPORTINGBASEL III BIS SEC IRBUDP for BASEL III BIS SEC IRBBASEL III BIS SEC SFAUDP for BASEL III BIS SEC SFABASEL III BIS SEC STDUDP for BASEL III BIS SEC STDBASEL III BRAZIL NON SEC STDUDP for BASEL III BRAZIL NON SEC STDBASEL III CBRC IRBUDP for Basel III CBRC for IRB ApproachBASEL III CBRC STDUDP for Basel III CBRC for STD ApproachBASEL III IFSB NON SECUDP for BASEL III IFSB NON SECBASEL III INDIA MR STDUDP for BASEL III INDIA MR STDBASEL III USA CAP STRUCTUDP for BASEL III USA CAP STRUCTOracle Financial Services Software15
OFSAA Data Model Naming Standards GuideColumn UDP NameColumn UDP DefinitionBASEL III USA LEVERAGE RATIOUDP for BASEL III USA LEVERAGE RATIOBASEL III USA NON SECUDP for BASEL III USA NON SECBASEL III USA SCP EXP LMTUDP for BASEL III USA SCP EXP LMTBASEL III USA SECUDP for BASEL III USA SECBASEL III USA SEC IRBUDP for BASEL III USA SEC IRBBASEL III USA STDUDP for BASEL III USA STDBASEL II BIS CAP STRUCTUDP for BASEL II BIS CAP STRUCTBASEL II BIS MR IMMUDP for BASEL II BIS MR IMMBASEL II BIS MR STDUDP for BASEL II BIS MR STDBASEL II BIS NON SEC AIRBUDP for BASEL II BIS NON SEC AIRBBASEL II BIS NON SEC FIRBUDP for BASEL II BIS NON SEC FIRBBASEL II BIS NON SEC STDUDP for BASEL II BIS NON SEC STDBASEL II BIS OR ALT STDUDP for BASEL II BIS OR ALT STDBASEL II BIS OR BIAUDP for BASEL II BIS OR BIABASEL II BIS OR STDUDP for BASEL II BIS OR STDBASEL II BIS REPORTINGUDP for BASEL II BIS REPORTINGBASEL II BIS SEC IRBUDP for BASEL II BIS SEC IRBBASEL II BIS SEC RBAUDP for BASEL II BIS SEC RBABASEL II BIS SEC STDUDP for BASEL II BIS SEC STDBASEL II BRAZIL CAP STRUCTUDP for BASEL II BRAZIL CAP STRUCTBASEL II BRAZIL MR STDUDP for BASEL II BRAZIL MR STDBASEL II BRAZIL NON SEC STDUDP for BASEL II BRAZIL NON SEC STDBASEL II BRAZIL OR ALT STDUDP for BASEL II BRAZIL OR ALT STDBASEL II BRAZIL OR BIAUDP for BASEL II BRAZIL OR BIABASEL II BRAZIL OR SIMPLE ALT STDUDP for BASEL II BRAZIL OR SIMPLE ALT STDBASEL II CBRC CAPITALUDP for BASEL II CBRC CAPITALBASEL II CBRC MRUDP for BASEL II CB
OFSAA Data Model Naming Standards Guide Oracle Financial Services Software 3 1 Overview 1.1 Introduction Oracle Financial Services Analytical Applications (OFSAA) leverages a common underlying data model. This document provides an overview of the naming standards followed in the development process of the OFSAA data model.File Size: 566KB
