Multiple Stars and Conformed Dimensions

The concept of conformed dimensions is central to the discipline of dimensional modeling. This post introduces conformed dimensions; future posts will explore them in more detail. 

In your data warehouse, each star corresponds to a business process. Combining facts from different processes can produce powerful compound metrics. The key to making this work is a set of conformed dimensions.

Conformed dimensions are closely associated with Kimball's "Bus Architecture," but are crucial in any scenario that involves dimensional data.

Multiple Stars

In a dimensional design, each star captures collects measurements that describe a discrete business process.

If we have two measurements that describe different processes, we place them into separate fact tables.

For example, a sales data mart may contain multiple stars:

• Proposal information by Salesperson, Prospect, Product, Proposal and Proposal Date
• Order information by Salesperson, Customer, Product, Proposal, Contract and Order Date
• Shipping information by Salesperson, Customer, Product, Proposal, Contract, Shipment, Shipper, and Shipment Date
• Return information by Salesperson, Customer, Product, Contract, Reason and Return Date

In each of these stars, the fact table will record measurements that describe the processes of issuing proposals, taking orders, shipping product and handling returns.

By recording measurements of each process in a different star, we are able to capture information at the most detailed level possible.  We can study each of these processes, complete with attendant details, by accessing the appropriate star.

Cross-process Metrics

Some of the most powerful measurements actually combine information from multiple processes. These metrics require combining facts from different stars.

In the sales data mart, the ratio of proposals to orders is the "close rate," a powerful indicator that sales managers and executives look at on a regular basis.

Similarly, the ratio of shipments to returns is the "return rate," an essential quality control metric.

Drilling Across

When we compare facts from different stars, we don't simply join the fact tables.  To do so might cause double-counting of some facts.

Instead we follow a process that Kimball calls drilling across.  The drill-across process can be broken down into two phases.  In the first phase, each star is queried, and results are aggregated to a common level of detail.  In the second phase, these result sets are merged based on their common dimensions.

For example, to compute the return rate by product for August of 2011, we do the following:

1. a. Figure out quantity shipped by product for August 2011
   b. Figure out quantity returned by product for August 2011
   
2. Merge these amounts based on the common product names and compute the ratio

We may perform this drill across operation at query time (many BI tools can do this automatically), or we may do it at ETL time, storing the results in a separate star or cube (sometimes called a second-line data mart.) 

Conformed Dimensions

The key to making all this work is the organization of the dimensions.  As you saw in the example above, we used the dimension values to link our results together: product names were used to merge together shipment and return quantities and compute their ratio.  This would not have worked if the product dimensions for Shipments and Returns had been different.

This is the basic idea behind conformed dimensions.  We say that two dimensions conform if they have the same structure and content.  Both our stars, had a product dimension table with a product name attribute, and the product names were specified the same way in each.  Sharing a single physical table is one way to ensure conformance, but it is not required.

Two dimensions can also conform if one has a subset of the other's attributes.  As long as the common attributes have the same structure and content, they are said to conform.

Planning Conformance

By planning a set of conformed dimensions, we ensure that fact tables can be used to compare processes.  This is important within a single data mart, such as the one above, and it is also important when looking across multiple data marts.

Conformed dimensions are the organizing principle in Kimball's architecture.  Conformed dimensions are planned up-front, as a part of a project that establishes a data architecture based on dimensional design. Implementation proceeds once this conformance bus has been planned.

The concept is also important in other architectures. For example, the sales data mart discussed above might be part of Corporate Information Factory architecture.  Within this data mart, conformance guarantees we can compare shipments to returns, proposals to orders, and so forth.

More to come

In the coming weeks, I will post more about conformed dimensions.  We will look at "levels" of conformance, how to document conformed dimensions, and how different tools work with conformed dimensions. If you have my book, I also encourage you to read Chapters 4 and 5, which look at these concepts in detail.

