THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Installing Analysis Services #ssas #Denali CTP3 and #PowerPivot #Denali CTP3

Microsoft released the CTP3 of Microsoft SQL Server Code Name “Denali”, as you can see in the SSAS product group blog post.

This is the first public release of Denali and after many months of forced silence (I was under NDA) I can finally write my first hand experience with this new version! In the next months I will write on the blog more technical content, but today I start with my setup experience.

The setup experience is similar to SQL Server 2008 R2. I installed Denali as an instance on my Developer Notebook side by side with an instance of SQL Server 2008 and an instance of SQL Server 2008 R2. Everything was fine and it seems that all the services (old and new ones) are still working regularly. Default settings are usually good, but there are a couple of notes important for BISM Tabular.

  • By default, Analysis Services install the Multidimensional instance.
    • Because you can have only one type of models for each instance of Analysis Services, if you want to install both BISM modes (Multidimensional and Tabular) you have to run the setup two times, one for each BISM model type required.
  • By default, Analysis Services runs with a “secured” account (technically, a service account)
    • If you try to import data in a BISM model from the user interface, you are using your own account during all the wizard.
    • However, whenever the model is populated, it is temporarily published on a SSAS server, which runs with a service account. If this account is not enabled to read data running on the relational source, the refresh will seem not to work in Visual Studio, whereas in reality it is the service that is not authorized to read data from the data source.

There is also a new CTP3 release of PowerPivot for Excel. I removed the previous version of PowerPivot to install the new one on my developer notebook. Everything seems ok and I appreciated that the user interface already has a good quality (no missing icons in the ribbon, for example).

I’ll start playing with this CTP3 as soon as possible, and I will have more content to blog about.

Published Wednesday, July 13, 2011 2:24 AM by Marco Russo (SQLBI)



James Serra said:

Hey Marco - to clarify, if I wanted to use both Multidimensional and Tabular models, I would have to setup two instances of SSAS?  One SSAS instance can't support both?

July 12, 2011 9:46 PM

Andrei said:

Hi Marco and thanks for sharing!

The question I have: how about BIDS?

Visual Studio 2010 has no BIDS (Business Intelligence Development Studio) and does not support SSAS projects. It means that one need to use 3+ years old Visual Studio 2008 for Analysis Services projects (or BIDS from SQL Server 2008). So my question is:

does CTP3 and Denali enabling/adding support for Analysis Services projects or we need to wait until some future release of Visual Studio for that?


July 12, 2011 11:52 PM

Marco Russo (SQLBI) said:


you have to install two instances of SSAS. To do that, you need to run the setup again after the first instance setup (you cannot install two instances in the same setup). I think it is important to carefully plan instance names (I avoid to install default instances - every instance has a name, but I have at least three different version installed on my developer machines)


Finally, CTP3 has BIDS and SSMS integrated in VS2010 shell.

We'll be synchronized with latest VS, at least as soon as a new version of VS will be released!

July 13, 2011 3:39 AM

Peter Schott said:

Any decent instructions exist for setting up Sharepoint + SQL + PowerPivot - especially for a VM environment? Can never seem to get that quite right and Sharepoint is definitely not my strong point, but we can't use some of the cooler features without it. :(  (Really wish MS could give us this stuff without requiring Sharepoint - make some other way that gives us a way to NOT set up a new SP environment or something)

July 13, 2011 12:31 PM

James Serra said:

Thanks Marco.  Do you know of a way to find out if an installed instance of SSAS is using the Multidimensional or Tabular model?

July 13, 2011 4:11 PM

greg kramer said:


Vidas has the guide to use at:

Hope he gets around to updating it for CTP3

July 15, 2011 5:27 PM

Valentin said:

@James Serra

Issue the below XMLA command against the instance:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">












and you will receive a response, which will contain either




In any case when you connect to the instance using SSMS, you will notice that multidimensional instance has Assemblies folder and an icon that looks like a yellow cube.

When you connect to the tabular instance though, you get the blue spreadsheet-like icon and the only folder available will be Databases.

July 19, 2011 10:26 PM

Marco Russo said:

July 20, 2011 1:17 AM
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement