Three ways to drill across

Previous posts have explored the importance of conformed dimensions and drilling across.  This post looks at the process of drilling across in more detail. 

There are three primary methods for drilling across.  These methods can be leveraged manually, automated with BI software, or performed during the data integration process.

Querying multiple stars

Conformed dimensions ensure compatibility of information in various stars and data marts; drilling across is the process of bringing it together.  (For a refresher, see this post.)
 
For example, suppose we wish to compute "Return Rate" by product.  Return rate is the ratio of shipments to returns.

Information about shipments and returns is captured in two granular star schemas:

• shipment_facts captures shipment metrics by Salesperson, Customer, Product, Proposal, Contract, Shipment, Shipper, and Shipment Date
• return_facts captures return metrics by Salesperson, Customer, Product, Contract, Reason and Return Date

Reporting on return rate will require comparing facts from each of these stars. 

Drilling across

Recall that fetching facts from more than one fact table requires careful construction of queries. It is not appropriate to join two fact tables together, nor to link them via shared dimensions. Doing so will double-count facts, triple-count them, or worse.

Instead, the process must be completed in two phases.

• Phase 1:  Fetch facts from each fact table separately, aggregating them to a common level of detail
• Phase 2:  Merge these intermediate result sets together based on their common dimensions

In practice, there are several ways that this task can be performed.

Method 1: Issue two queries, then merge the results

The first method for drilling across completes phase 1 on the database, and phase 2 in the application (or on the application server).

1. Construct two separate queries: the sum of quantity shipped by product, and the sum of quantity returned by product.
2. Take the two result sets as returned by the DBMS, and merge them based on the common products. Compute the ratio at this point.

While it may seem odd that phase 2 not be performed on the DBMS, note that if the data sets are already sorted, this step is trivial.

Method 2:  Build temp tables, then join them

The second method performs both phases on the DBMS, making use of temporary tables.

1. Construct two SQL statements that create temporary tables: the sum of quantity shipped by product, and the sum of quantity returned by product
2. When these are completed, issue a query that performs a full outer join of these tables on the product names and computes the ratio.

Be sure that the temporary tables are cleaned up.

Method 3: Join subqueries

Like the previous method, this method performs all the work on the DBMS.  In this case, however, a single query does all the work.

Queries for each fact table are written, then joined together in the FROM clause of a master query.  For example:


SELECT
  COALESCE (shp.product, rtn.product) as Product,
  quantity_returned / quantity_shipped as ReturnRate
FROM
  ( SELECT product, sum(quantity_shipped)as quantity_shipped
    FROM shipment_facts, product
    WHERE .....
  ) shp
FULL OUTER JOIN
  ( SELECT product, sum(quantity_returned) as quantity_returned
    FROM return_facts, product
    WHERE....
  ) rtn
 ON
    shp.product = rtn.product
   
The two subqueries in the FROM clause represent phase 1.  Phase 2 is represented by the main SELECT query that joins them and computes the ratio.

Applying these techniques

These techniques may applied in a variety of ways:

1. Report developers may write their own queries using one of more of these methods
2. You may have BI software that can automate drilling across using one or more of these methods
3. Drilling across may be performed at ETL time using one of these methods (or an incremental variant)

In the latter case, the ETL process builds a new star (or cube) that contains the result of drilling across. This is called a derived schema, or second line data mart.

Learn More

For more information, see the following resources:

• Multiple Stars and Conformed Dimensions (August 15, 2011)

• Conformed Dimensions (November 15, 2011)

Many pages are devoted to this topic in my books. In the latest one, Star Schema: The Complete Reference, the following chapters may be of interest:

• Chapter 5, "Conformed Dimensions" discusses these techniques in greater detail.  

• Chapter 14, "Derived Schemas" looks at special considerations when creating derived stars that pre-compute drill-across comparisons.

• Chapter 16, "Design and Business Intelligence", discusses how to work with SQL-generating BI software.

More to come on this topic in the future.  If you have questions, send them in.

-Chris

Image by Patrick Hosely via Creative Commons 2.0

Conformed Dimensions

This second post on conformed dimensions explores different ways in which dimensions can conform. 

There are several flavors of conformed dimensions. Dimensions may be identical, or may share a subset of attributes that conform.

Conformance basics

Conformed dimensions are central to the discipline of dimensional modeling.  The basics of conformance were introduced in a post from earlier this year.  In a nutshell:

• Measurements of discrete business processes are captured in individual star schemas (e.g. proposals and orders)

• Some powerful business metrics combine information from multiple processes.  (e.g. fill rate: the ratio of orders to proposals)

• We construct these metrics through a process called drilling across

• Drilling across requires dimensions with the same structure and content (e.g. proposals and orders have a common customer dimension)

For a refresher on these concepts, see "Multiple stars and conformed dimensions" (8/15/2011). 

Physically shared dimensions not required 

When two fact tables share the same dimension table, their conformance is a given. Since the shared dimensions are the same table, we know they will support drilling across.

For example, stars for proposals and orders may share the customer dimension table.  This makes it possible to query orders by customer and products by customer, and then merge the results together.

But this process of drilling across does not require shared dimension tables. It works equally well if proposals and orders are in separate data marts in separate databases.

As long as the stars each include dimensions that share the same structure (e.g. a column called customer_name) and content (i.e. the customer values are the same), it will be possible to merge information from the stars. 

Levels of conformance 

