Setting Up a Custom SQL2012 and Sharepoint 2010 BI machine

A few days ago I finally got my custom Sharepoint 2010 and SQL 2012 demo image ready. With only a few days to go till my PowerPivot and Power View presentation at SQL Saturday 138 in Sydney, this was very relieving!

There are a few changes to how you would do this compared to the SQL 2008 R2 setup. Hence I thought I would share my experiences as I went through some pains to get it all right. Note here I am setting up a single machine for demo/evaluation purposes only. So, I will not be following the general best practice recommendation of using separate service accounts and Application Pools.

My aim was also to build up a “lean” machine that has the minimal Sharepoint services required to run BI demos. Hence, the steps I follow are probably not what you would do for a production environment setup.

I also acknowledge that scripting a lot of this in PowerShell can save a lot of time. However, I believe you have to go through it at least once to understand the components and what to script in the first place J This is not a detailed step by step post for every item – links are provided at times. However for the BI specific configuration, especially what has changed, I’ve been quite specific.

Whats New in this setup?

Ok, the main differences in this install/setup compared to SQL 2008 R2 are:

1. SSIS is now a true service with its own Catalog
2. Data Quality Services (DQS) has been introduced
3. Master Data Services (MDS) has been revamped a lot
4. SSRS is now a proper Service Application in Sharepoint and hence its configuration is different. SSRS now also includes PowerView
5. You will need 3 instances of SQL Analysis Services
   1. Multidimensional and Data Mining (i.e. what we have had since SQL 2005)
   2. Tabular
   3. PowerPivot Integrated
6. There is now a PowerPivot configuration tool to assist with setting up the PowerPivot integration. This is greatly improved from the previous PowerPivot setup experience, where it was either all completely manual on an existing farm. The other option in SQL 2008 R2 was where you could use the PowerPivot for Sharepoint Setup option from the SQL installer on an unconfigured farm. This would configure the farm for you. This had a couple of drawbacks in my opinion:
   1. It would install an own named instance of the SQL Server called POWERPIVOT, containing the Sharepoint databases
   2. Unless you setup using PowerShell, you could not control the naming of the Service Application Databases, and those horrible GUIDs where always suffixed. Below is a reminder from my 2008 R2 VM. While we cant get rid of all of the GUIDs, we can for most. Again Powershell is the solution if you want complete control.

      

I have not yet configured MDS and DQS and I may blog this in another set of posts. From memory when I played with it in Denali RC0 DQS was fine but I had issues with MDS and its Silverlight 5 requirement, which was still beta.

Pre-requisites

1. If you don’t have an internet connection from your VM, make sure you have downloaded the Sharepoint 2010 pre-requisites. A handy PowerShell script exists on TechNet to save you manually doing all this.
2. Then you will also need the following

• Windows Server 2008 R2
• Microsoft Office (Excel and PowerPoint)
• Sharepoint 2010 Service Pack 1 (if your install media doesn’t already have this built in, mine did). You CANT use the SQL 2012 features without SP1
• February 2012 Cumulative Update for Sharepoint 2010 (current at the time of writing)
• SQL Server 2012 Samples. It’s a good idea to deploy all these to test all your features are working as expected.
• Visual Studio 2010 Tools for Office Runtime. The MDS Client for Excel and PowerPivot 2012 will need this. You can get these clients and other goodies in the SQL Server 2012 Feature Pack
• Microsoft Silverlight
• PowerPivot 2012 RTM
• SQL Server 2012 Data Mining Addins

Installing SQL Server 2012

1. Setup Windows Server 2008 R2 and add the Active Directory Domain Services role. This is important so we can use a proper domain account for services. Ive seen problems when using built in service accounts. It will suggest you add in the DNS Server role a well. I did so. In my virtual machine I setup 2 network adapters – one with a fixed IP on an internal virtual LAN Segment, the other bridged so it could access my host’s internet connection.
2. Install a DEFAULT instance of SQL Server 2012. Things to note:

• I used the domain Administrator account to run all services except for the Browser, Agent and Full Text Filter.
• Remember to add the Administrator account as ad admin to the relevant services as you configure them in the install Wizard. You don’t want to be locked out later

These are the features I selected (pretty much all).. noting that when I configured the services, I picked SSAS Multidimensional.

1. I then ran the Reporting Services Configuration tool to setup the Native SSRS instance.
2. I wanted a Tabular version of SSAS, so I ran the installer again and chose to install a NAMED instance of SQL Server 2012, which called TABULAR. This time the only feature I selected was Analysis Services, but when asked to choose the mode I selected Tabular.

Installing Sharepoint

1. This part is simple. I just ran the installer, went through the pre-requisites install and then installed Sharepoint. When the Sharepoint Installer asks you what type of install don’t choose Standalone. Use the Farm option. The standalone version will go ahead and install SQL express which is not what we want.

   

2. Once Sharepoint is installed don’t run the Configuration Wizard
3. Install SP1 (if you need to i.e. your install source didn’t have it built-in), and then the latest Cumulative Update

Base setup of up the Sharepoint Farm

