Education module: Scoping our first data model

You are a Data Architect or Modeler who downloaded the Financial Industry Business Data Model or Your organization has licensed FIB-DM, and you want to understand the design and contents.

This education module is a hands-on exercise. You can follow the steps in PowerDesigner or your modeling tool.

  • Example: FDIC Bank Call Report reference data
  • We use five of the FIB Fundamental Supertypes to create a concept map.
  • Transpose the concept map to a Data Model subject area
  • Review the conceptual data model and compare to the ontology graph

For full screen click maximize at the bottom of the online viewer.

Then start slide show in MS-PowerPoint online.

Download the tutorial PDF or PowerPoint

Text of the presentation

Education module – Scoping our first data model from FIB-DM

  1. Education Module Financial Industry Business Data Model (FIB-DM) An introduction course into the ontology-derived Enterprise Data Model. Jurgen Ziemer Ontologist & Data Architect at Jayzed Data Models Inc. Semantics for Data Architects Scoping our first data model from FIB-DM
  2. Data Architect Audience and content  a) You are a Data Architect or Modeler who downloaded the Financial Industry Business Data Model b) Your organization has licensed FIB-DM and you want to understand the design and contents. • Example: FDIC Bank Call Report reference data • We use five of the FIB Fundamental Supertypes to create a concept map. • Transpose the concept map to a Data Model subject area • Review the conceptual data model and compare to the ontology graph This education module is a hands-on exercise. You can follow the steps in PowerDesigner or your modeling tool. Data Architect
  3. US Bank Call Report. The Federal Deposit Insurance Corporation is a regulator of US Banks. One of the key reports required to be filed is the quarterly Consolidated Report of Condition and Income, generally referred to as the call report or RC report. The Federal Financial Institutions Examination Council (FFIEC) publishes the XBRL taxonomy and bank filings. You can view and download Call Report submissions on the FFIEC website: https://cdr.ffiec.gov/public/
  4. Data Architect Call Report Schedule ENT – reference data  The Entity Schedule, “ENT” has reference data about the reporting Bank. For this exercise, our data requirements are the Entity schedule data items, plus additional reference data about the FDIC, already in the FIBO. The example below shows JPMorgan Chase Bank, NA submission with call date, id, data item, value, definition, call schedule, and line number.
  5. Data Architect A simple diagram of our sample data  FDIC Certificate 628 identifies JPM Chase as a registered bank. The Certificate is registered in the FDIC Institution Directory. The FDIC registers the Certificate, and thus JPMC. JPMC has the identity of JPMC NA, the legal entity. Note the distinction of what JPMC is – a legal entity, and what JPMC does – taking deposits. The Legal Entity has a registered address with the US as the country. JPMC NA has a Legal Entity Identifier (LEI). The corporation has $176 billion issued capital.
  6. Data Architect 15 Fundamental Business Concepts  In our second step we conceptualize our sample data items. That means to assign fundamental business concepts and their specializations. The fifteen concepts most are extensive ultimate supertypes in the Financial Industry Business Data Model. In other words, they have the most subtypes and most relationships. In the FIBO ontology, the fundamental fifteen concepts are direct subclasses of the Thing. We use abbreviations and icons as mnemonics to teach the concepts to modelers and business users.
  7. Data Architect Identifying Entities and Concepts  We can use the modelling tool or the Supertype spreadsheet to research Concept Entities. For example to find an Entity for JPMC, the legal entity, we filter by the Fundamental Concept Autonomous Agent and find the Stoch Corporation.
  8. Data Architect Creating the concept map diagram  FIB-DM has an Entity called Legal Entity Identifier, an exact match for the LEI in the sample. Reference (REF) is the ultimate supertype of Legal Entity Identifier. Likewise, we have an Entity named Country in FIB-DM. The country is a specialization of the fundamental concept of Location(LOC). The FDIC Certificate Number is another direct match. The FDIC Institution Directory is a business Registry. The entity Registry belongs to the Arrangement (ARR) concept. The LEI identifies a Legal Entity. In our example, JPMC NA, a Stock Corporation, an entity in the FIB-DM. The stock corporation is a subtype of the Autonomous Agent (AA). The concept comprises of Person, Legal Person, Automated System, and Organization. The FDIC insures and regulates financial institutions that take deposits. Depository Institution is an entity in the data model, rolling up to the fundamental concept of the Thing in Role (TIR). The TIR is what the Autonomous does. The FDIC is the Registration Authority who issues the certificate number. The FDIC also has an identity as an Autonomous Agent, but we don’t need that detail for our scope. JPMC’s capital is a Monetary Amount. The entity holds the number and more descriptive properties, such as the currency and date. Following the supertype hierarchy we see that Monetary Amount is a Reference. The address fields in our schedule are a Registered Address, another subtype of Reference. The Reference functions like an associative entity with a role, linking Country to the Autonomous Agent.
  9. Data Architect Our sample data as a concept map  We replace the circles in our sample data diagram with shapes of the Fundamental Business Concepts. A concept map or conceptual diagram is a diagram that depicts suggested relationships between concepts.
  10. Data Architect Standardize concept map relations  In a similar way, we standardize relationships in our concept map. The data modeler suggests FIB- DM associations names that match the related concepts and requirements. Finally, we replace the sample values with identified FIB-DM entities. Here is our Fundamental Business Concept map for the Call Report Entity Schedule.
  11. Data Architect The FIB-DM Concept map  • Easy to understand for non- technical business users. • The methodology keeps the concept-mapping within the vocabulary and structure of the underlying model. We can see how the diagram expands. • The Monetary Amount has more defining concepts; • JPMC is a Thing in many more Roles, so is the FDIC; • we can add other Regulators to the picture, and so on.
  12. From Concept Map to Data Model • Populate a new diagram with the identified entities 1 • Add supertypes all the way up to the Fundamental Concept 2 • Add Associations and Associative entities 3 • Generate an LDM or export our FIB- DM subject area model 4
  13. Create a new diagram of the nine entities  Legal Entity Identifier Stock Corporation Monetary Amount Registered Address Registry Registration Authority Country (lcc) Depository Institution FDIC Certificate Number Open FIB-DM in your data modeling tool. The screenshots and diagrams in this education module are PowerDesigner. Populate your diagram with the nine entities of the concept map. These are existing FIB-DM entities derived from FIBO classes. In practice, you may draw the concept map and CDM in parallel. However you want to use the concept map to validate the design with the business.
  14. 2.1 Adding Depository Institution supertypes to the diagram  2.1 Let’s start with the Depository Institution. We use the data modeling tool to navigate to the Inheritance (a.k.a. subtype symbol) and the supertype. 2.2 In PowerDesigner this is a bit cumbersome: We pull up the entity properties dependencies tab; select parents to find Financial Institution subtypes; pull up the inheritance properties; find in browser; and copy the inheritance as link into the diagram. Service Provider subtype Thing In Role subtype Financial Service Provider subtype Financial Institution subtype Depository Institution Financial Institution Financial Service Provider Thing In Role Service Provider 2.3 We repeat the process until we arrive at the ultimate supertype, our Fundamental Concept, the Thing in Role. The Depository Institution is a Financial Institution, which is a Financial Service Provider, with is a Service Provider, a direct subtype of the Thing. Depository Institution Financial Institution subtype Depository Institution Financial Institution Data Architect 2.3 Adding the Registration Authority supertype  Service Provider subtype Thing In Role subtype Financial Service Provider subtype Financial Institution subtype Depository Institution Registration Authority Financial Institution Financial Service Provider Thing In Role Service Provider The Registration Authority, our entity for the FDIC and other registrars is a Service Provider, subtype of the Thing in Role (TIR).
  15. 2.4 Stock Corporation rolls up to the Autonomous Agent  Legal Person subtype Autonomous Agent subtype Legal Entity subtype Corporation subtype Stock Corporation Corporation Legal Entity Autonomous Agent Legal Person The Stock Corporation, our entity for JPMC’s Legal Entity, is a Legal Person, direct subtype of the Autonomous Agent (AA).
  16. 2.5 Country and Registry  Arrangement subtype Collection subtypeGeopolitical Entity (lcc) subtype Location (lcc) subtype Registry Location (lcc) Geopolitical Entity (lcc) <<Equivalent Entity>> Collection <<Equivalent Entity>> Arrangement Country (lcc) The country is a Geopolitical Entity, subtype of the Fundamental Concept of Location (LOC). The Registry is a collection, subtype of Arrangement (ARR).
  17. 2.6 Reference subtypes  Registration Identifier subtype Measure subtype Monetary Measure subtype Identifier subtype Organization Identifier subtype Reference subtype Index subtype Address subtype Physical Address subtype Legal Entity IdentifierMonetary Amount Registered Address Address Physical Address FDIC Certificate Number Index Reference Organization Identifier Identifier Monetary Measure Measure Registration Identifier Reference (REF) is the largest Fundamental Concept in FIB-DM and the FIBO. We see three major branches in the hierarchy. 1. The Monetary Amount is a Measure. 2. The Registered Address is an Address, which in turn is an index. 3. Legal Entity Identifier and the FDIC Certificate Number are both Organization Identifiers
  18. Our nine Entities and their five Fundamental Concepts.  Registration Identifier subtype Measure subtype Monetary Measure subtype Identifier subtype Organization Identifier subtype Arrangement subtype Collection subtype Service Provider subtype Reference subtype Index subtypeLegal Person subtype Geopolitical Entity (lcc) subtype Autonomous Agent subtype Location (lcc) subtype Address subtype Thing In Role subtype Financial Service Provider subtype Financial Institution subtype Physical Address subtype Legal Entity subtype Corporation subtype Legal Entity IdentifierStock Corporation Corporation Legal Entity Monetary Amount Registered Address Address Physical Address Depository Institution FDIC Certificate NumberRegistryRegistration Authority Financial Institution Financial Service Provider Thing In Role Location (lcc)Autonomous Agent Geopolitical Entity (lcc) Legal Person Index Reference Service Provider <<Equivalent Entity>> Collection <<Equivalent Entity>> Arrangement Organization Identifier Identifier Monetary Measure Measure Registration Identifier Country (lcc)
  19. 3.1 Adding Associations and Associative Entities.  Count of Relationships Business Concept Association Ultimate Supertype AA AC AGR ARR CE COM DOC LC LOC OCC PRD REF SVC TI TIR Grand Total fibo-fnd-rel-rel:has The next step is to connect the entities adding the Association or Associative entity appropriate for the business context. The Pivot table shows the 15 concepts and the number of participating relationships.
  20. 3.2 Finding the right association  has Issued Capital Monetary AmountStock Corporation has Issued Capital Stock Corporation Monetary Amount <<Associative Entity>> has Issued Capital The preferred association connects our scoped concept hierarchies at the leaf-level. In other words the green entities of the concept map. We use the modeling tool or the Relationships spreadsheet to investigate relationships of the Stock Corporation and find has Issued Capital. A perfect match, the Associative entity has Issued Capital directly relates to the Monetary Amount.
  21. 3.2 Associating the FDIC Certificate Number  is Registered By Registration Authority is Registered In Registry 0,n FDIC Certificate Number is Registered In 0,n FDIC Certificate Number is Registered By FDIC Certificate Number <<Associative Entity>> is Registered By Registry is Registered In Registration Authority Another easy match, the associative entity is Registered By has relationships to FDIC Certificate Number and the Registration Authority. The Association is Registered in directly connects the FDIC Certificate Number to the Registry.
  22. 3.3 Registered Address  has Address Physical Address Physical Address subtype has Address subtype Organization has Address Registered Address <<Associative Entity>> has Registered Address <<Associative Entity>> has Address Physical Address Organization The Registered Address has no matching relationship. So we investigate the supertype Physical Address and find has Address. The associative entity has a subtype has Registered Address. In other words, the FIBO/FIB-DM relates the entities at the supertype level. The associative entity has Address relates to the Organization. We pull in the Organization entity. Note: Organization and Legal Entity are both subtype of Autonomous Agent. An Organization may incorporate several Legal Entities to facilitate its business. The association identifies the legal entities of an organization.
  23. 3.4 Registered Address to Country  Physical Address has Country has Country Country Physical Address subtype Registered Address Physical Address <<Associative Entity>> has Country Country (lcc) The Associative Entity has Country does not have a relationship to Address. Under the ontology Open World Assumption anything can have a Country. For the Closed World relational model, we create the relationship from has Country to Physical Address.
  24. 3.5 The identifies association completes the model  FDIC Certificate Number identifies 0,n Depository Institution is Identified By 0,n Organization is Identified By 0,n Legal Entity is Identified By 0,n Legal Entity Identifier identifies 1,1 Legal Entity Identifier identifies Legal Entity Organization Depository InstitutionFDIC Certificate Number More than 350 base entities in FIB-DM have an association link to identifies. The association is a major design pattern relating the Fundamental Concepts. • The FDIC Certificate Number identifies the Depository Institution. • The Legal Entity Identifier identifies the Legal Entity. • The Organization is identified by a Legal Entity.
  25. 3.6ThecompleteCallReport conceptualdatamodel  
  26. Discussion – do I really need all these entities?  The short answer is no. For a project model it is perfectly fine to scope only the directly required green entities. In practice the following Logical Modeling phase attributes the model. The Logical Data Modeler looks at the full set of required data items and places them as attributes of the entities. A simple rule: You can remove any entity in the subtype hierarchy that does not have attributes or relationships. The FIB-DM is a reference data model. For the data modeler, the Financial Industry Business Data Model provides a rich library of building blocks to accelerate the design process.
  27. Sample Project Logical Data Model  Legal Entity Identifier identifies Stock Corporation has Issued Capital(D) has Issued Capital Monetary Amount Depository Institution is Identified By FDIC Certificate Number identifies FDIC Certificate Number is Registered By FDIC Certificate Number is Registered In is Registered In Registry is Registered By Registration Authority has Country Country Stock Corporation is Identified By Stock Corporation has Registered Address Registered Address has Registered Address Registered Address has Countryidentifies Stock Corporation Legal Name Cor_Legal Name Leg_Legal Name Transliterated Name Name Leg_Legal Name2 Leg_Transliterated Name Leg_Name Leg_Leg_Name For_Name Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Monetary Amount Measurement Date Measurement Period In Months Amount Date Integer Decimal <<AssociativeEntity>> has Issued Capital Registered Address Address Line1 Address Line2 Address Line3 Address Line4 Postal Code Reg_Address Line1 Reg_Address Line2 Reg_Address Line3 Reg_Address Line4 Reg_Postal Code Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters <<AssociativeEntity>> has Registered Address Depository Institution FDIC Certificate Number Tag Binary <<AssociativeEntity>> is Registered By Registry Registry Name Variable characters is Registered In Registration Authority <<AssociativeEntity>> has Country Country (lcc) Remarks Geo_Independent Geo_Remarks Geo_Independent2 Numeric Country Code French Full Name English Short Name English Short Name In Capitals French Short Name French Short Name In Capitals English Full Name Independent Variable characters Boolean Variable characters Boolean Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Variable characters Boolean Legal Entity Identifier Tag Binary
  28. LDM denomalization and simplifications  In the sample diagram the Logical Data Modeler scoped only the nine subtype entities identified in the concept map. The relationships from associative entities have been moved down to the subtype entity. E.g. the logical data modeler created a direct relationship from Stock Corporation to has Registered Address. Next steps: The modeler will review the associative entities and those that are not many-to-many with direct relationships. For example, the Registered Address has only one country; we can create a direct has Country relationship from Country to Registered Address. The multiple associative entity identifies can be resolved into direct relationships between Legal Entity Identifier and Stock Corporation and FDIC Certificate Number and Depository Institution.
  29. Excursus: Scoping an operational ontology  The 15 Fundamental Business Concepts apply to both the Financial Industry Business Ontology and Data Model. We use the same methodology to create the concept map. With the concept map we can easily scope a subset of the FIBO to hold the regulatory reference data. The Call Report presentation and the Semantic Compliance article on the Bank Ontology website show the instance graph below. We recognize the very same design pattern as in the sample data concept map