Blog

Get The MAX Date From A Cube Using MDX

Here's the goal: populate a sql variable with the max date from one of the dimensions in a cube. It takes a little bit of unusual code but it's relatively simple so pay attention! It's probably important to note this code is assuming you are using a smart key for your date dimension (e.g. the key 20130820 corresponds to 08/20/2013)

First things first: Get your MDX working. The following MDX will provide the max value from the adjustment date dimension.

WITH MEMBER [Measures].[Max Key] AS
	MAX(
		[Adjustment Date].[Adjustment Dt].Members
		, [Adjustment Date].[Adjustment Dt].currentmember.MEMBER_KEY
		)
SELECT	{
		[Measures].[Max Key]
		} ON COLUMNS
FROM SampleCube
See what I did here? There was a reference to the MAX function so...

See what I did here? There was a reference to the MAX function so...

Once you've got that working against your cube the next step is to run the MDX via a linked server so you can pull it into your stored proc. (Uh, obviously you need to have the linked server to your cube setup in order for this to work.) Also! you must set QUOTED_IDENTIFIER ON! This will allow you to select the specific column name into the variable.

 SET QUOTED_IDENTIFIER ON

 DECLARE @MaxDate VARCHAR(20)

 SELECT @MaxDate = "[Measures].[Max Key]"  --set quoted_identifier must be off for this to work
 FROM   OPENQUERY(SampleCubeLinkedServer, '
WITH MEMBER [Measures].[Max Key] AS
	MAX(
		[Adjustment Date].[Adjustment Dt].Members
		, [Adjustment Date].[Adjustment Dt].currentmember.MEMBER_KEY
		)
SELECT	{
		[Measures].[Max Key]
		} ON COLUMNS
FROM SampleCube')

 SELECT @MaxDate

That's it. Short and sweet.

But, Phil! Why would you want to select this data from the cube instead of from the underlying table or view? Well, I'm glad you asked! In the specific scenario we were dealing with there were frequent situations where the underlying data had been updated but the cube had not yet been processed. Thus the dates were frequently out of sync between the cube and the data warehouse.

But, Phil! How did you get so smart at MDX? Well, I'm glad you asked. Lots of trial and error and my little friends Google and StackOverflow. The majority of this little nugget came from the StackOverflow post: MDX Get MAX MEMBER_KEY from Dimension

Tags: , , , , ,

No comments yet.

Leave a Reply