6 Things You Can Do When Your Data Dictionary Gets Messy

When creating a data dictionary, it’s not uncommon for your spreadsheet to grow to have hundreds or rows and dozens of columns. Luckily, there are a few ways to simplify, format, and view data dictionary documents that keep them well organized and easy-to-use. In this article, we’ll look at 6 of them.spreadsheets

(By the way, if you are looking to learn more about data modeling, be sure to check out our Free Data Modeling Training.)

#1 – Hide Columns

Most of the following tips are going to talk about removing information from your model so that it’s simpler. But as business analysts, we often do not want to lose a piece of a template or information, just in case we need it later.

Other times, there is information we need for one stakeholder group that another stakeholder group doesn’t need to see. For example, our business users could probably care less about the database field name, but the technical stakeholders would definitely benefit from having this information.

If you are using Excel, a quick alternative is to hide any columns you don’t need, or don’t need at this time. Your view will be simpler and your information will be saved should you need to bring it back later.

#2 – Delete Columns You Don’t Need

Hiding columns is useful, but there is still intellectual energy invested in remembering what has been hidden. If you’ve determined that a type of information is irrelevant to your project, delete the column. As long as you do not save over your original template, it’s always possible to add a column back in if you need it.

(Quick tip: When I cut information I think I might need later, I create a dump file to store it. 95% of what goes into my dump file stays there.)

#3 – Group Attributes in Your Data Dictionary

One of the questions I received in the Tricky, Sticky, Icky Stuff webinar was about how to sort the attributes in the data dictionary. While it might be theoretically useful to sort a list of attributes alphabetically, more likely you’ll want to group them in a meaningful way.

Most commonly, attributes are grouped in the following ways:

  • By concept in your Entity Relationship Diagram,
  • By table in your physical database design,
  • By web form from your user interface,
  • By paper-based form from your business process.

If you find you want to group your attributes in multiple ways for different stakeholders or analysis tasks, simply create separate columns for each grouping so you can sort the attributes as needed.

(You can take a little solace here. Just imagine how messy this information would be if you weren’t using a data dictionary to organize it!)

#4 – Move the Spreadsheet to Word

Another way that data dictionaries can get messy is when the notes section begins to contain lots of information. In this scenario, moving your spreadsheet over into a table in a landscape-formatted Microsoft Word document allows you to add formatting and highlighting to make your data dictionary easier to read and update.

#5 – Create an Appropriately Incomplete Spec

Sometimes messiness is a sign that we are getting too detailed and crossing the line from the what to the how. A data dictionary created by a business analyst will often not include every single database field, because not every field required for a well-structured relational database is important from a business perspective.

Comb through your list of fields and see if you can remove or subset out any fields that are unnecessary for the business to review.

#6 – Use Two Monitors or Bigger Monitors

Finally, there are times when there is no way to consolidate the information, so we need to adjust our view of the data dictionary. Two monitors are extremely helpful, especially when reviewing two data dictionaries as part of a data mapping exercise. Two larger monitors are even better.

Compared to the amount of time you spend switching between and scrolling through spreadsheets, let alone the cost of missing an important data issue, one or two new monitors is a small financial investment.

A second best solution is to use a laptop and a monitor side-by-side when doing your data mapping. And a third best solution is to decrease the font size to make more of your spreadsheet readable in any given view.

Use Messiness to Your Advantage

You are probably going to be more invested in your data dictionary than everyone else on the project, except maybe the database developer. If the data dictionary seems messy and unwieldy to you, you’d better believe your business stakeholders are going to be reticent to review it.

Messiness is a sign to make a change.

More information is not always better information. Sometimes the best work we do as business analysts is to provide meaningful abstractions of complex situations to help stakeholders make very specific decisions. Then we provide different information (or the same information in a different way) to close another decision-making gap. And so on and so forth until we are done with our project.

Pay attention to messiness. Think of it as a slightly annoying mentor guiding you to a better solution. And use these 6 tips to figure out what that solution should be.

>>Learn More About Data Modeling (Free Training)

Learn the essential Data Modeling Techniques (even if you don’t know how to code) with this free training.

Free Training - Quick Start to Success

(Stop the frustration and earn the respect
you deserve as a business analyst.)

Click here to learn more

By signing up, you agree to our Privacy Policy.

Before you go, would you like to receive our absolutely FREE workshop?

(No formal experience required.)


Quick Start to Success
as a Business Analyst

By signing up, you agree to our Privacy Policy.