Dimensional Modeling - Queries without facts

Question

I'm creating a dimensional model about a "calls recording system", for a VoIP service. I'll give demonstrate just a little example to show my question.

Suppose I have a fact that represents a single call. And I have a dimension called Client, and another one called Provider. (pretend that there are other dimensions, like Date of course, and etc...)

(Dimension)Client ---> (Fact)Call <--- (Dimension)Provider

With this, i'll be able to see how many calls a client did, or how many calls were sent through a provider, and other questions.

And lets suppose that one client is associated with a provider, and one provider can have many clients.

So, here comes the question. Hhow can I create a query like: What clients each provider has?

It seems to be a query that is just between both dimensions. I cant involve the fact on that, because if a client never used the service, he wont be on the calls fact table, and he wont apper on this "Clients per Provider" query.

I was thinking with myself that one way to do that would be by creating a Role-Playing-Dimension, a view of the Client dimension and add it directly to the Provider dimension, just to do queries like this. It would be something like this:

(Dimension)Client ---> (Fact)Call <--- (Dimension)Provider <--- (Dimension)View Client

Of course, with this approach the user must be very carefull to dont use this View Client dimension with the fact table, because it would duplicate fact rows.

So, is this one of the situations where I need to use the famous factless fact tables?

Whats the right way to do this?

Thanks!

Answer 1

Role-playing dimensions should be used when you are "recycling" a dimension to be used multiple times in the same fact table (i.e Date of Call, Date of Service, etc).

It doesn't sound like that's what you're looking for. Instead, if the relationship is truly one to many, then I would just add the provider ID directly on the client dimension (no need for a view or anything), with the recognition that this relationship has nothing to do with the facts.

Essentially, think of the "provider" as just an attribute snowflaked off of client, when it comes to this sort of query.

However, it sounds like you might want to be sure that you don't have a many to many relationship between Clients and Providers (a client can use multiple providers, and a provider can have multiple clients). A many-to-many relationship is modeled dimensionally as a fact table. Your fact table could be a snapshot of the current point in time, with or without history. Just two columns are needed, Client and Provider. If you wanted to keep a record of the client/provider relationship by some timeframe, you'd just add a date stamp.

Note that a factless fact will work to model the one-many relationship as well (and if the model changes on the back end, your ETL is already done..)