Additional SSAS Instance Installations in SQL Server 2012 (RC0)

Overview

Please check out the other blogs in the series related to setting up each of the main SQL Server 2012 features. Links to each of the related blogs are shown below.
Installation Order:

1. SQL Server 2012 (RC0) Business Intelligence Configuration
2. SQL Server 2012 (RC0) Installation and Configuration
3. SharePoint 2010 Installation for SQL Server 2012 (RC0)
4. Additional SSAS Instance Installations in SQL Server 2012 (RC0)
5. SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
6. SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
7. SSRS Alerting (Coming Soon)
8. MDS (Coming Soon)

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

At this point in our configuration, we have already installed and configured one instance of SSAS in Multidimensional and Data Mining Mode. In SQL Server 2012, there are now two more types of SSAS services available for installation.
The first, PowerPivot for SharePoint Integrated Mode was introduced in SQL Server 2008 R2. This provides users the ability to view and store PowerPivot workbooks in SharePoint 2010. I would compare this functionality to Excel Services; however, these PowerPivot workbooks can be much more powerful since they truly extend the ability for Excel to provide insightful analysis on multiple data sources. The creation and use of PowerPivot within Excel is beyond the scope of this blog but I am already in the middle of putting together a blog on the new PowerPivot features in SQL Server 2012.
The second SSAS service that can be installed in addition to the classic Multidimensional Mode is new to the SQL Server business intelligence stack. This is SSAS in Tabular Mode. As opposed to a multidimensional cube, you can really think of the Tabular Mode as a simpler “table” approach for analyzing data. I think that Tabular Models will be easier for companies that are new to BI to understand and initially configure, and I really think of Tabular Mode as the “Corporate” version of a PowerPivot document. This gets into a larger discussion of the Microsoft BI Semantic Model (something we will all have to get used to hearing about). In fact, with a few clicks PowerPivot data models can be pulled out of Excel PowerPivot workbooks and loaded onto the SSAS Tabular Mode instance. At this point, IT can gain control of the model, enhance it, distribute through additional presentation layers, and add security. This will be quite beneficial for allowing IT put some additional structure around the use of Tabular Models that have become mission critical for an organization. There will be more specific blogs on this topic from me in the future.
For now, don’t worry too much about each SSAS mode if you have not used them before. Let’s just concentrate on how to install them. As I have mentioned a few times in this blog series, I am going to install all three types of SSAS on one server. This is for simplicity within this blog series, and does not represent how a production system would actually be configured.
Typically I would recommend at least a couple of the instances of Analysis Services be moved to a different server so that the processing of the SSAS databases could be spread across multiple machines. For a lot of the clients I work with, PowerPivot and Tabular mode are (or will be) new. Initially this means they probably won’t be used heavily so are good candidates to be combined onto a single server.

Step-by-Step Instructions

To begin, we’ll start with installing SSAS in Tabular Mode. Open up the SQL Server 2012 installation media and proceed through the Setup Support Rules. At the point where you are able to select an Installation Type, select the option to perform a new installation of SQL Server 2012.

Continue through the installation options until you reach the Setup Role screen. Since we are installing SSAS in Tabular Mode first we need to select the option of SQL Server Feature Installation. Once you have selected the proper options click on Next.

On the Feature Selection screen unselect all of the options except for Analysis Services. Once again, click on Next.

I prefer to name my instances in a way that will make them easy to identify in the future, so I will simply name this instance “TABULAR” (I am so creative right?) To keep things simple, I will give this nice instance the same Name and ID.

Skipping up to the Server Configuration screen, I will once again use my SSAS domain user. If you do not have your server setup as a domain check out the first blog in the series. You will need this server to be setup as a domain controller or part of a domain in order to configure SSAS PowerPivot with integration with SharePoint 2010.

The key to our installation is to select Tabular Mode on the Analysis Services Configuration screen. Set your administrators for the SSAS instance and then click all the way through to complete the installation. That’s it, very easy to install.

Now that we have walked through and completed the installation of SSAS in Tabular Mode, we now need to install SSAS for PowerPivot for SharePoint. For the sake of time I will skip over the steps that are the same as the Tabular Mode installation, however, there are a few points to make.
First, there is an option on the Setup Role screen to add a SQL Server database engine to the installation. Since we already have a brand new instance of SQL Server Relational Engine installed earlier in the blog series I unselect this option before moving forward.

Secondly, on the Installation Rules page you could receive two warnings. The first warning was caused by unselecting the option to install the database engine on the previous screen. This is okay. Essentially it is telling us that PowerPivot for SharePoint needs SharePoint, and that SharePoint needs the database engine. Again, since we have this already we are okay.

The second warning lets us know that we need SharePoint 2010 SP1. If you have not installed SharePoint 2010 SP1 please refer to the SharePoint 2010 Installation for SQL Server 2012 blog found earlier in this series.

Since none of these warnings apply for us, we can continue. Unlike the Tabular Mode, we are not able to provide a Name for the PowerPivot instance, this must be POWERPIVOT. Again, to keep things simply I give this instance the same ID as shown in the screenshot below. Click through the rest of the installation and you’re done!