Partager via

How to rebuild your Analysis database

  • Article

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

clip_image002

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)

clip_image003

5. Delete everything within the Cubes section (in my case 690.000 lines)

clip_image004

6. Save the file for later use.

7. Open the Service Manager console, Administration, Management Packs

8. Delete all the MPs related to cubes:

clip_image006

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.

clip_image007

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.

clip_image008

 

 

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.

clip_image009

16. The cubes then start to get processed

clip_image011

 

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

clip_image012

2. For each datasource, update the ID, Name, ConnectionString and Account

clip_image014

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.

 

clip_image016 clip_image017

CreateNewDWASDatabase.xmla

Comments

  • Anonymous
    November 28, 2014
    I’ve recently published a blog post, describing how to rebuild a corrupt SQL Analysis database
  • Anonymous
    December 04, 2014
    Thomas Ellermann a publié un article décrivant comment reconstruire une base de données
  • Anonymous
    December 04, 2014
    Thomas Ellermann a publié un article décrivant comment reconstruire une base de données SSAS corrompue utilisée par System Center 2012 R2.

    Pour plus d'informations: http://blogs.technet.com/b/thomase/archive/2014
  • Anonymous
    December 09, 2014
    Excellent.
  • Anonymous
    December 15, 2014
    The comment has been removed
  • Anonymous
    December 15, 2014
    The comment has been removed
  • Anonymous
    March 04, 2015
    Thomas, would this work with SCSM SP1 as well?
  • Anonymous
    May 29, 2015
    Mike, I am getting the same Error. do you know how to fix it