Is Your Cube Corrupt?

The Database Console Commands (DBCC) are a collection of Transact-SQL statements that allow you to perform maintenance tasks, validate operations on a database or database component, retrieve SQL Server information, and other miscellaneous tasks such as enabling a trace flag. These are critical capabilities when you’re responsible for ensuring your multi-dimensional environment is working properly. DBCC is sometimes referred to as Database Consistency Checks, something it was referred to as in some of the earliest versions of SQL Server.

This article will show you how to execute DBCC for Analysis Services using XMLA queries. The included examples were created on a Windows 2016 Azure Virtual machine running SQL Server 2017. The WideWorldImporters and WideWorldImportersDW databases were used along with the Multidimensional project for WideWorldImporters provided by Microsoft via GitHub. Microsoft currently does not provide a sample Tabular Project for WideWorldImporters, so I created a simple Tabular database with its compatibility set to 1400.

While DBCC validation commands have existed for relational databases going back to some of the first versions of SQL Server, it wasn’t until the release of SQL Server 2016 that a similar, though more narrow, set of commands was made available for Analysis Services. These new commands allow you to use either XMLA or MDX queries to execute DBCC in SQL Server Management Studio. DBCC for Analysis Services can be used to validate both Multidimensional and Tabular databases at any compatibility level of SQL Server 2016 or higher. There are two command syntaxes: one for Multidimensional or Tabular compatibility level 1100 and 1103 databases and another for Tabular model databases with a compatibility level or 1200 or higher

You will need to either be a member of the Full Control (Administrator) database role for the database you want to execute the DBCC commands against or be a member of the Server Administrator role.

DBCC command for Multidimensional models

For Multidimensional models, The Database Consistency Checker (DBCC) for Analysis Services only checks a multidimensional database’s partition indexes. The checks include validating a partitions index’s metadata, looks for the existence of physical corruption, and checks segment statistics and indexes. It does this by creating temporary indexes and compares them to the partition indexes saved to disk.

There are several parameters you can pass to the DBCC command. The first is Database ID, which you can find by right clicking on the data in Management Studio and selecting properties.

Once you have the Database ID, you can pass it as a parameter to execute a consistency check against the entire database. The database is not corrupt if the results returned are empty.

If you click on the message tab, you may see details about the partitions that were checked.

You may see less information returned in the message tab if you’re executing the command against a small database.

The next parameter you might pass with the database id is cube id.

You can also pass a measure group id parameter.

Finally, you might pass the partition id of a measure group.

DBCC command for Tabular models with a compatibility level 1200 or higher

The Database Consistency Checker (DBCC) for Analysis Services has a more comprehensive set of consistency checks for Tabular models compared to Multidimensional. It validates multiple types of Tabular objects for corruption including, databases, tables, partitions, relationships, hierarchies, columns and more. The checks performed are the same validation steps that are run when you restore, synchronize, or reload a Tabular database.

Similar to the Multidimensional model, the first parameter you might pass is database id.

The next parameter you can pass is the name of a table in the database.

Finally, you can pass the name of a partition name of a table.

Final thoughts

The Database Consistency Checker (DBCC) for Analysis Services allows you to easily check your multidimensional or tabular model database. If you do have corruption, the error messages returned will help you determine next steps. Microsoft provides a list of checks and errors along with some common error conditions and how to resolve them.