Chapter 5 Selected Detailed Examples
5.1. From Relational Model to Property Graph Model
Northwind was one of the sample databases published by Microsoft for SQL Server. It was for use with SQL Server 7 and SQL Server 2000. In 2005 it was replaced by the more comprehensive Adventureworks sample database. Northwind can still be downloaded from Microsoft: http://bit.ly/2a2xtGM. The data model looks like this:
Diagram built by the author in Oracle SQL Developer Data Modeler
Although not truly complex, the one-page diagram is a bit difficult to comprehend without a conscious effort. The presentation style simply underplays the structure of the tables’ field lists.
In the property graph style advocated by this book, the solution data model could look like this:
Notice that the ID fields are business level fields, and that we should probably generate surrogate keys across the board.
Structure is easily identifiable in this style of visual communication. If you look at the properties, you will notice that there actually are some relationships reduced to denormalized dependencies (dates and geography roles, such as country and shipcountry). Be vigilant and always be on the lookout for hidden dependencies.
Transforming a relational data model into a multidimensional model involves some restructuring of the relationships. Multidimensional data models are sometimes called “star schemas” because they center on a central fact (table) being surrounded by a layer of dimension tables.
For more on multidimensional modeling, read The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd Edition by Ralph Kimball and Margy Ross. Or Google the term – there are plenty of articles out there.
Let us tweak the data model we made in the previous section (inspired by Microsoft’s Northwind training data model) into a multidimensional model. First we have to identify the fact tables (business events). There are five candidates:
- Orders
- Purchase orders (with suppliers of products)
- Inventory (of product)
- Customer demographics, and
- Employee territories.
(Multidimensional models typically transform many-to-many relationships into separate fact tables.)
We can now create a first draft of a star schema for the orders fact, as shown on the next page.
Notice how the relationships are now targeting orders directly. This makes sense for two reasons:
- The shippers used to handle orders (which we now call order header) will degenerate in the next steps
- The suppliers do supply products, but that is part of the purchase order context, which we’re skipping in this book.
Also notice that there are some explicit hierarchies:
- Categories to products
- Regions to territories.
There are also some implicit denormalized relationships (dependencies) inside the entity types in the relational model:
- Various roles of dates with respect to a calendar dimension
- Various roles of geography with respect to a country dimension.
Let us visualize those hidden dependencies:
We could also question whether “contact” is an independent entity type. For this exercise, we’ll assume that there is a business rule saying that we maintain only one contact per customer.
Also note that the order header has degenerated into an OrderID. We will push that to the fact table, and get rid of the dimension (which will just become a passive property of the orders fact).
Good “Kimballian” star schema design does not accept so-called “snowflake” designs of dimensions, so we will denormalize the hierarchies into flat dimension tables. We’ll also skip some operational details (such as phone numbers, photo path, and pictures). Finally, there are some naming issues once we get to the final multidimensional model. The textbook rule is that facts are plural, whereas dimensions are singular.
Note that the dates on the employee are not included here. They might be relevant in a human resources context, but not really in an orders context.
Since an important aspect of multidimensional data models is the issue of hierarchies, you might be hesitant to hide the relationships in the territory and product dimensions. I recommend documenting these as concept maps, like this:
And this:
Notice that the “is” dependency between product and discontinued may imply that there is a business event called “product discontinuation” or similar; this event could be a business object type in its own right.
In Steve Hoberman’s book Data Modeling for MongoDB [9] we find a case study based on the transformation of a survey form to a logical data model, and then on to a collection-based model for the document database characteristics of Mongo DB. With Steve’s kind permission, the next page contains the survey form.
Apart from the form, the data model that Steve develops must also include the surrounding information that a company conducting a survey would require.
The data model in relational style appears on the following page.
As an overview, the business object type level concept model looks like this:
Remember that in concept models we prefer top-down, left-to-right organization, which is what Westerners are trained to perceive. If you are from Asia, you might want to organize it right-to-left, if it communicates better.
I recommend that you read Steve’s book, if you would like to get an in-depth explanation on how to construct document oriented collections for this data model.
5.4. FIBO Indices and Indicators Model
The FIBO, Financial Industry Business Ontology, is a standard developed by the Enterprise Data Management Council and standardized by the Object Management Group (OMG).
It is an evolving standard that aspires to cover all of the financial industry; it is intended to be used in data interchange between industry parties and with regulators. It has a section containing metadata for indices and indicators, such as benchmark indices and other rates as well as national economic indicators. This section is what we’ll take a stab at modeling.
The reference document is: EDMC- Financial Industry Business Ontology (FIBO), Indices and Indicators, Version 1.0 - Beta 2 Release Date: May 2015, dtc/15-05-11, Beta 2, PDF http://bit.ly/29PoJBh.
The rates and indicators span the following concepts (simplified for pedagogical reasons):
Here the concepts and relationships are transformed into a solution data model design:
The version above is before identities and uniqueness are resolved. You are welcome to give this some thought.