Download the free trial version below to get started. Doubleclick the downloaded file to install the software. 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. B9opj.png' alt='How To Install Ssms Addin' title='How To Install Ssms Addin' />SQL Server Analysis Services Cube Processing Status Report. 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. Similarly you may also want to. I can. also envision instances where you would want to see similar information about. One way. we can use SSRS is to generate an Analysis Services cube status. Sap Gui For Linux. This tip. continues our series on various Administrative. Intelligence reports. These reports help you to get your. RPWjQ.png' alt='How To Install Ssms Addin' title='How To Install Ssms Addin' />You can use one of two query methods to. One method is to. SSAS DMVs. Alternatively you can use the Analysis. Services Stored Procedure Project, which has a dll that must be. This addin, called ASSP. Codeplex https asstoredprocedures. We will use both. VisualStudioAdd-inWizard.png' alt='How To Install Ssms Addin' title='How To Install Ssms Addin' />The report itself for this example will use the ASSP. We will use the 2. Adventure. Works regular and data. Codeplex https www. Once. these sample databases are downloaded and installed, we. SSAS database to simulate a multi cube environment. Finally, to generate our report, we. SQL. Server Data Tools for Business Intelligence SSDT BI for Visual Studio. You can download SSDT BI from http www. PDF files that contain the Visual Studio 2005 documentation. 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. How To Install Ssms Addin' title='How To Install Ssms Addin' />These views are actually DMX. NOT be MDX. These management views actually grew out of the. SSAS. SELECTCATALOGNAME AS DatabaseName,CUBENAME AS Cube. Or. PerspectiveName,CUBECAPTION AS Cube. Or. PerspectiveDisplayName,LASTDATAUPDATE AS LastProcessDate,BASECUBENAME AS BaseCubeName if blank then Cube else Perspective,CUBESOURCE AS CubeSource. FROM system. mdschemacubes. WHERE CUBESOURCE1. The above query is certainly quite, short but actually provides us. Let us review the various. The CATALOGNAME line returns the SSAS database that we are querying. The CUBENAME is the name of the cube or the underlying perspectives for the database in being queried. The CUBECAPTION specifies the display name for the cube or perspective. The LASTDATAUPDATE is the last process date for the cube. The. BASECUBENAME shows the parent cube for any row listed that is a. If this field is blank, then that signifies that the field. The CUBESOURCE in the SELECT and. WHERE clauses tells the query to only return CUBESOURCES1 a cube. Cube or Perspective. A cube source of 2. This query creates the following results. These results are handy, but this method does come. The biggest of which is if the. As shown below, in the PNL database two cube. 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. Installing the Analysis Services Stored Procedure ASSP Project Addin. Even so, the ASSP. Analysis. Services Stored Procedure ASSP Project addin comes to our rescue. This dll opens up several methods of interrogating an SSAS database in. In. order to use the ASSP tools, we first need to install the dll on each. There are multiple download and. You can. download an XMLA version of the project and then use XMLA in Management. Studio to install. You will see the below results if the install was successful. Alternately, you can install the dll by adding. SSMS Register Assembly wizard. To complete this. Assembly folder in SSMS and then click New. Assembly. Next, using 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. ASSP. dll and then. On the General tab, shown next, you will want to. 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. The ASSP exposes several functions and methods that can be used to. SSAS cube and its. We will specifically use the Discover. XMLMeta. Data. Full function. The full syntax of our query. CALL ASSP. Discover. Xml. Metadata. FullDatabaseCubesCubeName,State,Last. Processed,. The CALL key word initiates the query. Next the ASSP. Discover. Xml. Metadata. Full words request the ASSP dll and specifically the Discover. XMLMetadata. Full Function. The DatabaseCubesCube items tell the Discover. XMLMetadata. Full function to get metadata at the cube level of detail. Finally the Name,State,Last. Processed section tells the function that we specifically want the Cube Name, Cube State, and Cube Last Process data please note additional data. The results of this. Also, note that all databases on the server are listed. Armed with this query we can now develop our new. Cube Status Administrative Intelligence Report The first step is to. SSDT BI edition and create a new report. Next we need to create a. Data Source, as illustrated below. In our example we called. ASSPReports. You will need to adjust to match your server. The second step is for us to define our. Discover. XMLMetadata. Full 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. Processed. and 0Unprocessed. Now because the last processed date comes. Last Processed. date field. We do that by creating two fields, shown below respectively. Last. Processed. Date and Last. Processed. Date. 2. You will note that. UTC format. Now all our fields are ready, we can. We add the Cube Database name. Next we add the cube name, cube status. Since the Last. Processed date is in UTC. 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. 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. Now your end users can quickly see the. They can also see. A copy of the. SSRS report can be downloaded below. Conclusion. This tip showed how we can generate a quick and easy status report. SSAS cubes. The report uses the ASSP. We specifically use Discover. XMLMetadata. Full. We use these data points as the basis for the. In addition to the cube status, you could easily take the. Connect Xbox 360 Controller Pc Vista. Next Steps. Last Update 2. About the author. Scott Murray has a passion for crafting BI Solutions with Share. Point, SSAS, OLAP and SSRS. View all my tips.