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

By Phil Steffek

Phil Steffek is a professional sumo wrestler who is hoping to represent the U.S. in the next Olympics. During the off season Phil is a Data Architect working on transactional databases, data warehouse implementations, and all aspects of business intelligence. Also, Phil is not really a sumo wrestler. You can get to know a bit more about my non-sumo doings by checking out my LinkedIn Page

Leave a comment

Your email address will not be published. Required fields are marked *