If you’ve been deploying tabular models to Analysis Services, at some point you may find you needed to have two different named instances on a server – one for test, and one for production. And once done with testing, you just used the Rename right-click menu option on the database in order to rename it as the production instance. As a result of using Rename, if you right-click on Properties, you’ll see that the ID for your Analysis Services database is now different than the Name.
Now, if you try to edit the model in SQL Server Data Tools, you get an unpleasant error that prevents you from opening it: “An error occurred while importing from Analysis Services server. Reason: The ‘Database’ with ‘ID’… doesn’t exist in the collection.”
How can you resolve this error? You have to first make a copy of your tabular model in Analysis Services. In this post, because you can’t use an ALTER statement to change the ID or Name, I’ll explain a quick copy process which enables you to create a copy of your tabular model database and rename the ID so that it matches with the Name. Then you can open up the new copy in SQL Server Data tools, edit it, and deploy back to the server.
How to Rename Your Analysis Server Tabular Model ID
- First, make a back-up of your Analysis Services tabular model! Right click and select Back Up.
- Then, in SQL Management Studio, right click on the name of your tabular model in SQL Analysis Services. Select Script > Script Database as > Create To > New Query Editor Window.
3. It may take a minute or so to create the script, which will open in a new XMLA query window. Scroll to the first few lines of the script. Look for the tags for <ID> and <Name>.
4. Now change both the ID and the Name so that they are both the same. For example, change by adding the extension _New_2 to both the ID and the Name:
CURRENT STATE- ID and Name Mismatch
NEW STATE – Both ID and Name are given new names that match
You now have a copy of the original database, but with matched ID and Name. This set of matched names will enable you to open up the database model in SQL Server Data Tools now. Work on it, and when done, deploy it back to the Analysis Services instance. If you need to deploy it with the same name as the original instance, then update the deployment properties, as explained below.
Updating Deployment Properties – Changing Name and ID
After you make your changes to a tabular model in SQL Data Tools, you can then deploy it back as the original database name by changing it in the deployment properties.
In the Solutions Window, make sure the name of your project matches the name of the database that you want to over-write. Right-click on the name and select Rename if you need to change it to match the original database name.
If you have a solution with a different name, then you just need to change the deployment properties.
In SQL Server Data Tools, in the application menu, select Project > Properties. In the Property Pages, change the Database name to the one that you want to over-write in production. Click OK.
Now when you deploy, it will replace the existing analysis server database with the new model you just updated.