Show Cube runtime in report

Question

Is there a way to show the last time a cube or dimension was last processed in a report (I use Report Builder)?

I tried to do this by starting to create a table called LastProcessTime with the “Type” and “DateTimeProcessed” fields, and I could insert into this table, but I don't know how to start Insert initialization. Perhaps there is a completely different approach. Thanks.

Answer 1

Not sure if you can add this to the report builder, but try the standard MDX report and use DMVs SSAS (Dynamic Management Views):

http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

Run this in the MDX query box against the cube (I know this looks like TSQL):

SELECT * FROM $system.mdschema_cubes 

Should you provide you with what you need?

Answer 2

A bit late, I know, but you can use custom stored procedures in SSAS to expose this information through regular members.

 with member [Measures].[LastProcessed] as ASSP.GetCubeLastProcessedDate() select [Measures].[LastProcessed] on 0 from [Your Cube] 

They are available from CodePex: Analysis Services Stored Procedure Project ,

 /*============================================================================ File: CubeInfo.cs Summary: Implements a function which returns the date when the current cube was last processed. Date: July 12, 2006 ---------------------------------------------------------------------------- This file is part of the Analysis Services Stored Procedure Project. http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. =========================== =================================================*/ using System; using System.Collections.Generic; using System.Text; using Microsoft.AnalysisServices.AdomdServer; using Microsoft.AnalysisServices; //reference to AMO namespace ASStoredProcs { public class CubeInfo { //the assembly must be registered with unrestricted permissions for this function to succeed [SafeToPrepare(true)] public static DateTime GetCubeLastProcessedDate() { string sServerName = Context.CurrentServerID; string sDatabaseName = Context.CurrentDatabaseName; string sCubeName = AMOHelpers.GetCurrentCubeName(); DateTime dtTemp = DateTime.MinValue; Exception exDelegate = null; System.Threading.Thread td = new System.Threading.Thread(delegate() { try { Microsoft.AnalysisServices.Server oServer = new Microsoft.AnalysisServices.Server(); oServer.Connect("Data Source=" + sServerName); Database db = oServer.Databases.GetByName(sDatabaseName); Cube cube = db.Cubes.FindByName(sCubeName); dtTemp = cube.LastProcessed; } catch (Exception ex) { exDelegate = ex; } } ); td.Start(); //run the delegate code while (!td.Join(1000)) //wait for up to a second for the delegate to finish { Context.CheckCancelled(); //if the delegate isn't done, check whether the parent query has been cancelled. If the parent query has been cancelled (or the ForceCommitTimeout expires) then this will immediately exit } if (exDelegate != null) throw exDelegate; return dtTemp; //return Context.CurrentCube.LastProcessed; //this doesn't work because of a bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124606 } . . .