Get started Bring yourself up to speed with our introductory content.

Expect programming overhead when you pursue dimensional modeling

Dimensional modeling can boost ad hoc reports, but may entail hidden development overhead, a data practitioner told EDW 2014 conference attendees.

Dimensional modeling can bring new capabilities for ad hoc reporting, but it also may entail hidden development overhead, according to a data practitioner helping to create business reports at a large insurance company.

The dimensional model, based on star schemas, is a key part of many modern data warehousing efforts. Reasons why the dimensional path is selected often center on business reporting. Notable here is the notion that data processes using dimensional modeling show benefits in supporting extensibility of queries and schema.

But working with dimensional models every day presents challenges, as David Blackfield told fellow data professionals attending a session about living with dimensional modeling at DAMA International's recent Enterprise Data World 2014 conference in Austin, Texas.

In dimensional models, you seek to bring context, or dimension, to facts, or measures. Blackfield, who is an information analyst with Allstate Insurance Co.'s Information Services Group, said his group created an integrated model that allowed use of common dimensions (such as names, codes and descriptions) across multiple facts (such as data, counts and amounts) to further the organization's ability to learn from data.

From Blackfield's perspective, dimensional models can help fulfill the basic mission behind the data warehouse, which is to provide information that the business can use. To this end, an underlying dimensional model can better enable users to ask ad hoc questions, he said.

The set of challenges that confront people implementing the dimensional model, in his estimation, is led by overhead. Said Blackfield: "There is no such thing as an easy fact table."

What he means is that there is complexity in expanding integrated dimensional models. "If we want to add a new fact table, we have to consider 20 common dimensions to be edited," he said.

For more data management tips

Joshua Greenbaum on new database technology

Forrester's Mike Gualtieri on data management strategy

David Loshin on the data steward role

The overhead incurred is on the development side of the data life cycle. The dimensional environment is more than just modelers doing their thing, Blackfield emphasized. "Somewhere behind that, somebody has to write code," he said. And that can get complicated.

Before, a team could build a new table with less consideration for other outcomes. Now, that table has to be linked, in the Allstate case, to 20 common dimensions. These in turn are connected to an even greater number of sources. Job scheduling and other elements constitute additional risk factors that adding fact tables can uncover.

The added software development time, or overhead, that Blackfield refers to can range from 20% to 50% over entity-relationship (ER) modeling jobs. While noting that this overhead is being reduced as teams gain more experience working with dimensional models, he told new dimensional modelers to anticipate added work along the way.

From a modeling or design standpoint, Blackfield said, adding new fact tables can be easy: "You just draw a line -- done and done." But, he reminds, ETL and other coding activities create overhead you should anticipate when entering the dimensional realm.

Jack Vaughan is SearchDataManagement's news and site editor. Email him at, and follow us on Twitter: @sDataManagement.

Dig Deeper on Data modeling tools and techniques

Join the conversation


Send me notifications when other members comment.

Please create a username to comment.

What is your experience with dimensional modeling? Is there advice you would share?
Over 10 years ago we solved this challenge by purchasing a terrific product from Dimensional Insight.  It does all the work for us.  We just identify the data elements to be included in the multi-dimension model.  Our users love it.