Displaying last cube processed time in SSAS

Recently, while browsing a cube excel, an end-user asked me when was it last processed? I had no quick answer and so had to connect to the SSAS server in management studio and check the properties of the cube. This got me thinking on how to expose the cube’s last processed date and time to end users through Excel or similar client tools. This is very helpful to them in that they can quickly check when was the data last added. Think about it like this. A user builds a pivot table report in Excel with a filter at top which shows when was the cube processed. He then copies the pivot table data and sends to his colleague. His colleague checks the numbers and instantly knows that they are based on data from certain date. If he did not have the last processed date, the assumption can be made that the numbers are based on latest set of data which might not be true. So with that in mind, I decided to implement a simple solution to expose when the cube was last processed and here are the steps.

1. Create table in data warehouse which would have just one column storing date and time when the cube processing finished. This table will always have one record.

2. The record in the table above will be updated with latest timestamp after the cube has been processed. This can be done in the ETL.

3. Import this table in the DSV and create a dimension based on it. This dimension will have just one attribute and one member which will the the last time cube was processed. You might want to be careful with naming of this dimension. It should appear either at the top or bottom in fields list in pivot table so that it does not interfere with the analysis.

4. Set IsAggregatable property of the attribute to False. This is to remove the ‘All’ member because there’s always going to be one member in the dimension attributes,

5. Add this dimension in Dimension Usage matrix. You don’t have to worry about attaching it to any measure group.

6. Add the new dimension to the perspectives if you have any and that’s it.Deploy and process the cube.

To test, connect to the cube in Excel, drag the attribute in report filter and it should show when was the cube last processed. Here’s a little screenshot when I implemented this for Adventure Works 2012 cube. And yes, don’t forget to process the dimension while processing the SSAS database.

CubeLastProcessedDateTime

Filter shows when the cube was last processed

Hope that was helpful. Any suggestions are always welcome.

Advertisements

6 comments

  1. I think that instead of creating a table, you can create a view like
    SELECT [CATALOG_NAME], CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES where CUBE_NAME = ‘Mycube’

    Then create a dimension and make it ROLAP, which means it will be queried on the source.
    Just my 2 cents

    1. Interesting but I would think slightly difficult to implement.
      1. Creating a view like this in SQL Server would need adding linked server to SSAS, which is, we all know, painful 🙂
      2. Creating a named query in DSV would need a OLAP connection and I am not sure it supports ROLAP mode.
      But nice idea. I will spend some time investigating if it can be implemented in some other ways.
      Thanks for sharing.

  2. Thanks for the idea. I used a named query, “SELECT GETDATE() AS ProcessDate”. IsAggregatable=False, add the dimension to the cube, and I’m done.

    I think it might be better placed in a junk dimension, if you have one already. Or in my case, I might expose a “CubeParams” dimension, which has a column specifying a filter (earliest date in the fact table to add to the cube), so that I just get a small section of the fact table when processing my cube during development.

    1. Hi there, the steps I took to achieve this are in the post? Is there any particular step you are having trouble with? Also look at some of the comments above which provide alternative implementations.
      Thanks

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