Enterprise Logical Data Models – Idealism vs. Reality

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Teradata Employee

Enterprise Logical Data Models – Idealism vs. Reality

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.

What is the significance of enterprise logical data modeling?

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.

Scenario 1

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.

Scenario 2

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.

Scenario 3

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.

Scenario 4

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:

  • A) CPR
  • B) Kung Fu
  • C) Ritual suicide
  • D) A disappearing act.

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:

http://www.teradata.com/library/pdf/eb4331.pdf

For information on Peter Chen and E/R diagrams and their origin:

http://www.csc.lsu.edu/~chen/chen.html

For information on Edgar F. Codd, the relational model for database management, and the theoretical basis for relational database:

http://www.absoluteastronomy.com/topics/Edgar_F._Codd

For information on Christopher J. Date, an independent author, lecturer, researcher, and consultant, specializing in relational database technology:

http://en.wikipedia.org/wiki/Christopher_J._Date

14 REPLIES
Enthusiast

Re: Enterprise Logical Data Models – Idealism vs. Reality

Good article. I have heard of tales of ELDM’s but have never seen one actually work. Usually, one of the other three scenarios or others spells doom for the ELDM approach. I have personally seen data modelers sit around looking busy because the system experts keep canceling appointments just as described scenario 2. I have never been at a site that actually completed an ELDM but I have tried to implement an LDM on a specific area of the business and I have yet to get one that is even close to correct. The ELDM story described in scenario 1 sounds like bliss but so do fairy tales. The method I have seen work best at most sites is to build the warehouse one data source at a time with at most a conceptual logical model that defines major entities and how they relate, then create a physical model from the data source in or close to third normal form. Thanks for a good article that I can reference in the future.
Enthusiast

Re: Enterprise Logical Data Models – Idealism vs. Reality

so, the logical data model and the underlying physical data could be different. LDM is just a guidance right? the physical don't have to follow the LDM precisely, is it?
Enthusiast

Re: Enterprise Logical Data Models – Idealism vs. Reality

The physical model should be true to the logical model as far as relationships between entities is concerned, one to many , one to one etc. Sometimes it is advantageous to de-normalized the LDM for reasons concerning performance and simplicity but these changes should be minimal if you have a 100% correct LDM. The problem I have encountered is the first cut of the LDM is never correct. The implementation of the physical model becomes an iterative process where the implementation team must work closely with the data modeler to be sure that LDM is kept current as they discover discrepancies. It usually takes a lot of time to develop the LDM and then a lot of time to keep the LDM current as the implementation team finds problems. In a perfect world this process works fine but in reality the time is usually not there. How do you explain to your sponsor that there are delays caused by errors in the LDM after he just spent thousands having the LDM built. It’s not easy and as described in scenario 1 in the article, the one time it worked was when the CIO of the company was the sponsor. That’s why most of the time, I think it’s better to get a conceptual model to use as a road map, and then go directly to the data. I would prefer to have scenario 1 but I have never seen it happen.
Teradata Employee

Re: Enterprise Logical Data Models – Idealism vs. Reality

Even if there were no errors in the LDM, it should be a depiction of data from the business perspective, and that depiction will change over time. LDMs can only represent data at a given point in time. So, if the business is changing over time, the LDM can’t really ever keep up with the business completely. That is why the commitment to the LDM over time is so important. If you are not committed to trying to keep your LDM current, then you have the “ivory tower” LDM view from one point in time, and it becomes inaccurate or obsolete as time goes by. I would say the conceptual model is a necessity, though, regardless of commitment to the LDM, if for no other reason than to make sure that everyone involved is on the same page as far as high level data requirements. In the case where the LDM is subsequently developed, the conceptual model becomes the precursor of the LDM. Uh-oh, I feel Scenario 5 coming on …
Scenario 5: A large multinational company was developing an ELDM. One of the data modelers discovered a LDM in the archives that was 25 years old. It was done at a pretty high level, and maybe now would be considered more of a conceptual model. However, the subject areas in the 25 year old model corresponded to those selected for the new model, and the data relationships in the old model were, by and large, still accurate. Some of the subject areas from the old model were adopted as a conceptual base for subject areas in the new model, particularly in subject areas where subject matter expertise was lacking in the company.
Scenario 5 illustrates another example of the data model that was stuck in a drawer somewhere and forgotten. But this data model got a reprieve from oblivion. This happened to be a business where the business concepts from 25 years ago could still be applied to the present day. If the 25-year-old model had been fleshed out into more detail and maintained over time, it may have made the task of developing a more current ELDM unnecessary, or at least much easier. The commitment to getting the true value of an ELDM is long term and it takes a certain amount of vision for the future to even get a clue as to what the value can be. Often the value of an ELDM is not realized because the vision goes no further than a project deadline or the next employee evaluation which determines the raise, promotion, or bonus of the individuals responsible for meeting the deadline. I agree that sponsorship and a commitment to effort over a long period of time is critical to getting the ongoing value from an ELDM. And hopefully, the sponsor would have a vision that extends well into the future.