Photo by Agnes Periapse, licensed under Creative Commons 2.0

Dimensional Modelers Do Not Focus on Logical vs. Physical

The separation of logical and physical models is useful entity-relationship modeling. Not so in the world of dimensional models, where final table structures are strikingly similar to business models. 

Instead, a dimensional model is best described in three levels of increasing detail.

Entity-Relationship Modeling and Dimensional Modeling

It is well known that students of dimensional modeling have an easier time if they have not had significant exposure to entity-relationship modeling (or ER modeling).

Entity-relationship modelers are tempted to normalize data, design relationships between dimension tables, and so forth. These steps make perfect sense in the world of OLTP systems design, but not for analytic solutions.

Similarly, when scholars of entity-relationship modeling turn their focus to the dimensional world, another kind of mistake can occur.

Given that it is "all modeling," one may be tempted to generalize a generic framework from ER modeling, and then apply it to dimensional modeling. This mistake is often made by software vendors, and sometimes by academics.

ER modeling and dimensional modeling are both forms of data modeling, but it is not necessary to have a single framework to explain both.

Different Disciplines, Different Frameworks

The traditional distinction between logical and physical model is useful in ER modeling, but not in dimensional modeling. In the world of ER modeling, a logical model captures data requirements without assuming particular data storage technologies. It captures business entities and their attributes and relationships.

The logical model is the basis for a physical model, with reflects the requirements of relational storage in tables and columns. For example, many-to-many relationships are resolved, data may be restructured for performance reasons, etc.

This distinction has significantly reduced utility in the dimensional world. A physical dimensional model stores data in a format that is very similar to how it is understood by the business. Aside from the addition of keys, there is very little difference between logical and physical.

The distinction is not useless, but it plays a much smaller part.  So small, in fact, that you will not find it the focus of most books on dimensional modeling. My own book on dimensional modeling, for example, makes first mention of the term "logical model" in the last two paragraphs of the last chapter.  (See the end of this post for a link.)

Dimensional Models: Three Levels of Detail

Instead of having multiple kinds of model, a dimensional model is best understood at three levels of increasing detail.  Like zooming in on a Google map, each reveals additional information about the model. Each level of detail has different uses for different audiences.

1. Business Requirements
   Requirements are grouped by subject area, correspond to business process, state measurement requirements in terms of facts and dimensions, and cross reference common dimensions.
   
   These business requirements clearly convey scope in business terms.  They link directly to the next level of detail in the model, which exposes the concept of table.
   
   
2. High Level Design
   This level of the model defines the same requirements in terms of fact tables and dimension tables, natural keys an surrogate keys, and exposes major attributes of significance.
   
   At this level of detail, we do not record every column of every table, or even assign data types.  But we do draw table diagrams, and rigorously define several important design elements such as grain, additivity, and slow change requirements.
   
   This level is useful for design reviews, educating users and developers, and describing project activities.
   
   
3. Detailed Design
   At the lowest level of detail, we expose every column of every table, define data types, provide definitions and sample data, map everything back to source data, and document transformation rules.
   
   This level of detail is useful for database administrators and ETL architects. It also contains metadata that will be useful for BI developers and end-users.

One Model, Three Levels of Detail

Don't make the assumption that these three levels of the dimensional model are developed in sequence. It is possible to do so, but not always the case.

Most dimensional designers do the bulk of their work at the second level - the high level dimensional design.

As the model nears completion, they will summarize it at a business level, and then develop the detailed model. In other cases, modelers may work through these levels in sequence.

Regardless of the process, in the end,  these three levels all describe the same dimensional model. Like levels of a Google map, they do so at different levels of detail. All should be accounted for.


For more information on what information to capture at each of these levels, see Star Schema: The Complete Reference.  Detailed descriptions and examples can be found in Chapter 18, "How To Design And Document A Dimensional Model."

Image licensed via Creative Commons 2.0
 from Patrick Hoesley

