Counting Customer Transactions

I have a question I'm hoping someone will be able to help me with - I've been working on it for a while and can't seem to find an obvious solution.

Basically I have a fairly standard transaction fact table, and customers may have made multiple transactions over the selected range.

I need to know how many customers made 1 order, 2 orders, 3 orders, etc. As far as I can tell, I'm effectively doing a count of a count.

It seems to me to be a fairly straightforward request, however I can't work out a way to do it in Analysis Services. If someone can help me out or point me in the right direction I would be very grateful

Thanks,
Matt

is the selected range fixed? if yes, you could create a set for this purpose.

If not, which i believe is the case, you can rely on the tool you're using as interface to do the filtering for you...

if you're writing an mdx query, did you try doing it in the where condition ?

|||

Hi Christina

Thanks for your response. You're right, the range isn't fixed, although I guess it probably could be for most purposes. Could you please give me an example of how you would do this with a set? I haven't had to use them yet.

The frontend we are using is web-based Dundas Charts for OLAP, and there is no provision for writing mdx queries. Ideally it would appear in the interface as just another attribute in my Customer dimension. Any other thoughts?

Thanks again for your help,

Matt

|||

i don't know if this is a good solution:

since you mentioned that it would appear as another attribute, then maybe you should create a dimension with the customer and the values per range. but the ranges should be predefined. you would lose the flexibility

this dimension would be based on a view in the DW based on this fact table. it should contain the counts and have its structure as such:

customer CountRange1 CountRange2 CountRange3 CountRange4...etc..

where the ranges are not overlapping, however they are the most used ranges. better if you have only 1 range, because on dundas, u'll have to add all these fields to use them as filter

i will think of another alternative and let you know..