MS SQL Server OLAP - Last Status MDX

Author

Last Status MDX

Milind

2005-11-17, 11:23 am

Hi

I have 2 dimensions Person and Answer with two levels "Field" and lower
level "Name".
Now with the below MDX query i get the output

Person Answer
____________________
______
Kimberly Accepted
Kimberly Declined
Tom Enrolled
Kevin NotInterested

MDX Query
___________
with
set [FinalSet] as 'NonEmptyCrossJoin(e
xcept({[Person].[Name].Members},
({[Person].[All Person]})), [Answer].[Name].members,
{[Question].[DMStatus]}, 2)'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&[DMStatus])

I want the Last Answer for that person
The output should be

Person Answer
____________________
______
Kimberly Declined (This is the lamost recent answer)
Tom Enrolled
Kevin NotInterested

I tried lot of options like LastChild, Closingperiod but all in vain.

Please help

Thanks in advance

Milind

Darren Gosbell

2005-11-17, 8:24 pm

I don't really understand the logic of what you are trying to do, it
seems strange that you are looking for the "Last" of something, but
there is no mention of a time dimension.

But that concern aside, you could try something like the following,
using the tail function and a second nested crossjoin. I have not been
able to test this so I hope there are no syntax errors in there.

I have broken out my change so that you can see it easier.

WITH

SET [FinalSet] as 'NonEmptyCrossJoin(e
xcept({[Person].[Name].Members},
({[Person].[All Person]})),

TAIL(NonEmptyCrossJo
in([Answer].[Name].members,{Person.CurrentMember},
{[Question].[DMStatus]}, 1))

)'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&& #91;DMStatus])[color
=darkred]

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <1132243859.351157.260150@g43g2000cwa.googlegroups.com>,
milind.bhabal@gmail.com says...[color=darkred]
> Hi
>
> I have 2 dimensions Person and Answer with two levels "Field" and lower
> level "Name".
> Now with the below MDX query i get the output
>
> Person Answer
> ____________________
______
> Kimberly Accepted
> Kimberly Declined
> Tom Enrolled
> Kevin NotInterested
>
> MDX Query
> ___________
> with
> set [FinalSet] as 'NonEmptyCrossJoin(e
xcept({[Person].[Name].Members},
> ({[Person].[All Person]})), [Answer].[Name].members,
> {[Question].[DMStatus]}, 2)'
>
> Select {[FinalSet]} on rows,
> {[Measures].[Encounter]} on columns
> from DM_Answer
> WHERE ([Question].&[DMStatus])
>
>
> I want the Last Answer for that person
> The output should be
>
> Person Answer
> ____________________
______
> Kimberly Declined (This is the lamost recent answer)
> Tom Enrolled
> Kevin NotInterested
>
> I tried lot of options like LastChild, Closingperiod but all in vain.
>
> Please help
>
> Thanks in advance
>
> Milind
>
>

Milind

2005-11-17, 8:24 pm

Thanks Darren for the response.

Sorry, I do have a time dimension called [ModifiedTime] with levels
[Year], [Month], [Actual Date] in my MDX ( Actually, it got deleted
while I was removing all the unnecessary dimensions for simplicity).

I dont want to show the ModifiedTime in the output but the Answer above
is the "recent" ModifiedDate for that person.

Sorry for the confusion.

Milind

Deepak Puri

2005-11-18, 3:23 am

To take [ModifiedTime] dimension into account, try this:
[color=darkred]
With
Set [FinalSet] as
'Generate([Person].[Name].Members,
Extract(Tail(NonEmpt
yCrossJoin(
{[Person].CurrentMember},
[ModifiedTime].[Actual Date].Members,
[Answer].[Name].Members)),
[Person], [Answer]))'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&& #91;DMStatus])[color
=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Milind

2005-11-18, 1:23 pm

Hi Deepak

I tried the above solution but it simply hangs for a long time in the
MDX Sample app.
If I remove "Generate" in the above MDX, the output is one line

Person Answer Encounter
_________ __________ _________
All Person DisEnrolled 15

Any ideas? Thanks for responding.

thanks

Milind

Deepak Puri

2005-11-19, 3:23 am

Hi Milind,

I created a similar query for Foodmart Sales, where the hanging problem
occurs when the set of persons in Generate is large (about 10,000 total
for Foodmart). One way around this is to work on smaller subsets of
persons, using nested Generate(). In the case of Foodmart Sales, the
following query starts with cities (< 100 with data), then processes all
Customer names under each city in turn:
[color=darkred]
With Set [TestExtract] as
'Generate(NonEmptyCr
ossJoin(
[Customers].[City].Members),
Generate(NonEmptyCro
ssJoin(
[Customers].Children),
Extract(Tail(NonEmpt
yCrossJoin(
{[Customers].CurrentMember},
[Time].[Month].Members,
[Promotions].[Promotion Name].Members)),
[Customers], [Promotions])))'

Select {[Measures].[Unit Sales]} on columns,
[TestExtract] on rows
from Sales
where [Product].[All Products].& #91;Food][color=dark
red]

In your case, it sounds like you have a [Field] level above [Name] that
might work, depending on the number of Fields, and Names under each one,
in the [Person] dimension:
[color=darkred]
With
Set [FinalSet] as
'Generate(NonEmptyCr
ossJoin(
[Person].[Field].Members),
Generate(NonEmptyCro
ssJoin(
[Person].Children),
Extract(Tail(NonEmpt
yCrossJoin(
{[Person].CurrentMember},
[ModifiedTime].[Actual Date].Members,
[Answer].[Name].Members)),
[Person], [Answer])))'

Select {[FinalSet]} on rows,
{[Measures].[Encounter]} on columns
from DM_Answer
WHERE ([Question].&& #91;DMStatus])[color
=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Milind

2005-11-22, 8:24 pm

Thanks Deepak! It worked.

Sponsored Links