Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Server Analysis Services Cube Processing Status Report


By:   |   Read Comments (12)   |   Related Tips: > Analysis Services Administration

Problem

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

Solution

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.

SELECT
[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
,CUBE_SOURCE AS Cube_Source
FROM $system.mdschema_cubes
WHERE
CUBE_SOURCE=1

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.

SSAS DMV

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.

Unblock

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

AssemblyInstalled

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.

DataSource

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.

dataset

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.

LastProcessedDate

LastProcessedDate2

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.

Indicator

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.

Conclusion

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


Last Update:






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

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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


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

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=12.0.0.0, 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

 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

Thanks

Varun

 


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

 Varun, 

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

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

 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.

 

Thanks

 

 


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

 

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

 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

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

:(
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

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

 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.

 


Learn more about SQL Server tools