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 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.
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.
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.
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:
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.
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.
Next Steps