This is where things get interesting. We are going to use the new PowerPivot Configuration tool to do this for us. The beauty of it is it will setup a bare minimum Sharepoint farm. Nice and lean, so it will run quicker which is important for demos. I find the Microsoft supplied VMs HUGE (75Gb) and slow. Check here for my guide on how to convert one from Hyper-V to VMWare.

1. Start the PowerPivot Configuration Tool from the SQL Server 2012 Program Group

   

2. Pick the first option as shown – there will be no choice on an unconfigured farm anyway

   

3. The tool will perform some checks and then present you with a list of tasks. Fill in the first screen with settings to match your setup, then click Validate. You’ll see this:

   

   At this point you could press Run and have it perform all those activities. However, whats great about this setup compared to the SQL 2008 R2 is that we can actually customise some of Service Application and associated database names to remove the dreaded GUIDs that get appended to them. You can also control some of the IIS setting such as Web Application name and App Pool. These are the tasks I suggest click on to customise:

• Create PowerPivot Service Application
• Create Default Web Application
• Deploy Web Application
• Create Site Collection
• Create Unattended Account for DataRefresh

1. When you’ve finished customising you’ll notice it informs you where you’ve made changes to the tasks. You can click the Script tab to view the PowerShell commands that will be executed. This is great to learn from and also to save away for a future scripted setup. I can see this being useful to tweak for a proper production setup. Beware: you need to select the topmost task to get the whole script showing up. This means if you are looking to find the PowerShell commands for just a select task/task group, you can click the appropriate section.

   

2. Click Validate, and if successful, click Run. This will take a bit of time… If all goes well you will see this:

   

   And you can verify in IIS that the sites were setup:

   

3. Navigate to our new SharePoint site. I usually at this point remember to turn off Internet Explorer Enhanced Security, and I add the Sharepoint Site to the trusted sites. Makes things easier!
4. So now our site should work and if you browse to it you should see the vanilla Sharepoint page configured with a PowerPivot Gallery. I wont include a screenshot. There are two more components left to configure for a basic BI setup. SSRS/Power View and then PeformancePoint.

Enabling SharePoint Enterprise Features and Services

Before we try to setup these other BI services it’s a good idea to turn on the Enterprise features for the Site Collection and Site.

1. In SharePoint go to Site Settings:

1. Then manage the Site Collection Features:

Make sure the following are activated (Many will already be active, but ive stated all for completeness):

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

1. Next we have to set enable some features at the Site level. So go back to Site Settings as in Step 1 above. This time select Manage Site Features:

   

   Make sure the following are active:

• PerformancePont Services Site Features
• SharePoint Server Enterprise Site features
• SharePoint Server Publishing

Note we don’t turn on the Report Server File Sync because that is only required for SSRS 2008 R2 to synch back to the SSRS catalogue. In SQL 2012 SSRS is now a proper Sharepoint Service Application – something that was done to address performance problems in Sharepoint integrated mode.

Configuring SSRS Integration and Power View

This part tripped me up the first time. I thought I had everything working but SSRS and reports simply wouldn’t work! After some research, trial and error I understood all the components and config required to get it to work. The main problem is that with the steps we’ve taken so far, the SSRS Service itself (new in SQL 2012) and its proxy are not actually setup in Sharepoint yet.

A couple of quick PowerShell commands I found on the MSDN SSRS Integration Configuration Guide helped here, in the section entitled “Install and Start the Reporting Services SharePoint Service”. The note there explains why we don’t have SSRS services configured – we installed SQL first so there was no farm for it to configure SSRS on.

1. Run the Sharepoint PowerShell as an admin:

1. Run some PowerShell commands to install the service installed, add the proxy and then start the service:
   1. Install-SPRSService
      

   2. Install-SPRSServiceProxy 

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

2. Next we can create a SSRS Service Application from Central Admin

   

   Heres where we select the one we want:

   

   The settings ive used are highlighted (first page):

   

   Then scrolling down:

   

   This should get you to the Success screen shown below – Click the highlighted link.

   

   This will take us to the page below where the SSRS service account is given access the SQL Agent. Note the credentials below are sent to the SQL server.. so using the “sa” account will work here too. Note that you can create subscription and alerts if the SQL Agent service is not running – so turn it on if you want to demo this feature.

   

3. How to test this? Make sure you have Silverlight installed and then upload a PowerPivot workbook into the PowerPivot Gallery. The Gallery has a link automatically if Silverlight isnt present. You will see a new “Create a PowerPivot Report” option in the gallery:

Clicking that will open up the Power View designer:

Configuring PerformancePoint Services

This part is much easier!

1. Verify the PerformancePoint Service is running. In Central Admin you need to go to “Manage Services on Server” (shown below) , and once in there just Start the PerformancePoint Service if it isn’t running.

   

2. Next we need to add the PerformancePoint Service Application. So in Central Admin once again we go to the Manage Service Applications page as we did with the SSRS service.

1. Configure the service similar to how we did SSRS, shown below:

and continuing:

If all goes well you should see this:

