SSAS MDX query

#MDXMonday: Finding the Current Day

This week I’m teaching the Pragmatic Works Intro to MDX virtual training class. A student in the class asked how they could find the current day sales amount using MDX (no SSAS functionality) and I thought this was a worthy blog topic. This solution assumes that the cube is processed at least once a day as the query you’re about to see returns the last day in the cube that we data for.

View previous posts in the #MDXMonday series

The first part is where the most work takes place. I created a named set to identify

the last day in the cube that has a Reseller Sales Amount.

WITH SET [Current Day] AS
   {
     Tail (
       NonEmpty ( [Date].[Calendar].[Date].Members, [Measures].[Reseller Sales Amount] ),
       1
     ).Item ( 0 )
   }

Reading the MDX from the inside out, I first use the NonEmpty function to identify only days in our date dimension that have a Reseller Sales Amount. So as long as we process the cube nightly, the last date in the set returned should be the latest day. Then I use the Tail function to return the last member in the set, which will be the last day.

Using this new set I created, called Current Day, I can then build calculations.

WITH SET [Current Day] AS
{
Tail (
NonEmpty ( [Date].[Calendar].[Date].Members, [Measures].[Reseller Sales Amount] ),
1
).item(0)
}

MEMBER [Measures].[Current Day Sales Amount] AS

Aggregate ( [Current Day], [Measures].[Reseller Sales Amount] )

MEMBER [Measures].[Current Day Order Qty] AS

Aggregate ( [Current Day], [Measures].[Reseller Order Quantity] )

SELECT { [Measures].[Current Day Sales Amount], [Measures].[Current Day Order Qty] } ON 0

FROM [Adventure Works]

Here I created a couple calculated measures: Current Day Sales Amount and Current Day Order Qty. Here is the result from the query:

SSAS MDX find current day

I hope you found this useful!

Resources

MDXpert.com is a great site with references and examples for MDX functions and expressions.

Learn more about the MDX Nonempty() function here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s