Name Value Pairs and Dimensional Models

A reader asks how name/value pairs are best translated into a dimension design.

I was wondering if you have ever had to setup a data warehouse with a source system that has the ability to have dynamic "virtual" columns using a set of name/value pair tables...If you have done this or have any cool ideas on how to tackle this setup in a dimension that would be greatly appreciated.

-Joshua

Belmont, CA

This is a common scenario. The preferred solution is to identify the most commonly used "virtual columns" and model them as standard attributes. Bridged solutions are also possible, but are much less flexible from a usability standpoint.

Name Value Pairs

Name/value pairs are often used in OLTP solutions to offer a flexible way to describe an entity.

Rather than store each characteristic of an entity as an attribute, a special table lets you identify unlimited attributes for something. Its main columns capture the name of the attribute and its value. (Hence the moniker "name/value pair".)

For example, suppose you are Apple Inc. Your OLTP system has a product table, but each product has a wide variety of attributes. Rather than model each attribute, you might capture them in a name/value pair table.

The rows in the table for the newly released iPad Mini might look like this:

As you can see, the 18 rows above describe one iPad Mini model. 

Elsewhere in the data model, and associative table (or "intersect table") cross references these 18 pairs with a single row in the product table for this item.

Apple has 23 other iPad Mini models, each with different of values for the capacity attribute, color attribute, carrier attribute, etc. 

In addition, this same table can be used to describe the characteristics of products with different named attributes - a phone, a computer, a notebook, and so forth. New attributes do not require changing the database design. Instead, all that is necessary is to insert the appropriate rows.

In a dimensional model, there are several ways to handle name/value sources. Each involves tradeoffs between flexibility and usability.

Flattened Solution is Most Usable

The preferred method is to model the "names" as explicit dimension attributes.¹

In the case of our product, for example, the resultant dimensional model might nook like this:

This approach produces the most understandable and usable dimensional model. Each important characteristic is present in the dimension, named according to business terms, and ready to provide context for facts.

Do not be discouraged if every instance of the source entity (product in this case) does not share the same set of attributes in the name/value pair table. Look for 100 or so attributes that are most commonly used. In many cases, this may satisfy most of your analytic requirements.

Core and Custom Dimensions

If there are simply too many names to flatten into a single dimension table, the next step is to consider "core and custom" models.

If you separate the entity by "types", can you find 100 named attributes for each type?  

If so:

• Build a single "core" dimension that contains an identifier and any attributes common across all types, along with a surrogate key.
• For each type, build an additional dimension that replicates the common attributes, plus contains the 100 or so attribute names specific to the type
• The core and custom versions should use the same surrogate key domain for each member of the dimension, so that they can be swapped in and out of queries as required.

For example, Apple might do the following:

• Build a single product table that has the part number, category, product name and a key. This table will have a row for all products, including computers, tablets, phones and music players. This is the "core" product table. 
• Build additional "custom" dimensions would be built for each category of product. The table pictured in the previous section might be the custom dimension for tablets.

This solution retains the understandability of the previous solution - each attribute is clearly and explicitly named. However, we must now take care to join the appropriate version of the dimension to fact tables - a technical consideration based on the business question being asked.

Bridged Solution Mirrors OLTP Design

The last option is to employ a bridged solution. This technique actually mimics the original OLTP design. An outrigger contains the name/value pairs, and a bridge associates it with the dimension.

For example, Apple's product dimension might be bridged as follows:

This solution has the benefit of providing access to the full range of named attributes. It is also flexible; new named attributes do not require any change to the schema design.²

The disadvantage here is that this solution is the most difficult to use. Facts can be easily double counted, triple counted, etc. It will be necessary to take great care in constructing and qualifying queries, and it may also be necessary to lock down "grand total" functionality in end-user reporting tools.

Both Ways

Remember that this is not an either/or solution. If a bridged outrigger is necessary, consider also capturing the 100 most commonly used attributes in the dimension itself.

This allows you to create a safe "sandbox" for less technically sophisticated users. They are able to access much of the analytic value of your solution, without having to expose them to the complexity and possible dangers of the bridge table.

More Reading