1. Next we need to configure the Unattended Service Account – its not absolutely necessary but a good idea to get done now. It all depends on how you plan to access your data sources – Unattended gives you the highest level of caching in PPS and the data source. So go back Manage Service Applications in Central Admin, and click on the newly provisioned service:

Once there, you need to change the app settings:

1. We only need to set the account, the other settings on the page can be left at their defaults

Client Tools

Since we haven’t used the Business Intelligence Centre site template there will be no shortcut button to get to Dashboard designer. This can be reached via the following URL, which you can add to the site menu, a page or even customise via Sharepoint Designer:

http://<siteURL >/_layouts/ppswebparts/designerredirect.aspx

Just a few things left now to make this complete. You will need to install:

1. Excel 2010
   
   1. PowerPivot 2012 RTM
      
   2. Data Mining Addins
      
2. Visio 2010
   
3. PowerPoint 2010 (to show export capability from Power View)
   
4. You need to install the Desktop Experience feature on the server in order for the Publish to Sharepoint feature of Excel 2010 to work. If you don’t, you wont be able to browse the Sharepoint site from Excel at all.

Adding SSAS roles via Powershell

I needed to delve into Powershell again recently as a means to automate the creation of a long list of roles and the addition of AD groups to them.

Since there were so many i thought it would be easier to have a list of roles in a text file and just parse that to create all your roles automatically. The script posted here is a first pass that was used during development and does the following:

• iterate through a text file containing a list of roles.
• For each line found:
  • check if the role exists and then delete it. (the script caters for an initial role creation so the delete was more for testing)
  • Create a new role object from the current line in the source text
  • Add role to SSAS database
  • Add database permission for role
  • Add cube permissions for role to all cubes in database
  • Associated an AD group and allowed set with the role – my example uses [Organisation].[Cost Centre Code] where the member names are the same as the role names. Another assumption for is that the AD group has the same name as the role. One could extend this script easily to parse a CSV for more complex requirements)
  • log any errors found when adding new objects to the database

I got the basic code from Darren’s post and made a bunch of changes to suit my requirements. So here it is….

# Script to create roles in EIS cube databases via a text file

[System.reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
[System.reflection.Assembly]::LoadWithPartialName(“System.IO”)

$logFile = “PATH TO LOG FILE”
$sw = new-object System.IO.StreamWriter($logFile)

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect(“localhost”)

$dbname = “YOUR_SSAS_DB_NAME”
$db = $svr.Databases.item($dbname)

# Print the Database Name
“Adding Roles to Database: ” + $db.Name

#read file here
$roleLines = Get-Content “PATH TO TEXT FILE CONTAINING LIST OF ROLE NAMES”

#repeat for every line in the file
foreach ($roleName in $roleLines)
{
“Processing Role for: ” + $roleName
$groupname = “DOMAIN_NAME\” + $roleName

#check if exists and delete if so
$roleToDelete = $db.Roles.FindByName($roleName)

if ($roleToDelete)
{
“Existing role found… deleting!”
$roleToDelete.Drop(“AlterOrDeleteDependents”)

$error.clear()
if ($error[0])
{
$sw.WriteLine($rolename + “: Error deleting existing : ” + $error[0])
}

}
else
{

#Create Role and add AD group
[Microsoft.AnalysisServices.Role] $roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($rolename)
$roleToCreate.Members.Add($groupname)

#Add to db
“… ‘” + $roleToCreate.Name + “‘ adding to database”
$db.Roles.Add($roleToCreate)

$error.clear()
$roleToCreate.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error Adding to DB: ” + $error[0])
}
else
{

#Create Database permission
“… ‘” + $roleToCreate.Name + “‘ updating database permissions”
$dbperm = $db.DatabasePermissions.Add($roleToCreate.ID)
$dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

$error.clear()
$dbperm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating DB Perms: ” + $error[0])
}
else
{

# add role to each cube
foreach ($cube in $db.Cubes)
{

#create Cube permission
“… ‘” + $roleToCreate.Name + “‘ adding to permissions in : ‘” + $cube.Name + “‘ cube”
$cubeperm = $cube.CubePermissions.Add($roleToCreate.ID)
$cubePerm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

$error.clear()
$cubeperm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating Cube Perms: ” + $error[0])
}
}

if(!$error[0])
{

#create dimension permission
“… ‘” + $roleToCreate.Name + “‘ adding to Org Structure Dim permissions”
$dim = $db.Dimensions.FindByName(“Organisation”)
$att = $dim.Attributes.FindByName(“Cost Centre Code”)
$dim.DimensionPermissions.Add($roleToCreate.ID)

$dimperm = $dim.DimensionPermissions.add($roleToCreate.ID)
$dimperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

“… ‘” + $roleToCreate.Name + “‘ adding cost centre restriction”
$attPerm = $dimPerm.AttributePermissions.Add($att.ID)
$attPerm.AllowedSet = “{[Organisation].[Cost Centre Code].&[” + $roleName + “]}”

$error.clear()
$dimPerm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating Dimension Perms: ” + $error[0])
}
}

}

}

}

}

#cleanup
$sw.close()
$svr.Disconnect()