다음을 통해 공유

SSAS Troubleshooting: Aggregation File Could Not be Opened (-1 Error)

  • 아티클

Applies to: 

  1. SQL Server 2012 SP1 (build number 11.0.3000 and later)
  2.  SQL Server 2008 R2 SP1 (build 10.50.2500 and later), using Analysis Services cubes having aggregation designs on a partition

Background

Aggregation indexing has been disabled in recent service packs, causing an error to occur when an aggregation index file no longer exists.

The error is ‘file could not be opened’ and it specifies a path to a non-existent aggregation index file (of either *.agg.rigid.map or *.agg.flex.map file type).  

Aggregation indexes were intended to speed up queries on very large aggregations. Midway through the SQL Server 2008 R2 SP1 release (in Cumulative Update 5), the server stopped building these index file because they were not delivering sufficient value. Aggregations are supposed to be much smaller than partition data, and much faster to scan. However, over time it was found that index files were not boosting query performance to levels that justified the cost of building and maintaining them during processing. For this reason, indexing was turned off.

This change was introduced in SQL Server 2008 R2 SP1, and then ported to SQL Server 2012 in its SP1 release. If you are running SP1 or later of either 2008 R2 or 2012, you might encounter errors due to missing index files.

To resolve this error, you need to rebuild the indexes. The following sections provide further information about the error and its resolution.

Error

Following is an example of an error that is likely to occur when a partition references an aggregation index file that no longer exists:

The file 'H:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2.0.db\Adventure Works.0.cub\Fact Internet Sales 1.0.det\Internet_Sales_2003.0.prt\ **-1.**Dim Product.End Date.agg.rigid.map' could not be opened. Please check the file for permissions or see if other applications locked it.

Notice the -1 in the filename. The -1 occurs when no version of the file can be found.

It’s possible to see similar errors with slightly different wording. The significant part of this error is the “-1” in the filename. The filename is constructed from metadata. The “-1” is read from metadata in the partition.  When the index was originally defined, this value would have been “1”. Now that indexes are disabled, the value is “-1”, which results in a file not found error.

Example File name and location (file type = MAP, inside a partition folder)

The following example provides background information on the files mentioned in the error message.

Given the following fully qualified path:

H:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Data\Adventure Works DW 2008R2.0.db\Adventure Works.0.cub\Fact Internet Sales 1.0.det\Internet_Sales_2003.0.prt\ 5.Dim Product.End Date.agg.rigid.map

The filename has the following parts:

  • Path is a partition folder (ending in .prt)
  • File type of the aggregation index file is .map (there is also a corresponding .hdr file)
  • Filename begins with a version number. In this example, the version number is 5.  File versions are incremented each time the partition is processed. On older cubes, this number might be very large.

These files were created in response to aggregation designs on a partition, for partitions exceeding a row count specified in the <AggIndexBuildThreshold>65536</AggIndexBuildThreshold> server configuration property.

If you do not have aggregation designs, or if the partition row count is below the threshold, you will not find any aggregation index files in your partitions.

NOTE: Partitions created or processed in full on SQL Server 2008 R2 SP1 CU5 or later will not have this problem. This error is primarily limited to older partitions that are processed before this change went into effect.

 

WORK AROUND

The solution is to rebuild the aggregations without indexing. Rebuilding the aggregation resets the metadata in the partition to correct values, clearing out any references to the index files.

You run ProcessClearIndexes followed by ProcessIndexes on each partition – this is the most efficient option. Orr you can do a Process Full on the partitions, measure groups, cube, or database. For more information about processing, see Processing objects in a multidimensional model.

Credits

Many thanks to Haidong Huang and Akshai Mirchandani for their considerable help in writing this post. Thanks to SQL Magazine for their article on build version numbers.