Consistency Checks for SSAS

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 >
    <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 >
     <Object>
          <DatabaseID>AdventureWorksDW2014Multidimensional-EE</DatabaseID>
          <CubeID>Adventure Works</CubeID>
     </Object>
</DBCC>

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

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

The PartitionID is found by right clicking on the Table | Partitions and Partition details.  Then double clicking on the Partition Name to reference the ID column of the General tab.

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

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book “DP-300 Administering Relational Database on Microsoft Azure.” I can be reached at: Rajendra.gupta16@gmail.com for any consulting help.

• MSSQLTips Awards:
• Author of the Year – 2022 | Author Contender – 2021/2023/2024 | Champion Award (100+ tips) – 2020