Consistency Checks for SQL Server Analysis Services

By: Rajendra Gupta   |   Updated: 2015-11-09   |   Comments (2)   |   Related: > SQL Server 2016

Problem

Currently we don't have any functionality to check for consistency errors in SQL Server Analysis Services (SSAS) databases, cubes or partitions. SQL Server 2016 solves this problem by introducing database consistency checks (DBCC) commands for SQL Server Analysis Services. In this tip we will walk through code examples and output to see how this new functionality works.

Solution

Database consistency checks (DBCC) for SQL Server Analysis Services (SSAS) analyzes databases for corruption across the entire database or individual objects within the database. The command is valid for both multidimensional and tabular databases although in some respects there is limited functionality as compared to the DBCC commands for the database engine.  Keep in mind, you must be a SQL Server Analysis Services database or server administrator (a member of the server role) to run the DBCC commands.

DBCC for multidimensional databases validates metadata, segment statistics and indexes and looks for physical corruption while DBCC for tabular databases checks for segments, dictionaries, column stats and compression.

Below is the syntax for DBCC which is similar for both the Multidimensional and Tabular databases.

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID></DatabaseID >
        <CubeID>    </CubeID>
        <MeasureGroupID>    </MeasureGroupID>
        <PartitionID>    </PartitionID>
    </Object>
</DBCC>

We can run the DBCC for the entire SSAS Database or a specific MeasureGroup or PartitionID.

If we run the DBCC command for SSAS in SQL Server 2014 or lower we will get output as shown below indicating the code is not supported.

Executing the query ...
The DBCC element at line 7, column 87 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) cannot appear under Envelope/Body/Execute/Command.
Execution complete

SQL Server Analysis Services DBCC Command Examples

1. Running DBCC for a MultiDimesnional Database for the entire Cube

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
     <Object>
          <DatabaseID>AdventureWorksDW2014Multidimensional-EE</DatabaseID>
          <CubeID>Adventure Works</CubeID>
     </Object>
</DBCC>

2. Running DBCC for a Tabular Database on a specific MeasureGroupID

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
     <Object>
          <DatabaseID AW Internet Sales Tabular Model 2014</DatabaseID>
          <CubeID>Model</CubeID >
		  <MeasureID>Product-d4e4bb29-f329-4edf-91ee-79289f732946</MeasureID>
     </Object>
</DBCC>

Here is how we can find the parameters for the DBCC Commands:

DatabaseID

DatabaseID is the ID field found on the Database tab of the Database Properties.

MeasureID

The MeasureID is found by right clicking on Table and Properties and referencing the ID column of the General tab.

PartitionID

SQL Server Analysis Services DBCC Command Examples

If we review at the output, from the Results tab in SQL Server Management Studio it will indicate an empty result set if no problems were detected as shown below.

The Message tab in SQL Server Management Studio provides detail information such as:

Executing the query ...
READS, 0
READ_KB, 0
WRITES, 0
WRITE_KB, 0
CPU_TIME_MS, 0
ROWS_SCANNED, 0
ROWS_RETURNED, 0

<DBCC xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>AdventureWorksDW2014Multidimensional-EE</DatabaseID>
<CubeID>Adventure Works</CubeID>
</Object>
</DBCC>

Started checking segment indexes for the 'Internet_Sales_2011' partition.
Started checking segment indexes for the 'Internet_Sales_2012' partition.
Finished checking segment indexes for the 'Internet_Sales_2011' partition.
Started checking segment indexes for the 'Internet_Sales_2013' partition.
Finished checking segment indexes for the 'Internet_Sales_2012' partition.
Started checking segment indexes for the 'Internet_Sales_2014' partition.
Started checking segment indexes for the 'Internet_Orders_2011' partition.
Finished checking segment indexes for the 'Internet_Sales_2014' partition.
Started checking segment indexes for the 'Internet_Orders_2012' partition.
Started checking segment indexes for the 'Internet_Orders_2013' partition.
Finished checking segment indexes for the 'Internet_Orders_2012' partition.
... 
Run complete

Running Profiler to capture SSAS DBCC Output

If we run SQL Server Profiler against SSAS while executing the DBCC command, we can see the individual checks of columns, tables, database and more. Keep in mind you have to include the Progress Reports Events to capture the necessary details.

Profiler Configuration to Capture DBCC Output

Sample SSAS DBCC Output

Additional SSAS DBCC Output

Next Steps

• Read More about In DBCC for Analysis service Databases on MSDN.
• Try it out yourself! Download and install the SQL Server 2016 preview
• For more SQL Server 2016, read these other SQL Server 2016 Tips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

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

View all my tips