Home > MDX Challenges > MDX #20 – Last Data Date

MDX #20 – Last Data Date

Need to check the last time when our ETL processes ran successfully

Data warehouse developers routinely query the last data date (the last date when we have fact data) to check the last time when our ETL processes ran successfully.

It’s not hard to do it in TSQL. It just requirements a bit of typing. In this example, I am showing the last data date in its readable format and the Sales Amount on that day.

clip_image001

A lot easier to do this in MDX

It’s actually a lot easier to do this in MDX. With the help of the TAIL() function and the NONEMPTY() function, the last data date is easy to query without any sub-queries or calculations.

clip_image002

Always verify

To make sure we got it right, run this simple query to verify.

clip_image003

Note: There is no need to order by the Date dimension. It’s already ordered in the SSAS.

About these ads
Categories: MDX Challenges Tags: ,
  1. Drew
    February 8, 2013 at 8:56 pm

    Nice demo. I had a project requirement a while back to include the cube last processed time in a dashboard on a SharePoint site (the cube was processed several timers per day). We leveraged the Analysis Services Stored Procedure Project cubeinfo class to get the info and displayed in using Excel Services

    Like

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 168 other followers

%d bloggers like this: