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 but I'm not sure it's built to give the information I need.

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'

I've come up with the following options:-

-I can write an SQL query against the fact table using MAX(CallTime) WHERE CallTime > StartOfMonth and CallTime < EndOfMonth. 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 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.

However, all of these strike me as inelegant. I'm sure I'm missing something somewhere. I've dabbled around the edges of MDX and was wondering if it is something I could use it for as I feel it is a relatively straightforward question to be asking of the data.

The cube is currently built in Analysis Services 2000. We are moving to SSAS 2005 imminently, but I have not had a chance to investigate its new features. Is there anything I can do relatively easily given the current cube design? Should I implement one of my three options, is there something else I can do that I've completely missed or is there something I could do once we move to 2005?

Many thanks

[dwbi_rb]

Based on the data set example that you have given, you could possibly try out the following query :

select *
from testtab
where SequenceNumber in (select MAX(tab1.SequenceNumber)
FROM TESTTAB tab1
GROUP BY tab1.CLIENTID
, TO_CHAR(tab1.CALLTIME, 'MON-YYYY'))

However, this will not work if it is possible for the client to make two separate calls within the same month for two different issues, for whcih different sequence of advice is given. If that is not a restriction probably you could use the above. However, I am not sure with the syntax on SQL Server. The above is based on Oracle syntax. You may/may not have to change it accordingly.

[Joy]

I feel like I'm missing something... why can't you just use your mdx query for "What is the most recent advice we gave to each customer?", and add a filter condition "Where year-month = 2009-01"?

[PSB1]

The "Most recent advice" dimension relates to the most recent entry in the cube regardless of calendar date. So, for example, if a client spoke to us three times in January and once in February, selecting the "Most recent advice" = "Current" would bring back the February data, whereas on this occasion we need to see the most recent of the three advices in January.

I'm afraid at the moment I'm pretty much limited to building MDX via the Proclarity GUI, but I've come to the conclusion that I'm going to have to dive in and write something myself

[Sponsored content]