Month Year Issue

February 2000 Issue: 12

Journal of Conceptual Modeling
www.inconcept.com/jcm

Success Story: Much Ado about ORM Modeling
by Necito dela Cruz

I was having lunch with one of the software engineers when she asked if I could spare a few minutes to discuss the latest enhancements needed to manage the Consumer Relations Data Mart. She explained that there is an operational as well as a management statistical report needed on a daily basis to track and to monitor the performance, storage and usage of the Consumer Relations Data Mart. She indicated to me that she wants to create a set of tables to capture the daily statistics from the job stream. She has some ideas of what they are but she has an urgent need to define the requirements correctly and present it to the manager for approval. I told her that I have only about 45 minutes to discuss this with her before I head to a meeting.

In a small conference room with an erasable board, I started a mini ORM session. Since she is the subject matter expert, I asked her to describe the contents of the report that has to be produced. I wrote down the following requirements:

Track the number of "Inserts" per job per program
Track the number of "Deletes" per job per program
Track the number of records "Extracted" from the Data Warehouse per record type
Track the number of records "Purged" per month per record type
Track the number of CPU per day, per job, per program
Track the number of "Retrievals" performed by Consumer Relations per day, per hour, per record type
Track the number of "Suppressions" performed by Consumer Relations per day, per record type
Track the number of Elapsed time per day, per job, per program

"Now that we have these requirements, I would like you to describe in a narrative fashion what happens in a Job. Let's start with this thing called Job".

"Okay, here's what I can say about that. Job is really a concept of a stream of job control language (JCL) that is executed at a given time"

"All right, here's what we are going to do. We'll break up the narrative description into simple sentences. Let's write them down in a semi-formal and conversational format".

Each Job is submitted to run at start time. Each job is identified by a unique system generated ID.
Each Job is submitted to run on start day.
Each Job stopped running on end day
Each Job stopped running with end time
Each Job executes one or more programs. Each program will have its own program identifier.
Each Job has dependency on another job. Not every job has dependency on another job.
The program within the job inserts one or more record types.
The program within the job has total count of the record type inserted.
The program within the job has total elapsed time of the record type inserted.
The program within the job has total CPU time of the record type inserted
The program within the job deletes one or more record types.
The program within the job has total count of the record type deleted.
The program within the job has total elapsed time of the record type deleted
The program within the job has total CPU time of the record type deleted
The program within the job retrieves one or more record types.
The program within the job has total count of the record type retrieved.
The program within the job has total elapsed time of the record type retrieved
The program within the job has total CPU time of the record type retrieved
The program within the job suppresses one or more record types.
The program within the job has total count of the record type suppressed.
The program within the job has total elapsed time of the record type suppressed
The program within the job has total CPU time of the record type suppressed
The program within the job purges one or more record types.
The program within the job has total count of the record type purged.
The program within the job has total elapsed time of the record type purged
The program within the job has total CPU time of the record type purged
The program within the job extracts one or more record types from the data warehouse
The program within the job has total count of the record type extracted from the data warehouse.
The program within the job has total elapsed time of the record type extracted from the data warehouse
The program within the job has total CPU time of the record type extracted from the data warehouse

The resultant preliminary ORM model was captured on the board. However, for the sake of presentation in this article, the diagrams were generated from Visio Modeler and split into several clusters to show the different functional areas captured:

Note: The ** denotes that the value is derived and stored.

Figure 1 Basic information about the object type JOB

Figure 2 Job Record Insert

Figure 3 Job Record Delete

Figure 4 Job Record Retrieve

Figure 5 Job Record Suppress

Figure 6 Job Record Extract

Figure 7 Job Record Purge

"What you have drawn on the board looks great. But I am used to seeing a logical model. Can you transform these ORM diagrams to one logical model?"

Looking at my timepiece, I still have another 15 minutes to spare. So I drew on the board a rough sketch of the logical model. Here's how it looks as generated in Visio Modeler:

Figure 8 Relational Model

"Whoa, I did not realize it will map to 8 tables", was her remarked after I finished sketching them out.

"Remember, this is a pure capture of what you just narrated. We can improve this. See those entities with names starting with 'Job Program …..?' Those are repeated ones, or are patterns, differentiated with the unique function or task being performed by the program. We can generalize those and make an abstract object type out of it. Let's call it 'Task'. The abstracted business fact then is 'the program within the job executes the task for the record type'. So, let's modify our ORM model and reflect this change."

The revised model looks like this:

Figure 9 Revised basic Job Information

Figure 10 Revised Job Program Task

Then, I said "There it is".

She remarked; "It's more compact, condensed and abstracted. Can you show the logical model then".

The revised logical model is shown below:

Figure 11Revised relational model

"I like it. This is cool…..from 8 tables down to 3 tables. And I don't have to do any table creation if I have to add another task. All I need to is to add the new value in the Task Table and it will run. This is great. By the way, if I am not asking too much, would it be possible to generate the DB2 DDL for this?"

"Certainly, but I have to go to my meeting. I'll meet with you after and let's generate the DDL".

Returning from the meeting, it would only take 15 minutes to enter the final version in the Visio Modeler and with the push of the button, the DDL was created and I had a happy software engineer in our group.

Necito dela Cruz is a Senior Partner and Principal Consultant for InConcept. He has over 20 years of ORM/NIAM experience and is an ORM expert and trainer and a certified Visio trainer.

Contact Information:

Necito dela Cruz
Vice President and Co-Founder
InConcept
8171 Hidden Bay Trail N
Lake Elmo, MN 55042
nc2@inconcept.com
(651) 777-8484
fax: (651) 777-9634
http://www.inconcept.com