Problem

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.

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_SourceFROM $system.mdschema_cubesWHERE 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.

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.

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.

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

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.

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:

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.

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.

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

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.

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.

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.

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.

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=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified..

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()...

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..

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.

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.

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.

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

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 ?