none
How to find the last processed datatime of SSAS cube? RRS feed

  • Question

  • Hi,

    I need to show the SSAS cube last processed datetime on SSRS report and I'm using below query to find the same but recently I've observed from my own testing that this gives you the database last update, which is not the same as the cube last update.

    SELECT  * FROM $System.MDSCHEMA_CUBES

    Will appreciate if someone can provide the exact way to find the SSAS cube last processed datetime.

    Thanx,
    Atul Sharan


    • Edited by Atul Sharan Wednesday, April 17, 2013 1:34 PM
    Wednesday, April 17, 2013 1:33 PM

Answers

All replies

  • With  help from the SSAS/BISM MD team I was told about this link:

    http://blogs.msdn.com/b/mcsukbi/archive/2010/06/01/lessons-learned-deploying-proclarity-6-3-on-sql-server-2008-analysis-services.aspx

    The Management Studio LastProcessed property will show you the server date and time of your box or laptop but the DMV Query, that you are using, and XMLA  a command will show the UTC date and time.

    You can retrive the DMV Query result and use the TSQL DateAdd() function to get your local time right.

    BR

    Thomas Ivarsson

    Wednesday, April 17, 2013 4:38 PM
    Moderator
  • Thomas has given you a solution to get the time using an independent query, which will work fine in Reporting Services.  However, if you need to have things like last processed time, last warehouse load time, latest transaction time etc for querying, it can be useful to have them in the cube.  For example, for last processed time, just create a measure group that has one measure, which is just an SQL statement with GETDATE().  It doesn't need any dimensions.  

    I quite often have a measure group like this, which might be attached to a Branch, Location or Store dimension, which returns the Latest Transaction Time per Branch, Location or Store.  This way, you can see how current the cube is by various sources.  Of course, the cube could do something very similar but a separate, and tiny, measure group is very efficient.

    http://RichardLees.blogspot.com


    Richard

    Thursday, April 18, 2013 3:35 AM
  • Hi Atul,

    In addition, here is a similar thread about this topic for your reference. Please see:
    http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/08fd5f9e-4377-4ab8-8347-9139ad6bd601/

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, April 19, 2013 7:04 AM
    Moderator
  • SELECT CUBE_NAME AS [Cube/Perpective], LAST_DATA_UPDATE,* FROM $System.MDSCHEMA_CUBES
    WHERE LEFT(CUBE_NAME, 1) <> '$' 
    ORDER BY LAST_DATA_UPDATE DESC


    SELECT CUBE_NAME AS [Dimensions], LAST_DATA_UPDATE,* FROM $System.MDSCHEMA_CUBES
    WHERE LEFT(CUBE_NAME, 1) = '$' 
    ORDER BY LAST_DATA_UPDATE DESC

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Friday, August 16, 2019 6:51 AM