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

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 (8)
  1. Anonymous says:

    I’ve recently published a blog post, describing how to rebuild a corrupt SQL Analysis database

  2. Anonymous says:

    Thomas Ellermann a publié un article décrivant comment reconstruire une base de données

  3. Anonymous says:

    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

  4. Aly says:

    Excellent.

  5. mike says:

    Hi Thomas, I have deleted my Cube management packs and ran the MPSyncJob but ConfigItemCube, WorkItemCube and ChangeAndActivityManagementCube are still showing in the DW Jobs within SCSM Console. I’m receiving the below message for each Cube in the event
    log:

    Deployment Execution Infrastructure encountered an error while executing a deployer.

    MP element ID: DefaultSchedule_CubeProcessing_Process.SystemCenterChangeAndActivityManagementCube

    MP name: ServiceManager.ChangeAndActivityManagement.Cubes.Library
    MP version: 7.5.3079.0
    Operation: Uninstall
    Error message: Microsoft.EnterpriseManagement.Common.ObjectNotFoundException: An object of class ManagementPackRule with ID 81e45e3d-78a0-e750-7f37-710dee9036b8 was not found.

    Any help would be appreciated.

  6. mike says:

    Hi Thomas, I have deleted my Cube management packs and ran the MPSyncJob but ConfigItemCube, WorkItemCube and ChangeAndActivityManagementCube are still showing in the DW Jobs within SCSM Console. I’m receiving the below message for each Cube in the event
    log:

    Deployment Execution Infrastructure encountered an error while executing a deployer.

    MP element ID: DefaultSchedule_CubeProcessing_Process.SystemCenterChangeAndActivityManagementCube

    MP name: ServiceManager.ChangeAndActivityManagement.Cubes.Library
    MP version: 7.5.3079.0
    Operation: Uninstall
    Error message: Microsoft.EnterpriseManagement.Common.ObjectNotFoundException: An object of class ManagementPackRule with ID 81e45e3d-78a0-e750-7f37-710dee9036b8 was not found.

    Any help would be appreciated.

  7. Dan_IT says:

    Thomas, would this work with SCSM SP1 as well?

  8. Zak says:

    Mike, I am getting the same Error. do you know how to fix it

Comments are closed.

Skip to main content