Connecting to a SSAS 2012 Database in Online Mode

When working with SQL Server Analysis Services (SSAS), it is sometimes convenient to be able to connect to a database in “Online Mode” which means that every change you make and save is immediately saved to the SSAS database, as opposed to having to explicitly deploy the changes.

In SQL Server 2008 R2, connecting to a SSAS database in online mode was very simple:

1. Launch the SQL Server Business Intelligence Development Studio (i.e the VS 2008 Shell for BI project templates commonly known as BIDS).

2. Select Open off the File menu

3. Specify the server and database name, press OK.

4. And, voila!

Unfortunately, connecting to a SSAS 2012 database is not that simple. Its actually downright confusing thanks to incorrect documentation from Microsoft.

MSDN and Technet have instructions for doing this, which are almost identical to the way it is done with SSAS 2008 R2, but using the new SQL Server Data Tools (SSDT) (which is the successor to BIDS) instead of using BIDS, but there is a major problem with the instructions. They simply do not work!

Check out the MSDN article here: http://msdn.microsoft.com/en-us/library/ms365358.aspx

Now, check out my SSDT screen below:
Notice that there is no “Open” item on the File menu. I poked around everywhere else within SSDT but could not find a way to open an SSAS db in Online Mode within SSDT.

However, all is not lost. You CAN open an SSAS 2012 db in online mode, just not with SSDT.

Use the new version of SQL Server Management Studio (SSMS) instead! That’s right. SSMS now has a lot of features that used to be only available in BIDS. In fact, SSMS is now a Visual Studio product (see splash screen) and you can arguably say that SSMS is the new BIDS.
1. Launch SSMS 2012, which should be under the SQL Server 2012 icon from your start menu.

2. Connect to a database when prompted or click on cancel.

3. Select “Open” from the File menu, specify the Server and Database name of the SSAS db you want to open in Online mode. (Make sure that the “Connect to existing database” option is selected rather than the “Create new database” option.)

4. Click on OK and you will now be connected to the database in Online Mode.

If the above instructions for SSAS 2012 sound similar to the instructions for BIDS in 2008 R2, you are on to something. It seems that some of the functionality that was in BIDS has been ported to the new version of SSMS INSTEAD of SSDT. This is arguably a handy new feature of SSMS, the ability to do what used to be only avaialable in BIDS. But, if SSMS now can do all of the Business Intelligence features, what is the purpose of having SSDT? And, Microsoft really should update there MSDN and TechNet documentation from time to time, especially after a major product release.

Leave a Reply

  • Microsoft SQL Server
  • Microsoft SQL Server
  • Microsoft Office 365
  • Microsoft SharePoint
  • Microsoft Windows Azure