• Bridges: Much has been written in this blog about bridge tables. The most relevant entry if you want to learn more is Resolve Repeating Attributes With A Bridge Table (1/28/11).  There are also two full chapters in my latest book (see below.)

• Core and custom dimensions have not been previously discussed here, but you can learn more about them in Star Schema: The Complete Reference.  It dedicates an entire chapter to "Type-Specific Stars."

Help support this blog

This blog is ad-free, and no one pays me to write it.  You can help out:  

• Use any of the links on these pages to pick up my latest book, Star Schema: The Complete Reference.  
• Or, if you already have it, use the links to check out any of the other recommended books.

When you do, a small portion of the purchase goes to this blog (you do not pay any extra).

Notes:

¹I'll call these "names" rather than "pairs", since its the name that will translate into a column. For example, "color" is a name, it may participate in several name/value pairs -- one for each possible color.

²The bridge table in this diagram contains product and attribute keys. If there will be many combinations, dimensional modelers often replace the product key with a group key, and store the group key in the dimension table. See Resolve Repeating Attributes With A Bridge Table (1/28/11). 

Handling Rankings

Dimensional modelers often struggle with issues surrounding rankings. Learn when to store them, where to store them, and where not to store them, by reading this post.

We need to calculate Top N rankings across several dimensions (account, channel, hour, day, week, month.) There are massive data sets involved. This must be a common problem. Is there an approach you can point us towards?
- Jeremy

Rankings are time and context dependent. When you need to track them, they pose challenges that are similar to non-additive facts such as period-to-date measurements or distinct counts.

The solutions, it turns out, are also similar.

Rankings and their Context

When thinking about how to handle rankings, it is useful to break them down. A ranking is actually made up of several parts. There is the dimension being ranked, the dimensions for which the ranking will be computed,  and the fact that is the source of the ranking.

For example, look at this simple statement:

"Show me the top 10 customers by week and product."

• The dimension being ranked here is Customer.  

• The dimensional context of each ranking is a product and week. For example, if we have 20 products, then each week we will need 20 top ten lists -- one for each product.

• The fact driving the ranking is not explicit in this question. It is probably spending, though it may be based on margin or transaction counts.  

That may sound simple, but things are usually a bit more complicated.

The reader, for example, does not just need a single ranking. He actually needs several -- daily, weekly, monthly. Presumably, these may be mixed and matched with the other dimensions he mentioned -- account and channel. In addition, there may be different rankings based on different facts.

Compute vs. Store

It may have occurred to you at this point that there are a multitude of possible rankings, even for a relatively simple schema.

In addition, once we compute a ranking, there is very little we can do with it. For example, we cannot sum it up over time periods, or other dimensions.  We may be able to compare it to another ranking (a different period or dimension, for example), or treat it as reference data describing the thing being ranked. But that's about it.

For these reasons, many organizations choose to compute rankings within reports, or at query time.  This provides maximum flexibility, and also sidesteps the ugly problem of having to store information that cannot be aggregated.

However, the reader indicates that data volumes are large. Let's assume they are so large that it is not feasible to keep the granular data around to compute rankings. Let's also assume that the rankings he needs are consistent over time. In his case, it may be necessary to compute all needed rankings and then store them.

Rankings fit with Snapshots or Aggregates

A ranking is only useful within the original context of its calculation. We won't really be able to aggregate it.  In this respect, its is similar to a distinct count or a period-to-date measurement -- topics I have addressed in the past.  (Some period-to-date metrics can be aggregated within a single period, but otherwise the comparison holds.)

The recommended solution is similar as well. Establish the granularity of the various rankings, then keep track of them fact tables of appropriate grain. These will either be periodic snapshots, or aggregate tables that summarize appropriate time periods. The rank itself will be stored in the fact table, even though it is not additive.

Many developers balk at this solution, observing that it increases the number of fact tables that must be maintained. But note that these fact tables do not introduce any new dimension tables, and they can usually be sourced from transaction-grained stars. And the alternatives have even less appeal.

Rankings don't fit with Transactions

Most of the time, it is not useful to store rankings with transactions. Unless all the things being ranked happen to have transactions that occur at the same time, it will be impossible to compare the ranking with anything -- including other things being ranked!

Resist abstracted models

