Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Consistency Checks for SQL Server Analysis Services


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server 2016

Quickly Resolve Performance Problems for IIS, .NET and SQL Server       >>>   Get Started


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.

DBCC

MeasureID

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

Table_and_Properties

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


DBcc

 

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.

Message_tab

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

SQLServer_Profiler

Sample SSAS DBCC Output


DBCC_command


Additional SSAS DBCC Output


Progress_Reports
Next Steps


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, February 02, 2017 - 6:25:02 AM - rajendra Back To Top

 

No, earlier versions of SQL SSAS (2012,2014) does not supports concistency check


Saturday, January 28, 2017 - 12:55:05 AM - Basavaraj Back To Top

 

 

Do earlier versions of SQL SSAS (2012,2014) supports concistency check

 .


Learn more about SQL Server tools