How to reset the Microsoft SQL Server Analysis Server database file folder used by ITA

Products

• IT Analytics

Issue/Introduction

Solution Background

Note: This article is primarily in reference to resolving severe issues with the Microsoft SQL Server Analysis Server (SSAS) database file folder when used by the Altiris IT Analytics Solution (ITA) for the Symantec Management Platform (SMP).

When certain severe issues occur with the SSAS, occasionally it is recommended to reset the contents of its database file folder. This is suggested when it is considered that the issue is not with ITA but with the SSAS files themselves. It has been found that by deleting all SSAS files created in the IT Analytics database folder created in SSAS that this can resolve such severe issues.

Issues that this has been so far proved to resolve include the following related articles:

Error "Exception: A task with Guid is already running." appears in the IT Analytics Event Viewer
https://knowledge.broadcom.com/external/article?legacyId=TECH159034

Error "Errors in the metadata manager" occurs when trying to access an IT Analytics report
https://knowledge.broadcom.com/external/article?legacyId=TECH184011

Unconfirmed but probable issues that this solution can resolve include:

Error "Server Error in '/Altiris/ITAnalytics' Application" occurs when trying to open the IT Analytics Configuration page
https://knowledge.broadcom.com/external/article?legacyId=TECH179454

And when certain cubes will not process.

Environment

ITA - all versions

Resolution

Other Troubleshooting

Similar troubleshooting may already have occurred, but if not, should be tried first, if they appear relevant, as it then takes less effort to get back to an operational ITA product state. These are:
 

1. Uninstall and then reinstall ITA and/or specific ITA report packs in the Symantec Installation Manager.
2. Creating a new, if only as a test, SSAS database in the ITA > Configuration window in an SMP Console.
3. Under extreme situations, uninstall SSAS from SQL directly, then reinstall it.

For #2 and #3, creating a new SSAS database should resolve issues with database file issues with the original ITA SSAS database, but under at least once circumstance (KB TECH184011), it did not, and therefore the following process should not be disregarded out of hand. Also, even if SSAS is uninstalled in SQL, it may still leave database files present on the hard drive. Neither of these are necessarily guaranteed to remove the database files.

Solution Instructions

The following instructions describe how to reset the SSAS database file folder. WARNING: Please ensure that it is understood what the following will do, which will alter the SSAS database. If the files are not backed up correctly, this may result in permanent data loss. But as ITA uses derived data from the Symantec_CMDB database, in theory this should all be able to be recovered once ITA is able to process its cubes successfully again.
 

1. Stop the Analysis Services (SSAS) service on the SQL Server, under Services.
2. On the SQL Server, locate where the SSAS database files are located at on the hard drive. By default, these are located at C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\<Name of the database, such as "IT Analytics">.
3. Back the files up to a folder outside of this folder, such as to a new folder on the Desktop.
4. Delete the files in the \Data\<Name of database> folder.
5. Start the Analysis Services.
6. In the Symantec Management Platform Console, click on Settings > Notification Server > IT Analytics Settings.
7. Click on Configuration.
8. Enter the Analysis Server Name, then click on the Verify Connection button.
9. Enter the Analysis Server Database name, then click on the Save Database Settings button.
10. If the issue was that cubes would not process, follow these sub-steps:
    
    a. Under Cubes, the cubes must now be reinstalled. Reinstall one cube to test.
    b. Under Processing, select the newly reinstalled cube and process this immediately.
    c. Is the issue resolved? If so, reinstall other cubes and then process them afterwards.
     
11. Otherwise if cubes not processing was not the issue, go to where the issue was found at and try to reproduce the issue.