It is easy to take this a step further.  We can also observe that there is compatibility between dimensions that are not identical.

If a subset of attributes from two dimension share the same structure and content, they form a sort of “lowest common denominator” across which we can compare data from the stars.

For example, suppose we establish budgets at the monthly level, and track spending at the daily level.  Clearly, days roll up to months.  If designed correctly, it should be possible to compare data from budget and spending stars by month.

The picture below illustrates the conformance of a MONTH and DAY table graphically.  The ring highlights the shared attributes; any of these can be used as the basis for comparing facts in associated fact tables.

In this case, the two conformed dimensions participate in a natural hierarchy.  Months summarize days. The month table is referred to as a “conformed roll-up” of day.

To successfully drill across, the content of the shared attributes must also be the same.  Instances of month names, for example, must be identical in each table -- "January" and "January" conform; "January" and "JAN." do not.

To guarantee conformance of content, the source of the rollup should be the base dimension table. This also simplifies the ETL process, since it need not reach back to the source a second time. 

Other kinds of conformance 

Identical tables and conformed roll-ups are the most common kinds of conformed dimensions.  Other kinds are less common. 

Degenerate dimensions (dimensions that appear in a fact table) may also conform. This is particularly useful with transaction identifiers. 

Overlapping dimensions may share a subset of attributes, but not participate in a hierarchy. This is most common with geographical data. 

More Info 

For more information, see the following posts: 

• Multiple Stars and Conformed Dimensions (August 15, 2011) 

• Q and A: Degenerate Dimensions, ETL and BI (October 15, 2010)
• Creating transaction identifiers for fact tables (October 17, 2011)  This post addresses degenerate dimensions and transaction identifiers

I also write about conformed dimensions extensively in Star Schema, The Complete Reference.  

If you enjoy this blog, you can help support it by picking up a copy!

Photo by Agnes Periapse, licensed under Creative Commons 2.0

Creating transaction identifiers for fact tables

Sometimes, the source data for a fact table does not include a transaction identifier. When this happens, we often crate our own. A reader asks if this goes against best practices:

Q: You said that we should not create surrogate keys in fact tables. But we have a source system that does not provide us with identifiers for phone calls. When we get the log files, we create surrogate keys for each call before loading them into the star.

A: This is a common practice, and it is just fine.

You are not creating what dimensional modelers call surrogate keys. You are creating transaction identifiers.  These will be helpful as part of the ETL and QA processes.

Surrogate keys vs. transaction identifiers

Surrogate keys should not be confused with transaction identifiers.

As I wrote in the post referenced in the question, a surrogate key is an attribute that is created to uniquely identify rows in dimension tables. It does not come from a source system; it is created expressly for the dimensional schema.

A transaction identifier is a dimension attribute in a fact table that defines its grain, or at least helps to do so. (If you need a refresher on grain, see this post.)

For example, PAYROLL_FACTS may have the grain "one row per pay stub."  Each pay stub has an identifying number in the source system, which is carried into the star as a degenerate dimension. This attribute is a transaction identifier.¹

When a transaction identifier defines grain, the DBA may define it as "the primary key of the fact table." Likewise, an ETL developer or QA analyst may use it as a sort of key to compare the star to operational data or staging data. 

From the dimensional perspective, however, these transaction identifiers are dimensions.  They can be used to filter queries, group or sort the results, and so forth. They will not appear in other tables as foreign keys that reference the fact table.

Creating transaction identifiers

When operational systems do not have good transaction identifiers, we often "cook one up" during the ETL process. They are not surrogate keys in the dimensional modeling sense, though the term might sound appropriate. They are transaction identifiers.

For example, suppose you are designing a star that records phone calls made from your call center. You want the grain of the fact table to be  "one row per phone call."  The data will come from your call switch.  As you've mentioned, it creates a log file, but does not provide an identifier for each call.

In your design, you may define your grain: "one row per date, time, originating number and number dialed." This works just fine, but may be difficult for ETL developers to manage.  Instead, they assign a unique identifier to each call in the log. You can use this identifier to define the grain of the fact table.

Carried into the fact table, this attribute aids in the data integration and quality assurance process. It can be used to quickly crosscheck the star against the log file.

However, for analytic purposes, it is not used as a key.  It is a dimension attribute -- a "cooked up" transaction identifier.

Other concerns

When you are forced to create your own transaction identifiers, you will have other things to worry about.

First and foremost, you must be sure there is a mechanism to prevent loading of duplicate facts. You will need to understand how the source system generates its log files, to be sure that you extract data that is complete, accurate, and not subject to revision.

Also, note that the artificial identifiers may convey a false sense that you can reach all the way back to the source to find the original transaction. While it may be possible, your identifier will not be the mechanism.

Thanks for the question, and keep them coming.

-Chris

More Info

For more information, see these posts:

• Avoid surrogate keys for fact tables (September 21, 2011)
• Degenerate dimensions, ETL and BI (October 15, 2010)
• Rule 1: State your grain (December 9, 2009)

Also check out my book, Star Schema: The Complete Reference.  I discuss surrogate keys in Chapter 1, Analytic Databases and Dimensional Design.  Degenerate dimensions and transaction identifiers are covered in Chapter 3, Stars and Cubes.

¹Not all degenerate dimensions are transaction identifiers.  For an example that is not a transaction identifier, see my post of October 15, 2010.

Image licensed via Creative Commons 2.0
 from Patrick Hoesley