Customer - User Model

View previous topic View next topic Go down

Customer - User Model

Post  jimlad on Wed Mar 30, 2011 5:16 am

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.

jimlad

Posts : 4
Join date : 2011-03-22

View user profile

Back to top Go down

Re: Customer - User Model

Post  Jeff Smith on Wed Mar 30, 2011 9:10 am

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Customer - User Model

Post  jimlad on Wed Mar 30, 2011 9:58 am

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?

jimlad

Posts : 4
Join date : 2011-03-22

View user profile

Back to top Go down

Re: Customer - User Model

Post  Dave Jermy on Wed Mar 30, 2011 10:20 am

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

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Customer - User Model

Post  hang on Wed Mar 30, 2011 6:04 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Customer - User Model

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum