SQL Server Analysis Services Cube Processing Status Report

By:   |   Comments (21)   |   Related: > Analysis Services Administration


How can I generate a SQL Server Analysis Services (SSAS) cube status report using SQL Server Reporting Services (SSRS)?


As you begin to work more with SSAS and create more cubes, you often will need to let end users know if a cube is in a processed or an unprocessed state. Similarly you may also want to convey the date and time when the cube was last processed. I can also envision instances where you would want to see similar information about dimensions or measure groups. One way we can use SSRS is to generate an Analysis Services cube status report that provides us with these details. This tip continues our series on various Administrative Intelligence reports. These reports help you to get your job done and hopefully provide you with a basis for ideas on customizing and expanding these base reports to fit the needs of your current organization.

You can use one of two query methods to obtain the needed information for our cube status report. One method is to use the SSAS DMVs. Alternatively you can use the Analysis Services Stored Procedure Project, which has a dll that must be separately installed. This addin, called ASSP.dll is available from Codeplex (https://asstoredprocedures.codeplex.com/ ) and actually provides a rich and expansive list of query methods including cube and dimension status details. We will use both methods in this tip to show you how to get the main data for our report. The report itself for this example will use the ASSP.dll.

We will use the 2014 versions of the AdventureWorks regular and data warehouse databases which are available on Codeplex: https://www.microsoft.com/en-us/download/details.aspx?id=49502. Once these sample databases are downloaded and installed, we subsequently create several "copies" of SSAS database to simulate a multi-cube environment. Finally, to generate our report, we will use the SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop our report. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

SSAS Cube Status

Our first method of getting the cube status details is to use the SSAS DMVs which provide a "SQL Like" method of querying various SSAS cube properties. Specifically we will use the $system.mdschema_cube view to get our cube status (along with a few other pieces of information. These views are actually DMX queries which stand for data mining extensions (that is not a typo and should NOT be MDX). These management views actually grew out of the data mining module of SSAS.

[CATALOG_NAME] AS Database_Name
,[CUBE_NAME] AS CubeOrPerspective_Name
,[CUBE_CAPTION] AS CubeOrPerspective_Display_Name
,[LAST_DATA_UPDATE] AS Last_Process_Date
,[BASE_CUBE_NAME] AS Base_Cube_Name --if blank then Cube else Perspective
FROM $system.mdschema_cubes

The above query is certainly quite, short but actually provides us with quick details about our cube. Let us review the various parts of the query.

  • The CATALOG_NAME line returns the SSAS database that we are querying
  • The CUBE_NAME is the name of the cube or the underlying perspectives for the database in being queried
  • The CUBE_CAPTION specifies the "display" name for the cube or perspective
  • The LAST_DATA_UPDATE is the last process date for the cube
  • The BASE_CUBE_NAME shows the parent cube for any row listed that is a perspective. If this field is blank, then that signifies that the field is actually the root or base cube (or could also be a dimension if we were showing dimensions)
  • The CUBE_SOURCE in the SELECT and WHERE clauses tells the query to only return CUBE_SOURCES=1; a cube source of 1 signifies a Cube or Perspective. A cube source of 2 signifies a dimension.

This query creates the following results.


These results are handy, but this method does come with some caveats that must be noted. The biggest of which is if the cube (or dimension) is in an unprocessed state, it will not be returned in the query results. As shown below, in the PNL database two cube exists, but only 1 cube appears in the query results. That could be problematic if we want to show both processed and unprocessed objects. Also, the query only shows the cubes in the "currently selected" database.

Only 1 cube showing up

Installing the Analysis Services Stored Procedure (ASSP) Project Addin

Even so, the ASSP.dll or Analysis Services Stored Procedure (ASSP) Project addin comes to our rescue. This dll opens up several methods of interrogating an SSAS database in order to return items such as last processed date and object status. In order to use the ASSP tools, we first need to install the dll on each server we want to use the addin. There are multiple download and install methods to get the dll installed on your server. You can download an XMLA version of the project and then use XMLA in Management Studio to install, as shown below.

XMLA Install

You will see the below results if the install was successful.

XMLA Success

Alternately, you can install the dll by adding the assembly using the SSMS Register Assembly wizard. To complete this task, you right click on the Assembly folder in SSMS and then click New Assembly. Next, using the ellipse button to the right of the File name textbox, we navigate to where we downloaded the dll. We have to select Unrestricted under permissions and Default under Impersonation.

New Assem

Register DLL

For some machines, we also may need to Unblock the dll by right mouse clicking on the file ASSP.dll and then selecting properties. On the General tab, shown next, you will want to click Unblock button.


The end result of this second method is the same as the first method and is illustrated below.


Using the Analysis Services Stored Procedure (ASSP) Project Addin

Now that we added the ASSP dll we can use it to expose the information we need for the report. The ASSP exposes several functions and methods that can be used to retrieve property information concerning an SSAS cube and its respective objects. We will specifically use the DiscoverXMLMetaDataFull function to request the status and last process data. The full syntax of our query will be:

CALL ASSP.DiscoverXmlMetadataFull("Database\Cubes\Cube|Name,State,LastProcessed","").
  • The CALL key word initiates the query
  • Next the ASSP.DiscoverXmlMetadataFull words request the ASSP dll and specifically the DiscoverXMLMetadataFull Function
  • The "Database\Cubes\Cube" items tell the DiscoverXMLMetadataFull function to get metadata at the cube level of detail
  • Finally the "Name,State,LastProcessed" section tells the function that we specifically want the Cube Name, Cube State, and Cube Last Process data (please note additional data points are automatically returned in these queries and can be ignored or used as needed)

The results of this query provide us with several key pieces of information and includes among various other fields, the last process date and the current cube status or state! Also, note that all databases on the server are listed and we do not have to contend with perspectives in this view.

ASSP Query

Armed with this query we can now develop our new Cube Status Administrative Intelligence Report! The first step is to open SSDT-BI edition and create a new report. Next we need to create a new Data Source, as illustrated below. In our example we called it ASSP_Reports. You will need to adjust to match your server environment.


The second step is for us to define our dataset by specifying a dataset name, specifying the data source (we just created in the above step), and finally pass in the DiscoverXMLMetadataFull query.


Our next step is to add a few custom fields to the dataset to prepare the data for display.

additional fields

First we add a 1 / 0 flag to determine if the cube is processed or not. 1=Processed and 0=Unprocessed.

state to number

Now because the last processed date comes with some extract characters, we need to clean up the Last Processed date field. We do that by creating two fields, shown below respectively, called LastProcessedDate and LastProcessedDate2. You will note that times are in UTC format.



Now all our fields are ready, we can create the layout for our report. We add the Cube Database name in column 1 and as a row group. Next we add the cube name, cube status (and indicator which we will discuss shortly) and last processed date.

Report Layout

Since the LastProcessed date is in UTC time, we need to adjust to our local time zone. In my case, we are UTC-5, so I use the DATEADD function in SSRS to adjust the time zone.

Correct Time

Finally, we setup an indicator, displayed below, which uses our numeric representation of our processed or unprocessed state to show either a red X or a green check mark. The numeric version of the state helps us to quickly make this assignment.


At last, we are now ready to show our newly created status report. Now your end users can quickly see the status of their cubes without having to call you. They can also see when the cube was last processed.

Status Report Final

A copy of the SSRS report can be downloaded below.


This tip showed how we can generate a quick and easy status report for our SSAS cubes. The report uses the ASSP.dll to query the current state of the various cube databases on a server. We specifically use DiscoverXMLMetadataFull method to get the last processed date, the current state, the database name and the cube name. We use these data points as the basis for the report. In addition to the cube status, you could easily take the report a step further and drill down to get the status of each dimension too.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, July 13, 2020 - 10:26:28 AM - Scott Murray Back To Top (86131)


I, unfortunately, do not have a SQL 2019 SSAS database to test this solution on. Additionally, the dll has not been updated for some time; you could maybe contact the ASSP project to see if they will produce an update?

Monday, July 13, 2020 - 9:22:52 AM - Sherri McDonald Back To Top (86129)

Hi Scott,

I emailed you late last year regarding the dll's that can be installed to have more insights as to the processing status of our measures and dimensions of our cube.  At that point, we were on SQL Server 2012, but we have since moved up to 2019.

Will this solution work for 2019?

When click the link: https://archive.codeplex.com/?p=asstoredprocedures and I click download archive.

When I click the install instructions, it doesn't actually provide the instructions.

If it is compatible with 2019, can you provide the install instructions?


Wednesday, November 27, 2019 - 2:00:08 PM - scott murray Back To Top (83211)

You would want to review the https://asstoredprocedures.github.io/ASStoredProcedures/ stored procedure documentation.

Wednesday, November 27, 2019 - 1:10:36 PM - Sherri McDonald Back To Top (83210)

Hi Scott,

In your post you stated "As you begin to work more with SSAS and create more cubes, you often will need to let end users know if a cube is in a processed or an unprocessed state. Similarly you may also want to convey the date and time when the cube was last processed. I can also envision instances where you would want to see similar information about dimensions or measure groups."

However, in your report, you are only showing if a cube was processed or not. I am specifically interested in whether a measure group has processed or not. Are you sure this assembly will provide that information?


Tuesday, May 8, 2018 - 9:12:13 AM - Scott B Murray Back To Top (75897)



It is strange that the assembly is showing version 10.00...seems like somehow it is pointing to an older version.  Unfortunately, I do not have SSAS 2014 on any lab machine, so I am unable to test thoroughly. Maybe check on the versions at:  https://asstoredprocedures.github.io/ASStoredProcedures/Installation-Instructions/ 

Tuesday, May 8, 2018 - 3:48:10 AM - Justin Swenson Back To Top (75891)


Similar issues as two other comments. Here's what's weird, we have SSAS 2014 and that's the version I downloaded. Each time I install the assemply the version listed for the assembly is, but if I dig into the assembly it's SSAS Version 12 (which is 2014). A little strange seeming.

Query (1, 6) Execution of the managed stored procedure DiscoverXmlMetadataFull failed with the following error: Exception has been thrown by the target of an invocation.Could not load file or assembly 'Microsoft.AnalysisServices.Xmla, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..

I've uninstalled and reinstalled multiple times. Including trying to restart both the services and the server. I may have to investigate the permissions more, but right now it's set to the server's service account, which has sysadmin access.

Any suggestions outside of permissions or installing? How would I verify that it installed correctly? Thanks.

Friday, January 26, 2018 - 10:37:34 AM - Scott Murray Back To Top (75046)

I would check to see if the permissions are set on the assembly or if the assembly installed correctly.


Friday, January 26, 2018 - 9:15:36 AM - Rene Back To Top (75044)

 Hi there,


I've got the same error as Wes:

Query (1, 6) Execution of the managed stored procedure DiscoverXmlMetadataFull failed with the following error: Exception has been thrown by the target of an invocation.Could not load file or assembly 'Microsoft.AnalysisServices.Xmla, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..

 What can I do?

Thursday, March 30, 2017 - 3:10:01 AM - Bob Back To Top (53930)

Fields!LastProcessed.Value is a boxed .net DateTime, so formatting it via implicit conversion, string processing that and re-parsing is inefficient. One CDate() would be more robust and efficient - which can be left to implicit conversion. Also, hardcoded TZ offset is a bad idea when you have the full power of .net - System.TimeZone.CurrentTimeZone.ToLocalTime()...

Friday, July 22, 2016 - 3:37:21 PM - Scott Murray Back To Top (41955)

 Wes... I would check the permissions for the assembly first.

Friday, July 22, 2016 - 1:31:47 PM - Wes S Back To Top (41953)

I installed the ASSP DLL without issue, but am receiving an error when attempting to execute the DiscoverXmlMetadataFull procedure. The error message is as follows: 

Query (1, 6) Execution of the managed stored procedure DiscoverXmlMetadataFull failed with the following error: Exception has been thrown by the target of an invocation.Could not load file or assembly 'Microsoft.AnalysisServices.Xmla, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..

Wednesday, March 30, 2016 - 1:59:41 PM - varun Back To Top (41097)

 Hi Scott, I am using SQL Server 2012, I tried creating reports from Visual studio, and it started working fine now, once i close the dataset and open t agin(weird). However after creating the report If i deloy it in report server its not able to connect to the datasource. I tried creating 2 reports with different servers and both couldnt establish a connection.

PLease see the error below:

  • An error has occurred during report processing. (rsProcessingAborted)
    • Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
      • The connection either timed out or was lost.
        • Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
          • An existing connection was forcibly closed by the remote host

I am using SQL server 2012, SSAS version 11.0.5556.0




Tuesday, March 29, 2016 - 6:15:40 PM - Anil Back To Top (41093)


I had no issues with the connection string. This is what I used:
Type: Microsoft SQL Server Analysis Services
Connection String: Data Source=localhost;Initital Catalog=My_SSAS_DB

Note: I tested on SQL Server 2008R2 


Tuesday, March 29, 2016 - 6:12:59 PM - Scott Murray Back To Top (41092)

What version of SSAS are you using?  You need to be using the MS SSAS data provider (ADOMD).  I am not sure if you are running into maybe a security issue on your end.


SSDT-BI is BIDS, so I am not sure about that item...  I have not tested from Report Builder.

Tuesday, March 29, 2016 - 5:16:11 PM - varun Back To Top (41090)

 Hi Scott,

Thanks for the update, From SSBT BI its getiing connected without any issue. However its not letting me in from ReportBuilder/BIDS

In reportbuilder, if I test connection from the connection properties window it shows as conection is succesfull but from the datasource properties test connection fails with either timeout.

Apart from this isuue, If I connect to the server with Microsoft SQL Server Analysis Services its not allowing me to run

CALL ASSP.DiscoverXmlMetadataFull("Database\Cubes\Cube|Name,State,LastProcessed","") it says Query Operation failed, failed to parse the cube name from the query (Microsoft.ReportingServices.QueryDesigners) . This is the reason why I used OLEDB connection since OLEDB is allowing me to make the call ASSP.





Tuesday, March 29, 2016 - 3:18:50 PM - Scott Murray Back To Top (41087)


I would first test the connection in SSDT-BI to each server. SSAS is the provider.

Tuesday, March 29, 2016 - 1:38:23 PM - varun Back To Top (41084)

 I tried to implement it thought I am getting connection issues, Can someone let me know which connection is used to connect to Analysis services? I am Using OLEDB since thats the connection which allowed me to run the CALL ASSP. However The same connection without any modification is not workign in another server.

Connection String: Provider=MSOLAP.5;Data Source= 

Let me know in case someone has any thoughts on this.


Friday, March 25, 2016 - 3:19:38 PM - scott murray Back To Top (41061)

The current status will be the current status and last time no matter the method used to update the cube.  

Friday, March 25, 2016 - 2:32:12 PM - Anil Back To Top (41060)

Also, on another note does this report or the query used returns the cube information if processed by sql agent only or regradless. Because I did process my cube successfully from SSMS but the report never updated the Last Processed time. It kept rendering the last processed time when processed by sql agent.  


Thursday, March 24, 2016 - 7:57:13 PM - Scott Murray Back To Top (41054)

Anil... Unfortunately, it does not record the error reason as they are runtime messages :-(

Thursday, March 24, 2016 - 2:03:28 PM - Anil Back To Top (41049)

 Hi Scott, 

Another one of your awesome tips. Thank you very much for sharing. 
Quick question on this however, in your final report how can I add another column may be after status to display the error message for the cubes that did not get processed ?


Thanks a lot.


get free sql tips
agree to terms