CODEWITHSUNDEEP
sql-server-2008mdxssas-2008
Jacques
Jacques

Reputation: 143

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?

Upvotes: 0

Views: 249

Answers (1)

whytheq
whytheq

Reputation: 35605

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];

Upvotes: 1

Related Questions