In this article I will evaluate the latest functionality provided by Microsoft SQL Server 2012 - Power View for Multidimensional Models. This SQL Server 2012 Community Technology Preview (CTP) release allows connections between Power View and Multidimensional Models (cubes),  in addition to the Tabular Models which have been supported since the release of SQL Server 2012 RTM/SP1.

I will provide step–by-step instructions on installing and creating a Microsoft Business Intelligence infrastructure based on Power View, a new tool for Data Visualization to demonstrate this new functionality.

At the end I provide some conclusion about this product, what is missing, some suggestions about improve the UI and finally an interesting demo with Natural Interaction through Kinect from Microsoft Team. 

System Requirements

Before installing this new SQL Server release, there are some requirements that we must be fulfilled.

Hardware

The hardware requirements for installing the required software was very demanding for my VM running on a desktop environment used for this evaluation. You must satisfy SQL Server 2012 requirements and SharePoint 2013 requirements to try out the CTP. You can see more detailed information on the minimum and recommended requirements here:

• Hardware and software requirements for SharePoint 2013
• Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP

Internet connection

Having a network adapter with access to the internet is recommended. Also we must turn off the Windows Firewall in the server. Finally, for testing purposes, it's a good idea to turn Internet Explorer Enhanced Security Configuration off.

Software

These are the required software componentss you need to install for Power View for Multidimensional Model:

• Microsoft Sharepoint 2013 is an integral part of the Microsoft Business Stack. We need to complete the prerequisites through Microsoft SharePoint 2013 Preparation Tool. This download installs the prerequisites for a single server with built-in database e.g. Microsoft WCF Data Services 5.0, Framework 4.5, Windows Server AppFabric, etc.
• We need Microsoft SQL Server 2012 with Power View for Multidimensional Model CTP; unfortunately, this is not a SQL Server Production Version when I wrote this document.
• Microsoft Silverlight in order to work with Collection Site inside of SharePoint 2013 and PowerPivot Gallery.

Installation Overview

These are the steps we will be following in this article.

1. Assign a static IP on a LAN Segment for a Domain Controller.
2. Add a Domain Controller Role to the Server.
3. Promote Windows 2012 Server to Domain Controller.
4. Create Domain Accounts for SQL and SharePoint Services.
5. Install a new and complete instance of SQL Server 2012 CTP.
6. Install prerequisites for SharePoint 2013, Restart and complete the Installation.
7. Install SQL Server Power Pivot for SharePoint.
8. Run the Tool PowerPivot Configuration Tool to integrate both SQL Server and SharePoint.
9. Enable SharePoint Enterprise Features and services.
10. Configure PerformancePoint Service Application.
11. Configure SSRS Integration and PowerView.
12. Add Report Server Content Types to a Library.

1. Assign a static IP on a LAN Segment for a Domain Controller

The first step is assign a static IP to the server as is a requirement of  a Domain Controller.  For example we are going to use 192.168.1.11 with subnet mask: 255.255.255.0 and default Gateway: 192.168.1.1 however you can use another values as need it. The other setting is shown below:

2. Add the Domain Controller Role

Before installing anything, we need to add the "Active Directory Domain Services" role by using the Add Roles and Features wizard as depicted in the following screenshots (if we run from Windows Server 2008 we can run the command "dcpromo"):

Run "Server Manager" for Windows Server 2012 and set up this feature:

Select the Role-Based installation type:

In "Select destination server", check "Select a server from the server pool":

To install Active Directory select Active Directory Domain Services. In "Add Roles and Features Wizard" dialog, select "Add Features":

Then, select Active Directory Domain Services and click "Next >"

By default the Group Policy Management is selected, click "Next >":

The next screen shows basic information, click "Next >".

We need to confirm to continue with the configuration wizard:

The installation of the wizard begins:

3. Promoting Windows 2012 Server to Domain Controller

After going to Server Manager and selecting the left upper option with a warning signal, select "Promote the server to a domain controller":

When the Active Directory Domain Services Configuration Wizard appears, select "Add a new forest" and specify the root domain name e.g. "bigdata.com":

In the "Domain Controller Options", for "Forest Functional Level" and "Domain Functional Level" options, select "Windows Server 2012". For "Specify the Domain Controller capabilities" check Domain Name System (DNS) Server:

In the next DNS options warning dialog, just select OK:

Verify that the NetBios name is taken from Root Domain Name:

Leave paths in default values:

