Most recent entry in a given time period

[PSB1]

Hello, I've never posted before, so please let me know if I've done anything wrong, it's too long or I've posted in the wrong place.

I'm trying to generate some information from an OLAP cube. After doing some reading around, I'm sure it's possible but I'm not familiar enough with MDX to do it.

A bit of background:-

A client can call my business any number of times in a given month and get advice. Each client call is a line in the fact table.

Sample Data:-

Create Table TestTable (ID int, ClientId char(1), CallTime datetime, SequenceNumber int, CurrentStatus varchar(10), AdviceGiven varchar(50))

Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (1, 'X', '1 Dec 2008', 1, 'Other', 'Sell House')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (2, 'X', '3 Dec 2008', 2, 'Other', 'Token Payment')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (3, 'X', '24 Dec 2008', 3, 'Other', 'Sell House')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (4, 'X', '3 Jan 2009', 4, 'Other', 'Remortgage')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (5, 'X', '14 Feb 2009', 5, 'Current', 'Sell Car')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (6, 'Y', '12 Dec 2008', 1, 'Other', 'Sell Stereo')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (7, 'Y', '25 Dec 2008', 2, 'Other', 'Remortgage')
Insert TestTable (Id, ClientId, CallTime, SequenceNumber, CurrentStatus, AdviceGiven) Values (8,'Y', '30 Dec 2008', 3, 'Current', 'Bankruptcy')

I have a cube which contains the following dimensions (amongst others):-

-Time (Year Quarter Month Day).

-SequenceNumber. This increments with each phone call a given client makes so we can track how our advice changes over time.

-CurrentStatus. This is a simple Current/NotCurrent dimension which indicates the most up to date line in the fact table for each client.

-AdviceGiven.

Given these dimensions, I have a cube that I can use to answer questions such as:-

-How many times were we called in a given month? e.g. 6 times in December
-How many times did we give each type of advice in a given month? e.g. in December we said 'Sell House' 2 times, 'Token Payment' 1 time, 'Sell Car' 1 time, 'Sell Stereo' 1 time, 'Remortgage' 1 time
-What is the most recent advice we gave to a client? - 1 'Sell Car', 1 'Bankruptcy'
-What was the first piece of advice we gave them? - 1 'Sell House', 1 'Sell Stereo'

However, I also need to be able to ask the question:-

-What is the most recent piece of advice we gave each of our clients in a given month? i.e. for December, 1 'Sell House', 1 'Remortgage.' Ideally I'd like the solution to be flexible enough to ask the same question for end of quarter, end of year etc.

I've come up with the following options:-

-I can write an SQL query against the fact table using MAX(CallTime) WHERE CallTime > StartDate and CallTime < EndDate. However, this removes the versatility of being able to slice and dice using the other (not shown here) dimensions in the cube.
-I could build a second cube containing a snapshot of each client at the end of each month. I don't want to do this as it would limit queries to month end only, rather than the end of any given period.
-I could add another dimension in which I mark the latest entry for each client in a given month, thereby creating the month end snapshot as a subset of the original cube. Again, I don't want to do this as it would limit queries to month end only, rather than the end of any given period.

All of these strike me as inelegant. I'm sure I'm missing something somewhere. I've dabbled around the edges of MDX and done some reading around and am sure it can be done without resorting to any of the options above. However, I desperately need some pointers in how to go about it.

The cube is currently built in Analysis Services 2000. We are moving to SSAS 2005 imminently, but I'm sure I can do it with what I already have.

Many thanks

[Island1]

The MDX time functions exist to do precisely what you seem to want to do - and much, much more. Take a look at the several articles I've written surrounding these functions within my MDX Essentials series here at Database Journal. The index page is here:

http://www.databasejournal.com/article.php/1459531

Take a look at these (which include working Analysis Services 2k examples, conveniently enough), and get back once you have specific questions. I'm sure that a solution will be prettyy straightforward.

Good Luck!

Bill

[Island1]

SELECT ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].DefaultMember) ON 0 FROM [Sales Summary] WHERE ([Measures].[Sales Amount])

This will give the "last populated month," and will need to be altered to your data source / time requirement, etc.

HTH - if you can specify furtther needs, please let us know ...

Bill