Power View for Multidimensional CTP available

Well this is happening much sooner than I expected. T.K. Anand posted in his blog yesterday that there’s now a preview available for Power View against SQL Server Analysis Services multidimensional models.

Why is this a big deal? As you may know, Power View is a very cool data visualization tool which shipped with SQL Server 2012 RTM, which becomes available if you install Reporting Services (2012) in SharePoint mode, and also deploy SharePoint 2010 Enterprise. (Full set of requirements and deployment checklist available here.)

In the RTM release, Power View could consume only tabular semantic models, such as those created in PowerPivot for Excel and uploaded to SharePoint, or models created in SQL Server Data Tools and deployed to SQL Server 2012 Analysis Services in Tabular mode (not quite an exhaustive list, there are a couple of permutations of these).

Multidimensional models, better known as cubes, were not accessible to Power View (though they’re certainly available to other Reporting Services tools as well as PowerPivot).

Earlier this month, during a keynote at the PASS Summit, Microsoft announced that support for querying cubes from Power View was coming, but that it would be a v.Next timeframe. Imagine my surprise and delight to see yesterday’s announcement.

CTP/Preview download here. Release notes and links to documentation here. New to Power View? Start here.



SQL Server 2008: Forgot to add an administrator account?

(Note: This post was originally written on SQL Server 2008. I have migrated it intact as it still applies to SQL Server 2008R2)

File under: letting myself serve as an example for others.

Screen shot 2009-12-10 at 8.16.51 PM.png

I recently built a test box with an instance of SQL Server 2008 Developer Edition. I’ve done this so many times that I apparently got sloppy and stopped paying attention during setup. This time it caught up with me. On first launching Management Studio to do some post-install configuration, I realized that I’d “locked myself out” – I skipped the step to add my account to the sysadmin role on one of the instances. Setup requires that some account be added as sysadmin, but I didn’t remember what I’d chosen, and there were no other accounts on the box that stood out as likely candidates.

I’d also opted to stick with the default Windows authentication mode, meaning the built-in sa account was not available.

For those who haven’t made the move to SQL 2008, you’ll find the following step in the Setup program. Unlike in earlier versions, local Windows admins aren’t automatically granted access to SQL Server 2008. You’ll want/need to designate one or more account to be added to the sysadmin server role. Skipping that step somehow got me in this mess.


I really wanted to avoid the time it would take to uninstall and reinstall the instance. Happily, I remembered another way: as a member of the local Windows Administrators group, I could start the SQL instance from a command line with the -m parameter for single user mode, connect to the service, and let myself in the “back door”. (“m” for single user? Sure, that makes perfect sense. I remember this as “mono” for one at a time.)

One of the side effects of starting in single user mode is that members of the local Windows Administrator group can connect to SQL Server with its one connection. (Whether this is a good idea from a security standpoint remains open for debate, but it sure saved my bacon this time.) This would give me the ability to log in, map my Windows account into a SQL Server login, and add my new login to the sysadmin role. Here are the steps I followed.

  1. Using the SQL Configuration Manager, I stopped the instance I needed to work with.
    Screen shot 2009-12-10 at 8.18.14 PM.png


  2. I also stopped any other SQL-related services which might try to connect to the instance, using up my one connection. This included Agent, and might also include Analysis Services or Reporting Services, depending on your installed options.
    Screen shot 2009-12-10 at 8.19.07 PM.png


  3. From a command prompt, I navigated to the SQL Server folder where the program executable lived. 
  4. I started SQL Server’s executable with the -m parameter and waited for it to complete startup and recovery of any databases. (Note that the error log information echoes to the command prompt window but will simply pause when it’s done startup – leave this window open to keep SQL Server running in this single-user mode. You can confirm if you successfully launched in single-user mode by looking for a line in the output that reads “SQL Server started in single-user mode. This an informational message only.
    Screen shot 2009-12-10 at 8.28.49 PM.png


  5. From a query tool, I connected to SQL Server using my Windows account. While I could have used a new query window in SQL Server Management Studio, I didn’t want to wait for it to load, so I used SQLCMD from another command prompt session. (You can’t use the Object Explorer pane in SSMS to connect when in single-user mode: clickers beware! This is one of those times where knowing the code (or having a saved script) is essential) If you’re unfamiliar with SQLCMD, the -S switch specifies the instance name, the -E specifies that you’re using Windows Authentication. These switches are case-sensitive.
    Screen shot 2009-12-10 at 8.35.19 PM.png


  6. Once connected, I created a login for my Windows account:
    Screen shot 2009-12-10 at 8.39.23 PM.png


  7. Then I used a system stored procedure to add my account to the sysadmin fixed server role.
    Screen shot 2009-12-10 at 8.43.30 PM.png


  8. Now that I’ve let myself back in to SQL Server, I stopped the service with the SHUTDOWN command. I could have closed the other command prompt window as well. 
  9. Finally, I restarted the service. Since I already had a command prompt window open, I opted for Windows’ NET START command. The SQL Configuration Manager, the Windows Services Control Panel, or the Registered Servers pane in SSMS would also have worked.
    Screen shot 2009-12-10 at 8.44.59 PM.png

And here’s my visual confirmation (I could have simply tried to reconnect using SQLCMD, but I think we’re all tired of command prompt screenshots by this point). Note that I used the system function IS_SRVROLEMEMBER to check my status. Looking at the properties of my login, or of the sysadmin role in Object Explorer would have shown this as well.

Screen shot 2009-12-10 at 8.46.47 PM.png

I hope this helps someone else in the same predicament!