Review the options:

Run the prerequisites check:

4. Create Domain Accounts for SQL and SharePoint Services

Run Server Manager-> Tools-> Active Directory User and Computers:

Add the following Accounts:

• SQLS (SQL Server database engine Account)
• SSAS (SQL Server Analysis Services Account)
• SSRS (SQL Server Reporting Services Account)

It's best practice to create an account for each SQL and SharePoint service. The service accounts should also be added to the "Administrators" group as local administrators on the server and as "Domain Administrators".

5. Install a new and complete instance of SQL Server 2012 CTP

Select the first installation option of SQL Server 2012 CTP installer. A new SQL Server stand-alone installation or add features to an existing installation:

De-select “Include SQL Server product updates” because at the moment of installation this is the latest version.

Check "Run", a warning will pop up because it's not recommended to run an instance of SQL Server on a Domain Controller:

Specify the product Key (I left in an Evaluation version):

Select SQL Server Feature Installation:

Select Default Instance and the name of Instance ID to MSQSQLSERVER:

Press "Next >" in the "Disk Space Requirement" window:

Select the appropriate accounts created for this purpose during the "Create Domain Accounts for SQL and SharePoint Services" step:

Set the Authentication Mode to Mixed and provide a strong password. Also specify an SQL Server administrator (I select Add Current User):

This point is very important, we should select Multidimensional and Data Mining Mode:

Choose the "Install only" option.

Click "Next >" in the Error Reporting screen:

Click "Next >" in the "Installation Configuration Rules" screen when it's completed:

Click "Install" in the "Ready to Install" screen:

6. Install prerequisites for SharePoint 2013, restart and complete the installation

Before installing all the prerequisites for Sharepoint 2013, make sure you have a stable internet connection for your downloads.

This is the summary of the software prerequisites installed for SharePint 2013

Run the SharePoint 2013 setup, in my example I used the default File Location:

Check "Run the SharePoint Products Configuration Wizard now."

The SharePoint Products Confiiguration Wizard shows a warning because IIS, SharePoint Timer Services and SharePoint Administration Services will need to restart:

The intallation process keeps going uninterrupted. What is restarted are these services.  Then select Create a new server Farm:

Specify the Configuration Database settings and the Database Access Account. This account must be a Domain Account for the install to work correctly.

Then specify the port number and configure security settings.

We're given a summary of the steps that have been taken:

To see if the installation was successful we can run the tool "SharePoint 2013 Central Administration". This will open the administration Site of SharePoint.

7 Install SQL Server Power Pivot for SharePoint

Run the Setup from SQL Server SQL 2012 CTP. On the "Setup Role" screen, select "SQL Server PowerPivot for SharePoint" but don’t check the "Add SQL Server Database Relational engine Services to this installation" box at this time.

In the "Feature Selection" screen, leave the default options checked.

Click "Next >" when the operation completes:

Enter "POWERPIVOT" for the Instance ID on the "Instance Configuration" screen:

Select the appropriate accounts created for this purpose in the  previous step "Create Domain Accounts for SQL and SharePoint Services":

Set the Authentication Mode to mixed and provide a strong password. Also specify an SQL Server administrator:

Add a user to have administrative permissions for SQL Server Analysis Services:

The rest of the steps are very similar to the previous installation and should be intutitive.

8. Run the Tool PowerPivot Configuration Tool to integrate both SQL Server and Sharepoint

Once we've installed SQL Server Power Pivot for SharePoint we have two Tools to integrate and configure Sharepoint and SQL Server. One is called "PowerPivot Configuration Tool" and this is only for Sharepoint 2010. As we are integrating with Sharepoint 2013, we are going to use the second one "PowerPivot for Sharepoint 2013 Configuration Tool".

In the PowerPivot Configuration Tool we select "Configure or Repair PoverPivot for SharePoint":

The validation process has 24 steps to complete. Then we get the PowerPivot Configuration Tool with all the steps and parameters to configure.

In this wizard we need to set values for the SQL Server to create the database and specify the PowerPivot Server for Excel Services. This is the another instance of SSAS. This is the another instance of SSAS.

The next screen shows this screen with some values. Another value that we need to set in this PowerPivot Configuration Tool is the "Create Default Web Application" we must provide a URL to run, http://SERVER2012DC, which is the name of the server, has been used. Don't forget to include a contact e-mail in the "Create a Site Collection" step.

