This article examines what Enterprise Logical Data Models (ELDMs) are, what the value of Enterprise Logical Data Models can be, and why that value is sometimes not able to be realized. Some real world scenarios are offered to illustrate what can happen with regard to the use, disuse, and development of ELDMs.
The significance of enterprise logical data modeling is that it is a process-independent representation of business data.
It also serves as a valuable data management tool and a bridge of understanding between the technical (Information Technology) and business branches of an organization. The value of logical data modeling has always been to establish the “single version of the truth” of an organization’s business functions. Or in other words, a single view of the business that is not adulterated by IT concepts and processes.
When the term “enterprise logical data model” (ELDM) is used in this document, it refers to an entity/relationship model in third normal form with fully attributed entities. Or what you would see in most Erwin LDMs.
If the ELDM is utilized when designing the physical data model for the target database, a centralized enterprise database can be the result. Data will not need to be stored redundantly to support disparate reporting requirements. All queries, reports, and applications can reuse the data from a single database, which greatly reduces the possibility of data inconsistencies when using redundant data from different databases.
At least, that is the ideal with which we can justify and support the creation and ongoing use of logical data models.
Unfortunately, when idealism meets budget and time constraints, it is forced to transform itself into reality.
“Idealism is fine, but as it approaches reality, the costs become prohibitive." - William F. Buckley, Jr.
An ELDM should focus only on the business data. We disregard all that nasty IT stuff – queries, reporting tools, ETL, OLAP, databases – and focus on the business perspective. The business gets to have it’s opportunity to lay a foundation for the ongoing documentation of the business definition of the organization for the time being and hopefully in a format that can be updated to address changes to it in the future. The ELDM would focus on data analysis, not system analysis.
The process of building the ELDM could possibly identify synonyms for a particular business entity. Different areas of a company could unknowingly refer to the same entity by different names. By obtaining and reviewing the definition of this particular entity, the duplication of this one entity with two different names can be avoided.
In fact, the whole process that is referred to as “normalization” – making sure each entity has its own unique identifier (primary key), using the rules of data normalization to assign a fact to its own unique place, etc. – ensures that data descriptions are reviewed and are not duplicated.
The relationships illustrated in the ELDM indicate which entities are considered related for business reporting purposes by the people that make business decisions.
The validation of the ELDM components and the analytical questions that arise from doing so can expose data quality problems in operational source systems that weren’t brought to the surface at the time of system design or testing. So, logical data modeling can contribute to overall data quality.
OK, so if enterprise logical data models are so great, why are there so few of them around? Why aren’t everybody’s IT projects driven and prioritized by the changes to their Enterprise Logical Data Model? Because if they are so wonderful, everybody would want one that encompassed their entire organization, right?
Right! Except remember that part about idealism meeting time and budget constraints and metamorphosing into reality?
Let’s consider some scenarios based on situations I have encountered over the last twenty years that might illustrate how ELDMs get derailed, or are never even started. And some scenarios that illustrate what happens when ELDMs are developed and incorporated into a company’s enterprise data warehouse development and data management philosophy.
A company contracted with two very experienced and savvy logical data modelers to create an Enterprise Logical Data Model. The data modelers worked by subject area. As a subject area was completed, the data modelers passed it on to a sourcing team so they could begin identifying source systems and data elements. The whole process took nearly two years, but the data modelers had the complete support of the CIO of the company. During the data modelers’ time with the company, they not only created the data model by meeting with the business experts in each subject area, they also worked with the sourcing team and taught classes on enterprise data modeling and enterprise data management for the company’s employees. They were also very responsive to informal questions employees had on those subjects.
The company developed a data warehouse driven by the ongoing changes they make to their enterprise logical data model. They also introduced enterprise data management methods which incorporated the ELDM. Fifteen years later they are still expanding their ELDM, their data warehouse, and their company. Many of the employees who worked on the original data warehouse implementation and data modeling efforts have moved on to become successful data warehouse consultants and executives.
A data modeling consultant was excited to obtain a contract to create a physical database design for one of the major subject areas in the Enterprise Data Warehouse (EDW) of an international company with a name that is recognized worldwide. On her first day at work she met with her project manager, a systems analyst with many years of experience at the company.
“I’m very anxious to see your logical data model,” she told him.
He had a blank look. “We don’t do logical data models here,” he said.
“Then what will I base my physical design on?” she asked.
“Our source systems,” he said.
The consultant worked hard, determined to come up with a good design despite the lack of a logical model. Her project manager had a good general knowledge of most of the source systems, but when she had more in-depth questions, she discovered that the system experts had little time to spend with her, or in even more cases, that they had left the company, retired, took a buyout, opened a barbecue joint, went to India to become a veterinarian, moved on to another company, etc. People that she could meet with and ask questions of were very helpful, though, and her project manager would research questions he couldn’t answer by himself.
The contract specified a finite number of hours and a delivery date for the physical database design. The hours were completed and the contractor’s physical database design was delivered and accepted by the company. The person who was contracted to architect and develop the ETL process for the contractor’s physical database design was coming in the door as she was going out. By the time the ETL architect saw the contractor’s physical database design, the database designer was on a plane, never to return.
A recognized industry leader acquired a slightly smaller company in the same industry. Within each of the two merged companies there were smaller companies and subsidiaries that had been acquired or formed before the merger.
The newly formed parent corporation, which had been formed when the two companies merged, continued to acquire international companies in their industry. There were dozens of companies and subsidiaries underneath their corporate umbrella.
The two companies from the original merger had continued to function as independent entities during a period of several years. At that point, the upper management of both companies realized that they had actually been competing with each other and undercutting each other. They looked for a way to combine the information assets of the two companies and discovered that data warehouse development projects were underway in both companies.
One company had nearly completed an Enterprise Logical Data Model, along the lines of the Bill Inmon methodology, and the other had developed a core subject area physical design while following the methodology of Ralph Kimball. Although IT personnel from both companies agreed that the physical design created at one company definitely corresponded to the logical model subject area done at the other company, management was not convinced. They hired a Big 5 consulting firm to sort out the mess. Big 5 said that both the ELDM and the development of the core subject area physical design were “distractions”, and that the parent company needed to document all of their current operational data systems, recreate or redesign them, and maybe then think about an EDW at some point down the road.
The enterprise logical data model was scrapped.
In the mean time the parent company had continued to acquire more companies within their industry.
A contractor assisted a manufacturer in developing and implementing a database which contained data related to the global demand for their products.
Shortly after implementation he noticed that duplicate data was being loaded repeatedly and that rows were only made unique by a “load time stamp” column which contained a system provided time stamp. He decided to meet with his manager to discuss this and set up an appointment in the manager’s office.
The discussion progressed, the problem was explained, and the contractor asked, “Could I see the logical model this database was developed from so I could determine what the keys should be?”
The managers face turned red, then purple. Outlines of veins began to pop out on his forehead and neck. Then, outlines of veins began to pop out from the outlines of other veins.
“Logical model!,” he fumed, “We have a logical model from six or seven years ago that’s sitting in a drawer somewhere! I don’t want to ever hear anymore about logical models! They have never done us any good at all! Just do your job! Just do what you’re told!”
The contractor was very confused. He did not know if he would need to perform:
There were some tense moments, but finally the managers face faded from purple to red and eventually to a rosy pink. The subject was never broached again. The contractor was given new assignments and eventually offered a job as an employee.
These four scenarios illustrate four common situations related to logical data modeling.
Scenario 1 illustrates a successful enterprise logical data modeling effort. Why did it succeed? Because the CIO was an experienced veteran of data warehousing, and knew the advantages of developing an enterprise logical data model and was therefore ready to spend the time and money to do it. Also, the CIO hired experienced data modelers who not only created a viable model but mentored and educated employees.
Although this was a successful example of enterprise logical data modeling, it was still subject to some of the common problems of ELDMs. Such as, another area of the company deciding to physically implement a given core subject area on their own. Or another area of the company deciding to create their own ELDM, a situation I refer to as “dueling data models”. The CIO’s support of the ELDM was invaluable in this scenario.
Scenario 2 is the classic “we‘ve never done it that way here” syndrome. The company was an old, well established company that saw no reason to change too quickly because they were very profitable and at the top of their industry. They adopted physical relational database technology, but didn’t grasp the logical design and data management concepts they needed along the way. They went from top dog to just running with the pack.
“Even if you're on the right track, you'll get run over if you just sit there.” - Will Rogers
Scenario 3 is an example of a corporation that gets so big it doesn’t even know what it is. Mergers, acquisitions, etc. A corporate monster that swallows Fortune 500 companies whole and doesn’t even belch. Which company’s business should be modeled? Who would accept and bless an ELDM in this situation? Who’s in charge? What is the Enterprise that needs to be modeled?
Scenario 4 is one that is all too common. An ELDM is created, and possibly even done very well, and then discarded and not integrated into the company’s data management philosophy. This can happen for a variety of different reasons. But obviously, the ELDM did not have the upper management support the model in Scenario 1 had. Perhaps the manager was one of the ELDM creators, and the contractor’s question struck a raw nerve.
In any scenario, if an ELDM is created and kept current with the company’s business practices, it will remain useful when contractors, employees or logical data modelers move on. In this respect alone, it can add tremendous value to any enterprise. It remains as documentation when the individuals possessing the “tribal knowledge”, meaning the undocumented knowledge, are no longer available.
In the overall view of data management, ELDMs are just the tip of the iceberg. But, that also means they are the part that everyone can view without doing a deep dive into the metadata that lies beneath. The visual representation of data and relationships that the ELDM provides
should not be underestimated, particularly in the visually oriented society we live in. The visual representation of the ELDM can be an incredibly valuable communication device and learning tool.
At present, we seem to associate the ELDM with enterprise data warehousing, but entity/relationship diagrams were used for business logical data modeling before RDBMS’s were commonly used and data warehouses were ever heard of. There is absolutely no reason that the business oriented individuals within an organization cannot learn to understand or even learn to create and maintain ELDMs without any assistance from IT.
“An idealist believes the short run doesn't count. A cynic believes the long run doesn't matter. A realist believes that what is done or left undone in the short run determines the long run.” - Sydney J. Harris
For more information on the importance of data modeling as a foundation for business insight:
For information on Peter Chen and E/R diagrams and their origin:
For information on Edgar F. Codd, the relational model for database management, and the theoretical basis for relational database:
For information on Christopher J. Date, an independent author, lecturer, researcher, and consultant, specializing in relational database technology: