How to rebuild your Analysis database
At a Customer, there was an issue where the SSAS cubes suddenly stopped working and in the event log, on the DW Management server, there were many ID 33522 events:
Unhandled exception in data warehouse maintenance:
Work item: 155150404
Maintenance action: ManageCubePartitions Exception details:
Exception message: File system error: The following file is corrupted: Physical file: \\?\D:\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\DWASDataBaseSP1.6.db\ConfigItemDim.0.dim\302.ConfigItemDimKey.ahstore. Logical file .
Errors in the metadata manager. An error occurred when loading the ConfigItemDim dimension, from the file, ' \\?\D:\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\DWASDataBaseSP1.6.db\ConfigItemDim.302.dim.xml'.
Errors in the metadata manager. An error occurred when loading the SystemCenterServiceCatalogCube cube, from the file, ' \\?\D:\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data\DWASDataBaseSP1.6.db\SystemCenterServiceCatalogCube.6997.cub.xml'.
The customer also realized that Data Protection Manager does not backup their Analysis databases, and as a workaround they dump the database on a daily/weekly schedule. With the SSAS cubes not working, their only option was to create a new instance of the DWASDatabase.
A good thing is that all the data in the DWASDatabase can be found in the DWDataMart, so when we had to rebuild the DWASDatabase, all that needed to be done is run a synchronization, after the rebuild.
Luckily one of our highly skilled Support engineers, Alex Young, was able to provide the high-level details on how to rebuild the analysis database.
The high level steps, to rebuild the SSAS DB are the following:
1. Export a XMLA file from the corrupt DB and modify a bit.
2. Take back up of all SM DBs and Cubes (DWDatamart, DWRespository, DWStagingAndConfig , Sevicemanager, DWASdatabase)
3. Uninstall all the cube Management Packs from Service Manager
4. Let the MPsync job run to sync the deletion in the DW
5. Create a new DWASDatabase, by using the modified XMLA file
6. Import two Cube related Management packs
7. Wait for MPsync to complete again
8. Cubes and dimensions are rebuilt automatically following this.
Sounds easy, but it can be complicated though. Here are the steps in more details (please follow the steps carefully):
1. Open SQL Management studio and connect to the Analysis instance
2. If you can still access the DWASDatabase, you can extract the XMLA and modify it to create a new database. (Otherwise read the last section of this blog post)
a. Right click on the DWASDatabase, Script Database as, Create to, File
3. Save the file and open it for edit in a XML Editor. Since it’s a very large file, I normally use Notepad ++.
4. Delete everything within Dimensions section (in my case 51000 lines)
5. Delete everything within the Cubes section (in my case 690.000 lines)
6. Save the file for later use.
7. Open the Service Manager console, Administration, Management Packs
8. Delete all the MPs related to cubes:
9. Let the MPSyncjob run to sync the deletion. Do not continue until the Cube MPs are NOT listed in the Data Warehouse Management packs view.
10. Open SQL Management studio and connect to the Analysis instance again
11. Delete the DWASDatabase
12. Open a new XMLA Query and paste in the XMLA file you edited in the earlier steps.
13. Click on Execute and a new DWASDatabase is created, but without the cubes and dimensions, as we deleted those earlier when editing the script.
14. Next you need to import all the cube MPs again. The files are either located within C:\Program Files\Microsoft System Center 2012\Service Manager or else you have to extract them from the SCSM 2012 R2 install media:
a. Locate the SM.MSI file (Installmedia\amd64\Setup\Server\) and run e.g:
msiexec /a SM.msi /qb TARGETDIR=C:\temp\SCSM2012Installfiles
b. Files are now located in C:\temp\SCSM2012Installfiles\Program Files\Microsoft System Center\Service Manager
c. First Import the ServiceCatalogCubes.mpb and then the Cubes.mpb
15. Wait for the MPSyncjob to finish, after this, the cubes and dimensions will be created again.
16. The cubes then start to get processed
Hopefully, you now have a new and functioning SSAS database.
NOTE: if your DWASDatabase is so corrupt that you cannot export the XMLA, you can use the attached file, but you have to change the following sections:
1. ID and NAME to reflect the name of the corrupt DB
2. For each datasource, update the ID, Name, ConnectionString and Account
3. After the database has been created, you have to manually add the users and groups, by using the SQL Management Studio, to the SCDW_Report_Readers and SCDW_Adminstrators roles, as they are not defined in this file.
