I'm looking for some guidance on how to approach an MDX query. My situation is that I have sales occuring, which make up the grain of the fact table, and are measures. I have a products dimension and a customer dimension. I also have a date dimension and a time dimension, I made them seperate to keep member counts low on the dimensions.
The query I'm trying to write, is one that asks for the first and last purchase, per customer per product. So, an example result set may look like:
Car - Bob - 2008-12-10 - 15:39 - 2008-12-11 - 16:44
Car - Bill - 2008-12-12 - 09:16 - 2008-12-12 - 09:16
Van - Jim - 2008-12-11 - 14:02 - 2008-12-12 - 22:01
So, Bob bought two cars, and we have the first and last purchases, Bill bought one car so the first and last purchases are the same, Jim may have bought three vans but we only show the first and last.
I've tried using TAIL, but can't seem to get the sets correct to show the last purchase per customer. Even then, expirements with HEAD for the first purchase showed I couldn't use the same dimension twice on the same axis. It's also made harder by the fact that there may be several purchases per day, so the query I need is the last time for the last date for each customer for each product, and the first time for the first date for each customer for each product.
I'm not neccesarily asking for an exact query answer, although that would help, but I am interested in the approach and best methods to use. The platform is SQL Server Analysis Services 2005.