fact table as a dimension? Is that bad?

I'm working on my first business process in a dimensional model for a call center. The process we've selected is Quality Assurance; the grain of the fact table is one record for each Quality Category. Another fact we'll be creating is a contact point fact, one record for each time a customer talks to a call agent. These tables have a many-to-many relationship, but in any real world query it will be a one (contact point) to many (quality categories).


At some point business users will want to use information about the contact point to filter and group the quality categories (e.g. failure rate on a category for contact points with duration of 30 to 60 seconds).

My issue is, clearly the contact point table is a fact table as we'll use it to calculate things like an agent's Average Talk time for the month. But in the situation above I'm using it as a dimension table.

Is there something wrong with this? Is there better solution?

If not, what's the best way to join these tables?

The natural key for the contact point table is call agent + start timestamp , the quality category table will link to the call agent dimension as well but will be at too fine of grain to naturally include the start timestamp as a fact. Should I still include it anyway? Would it be considered a Degenerate Dimension?

Another option I can think of is to use a time dimension down to the second (don't think we'll need this for grouping and filtering) and have Foreign keys in both tables for call start time.

Last thing I can think of is to include a surrogate key in the contact point table and actually treat it like a dimension in these situations.

I simply don’t have enough experience to know what the correct option is, or if I’m way off and there’s something I’m missing.

the grain of the fact table is one record for each Quality Category

But your remaining discussion seems to indicate it is not...

Whatever your dimensions are, the practice of combining measures from different fact tables is not using a fact table as a dimension, but rather the normal course of analysis in a dimensional query.

The basic process to combine different facts is to aggregate the facts individually across the comforming dimensions, then combine those sets (join) on the conforming dimensions. If you are using a BI tool, all of them will do this for you.

ngalemmo wrote:the grain of the fact table is one record for each Quality Category

But your remaining discussion seems to indicate it is not...

You're absolutely right, the grain is one record each time a quality category is evaluated. Thanks for calling me on this.

ngalemmo wrote:
Whatever your dimensions are, the practice of combining measures from different fact tables is not using a fact table as a dimension, but rather the normal course of analysis in a dimensional query.

So that goes for wether you creating new calculations with the combined measures or using the measures for filtering and sorting?

ngalemmo wrote:
The basic process to combine different facts is to aggregate the facts individually across the comforming dimensions, then combine those sets (join) on the conforming dimensions. If you are using a BI tool, all of them will do this for you.

So it sounds like I should use a time dimension down to the second? my only concern about this is the Data Warehouse Life Cycle Toolkit (second edition) says " Time of day should be treated as a dimension only if there are meaningful textual descriptions for the periods within the day". (Chapt 6, pg. 255) I can't think of meaningful textual descriptions for periods that break off at the second.

So if I follow that rule I should just use a timestamp for the call start in both tables, but that kind of seems inconsistant with the grain of the (evaluated) quality category table.

Timestamp would be a degenerate dimension (i.e just a value in the fact table), but you would probably have difficulty using it in a join. For example, call ticket number may be a better option.

You may till have a physical time of day dimension table (usually to the minute) if there were groupings, such as hours, 10 minute intervals and such that you wish to include in analysis. Date (without time) it always its own dimension.

Thank you so much. I really appreciate all your advice.