MSAS - Troubleshooting Cube Processing

Microsoft SQL Server 2000 Analysis Services provides the user with many ways of tracking and solving errors. A large number of error logs built into the service help the user pinpoint the exact nature of errors and find solutions for them.

Microsoft Tech Net also provides the user online support to troubleshoot problems. This requires paid membership. FAQs of different kinds are available for the reference of users.

• SQL Server Books Online FAQ
• SQL Server Enterprise Manager FAQ
• Administration Tools FAQ
• Failover Clustering FAQ
• Multiple Instance FAQ
• Programming FAQ
• Replication FAQ
• Server FAQ
• Setup and Installation FAQ
• Upgrading to SQL Server 2000 FAQ

Microsoft Product Support Services (PSS) has implemented online troubleshooters, which are Web-based interactive guides for pinpointing and resolving customer problems. Finally SQL Server 2000 Analysis Services Books online and help module offer extensive information to the users and help them use the tools effectively.

In this section we will be focusing on the common problems encountered by users while processing cubes and will highlight the solutions that have been offered by Microsoft for the same. There are several common problems that users encounter while processing a cube.

1. One of the most common ones is that after the data source for the cube is changed error occurs in processing. The reason for this could be that the data source has SQL incompatible dialects. The solution offered is that if the error message identifies invalid syntax the Decision Support Objects (DSO) or Cube Editor should be used to correct the problem. An omnibus solution would be to follow the golden rule not to change the data source provider.

2. Often an error occurs while processing a dimension and the user gets a message “Maximum number of child members exceeded”. This error occurs because one or more members of a dimension contains more than the maximum number of children allowed per parent. The solution would be to create member groups to provide intermediate levels that organize the dimensions so that no member has more than the maximum number of children allowable.

3. Sometimes member keys are found in the fact table but not in the dimension level, the set of leaf members or in the dimension. The reason for this could be that the dimension table does not contain a member related to a member in the fact table or the dimension was not processed after changes were made to the table. The solution offered is that the user should verify the referential integrity of the dimension tables and fact tables before processing the cubes. Key errors can be logged in various ways to enable trouble shooting. Missing members can be logged to a file and can be imported into a relational database using the Decision support Services(DTS). The keys can then be removed or modified in the fact table or added to the dimension

4. Fact table rows are not read when a cube or partitions of a cube are processed. If this error occurs the cause can be traced back to the fact that the source data lacks referential integrity. The referential integrity has to be corrected so that the inner joins between the fact and dimension tables include all the rows of the fact table.

5. New versions of the virtual cube are not visible to the user. The reason is that the virtual cube was changed but not processed. The cube must be processed before the end user can see the cube. Another reason for this could be that the cube was processed while the user was browsing the cube. The user will have to disconnect and reconnect to the server.

6. Analysis services is processing more records than exist in the fact table. The fact table may have many to many relationship instead of one to many relationship with a dimension table. The redundant members will have to be removed from the Dimension tables.