To check if the installation was successful, we should run the tool "SharePoint 2013 Central Administration". This will open SharePoint 's administration website running from a different port than the default TCP/80.

In addition to the administration site, another Sharepoint website known as a "collection site" should also be present. This collection site hosts a PowerPivot Gallery (a new template for BI sites) installed by default at http://SERVER2012DC.

9. Enable SharePoint Enterprise Features and Services

Initially, we must enable features needed to create Reports in Power View. On the main page of the SharePoint Administration Site, we have "System Settings" section under which we should select "Manage Services on server":

We need to enable: Business Data Connectivity Services and Performance Point Services just click on start link :

Next, in the page "Site Settings" and under "Site Collection Administration", we select "Site collection features":

At this screen, we activate these features:

• PerformancePoint Services Site Collection Features
• Power View Integration Feature
• PowerPivot Feature Integration for Site Collections
• SharePoint Server Enterprise Site Collection features

Go back to "Site Settings" screen, select  "Manage Site Features" and activate:

• BICenter Data Connections Feature
• PerformancePont Services Site Features
• SharePoint Server Enterprise Site features

 10. Configure PerformancePoint Service Application    

On the main page of Administration Site and under Application Management -> Manage Service applications.

From there, we select the "New" menu in the ribbon menu and select "PerformancePoint Service Application":

We must provide a Name for this application and also specify the Database Server and name. Then we use Windows authentication as credentials:

We set create a new Application Pool in IIS and provide a name for it.

After we finish creating the integration we have:

11. Configure SSRS Integration and Power View

To enable the integration with SQL Server Reporting Services and Power View we need to run some commands from SharePoint 2013 Management Shell and Run as Administrator.

Run the following PowerShell commands to install SharePoint integrated to SSRS:

• Install-SPRSService
• Install-SPRSServiceProxy
• get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

We return to Admin Site and select under Application Management -> Manage Service applications:

From there, we select the "New" in the ribbon menu and select "SQL Server Reporting Services Service Application":

We must set some values as the Name of Service for this SSRS Service Application. Create a new application pool for IIS and enter a name for it.

Scroll the page and enter the database server and a name for a database. Use Windows Authentication for credentials:

Set the Web Application Association to SharePoint -80:

After we finish creating the integration we get:

12. Add Report Server Content Types to a Library

The last step to enable and create Reports in Power View is adding report Server Content Type to a Library. We set this the default collection site running on http://SERVER2012DC and in the main page go to Settings -> Advanced Settings. In the Content Types section, select Yes to allow management of content types.

To add Reporting Services content types we go to Library Settings and under Content Types, click Add from existing site content types:

Select the site content types from SQL Server Reporting Services Content Types. In the Available Site Content Types list, click Report Builder and Report Data Source, then click "Add" to move the selected content type to the Content types to add list:

We should get this configuration:

After and before to create Reports in Power View we must download and install Microsoft SilverLight 5:

In this phase we need to deploy the database and Cube on SSAS, in this example I the very classic Adventure WorksDW and related cubes. You can download both from http://msftdbprodsamples.codeplex.com/releases/view/55330

We must create a Report Data Source similar to what we did in Reporting Services.

We must provide a Name for this Report Data Source and select the Data Source Type to "Microsoft BI Semantic Model for Power View". Also we left the windows authentication and test the connection.

Don't forget to set "Enable this Data Source"

Once we create a new Data Source we select the three points and in the options OPEN SHARE FOLLOW we select three points again to show the menu and select "Create a Power View Report"

You can follow the instructions here to populate and create an amazing PowerView Report:

http://social.technet.microsoft.com/wiki/contents/articles/14707.explore-the-adventure-works-multidimensional-model-by-using-power-view.aspx

Conclusions

Power View show us an interactive data exploration and presentation experience in this configuration with SQL Server 2012. A downside of this platform is the use of Silverlight which Microsoft will eventually stop supporting. For me, it would be better to follow a standard and clean way with HMTL5 without any plug-ins. Another drawback is the dependency on SharePoint to work with Power View, I would prefer working more exclusively with Reporting Services, as I have suggested through the Microsoft feedback portal: http://connect.microsoft.com/SQLServer/feedback/details/738938/power-view-without-silverlight

At the end I want to show a demo about Natural Interaction with Power View and Kinect made for the Microsoft Team

http://blogs.msdn.com/b/sqlrsteamblog/archive/2012/01/09/natural-interaction-and-microsoft-bi.aspx