The Tolkien Effect

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 Reverse-Tolkien

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.

-Chris

Image Attribution:  lrargerich
CC BY 2.0

Highly Summarized Dimensions

From the inbox today, a question about aggregate design:

Q:  "If you roll up a dimension that is hierarchical, but you are now left with the highest level of the hierarchy (no further roll-up would be possible in that dimension) - would you maintain a separate dimension for this in the aggregated star?

"Or would you join a number of these unrelated top hierarchy levels in a junk dimension?"

- Dublin, Ireland

The reader has a potential aggregate that will summarize more than one dimension at a very high level.  For example, an aggregate of sales that summarizes the customer dimension by country and summarizes the product dimension by category.

As a general rule of thumb, try to set up aggregate dimensions that preserve the original separation of the base dimension tables.  So rather than have country and category combined as a sort of "junk" dimension, create a rollup of customer called "country" and a rollup of product called "category." 

This gives the aggregate star a kind of "symmetry" with the original star.  It tends to be the most workable solution with aggregate-aware query tools, databases, etc.  It also is the easiest to manage when no tools are aggregate-aware, as it minimizes the effort required to rewrite SQL.

If the summary is very small, it may be workable to create a "big wide table" that stores the dimension values directly with the summary facts.  This works best if you are using a tool to implement the aggregate that is able to exploit the query rewrite function of a database, like a materialized view or materialized query table.  In this case, applications can still query the "base" star, but receive the benefit of the aggregate.

But do you really need this aggregate?  The more highly summarized an aggregate is, the fewer queries or reports it is able to accelerate.   It is usually possible to construct aggregates that are not quite so highly summarized, giving a performance boost to a wider range of queries.  For example, summarizing a customer dimension by region, rather than country, will allow the aggregate to work with more reports--including those that focus on country.

- Chris

Image credit:  ZapTheDingbat

More on Surrogate Keys

This post addresses a common follow-up question regarding surrogate keys:

"My source system already tracks changes. In this case, do dimension tables really need their own unique identifiers?"

Yes. There may be exceptions, but they are far less common than you may think, and they limit your future options.