//
you're reading...
SQL Server

Changing the Tabular workspace database name\ID in SSAS 2012

 

Use with caution:  Before attempting to implement any steps mentioned in this post, be it known that this type of direct settings modification is generally not recommend. I do not make any implied warranty of usability.

A tabular model workspace database is created automatically when you create a new SQL Server Tabular Project in SQL Server Data Tools (SSDT). By default it is created on local SSAS Tabular server but you can choose a different server as well. The workspace database name consist of the project name, user name ,GUID and frankly it looks like gibberish (see below). For example look at the database name below. Yikes!! Now look at the screen shot of a development server and imagine this on a server with 10 concurrent users, each with 10 projects and you will get the idea.

OppsReportingDetail_Userid_56703363-2931-47bc-af92-c2c525610e89

Its always annoyed me that you can not set this property with your ptoject and made me wonder if there was not a way to rename them. As luck would have it there is… And here are the steps.

1. Start SSTD (obviously!!) and create a new Analysis Services Tabular Project. At this point I would also recommend that you rename the default ‘Model.bim’ to something more meaningful to your project. Or Open an existing project.

2. Right click on bim file and click properties.

3. Apart from all the other properties, you will find Workspace Database property in the bottom portion. The description of the properties is here. If you open SSMS and connect to SSAS Tabular instance, you should find a database with the same name as this property value. As you would notice, Workspace Database cannot be changed. It is read-only and that’s the one we want to change.

4. Now right click on project ,click Open Folder in Windows Explorer and close SSDT.

5. In windows explorer, there would be *.settings file. The format of this file seems to be ModelName.bim_username.settings. It might be hidden so change the folder settings to show hidden files.

6. Open this file in notepad. It is an xml although everything will come on one line. I would also recommend that you don’t try to format the file. Save the settings file as is with a bak extension.

7. Now look for <DatabaseName> </DatabaseName> tag. The value between this tag is name of the database. Change this value to something more meaningful, save with the .settings extension and close notepad.

8. Open the project/solution in SSDT. Check the properties of bim file. The value of property Workspace Database would be what you set in Step 7.

9. You can also open SSMS and verify this.

For pre-existing projects you may get a generic error message the first time you re-open the project just ignore, save and reopen the project.

ldgaller2

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: