Customer - User Model

[jimlad]

We operate a commercial online business and one customer can have multiple users. It is this customer-user-orders relationship I am having issues modelling.

The business wants to implement two inidivual dimensions, one for Customer and another for User and have the ability to drill across from Customer into User and vice versa. The Orders fact contains both CustomerID and UserID so I have the ability to use either/or/both.

The business also needs to see which Users aren't ordering, this is a very important bit of analysis.

What I've tried so far has failed in SSAS. I've created two dimensions with a bridge table in between the two, linking Customer to User. This works well until I try and find out which Users against a particular Customer are not ordering. When I do this, (by selecting Show Empty Cells in SSAS) I am shown a list of ALL users regardless of the Customer they belong to.

Any help would be greatly appreciated.

[Jeff Smith]

Why do you need a bridge table between the customer and user dimensions?

If you need to see which users aren't ordering you could create a fact table with all users and the total number of orders by each user for a specified period of time (prior 12 months). Or you could add some type of indicator on the user Dimension to denote Active or In Active Users based on some business rule. Another way is to put a "Date of Last Order" on the User dimension.

[jimlad]

What I'm trying to do is create a hierarchy across two dimensions. Customer drill across to User just to do simple analysis on customer orders, then drill into which users are placing the most orders etc.

What I've implemented so far enables most of this apart from the 'which customers aren't ordering' question. From a user perspective, they'd prefer to see a blank or zero against the users that weren't ordering, but I don't want to have to create a fact to hold these exceptions.

Is there a way to create a hiearchy between dimensions without duplicating any attributes in both dimensions?

[Dave Jermy]

You would need to get your hands dirty with MDX to achieve what you want. In particular, judicious use of the NONEMPTY function.

For an example of something similar: Counting New and Returning Customers in MDX

[hang]

From dimensional modelling perspective, what you have is almost right. In dimensional modelling, such a table that contains the Customer-User relationship is normally called Coverage Fact, and considering that the relationship will change over the time, you may need a date key in the table.

I guess it would not be too hard to create a fact view showing the customers and users based on the two fact tables, order fact and customer-user fact, by using NOT EXISTS clause. Having this fact view in place, all the required measures you mentioned should be fairly straightforward.

[Sponsored content]