This class is a hands-on exercise for data modelers. You can follow the steps in PowerDesigner, ERWin, or your modeling tool. The example is the Federal Deposit Insurance Corporation (FDIC) Call Report, Entity Schedule, a regulatory report for U.S. Banks. Our task is to design the Logical and Physical Data Model for the Call Report project. The lecture recaps the middle-out approach from “Semantics for Project Architects” and the method for deriving it from FIB-DM and other models. We use FIB-Concept Maps to design with the Business Users, and then scope identified Base Entities from the FIBO Data Model. The Data Modeler completes the design, adding supertypes and Associative Entities. Finally, the class discussed denormalization considerations for the generated Logical and Physical Data Model. Finally, we show that Ontologist can leverage Concept Map when scoping an Operational Ontology FIBO subset. Because ontology and data model have the same design patterns, names, and definitions. When I teach this class at Financial Institutions, it takes about 3 hours, including follow-along, questions, and discussion.
You can read the presentation or download the PowerPoint here.
Transcript of the video lecture
Welcome back to the FIBO Data Model training.
This is Jurgen.
Today, we scope our first data model from FIB-DM. The example use case is the United States Bank Call Report. We will be using Concept Maps.
If you remember the class, “Semantics for Project Architects”:
Solution Architecture is middle-out. Instead of the Enterprise approach, top-down rationalizing the whole model, we focus on FIB-DM packages, the FIBO modules for the project, not the whole thing.
We may be looking at Loans and Mortgages, Securities, or other packages in FIB-DM.
Our implementation is iterative, not a big bang, not a waterfall.
We start with small, manageable projects. Here, for example, we may scope for Core Banking Entities. In other words, Agents and Roles, and then we address a package like Mortgages, and the next iteration may have Cards.
Departments and Projects leverage and derive. Our goal is implementation: of a department, a project, or an application. We use data models and UML; we may implement in Java code; we may create an RDBMS; and our method is to derive.
We derive from the industry standard, that’s the normative FIBO Production and its Enterprise Data Model, and we also consult other models. We may have a customized FIBO model. We would have in-house models, consult third-party vendor models, and other standards.
To derive a project model means scoping.
FIB_DM can be intimidating, like a monster with more than 3,000 entities.
To be clear, we don’t need a giant model for our project or even a department within the Bank or Financial Institution. And we certainly won’t create 3,000 tables in our RDBMS, most of which will never be populated with data.
So, we cut, we extract a subset model from FIB-DM that comprises only the required entities.
In PowerDesigner, we create a new Package for our project and add the required, in other words, scoped entities to it and its diagram. In ERWin, we do the same. We create a new Subject Area and add the entities we need for our project to it.
Independent of the data modeling tool, we generate a new Project Logical Data Model.
Notice that we take a step here. FIB-DM is a CDM (Conceptual Data Model), but as we create a new, smaller FIB-DM model, we can do it as an LDM right away, and subsequent iterations, as on the previous page, can scope additional content.
And then we merge it from FIB-DM into the Project Logical Data Model.
Within the FIB-DM Training course, we are at “Scoping our first Data Model,” an advanced class for Data Architects.
“Semantics for Project Architects” is a prerequisite, particularly “Semantics for Finance Users,” because that class explains the 15 FIBO Fundamental Concepts in detail.
Then we should uh have the class before this one, “Semantics for Data Architects,” which teaches the structure of FIB-DM.
The FIB-DM Concepts article is a good reference to study after completing this class.
Audience and content.
This lesson is primarily for Project Data Architects/Modelers with experience in Enterprise Reference Models.
You may have used FIBO design patterns and definitions.
Finance Users and Subject Matter Experts define the scope.
Project Managers want to understand the process. You should pay attention to the first half of this class, but you don’t necessarily need to attend to the details of the data architect’s work in the data modeling tool.
Ontologists follow the same approach when scoping an Operational Ontology from the FIBO. Ontology and Data Model are in sync, and the project scope is the same. This class is a hands-on exercise. You can follow the steps in PowerDesigner or your data modeling tool.
The example we are looking at is the FDIC Bank Call Report reference data.
We use eight of the 15 Fundamental supertypes to create a Concept Map.
We transpose the Concept Map to a data model subject area or package.
We review the Conceptual Data model and compare it to the ontology graph.
The US Bank call report:
The Federal Deposit Insurance Corporation (FDIC) is a regulator of US banks. And the key report that all banks in the US must file is the quarterly Consolidated Report of Condition and Income, usually called the Call Report.
FDIC publishes the XBRL taxonomy and bank filings. You can view and download the Core Report of your bank.
One of the schedules in the Call Report is the Entity Schedule, which is reference data about the submitting Depository Institution.
For this exercise, our data requirements are limited to the reported data items.
Think of it this way: if we had to cut a FIB-DM Subset Model that should hold an RDBMS with the data to provide the Call Report data.
We see that we have a date, a value, a definition, and so on. In the example, we have JPMC North America’s submission, which has a date ID data item value definition, a Call Schedule, and line numbers.
From these Call Report data-item requirements, we can create a simple Concept Map. This is a generic concept map. You have probably done some of these simple circles for the Concepts and arrows for the relations.
Here, the FDIC certificate required data item 621, which, in this example, identifies JPM Chase, and the Certificate is registered in the FDIC Institution Directory.
The Federal Deposit Insurance Corporation registers the Certificate. Note that JPMC is a Legal Entity and that there is a distinction between the Legal Entity, JPMC National Association, and what the Legal Entity does, which is a Role as a Domestic US bank.
The Legal Entity has a Registered Address in the United States.
JPMC NA has a Legal Entity Identifier (LEI ).
The corporation has $176 billion in Issued Capital.
You should all remember the 15 Fundamental Concepts from our “Semantics for Finance Users” class. In the second step, we take our impromptu concept map and conceptualize our sample data items. That means to assign Fundamental Business Concepts and their specializations.
The 15 Concepts are all ultimate supertypes in the Financial Industry Business Data Model. They are the entities with the most subtypes. As you recall, they have an icon name and abbreviation.
In the FIBO ontology, the Fundamental 15 Concepts are direct subclasses of the Thing. Yeah, we use abbreviations and icons as mnemonics to teach concepts to modelers and business users.
Identifying Entities and Concepts.
We can do that in the data modeling tool or in MS Excel. What we do is we research concept entities. In this example, we look at the FDIC Certificate Number, an item in the FIBO data model, and we look up its associations. This gives us the concept in the Concept Map. And the Associative Entity provides a standardized label for the arrows.
Let’s uh create the Concept Map diagram. We see FIB-DM has a Legal Entity Identifier, an exact match for the LEI.
If we look up its supertype, we see that it’s a Designation, which gives us the symbol that we want for our concept map.
Likewise, of course, FIB-DM has a country. That rolls up to the concept of Location. The FDIC certificate number is another direct match, rolling up to the Designation.
The FDIC Institution Directory. If we look up its ultimate supertype, it’s a Collection.
The LEI identifies a Legal Person, in this example, JPMC.
And that’s a Stock Corporation, another entity in FIB-DM, and a Stock Corporation is a subtype of the concept of an Agent. The concept also includes Person, Legal Person, and Organization.
So the FDIC (Role) insures and regulates Institutions that take deposits.
The Depository Institution is an entity in the data model that rolls up to the Fundamental Concept of Role and to the FDIC itself. You find that it’s a Registration Authority that issues the Certificate. The FDIC has an identity as an agent, but we don’t need that detail for our scope.
The Capital is a subtype of a Monetary Amount. The entity holds numbers and descriptive properties, such as currency and date.
Again, if we traverse up the supertype hierarchy, we find that Balance and Monetary Amount are Scalar Quantity Values. Finally, the address field in our schedule is a Registered Address, a subtype of Reference.
In the next step, we simply replace the circles with the conforming icons of the concept.
And then, we standardize the Concept Map Relations.
The data modeler suggests FIB-DM Associative Entities that match related Concepts and requirements. We looked at this already, the FDIC certificate number. We find associations like “registers” and “identifies”.
If we look at the Stock Corporation, we find that it has a Legal Entity Identifier, has issued capital, and has a legal address.
So we add these conformed associative entity names to our standardized concept maps.
Here’s our finished FIB-DM Concept Map. All the sample values were replaced with the entity names, and we replaced any labels on the arrows with standardized relations between concepts. In other words, with standardized associative entity names.
That methodology keeps the concept map within the vocabulary and structure of the underlying data model. You can then, of course, expand a small example. The Monetary Amount has more defining Concepts. JPMC, or the Stock Corporation, the Bank, would have many more roles. We can add other Regulators to our Legal & Compliance concept maps or data model.
From Concept Map to Data Model scope, we have four steps.
First, we populate a new diagram. In other words, a PowerDesigner Package or ERWin subject area with the identified entities. Then we add supertypes all the way up to the Fundamental Concept Entity. We add Associative Entities. When we are done, we generate a new LDM export of our FIB-DM Subject Area Model.
Step 1: We create a new diagram with the nine entities. We open FIB-DM in the data modeling tool. The screenshots are of PowerDesigner, but you can easily replicate and follow along in ERWin or other tools. We create a PowerDesigner Package (ERWin Subject Area) and just populate it with these nine entities.
In practice, as a data modeler, you may draw the Concept Map and Conceptual Data Model in parallel.
However, I highly recommend using the Concept Map to validate your design with the business, as it is easier to understand than the complex entity-relationship diagram.
Step 2: Adding the supertypes all the way up to the concept, the Ultimate Supertype. Let’s start with the Depository Institution. In the data modeling tool, we simply navigate the Inheritance (or the supertype in ERWin). We find that the Depository Institution is a Financial Institution. We move on, and we continue the exercise, adding the Financial Institution to our scope. We follow this all the way up via Financial Service Provider, Agent, and Role.
Next, we do the same for the Registration Authority. It’s our entity for the FDIC and other Registrars. It’s also a Service Provider, a subtype of Agent Role, and the fundamental concept of the Role. The Stock Corporation is a Legal Entity, a subtype of Legal Person, Party, and the Agent.
The country of registry is a Geopolitical Entity, a subtype of Geographic Region, ultimately the Fundamental Concept of region.
The Balance for the capital is a Monetary Amount, a subtype of the Scalar Quantity. The Registry is a Structured Collection, a subtype of OMG Commons Collection.
The Conventional Street Address is a Physical Address, an Index, a subtype of the concept of Reference.
The FDIC certificate and the LEI are Registered Identifiers, a subtype of Contextual Identifier, which is an identifier that rolls up to the Fundamental Concept of Designation.
Here, at the end of step two, we see all our nine entities and their supertypes, all the way up to the Fundamental Concepts.
In step 3, we add Associative Entities to our diagram, which connect the Base Entities in the business context.
Finding matching associations is a challenge, even for experienced FIBO experts.
We ask, how does the Issued Capital relate to the Stock Corporation?
We can research the entity’s relationships in the data modeling tool.
Here, for the Stock Corporation, we see associations like “has Date of Incorporation”, “has Date of Registration”, and finally “has Issued Capital”.
So let’s connect the Stock Corporation to its Capital Balance.
My recommendation is: Always use the Concept Hierarchy at the leaf-level, at the lowest level. In other words, the green entities of the concept map, not associations, between the supertypes were possible.
We have used the data modeling tool to find the relations, and we investigated the Stock Corporation, and we found “has Issued Capital”, and we see here that connects the Stock Corporation, Monetary Amount, and its subtype, the Balance.
So that’s a perfect match.
Associating the FDIC Certificate Number is another easy match.
We find the Associative Entity “registered” and that has relations to the FDIC Certificate Number and the Registration Authority. And we also find that the FDIC Certificate is registered in a Registry.
Here’s a preview of the next class, the FIBO/FIB-DM Navigator.
It can be quite tedious and time-consuming to traverse the model hierarchy and model relationships in the data modeling tool to find the correct FIB-DM associations.
The Navigator is an accelerator, an MS Excel workbook populated with FIB-DM metadata. Here, for example, we want to know [the path] from the Stock Corporation to its Capital Balance.
Entity-1, we filter with the Stock Corporation, and we see the Stock Corporation has an Associative Entity related to it, “has Issued Capital” via its subtype, the Balance, and it is a Monetary Amount. account and also, um, it then associates with “has Issued Capital”.
Likewise, we can look all the way from the Registration Authority to the FDIC Certificate Number to the Registry. We filter by Registration Authority.
We see the Registration Authority is related to the Associative Entity “registers”‘. Among many other things, like Routing Numbers, Issuer IDs, and Listed Securities, the Registration Authority also registers FDIC Certificate Numbers, which we have scoped for our Call Report.
Then the FDIC Certificate Number is registered in a Registry. So we can see that this is much easier and faster to research than traversing within the data modeling tool.
Stock Corporation to address.
We see the stock corporation has no direct Associative Entity relating it to an Address. However, its supertype, the Legal Entity “has Legal address”, and that connects to a Conventional Street Address, which is an Address in FIB-DM. The Address has a Country, and the Conventional Street Address is a Physical Address that has a Country.
The “identifies” association completes the model.
That is the catch-all.
As you can imagine, there are many identifiers and many things that have IDs in Finance. So, as we scope the Associative Entity “identifies” and “complete relationship links” in PowerDesigner, we see it create many of the relations we need for our scope.
It rolls up here: the Legal Entity Identifier identifies a Financial Service Provider, and our FDIC Certificate Number is a subtype of the Financial Service Provider Identifier, which also identifies the Financial Service Provider.
Here’s our complete CDM scope for Bank Call Report Entity Schedule.
On the left, we see our Depository Institution. The Depository Institution is Identified By a Legal Person. Also, we can see that it is a Service Provider, and the registration authority is a Service Provider as well. It manages the FDIC Registry, and it registers the FDIC Certificate Number, and in turn, the legal entity is registered with the FDIC Certificate Number.
Here is how the Legal Person connects to our Depository Institution.
The Legal Entity is a Corporation, subtype Stock Corporation.
Stock Corporation has issued capital, which is the Capital Balance, a Monetary Amount, Scalar Quantity.
Also, the Legal Entity has a Street Address, which is a Physical Address that is in a certain country.
This question always comes up: Do I really need all these entities?
The short answer is no, you don’t. For a Project Model, it’s perfectly fine to scope only the directly required green entities. In practice, after you cut your model (in the Logical Data Modeling Phase), you add Attributes to it from your source system feeds.
Then, as a Logical Data Modeler, you examine the full set of required data items, and you map them to the Entity Attributes.
Here’s a simple rule: If, after this mapping/attribution, you have an Entity that doesn’t have entity attributes, you can remove them from the hierarchy and remove them from your Project Model.
Remember, FIB-DM is a Reference Model. For the data modeler, it provides a rich library of building blocks to accelerate the design process, but it doesn’t stipulate how to design your LDM, and even less how to denormalize in the PDM.
Here is a sample, Logical Data Model, derived from this CDM scope. It shows out-of-the-box attributes derived from FIBO data properties. As a data modeler, you add more attributes from your source systems and data requirements, and then if we eliminate (if we remove) entities that didn’t receive attributes in this exercise, we are left with the core design.
The Depository Institution, likely the Financial Institution, because there are other subtypes that we may need, and we have the Registration Authority, the FDIC Certificate Number, and the associated entity.
We have the Balance Amount, the Legal entity, and we have the Address with the Country.
Some further denormalization and simplification steps: As a modeler, you review the Associative Entities. For those that are not many-to-many, you’re OK to replace them with direct relationships.
So if the Physical Address has only one Country, we can replace the Associative Entity with a direct relationship.
Likewise, here are supertype/subtype relations. The Physical and Conventional Address can be configured to roll up or roll down during Physical Model generation. The same applies to the Legal Entity Hierarchy. It’s a Logical/Physical Model decision whether certain Scalar Quantities and Dates should be transformed into plain attributes or columns in the physical model.
Here’s a little excurs: The approach with the 15 Concepts and the standardized associations. It’s also fully applicable for Ontologies. Think of when you have to scope an operational ontology, maybe also for FDIC regulatory requirements.
The 15 Fundamental Business Concepts apply to both the FIBO and its data model. Remember, in the data model, they are ultimate supertypes. In the ontology, they are the highest-level classes, subclasses only of The Thing.
So we use the same methodology to create the Concept Map. And then with the Concept Map, we can easily scope a subset of the FIBO to hold the Regulatory Reference Data.
The Call Report presentation and Semantic Compliance® article on the Bank Ontology website show the instance graph below.
We also have our Stock Corporation. We see it’s the Monetary Amount with the Object Property “has Issued Capital” that rolls up to the Balance. And also, we see Stock Corporation is a Corporation which is a Legal Entity which has a Street Address, which is a Physical Address, and the address has a Country.
We recognize the exact same design pattern as in the Concept Map and the Data Model.
Resources for further study.
You find more than 50 diagrams on the FIB-DM website and also in your full model delivery folder. Open-source users can review this PowerPoint in MS Office online and download a PDF. The PDF and MS Office online have all the links shown in this video. Full-model licensees can find the original PowerPoint file in the delivery folder.
You should study the articles for a deeper understanding of FIBO/FIB-DM Fundamental Concepts. You can read this article. It’s also a concise reference list. Well, and finally, when scoping, avoid islands and watch the next lesson, the Navigator in the YouTube course.
Well, thank you for watching this class.