I recently had my first “legitimate” purpose for opening and modifying a production SQL Server Analysis Services database in online mode. I needed to add a new cube calculation to one of the existing production cubes. As you’d expect, I opened up the project, added the calculation, deployed it to the test environment, and was happy with what I saw so I proceeded to check in my changes to the project. The project is integrated with TFS source control. Now it’s time to get the new cube calculation migrated to production.
Here’s where things got interesting. Also checked into TFS were other changes to the SSAS database, made by another BI developer, which weren’t scheduled to be deployed for another couple of weeks yet. Since you must deploy an SSAS database in its entirety, deploying in its current state wasn’t an option.
One choice would be to temporarily roll back the other developer’s changes from source control and deploy from the project – that would be appealing if I had quite a few changes to deploy & if the sequence of events in a multi-developer environment made reverting to a prior changeset possible. However, since my change was one very small calculation, I chose to open up the “online” version of the SSAS database and add the calculation directly. The change was also checked into the project file, so next time it’s deployed my change won’t be lost.
Let me just be clear this is absolutely not the first choice – there’s a much bigger risk of error when you make the change twice (i.e., once in the online version & once in the project). And working in the online version just shouldn’t be a habit. However, in a pinch it’s nice functionality to have in the ‘ol tool belt.
Opening an Analysis Services Database in Online Mode
Opening an SSAS Database in SSDT (SQL Server 2012) or BIDS (SQL Server 2008 R2 and prior):
If you’ve connected in online mode previously, the server(s) and database(s) will be listed in the middle box. If not, enter them at the top to connect:
When you are working in online mode, it will tell you so within the name of each tab across the top:
As soon as you make your changes and save, you're done. Saving changes while in online mode can be a bit slow.
Finding More Information
Technet – Working with Analysis Services Projects and Databases During the Development Phase