It is tempting to come up with designs that abstract the concept of a ranking. For example, one can imagine setting up a dimension that describes a ranking, the dimension being ranked, and the period. A fact table can be set up that carries the key for this metadata, plus 10 or 20 keys that will be used to link to top 1, top 2, etc.

Abstracted designs are appealing because they may appear to limit the number of stars to be maintained and don't require schema changes when new rankings are added. But note that the same amount of work must be done to load each ranking.

More importantly, these abstracted designs severely hamper usability. With such a design, it is much harder to merge information about a ranking with other data. A report that contains product level detail, for example, cannot easily pull in info indicating the product appeared in a Top 10 ranking -- this would require decoding metadata and then checking each of the various keys.

Further Reading

As mentioned, some of the challenges related to rankings are similar to those involving distinct counts or period-to-date metrics.  I've covered these topics before:

• Dealing with Period-to-date Measurements (4/18/2009)

• Is a Unique Count a Fact or Dimension?  (3/19/2009)

• Storing Non-additive Facts (3/16/2010)

I also touch on the topic in Chapter 8 of  Star Schema: The Complete Reference, where I address the proper home of a period-to-date measurement.  Use the link to purchase a copy, and you will be helping to support this blog.

Image licensed via Creative Commons 2.0

from Patrick Hoesley

Business Analytics at Work, Almost

Back in February, business analytics briefly broke into the mainstream consciousness thanks to an article in the New York Times Magazine called How Companies Learn Your Secrets by Charles Duhigg.

When I teach, I frequently use an example from this article. I get lots of email asking me for more information, so I figured I might as well post it here.

Breaking Habits

Duhigg's purpose is to show that human behavior is driven by habit. The key to changing behavior is understanding the underlying habits. 

He tells several stories where businesses use this information to drive positive results.

One of his stories happens to touch on predictive analytics.  

The setup is simple: back in 2002, marketers at Target had realized that shopping habits are disrupted with the arrival of a first-born. Bring expecting parents into stores, and you have an opportunity to cultivate new patterns. The result: new lifetime customers for all manners of product--not just baby items.

The key, then, was to identify people who might be pregnant. With the help of a team of statisticians, Target figured out that they could do just that.

Enter Analytics

Duhigg describes how Target tapped into their vast data reserves, and eventually came up with a market basket profile that was a good predictor of pregnancy. This profile could then be applied to their customer base to identify thousands of people who were likely to be expecting.

They were surprisingly effective. Duhigg tells the story of a gentleman berating a store manager for marketing baby products to his teenage daughter:

“She’s still in high school, and you’re sending her coupons for baby clothes and cribs? Are you trying to encourage her to get pregnant?”

The manager didn’t have any idea what the man was talking about. He looked at the mailer. Sure enough, it was addressed to the man’s daughter and contained advertisements for maternity clothing, nursery furniture and pictures of smiling infants. The manager apologized and then called a few days later to apologize again.

On the phone, though, the father was somewhat abashed. “I had a talk with my daughter,” he said. “It turns out there’s been some activities in my house I haven’t been completely aware of. She’s due in August. I owe you an apology.”

The analytics, it seems, did their job. But did they really?

Analytics and Business Strategy

This anecdote went viral when the Times published Duhigg's article. Ethical issues notwithstanding, it offered an updated version of the apocryphal  "beer and diapers" tale from the 1990's.

But there's more here than market basket analysis. This story illustrates a key aspect of predictive analytics: its important to engage with the owner of the business problem. In this case, it seems, that did not happen. Instead, someone made a tactical decision about how to act on analytics.

In this case,

• A business analyst had already identified something important: pregnancy. 
• An analytic modeler took things from there, and developed a way to identify pregnant shoppers. 
• The analyst chose to mail to people identified by the analytic model.

This last step is where the fumble took place. Every analytic initiative must be connected to the owner of the business problem. This person may not be actively engaged in the analytics, but it is her job to decide what to do with the insights. Deciding how to act on analytics is a key part of the process. It involves communication, and requires the attention of executives who are responsible for business objectives.

The Book

Duhigg has now published a book on the same topic. You might want to check it out.  Just remember, this is not a book about analytics -- it's about habits.


The Power of Habit: Why We Do What We Do in Life and Business by Charles Duhigg.  (Random House, 2012)