Re: Enterprise Logical Data Models – Idealism vs. Reality

Thanks for the good article. For sure I will use it as reference in the future !

There is no doubt regarding the importance of having an ELDM representing the logical, process independent, business view of the EDW.

I would like just to add the following point to the discussion:

The process of keeping this documentation updated and synchronized (LDM X PDM X Implemented Physical DB) is a challenge to several Teradata Sites. The complexity increases when there are several teams working in parallel on maintenances and enhancements projects.

Seems to me that Erwin pushes down the team productivity, sometimes taking more time for fixing tools issues than doing real modeling.

Any comments on this? What set of productivity tools are you using to keep all the documentation synchronized and updated? Any special working methodology?
Teradata Employee

Re: Enterprise Logical Data Models – Idealism vs. Reality

I have encountered a couple of different ways of dealing with this. One was a check-out, check-in mechanism for the models. That is, the models could only be checked out of the library they were in by certain authorized individuals, and could not be checked out for editing while they were checked out by somebody else. This still required that the models be changed before any modification was made to database design and that all changes were approved by committee of modelers and/or DBAs .
Another way was to use Erwin's model comparison capabilities to compare different versions of the same model that had been worked on by several teams or individuals in parallel, and decide (again by committee of modelers and/or DBAs ), which changes were to be kept in the single, authoritative version of the model. Kind of a reconciliation process.
I think Erwin just is what it is. It was originally just software that drew pretty pictures of data models. Then, as time progressed, Erwin tried to add more metadata capabilities, more forward and reverse engineering capabilities, Model Mart, etc. Trying more to be more of a player in the metadata management and physical database development areas and not just a data modeling tool. Over many years of using the product, I have both cursed Erwin (also its god, country and ancestry all the way back to Darwinian apes) and also enjoyed the productivity benefits it offers. In general, I would say I have seen great successes with Erwin, but also think as Erwin tried harder to be much more than a modeling tool, enhancements to the product may not have been as perfect as we would have liked them to be. Sometimes ,yes, even impacting productivity. But, where would we be without it? There are other modeling tools, too, of course. I don't endorse one over the other. It just seems like Erwin became the de facto standard for data modeling.
There are probably a varietyof tools in combination that could provide some type of end-to-end data management solution, but I think that regardless of tool sets , the methodology that works is one that recognizes and allows data management and data modeling to be not just just an integral part of EDW development, but to actually drive the EDW development.
But there is idealism rearing its head again. In reality, what matters is the communication to keep the model in sync between multiple users, and the acknowledgement that it is necessary and vital to maintain one authoritative version of the model..
Choosing all the right tools for end-to-end data management is a big project in itself , and I don't think I can honestly recommend any one single product or group of products that is right for everybody.
N/A

Re: Enterprise Logical Data Models – Idealism vs. Reality

I have a good experience in implementing a ELDM in the telecommunication area.
Well first of all using the Teradata cLDM helped a lot; it's really a strong & accurate LDM.
but it's not enough; to successfully implement a cLDM (in this case) you need a sponsor from the top management in order to initiate at entreprise level a common & standard way of defining the business & business terms.
It's really hard to get the datawarehouse up & running with operational sources having different "business languages"; integration is really a nightmare when trying to "merge" data coming from different systems that speak different languages.
thus definitely scenario 1 is for me the ideal situation to successfully implement a ELDM.
Now another problem that's not covered in your article: the bridge between LDM and PDM;
When buying a cLDM a company could think that the model is ready to implement but that's absolutely false!
The main problem is then the budget allocated to the implementation; budget defined based on the "ready to implement"; seen the budget we were nearly obliged to map a LDM entity to a PDM table (1-1 mapping); finally we get a PDM which is nearly equal to a LDM. You can easily imagine all the problems using such a PDM: performance, ETL dependencies due to generic structure (entities), difficult to // development teams work,...

Re: Enterprise Logical Data Models – Idealism vs. Reality

I have done quite a few ELDM, in reality the problem what I see is that :
ELDM is a living breathing document and it needs to be regularly maintained and updated to produce value.
Most companies get 1 cut done when they implement a DW, but then don't find value in budgeting for the Data modeler to maintain it.
Data governance and the value that can come from mainatining ELDM is yet to be realised!!

Re: Enterprise Logical Data Models – Idealism vs. Reality

Fantastic read. Thank you very much.
If I can add a thought here... Perhaps the problem with sponsorship over time can be solved by having the LDM maintained by the enterprize architecture team. In specific, the data architect is responsible for overseeing the overall implementtaion of data structures in the company. The lead data architect works closely with the team responsible for the data warehouse. I have been in data warehousing for a number of years, and experienced many of the problems described here by the audience. It is only now that I am in the lead data architect position that I can say that the LDM is invaluable to the whole of business and technology, and I have the relative easy buy-in from the company to maintain the LDM as the business grows.