Allocation Factors Are Not Magic Bullets

Reader interest in bridge tables continues.  Today, I'll address a topic that was raised in the comments: the use of allocation factors.

Allocation factors are often seen as the cure-all remedy to avoid double counting.  Unfortunately, they are not always realistic. And when they are possible, there is often a better solution.

The back-story: double counting

A dimension bridge allows a single fact row to be linked to two or more rows in a dimension.  In a previous post, I showed how this technique allowed two or more salespeople to be linked to a row in an orders fact table.

As I pointed out, the danger is that queries may accidentally count the order dollars repeatedly -- once for each associated salesperson.  This is the danger of double-counting.

The potential solution: allocation

An allocation factor can sometimes be used to avoid this danger. To the bridge, we add a column that indicates the "share" of the facts associated with each group member.  In queries, before we aggregate facts, we apply this allocation factor.

For example, we can add an allocation factor to the salesperson group bridge: 

If two members collaborate on a sale, and split the credit 75/20, there will be two rows in the bridge for their group.  One would contain the group key, the first salesperson key, and a .75 allocation factor.  The other would contain the group key, the second salesperson key, and a .4 allocation factor.  (For sample data, see Figure 9-5 my book.)

Now, when we connect the bridge to the fact table, we can apply the allocation factor:

select sum(order_facts.order_dollars * salesperson_bridge.allocation)

Seems to be the perfect solution, right?  Well, not always....

There must be a business rule

An allocation factor only works where one exists. Do not come up with your own rules -- e.g. "split transactions evenly among group members." This may not reflect how the business studies their key metrics.

Allocation factors are often not part of the business. If an insurance claim is tracked in a fact table, it may not make sense to allocate it against multiple parties. If a fact table represents a insepction event, it may not make sense to allocate it against multiple infractions.

There must be a definitive and consistent business rule that can be used. If there is no business rule, then obviously allocation will not work. Note, too, that they are even less likely to be found in situations that call for an attribute bridge.

There should be a source of record

So you have a business rule?  You are not out of the woods yet.

Next, you need to make sure you have a source of record for the allocation factor.

For example, many systems that support the sale of securities calculate, and split, broker commissions at the time of sale. These systems provide a nice, clean allocation factor.

If you cannot find a source of record, you should think long and hard about using the "business rule." In all likelyhood, it will be subject to exceptions, and may even change over time. 

For example, your business may tell you transactions can be allocated evenly among group participants. You may choose to "hard-code" this rule into your ETL process. But one day, you will encounter a situation where the business wants to "override" this rule for a particular transaction. Or, a new rule is instanitated that one particular salesperson always gets at least 40% of their sales, and so on...

Each time the business changes its mind, ETL rework will be necessary. If you don't have a system of record, think long and hard before hard-coding a business rule.

Consider altering fact table grain

Got a business rule AND a source of record?  Fantastic!  You are very lucky, and you may not even need an allocation factor at all!

When you have a clear source for an allocation factor, consider skipping the bridge altogether. Instead, restate the grain of your fact table as allocated transactions.

In the orders example, we can change the grain of the fact table from "one row per order line" to "order lines allocated to salespeople."

Now, if two salespeople collaborate on a sale, there will be two rows in the fact table.  They will be fully addtitive, with no risk of double counting the facts. No bridge needed!

Note that this works best of the orders are allocated in the source system.  Otherwise, you will wind up with splits that are not even (33/33/34) or fractional quantities that do not total to the same amount as the operational system.

More to Come

My inbox is full of additional questions and comments about bridge tables.  I promise to post some more about bridged designs in the months to come.

In the meantime, I encourage you to check out Star Schema The Complete Reference, which contains two full chapters on bridge tables.  Chapters 9-10 offer a far deeper treatment than I can provide on this blog, with 30 figures, sample instance charts, sample queries and results, etc.

Bridge photo by Branwebs via Creative Commons