Star chart: individual sizes for customers and non-customers, or total size for staff?

Question

I am new to stellar modeling, having just read the data warehouse toolkit .

I have a business process in which clients and non-clients make conference calls with some of our employees.

My fact table, called the “Audience”, will contain information about how long the service person has been connected to the call, as well as the cost of connecting this person to the call. Corn is an "individual connection to a conference call."

Should I use my agreed “Client” dimension and create a non-client dimension (for subscribers who are not yet customers) as follows (without dimensions that are not part of this question):

Or it would be good / better to have an inappropriate Attending dimension associated with a consistent Client dimension as follows:

Or is there a better / standard mechanism for modeling business processes like this?

Edit:

How about using model 2 described above, but creating a view on top of the customer’s dimension table and the companion dimension so that it looks like one dimension?

Is this an acceptable alternative to Damir's answer below?

Answer 1

There is no need to divide customers into two tables (sizes). Just put all customers, active and potential customers in the same size chart. You can then enter the IsActive attribute (column) to distinguish between billing customers and potential customers. Sooner or later, you will use a data mining tool to find out more about customers and what distinguishes people who are willing to pay for your service from those who do not. For the algorithm to work, you need to provide data for both groups of people - those who pay and those who do not pay. To summarize, prospects refer to the same table as payment customers.

However, you can use your model No. 1. Make sure that the measures in the fact table make sense. For example, if call_id = 123 had 10 people, then

sum(cost_of_connection) from factAudience where call_id = 123; 

should return the total cost of the call, and not something meaningless - for example, 10% of the real cost.

EDIT

"Payment client" and "prospect client" are both a type of client, therefore they belong to the same size table - dimClient. Somewhere in DW there is a factSale (or similar) with FK for dimSale. Even if you don’t have a column in dimClient to distinguish between payment and prospects, you can still receive payment customers by joining factSale and dimClient.

"Who is the client?" This is a general discussion of implementing DW in an organization. To be able to analyze the process of acquiring, retaining, converting, etc., Prospects have the same approach as paid customers - at least in DW. Keep in mind that acquiring and creating new customers is at the top of the list of (almost) any CEO.

Answer 2

I would go for the second: it models the participants in their own, separate dimension, allowing you to reveal your client ability (or otherwise) through an attribute in this dimension, which is probably the way you would like ("show me all the participants", and then "and now which of them are customers").

In your client dimension, I would fill in the client_id for all members corresponding to the "unknown" element where the visitor is not a client.

There is a nice discussion here:

http://crpit.com/confpapers/CRPITV75Riazati.pdf

Answer 3

This is not much different. The second version is probably more correct, but does your olap system support this?

Answer 4

The second looks like a “snowflake pattern” to me. Take a look at the snowflake diagram, starting with the Wikipedia article. You will see several comparisons between a star and a snowflake.