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.
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.
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
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.
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.