Partilhar via

Renaming Olap Databases Issues within Analysis Services

  • Artigo

If you are view the XMLA script that is generated for an Analysis Services database, you will notice that there are both ID and NAME attributes.  The reason Analysis Services has this differentiation is so one could rename an Olap database (which you could not do officially within Analysis Services 2000). Database name defaults to its ID and the ID cannot be modified. You can, on the other hand, change the database name. This way end-users could have a different name to the Olap database irrelevant of what its ID was.  For example, you could have the ID of the Olap database as "Foodmart" while the name of the database could be "Wade's Groceries".  The file system itself, i.e. in the c:Program FilesMicrosoft SQL ServerMSSQL.2Data folder has a Master.vmp and a folder name (e.g. Foodmart) that still refers to the ID.  That is, the folder name and any ID references are to Foodmart irrelevant of whether the database name is "Foodmart", "Wade's Groceries", or "Sally's Deli".

Scenario

A scenario discovered by our customers had noticed an inconsistency with the renaming scenario as per below.

1. Sync with the Foodmart database

2. Rename the Foodmart database to Foodmart_1:

You will notice while the database is now Foodmart_1, the folder name for the database is still called Foodmart - just like the ID.

3. Import the definition of the Foodmart_1 into Visual Studio and make the changes you would like to make (e.g. new dimensions, changes in measure definitions, etc.)

4. Attempt to deploy this Foodmart_1 database back to the original server and you get an error that it already exists.  You do not have the option to overwrite it.  The error is in the form of:

Error 1 Errors in the metadata manager. The database with the name of 'foodmart_1' already exists in the 'server_name' server.  0 0 

5. Try to deploy the database back as Foodmart and you get the following warning.

The 'foodmart' database already exists on the 'server_name' server. If you proceed with deployment, the database will be overwritten.

 

Would you like to continue?

Now you can overwrite the database, but you'll have the db named "foodmart" as opposed to what you wanted - foodmart_1.

 

Discussion

Basically, exemplified in this scenario is that there is a small bug within Visual Studio where it does not differentiate between the ID and the NAME.  To work around this problem, you can rename the Foodmart_1 database back to Foodmart before importing it back into Visual Studio.  Once you're done with your modifications, you can then re-deploy it back to your server as Foodmart, and then rename it back to Foodmart_1.

Often people will rename a database because they plan to version the databases.  For example, over a particular period you have three versions of the same database (e.g. new dimensions, new measures, etc.).  In this example, your database is Blah_V2 for a previous version and Blah_V3 for the current version.  But you do not want users to need to switch to a different database name each time a new version is created so you want to call it Blah each and every time.  To do this, you can build and deploy your database with the ID and NAME of blah_v3.  Once you deploy it, you can rename the original "Blah" database back to blah_v2 and rename the current one to "Blah".  Please note, there can be issues with this approach because your front-end applications will need its metadata to be refreshed when switching from the V2 to V3 due to any of the changes made.

-------------------

Author: Denny Lee

Reviewed by: Kevin Cox, Lubor Kollar, Baya Pavliashvili (baya_baya@hotmail.com), and Nicholas Birke (nick@birke.ws).

Comments

  • Anonymous
    March 15, 2008
    Authors : Denny Lee, Edward Melomed, Mike Vovchik Contributors: Duy Vuong, Greg Washburn Reviewers :

  • Anonymous
    April 22, 2013
    This has been a lot of help! Thanks

  • Anonymous
    February 19, 2014
    You can work around the issue by editing the <ID></ID> value in your olap .database file directly ( utilmately it is an xml file ) - close solution, edit the file in text editor,  re-open solution, re-process.