Designing Operational Data Stores

Operational Data Stores (ODS) are entity-centric and should only contain data about that specific entity. When designing an ODS, you will use relational database design methodology. I try to match my ODS data model as closely as possible to the source systems data model. By keeping the models similar, you will make the loading and synchronization of data to and from ODS and the source systems easy and efficient.

Let’s take a look at a Constituent (Person) for a membership and publishing company. If you remember from my previous post on how to use an ODS, this company has many different personals a single person can have when interacting with this company. They can be an author, an editor, a reviewer, a member, a student, an educator, a meeting attendee, a customer, or on online contributor, etc. How would I design an ODS for such a wide range of personas?

For the Constituent ODS, I only care about all of the information concerning the Constituent. This is more than name and address. I care about what manuscripts they have submitted, what articles were published, what journal they review for, etc. This information is critical to the operational usage of the ODS. What I don’t care about is what school they were teaching at in 2015 when they published an article or what did the previous version of this manuscript look like. History does not live in the ODS; that information is stored in the data warehouse.

Consider a person who is a member, author, editor, reviewer in the peer-review system, a subscriber to one or more journals in the journal delivery system, and an active member of the online community application. As you can see, this single person has many personals and may, in turn, have many distinct values for key information that will need to be addressed so we can have a complete view of this person.

For this person, we can use the member number as the unique identifier and create a single record for this person with flags denoting the different roles this person plays in the various systems. For authors, reviewers and member s of the online community application who are not members we can try to combine the data using a unique attribute like email address or cell phone number. Again, we would use flags to define the roles this person plays. Now I can easily ask how many authors are members?

Here is what an ODS data model might look like for persons interacting with this organization:

As you can see there is a single table containing person data. Using flags for the roles a person plays when interacting with the organization provides great flexibility in answering the operational questions an ODS is created to answer. This design will also allow me to synchronize the changed data with my source systems.

I hope you see the power a properly designed ODS can provide your organization.