Schema designers must be on the lookout for data elements that are known by more than one name. Equally common is the use of a single name to signify very different things.
It may surprise you to learn that there is an important connection between data warehouse design and The Lord of the Rings. In the books, author JRR Tolkien challenges readers by using many different names, without explanation, for the same character.
The character Aragorn, for example, is also known as Strider, Dunadan, the Heir of Isildur, and several other names and titles. Each name, it turns out, is associated with a different culture or point of view.
At first, all this can be deeply confusing. With a little effort and patience, however, things begin to make sense, and the end result can be deeply rewarding.
The Tolkien Effect in Business
The same kind of thing happens when gathering requirements for a dimensional model. Within a business, it is commonplace to find several names for the same thing. Different departments, for example, may refer to products in different ways. Even within a department, there may be multiple names for the same thing.
Depending on your previous experience with the area in question, it may take you some time to realize this is going on. I will never forget the day I realized that a finance group meant the same thing by Ten-Digit-Department, Level 3 Code and Budget Line.
It’s crucial to identify these situations, or the capabilities of your model will be crippled. Data elements of interest in multiple contexts should be given a single, shared definition in your model. For dimensions in particular, this will be crucial in supporting analysis that crosses subject areas.
These shared dimensions are called conformed dimensions, and they are the key to avoiding stove-pipe subject areas. Even within a subject area, this can be crucial. The Ten-Digit-Department realization was essential in permitting comparison of budgets to actuals.
The converse is also a commonplace: a single name used to signify very different things. The best example of this is “Sales.” A salesperson will often use this word to refer to an order or contract. In finance, however, the word is reserved for the event that allows the recognition or revenue, which is often fulfillment or shipment of the order.
Once again, it is crucial that analyst keep an eye out for these situations; failure to produce consistent and well defined definitions for each fact or measurement is also a failure of conformance. The result will be challenges to the accuracy of the data, distrust of the solution, and a lack of user adoption.
What You Can Do
How then to avoid these problems? Listen. Don’t assume you know what people mean. Repeat things back in your own words. Be sure to write down and review definitions of each data element.
Look out for The Tolkien Effect. Pay close attention to people who live and work on the cusp of two subject areas or departments, as they will be keenly aware of these kind of linguistic challenges. So will the data administrator, if your organization has one.