Sunday, March 26, 2017

How to fix your corrupted SSAS database?

If your SSAS database is corrupted, then you can either 1) restore the database or 2) delete the existing SSAS database and then process the cube to load the fresh data. 

I would like to share the second one: Delete the existing database and then process the SSAS cube to load the fresh data.

For example, my SSAS database 'HelloWorld' got corrupted and now you would  like to fix the SSAS database, so following steps should be taken to fix it.

Step 1: Delete the existing database

Fig: Find your corrupted database

Hit the delete button

Fig 2: Delete the database
Choose any one of the options that you prefer:

Fig 3: 

Since database is deleted, now we can open SSAS package and hit the process button to deploy and process it as like below:

Fig 4: Deploy the SSAS Cube

You may unfortunate:

However, if we may unfortunate sometime when you will find error like :

Fig 5: Deployment error

Step 2:  Delete the .xml file from data directory

To fix the error we need to delete .xml file from the data directoy, Depend on your Windows version (32/64 bit), you can go to data dictionary and delete the xml file
C:\Program Files\Microsoft SQL Server\MSAS11.TABULAR\OLAP\Data

The XML file format will look like: <CubeID>.<VersionNum>.db.xml, e.g. this case:  HelloWorld 1.0.db.xml . You can also delete the folder as like same name:  HelloWorld 1.0.db

Fig 6: Deletete the .xml file

 Step 3: Restart the SSAS 

Restart the SSAS 
Fig 7: Restart the SSAS

Step 4: Run the SSAS package

You are all set to process the cube. So open your SQL Server Data Tools and SSAS solution, process the cube and you will be able to load fresh data structure and the data, it means your databse is back again and functional as like before.