[SOLVED] MDX MAX MEMBER_KEY return wrong result

MDX MAX MEMBER_KEY return wrong result

I am trying to determine the max dimension key currently processed by the cube so that I can write a view which will return only new data for a Process Add operation.

I came across a couple of links all showing almost the same thing. I changed these examples a bit and successfully determined the max date key within my date dimension.

WITH MEMBER [Measures].[MaxKey] AS
    MAX([Dim Date].[Date SK].ALLMEMBERS
    ,STRTOVALUE([Dim Date].[Date SK].CURRENTMEMBER.MEMBER_KEY))
SELECT
    {[Measures].[MaxKey]} ON 0
FROM
    [PGL DW]

As expected the results of the above query is 20170730

When I change this query to execute against my Agent dimension it returns the incorrect value.

WITH MEMBER [Measures].[MaxKey] AS
   MAX([Dim Agent].[Dim Agent Key].MEMBERS
   , [Dim Agent].[Dim Agent Key].CURRENTMEMBER.MEMBER_KEY)
SELECT
   {[Measures].[MaxKey]} ON 0
FROM
   [PGL DW]

The value return by the above query is "-6" which is incorrect.

If I list all Dim Agent Member Keys using the below query I get values of 100000+

WITH
MEMBER [Measures].[Dim Agent Key] as [Dim Agent].[Dim Agent Key].Currentmember.Member_Key
SELECT {Measures.[Dim Agent Key]} ON axis(0),
[Dim Agent].[Dim Agent Key].Members on axis(1)
FROM [PGL DW]

Query results

The Dim Agent Key attribute is the dimension's key attribute with it's KeyColumn set to Dim Agent.Dim_AgentKey (Integer).

Any idea why the query is returning the incorrect results when querying the Agent dimension?

Solution

If you find the maximum of the MemberValue rather than the key do you still get -6?

WITH MEMBER [Measures].[MaxKey] AS
   MAX([Dim Agent].[Dim Agent Key].[Dim Agent Key].MEMBERS
   , [Dim Agent].[Dim Agent Key].CURRENTMEMBER.MEMBERVALUE)
SELECT
   {[Measures].[MaxKey]} ON 0
FROM
   [PGL DW];