The Ultimate SharePoint Business Intelligence – Setting up SharePoint 2013 Demo with SQL 2012 PowerPivot and Visio Services Step by Step

– Elaborate Real Strength of SharePoint in Business Intelligence Perspective –

    Earlier We supposed to download Information worker (IW SharePoint) SharePoint Demos in SharePoint 2010 and 2007 Days as Virtual Disks which takes lot of time to download due to the size of the HDDs and VM Machine Configuration flies. IW 2010 Came out as a single box along with AD | SQL | SharePoint all in one and Exchange Server Separately as another Box.

    The one I’m stepping today is an great Demo Setup for those who were waiting to elaborate and Demonstrate Real SharePoint 2013 BI which is the ultimate goal of Having SharePoint in place at Enterprise Organizational level. Microsoft has not released IW Setup for SharePoint 2013 yet but instead CodePlex has done a great job on sharing such a setup together with Microsoft Tools and Content Packs of their Demos. Really Appreciate the developers who did this outstanding job for the global SharePoint Community.

    This setup demonstrate real awesomeness of SharePoint BI together with SQL PowerPivot for SharePoint and Visio Services. Once you set it up you will get various types of Reports | Dashboards | Workflows etc.. So before we begin have a look at below captures of the real output I got through this !

    1. The Giant SharePoint BI with PowerPivot            2. The Ultimate Visio BI

    clip_image001                  clip_image002

    3. People Sites (My Sites) and the great New SharePoint Profile Look in Social Perspectives

    clip_image003

    This is only a PowerShell based script which can be easily downloaded from CodePlex and run on a Prepared VM. It also contains documentation for each package you download but since it doesn’t have screenshot and steps on preparing AD I thought of writing this article with step by step screen shots and little bit of experienced issues and troubleshooting.

    Below are the steps (Installation Order) that we will take to prepare this lab. Following this order to install is Important

    1. Download Scripts from Codeplex
    2. Create a Virtual Machine with Recommended configurations
    3. Install Prerequisites on top of Created VM
    4. Configure Active Directory and DNS
    5. Execute AD Builder Script
    6. Execute SQL Script
    7. Execute SharePoint Script
    8. Execute People Pack Demo Script
    9. Run PowerBI Deme Script
    10. Configure PowerPivot For SharePoint 2013
    11. Execute Visio Services Demo Script

    Download Below Scripts

    For More Refer – http://sharepointdemobuilds.codeplex.com/

    Media Requirements

So Let’s get this done. So excited isn’t it ? I was too !!

First Create a VM (Hyper V or VMware) with Minimum of 16 GB Ram together with 4 Virtual Processor Cores. You can run with less resources but it won’t perform really well since it runs three major roles (All in one – SQL, SharePoint and AD) together with all SharePoint Services and three SQL Instances.

Map the Windows Server 2012 Media in to Virtual Machine, Start it and boot it using Windows Server. Once Windows Is installed give the Administrator’s Password as – pass@word1. across the whole setup all the user and Service accounts will be using this password to make it easier of using.

clip_image004

Then after Windows Installation, modify the Local Machine name as – DEMO2013A from the Server manager. All the scripts will be referring to this name so it’s compulsory to use the same name.

Log-in to the VM as ‘Contoso\Administrator’

Give a Static Class C address to your Virtual Network Adaptor (e.g. 192.168.150.101).

Step 1 – Installing Windows Server and Configure Active Directory with DNS

Assuming you have followed above steps to install and configure Windows Server.

Next is to Add AD Role from the server manager: Prerequisites – Static IP for Primary Adaptor (for DNS Role)

Simply Click ‘Next’ here

clip_image005

Leave with the Default option here and ‘Next’

clip_image006

Here too it’s default and click ‘Next’

clip_image007

Choose the Server role here – Just ‘Active Directory Domain Services’ and ‘DNS Server’ we need. Click Add to include the chosen one and ‘Next’

clip_image008

clip_image009

Nothing to do here so simply hit ‘Next’

clip_image010

Summary will be displayed here. Review it and hit ‘Install’ to proceed.

clip_image011

Give it a moment. Probably it will take 6-8 Minutes. Depends on your HW resources

clip_image012

Once you are done with the Role installation. The completion pop up will be prompted, Click on the Link ‘Promote this server as a domain’ to configure AD DNS.

clip_image013

Below Wizard will Appear. Nothing to think much just Put the domain name and hit next (Its compulsory to put it as – ‘Contoso.com’ here )

clip_image014

Choose Windows Server 2012 in both Functional Levels and Leave the password with something you remember if in case of recovery (this isn’t the common password we use often in this setup)

clip_image015

Leave these defaults as well

clip_image016

clip_image017

Review it and go ahead

clip_image018

It will check all the prerequisites and validate. Click ‘Install’ to proceed.

clip_image019

Finally you will get this banner across so let it restart the server automatically.

clip_image020

Once restarted launch it will launch the Server manager. You should be able to see the new roles added.

clip_image021

Go to DNS Tab and right click and Launch the DNS Manager from there.

clip_image022

It will Launch the DNS Manager UI. Right Click and Create a new Reverse Look up zone via ‘New Zone’

clip_image023

Hit ‘Next’

clip_image024

Leave with the Defaults here and ‘Next’

clip_image025

Here you have to bring the tick up. Choose the ‘first option’.

clip_image026

Leave the Defaults Here too

clip_image027

Give the network address of your Adaptor here.

clip_image028

Go with the Defaults again

clip_image029

Click finish to Complete the wizard.

clip_image030

Now Expand the Reverse Lookup zones and right click on New Zone we just Created and ‘New Pointer PTR’

clip_image031

Give the IP of your Server Adaptor here and browse to get the Host Name from Forward Lookup Zone.

clip_image032

It will look like below once added.

clip_image033

Now Configure the Local Server adaptor as below

clip_image034

Right click on Server Name under ‘DNS Manager’ and click ‘Launch NS Lookup’. It Should Resolve the Name as below

clip_image035

Now we got few Setting to be configured which are important for moving with further steps.

Launch Windows Powershell as Administrator and type ‘Set-ExcecutionPolicy RemoteSigned’ and hit ‘Enter’

clip_image036

Launch the Server Manager and turn off Windows Firewall for Domain Networks, Remains ‘On’ for Private and Public. Remote Management and Remote Desktop must be Enabled too.

clip_image037

clip_image038

And then Turn off the ‘IE Enhanced Security Configuration’ as well.

clip_image039

Step 2 – Building AD for SharePoint and SQL (This will Create all the Active Directory Objects for SharePoint and SQL Such as user Accounts and Service Accounts)

Log-in to Server as contoso\administrator and Copy the Downloaded AD Script (ActiveDirectoryDemo15.2.3) to Server (make a folder in C: Drive and copy there). Unzip it and you will see the ‘ContentPackInstaller.exe’ utility there. Right Click on it and Run as Administrator.

Note: There are two ways that you can run these packages hereafter. You don’t need to run ContentPackInstaller every time, instead you can simply run them through Windows PowerShell. Simply run below line customized with each Package Name and Path.

Prerequisites:

1. AD, DNS Configured

2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)

3. Excecution Policy Modified (Set-ExecutionPolicy RemoteSigned)

4. ActiveDirectoryDemo15.2.3 Package Downloaded and Copied to VM

Locate to the Extracted Folder from PowerShell and run below Line (When use PowerShell it has to be Extracted)

.\Install http://intranet.contoso.com (This URL Depends on the Package you Deploy. e.g. for People Pack you have to use http://mysite.contoso.com)

e.g. :-

clip_image040

If you use ContentPackInstaller, When you run it first time you get the below choices. Go with Defaults here. (No Need to Extract When use Tool) and click ‘Next’

clip_image041

Browse the AD Content Pack (Zip File) and Click ‘Add’

clip_image042

Hit ‘Install’ to proceed

clip_image043

Leave the Default URL Here and Click ‘Ok’

clip_image044

Give It a Moment… This will Probably Take 3-4 minutes

clip_image045

Once done you will see it under installed packs. It will also generate the result in a Report and open up automatically right after the completion. So this completes our first package Installation which creates all the Demo AD Objects for SharePoint and SQL.

clip_image046

Step 3 – Install SQL Content Pack

Prerequisites:

1. AD, DNS Configured

2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)

3. Execution Policy Modified (Set-ExecutionPolicy RemoteSigned)

4. SQL Server 2012 With SP 1 ISO has to be mapped to VM as ‘D:’ Drive

5. SQL2012SP1Demo15.2.5 Content Pack File Copied to VM

6. Logged in to the VM as ‘Contoso\spadmin’ (Password is same for all the Accounts in this setup which is – pass@word1)

Here also You can Simply User PowerShell to Execute this. I’ll be showing the way through the tool. (as always PowerShell if Preferred and Reliable)

Same as the Previous Step, Browse and Locate the ‘SQL2012SP1Demo15.2.5’ Zip File and hit ‘Install’

clip_image047

URL is Default here (http://Intranet.contoso.com) so simply click ‘OK’

clip_image048

This will probably take 15-30 Minutes Depends on your resources. It installs 3 SQL Instances specifically for SharePoint Databases, PowerPivot requirements.

clip_image049

Once Completed It will also Generate and automatically open up the Result.

clip_image050

Step 4 – Install SharePoint Content Pack

Prerequisites:

  1. AD, DNS Configured
  2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)
  3. Execution Policy Modified (Set-ExecutionPolicy RemoteSigned)
  4. SQL2012SP1Demo15.2.5 Content Pack Installed
  5. SharePoint Server 2013 RTM Media Copied to VM as ‘D:’ Drive (Required SP Version : 15.0.4420.1017 RTM – Do not use the latest releases which may not be able to restore the backup files through the script in the next steps)
  6. All SharePoint Prerequisites are Installed
  7. Logged in to the VM as ‘Contoso\spadmin’ (Password is same for all the Accounts in this setup which is – pass@word1)

    First thing to get the SharePoint Server 2013 Binaries Installed (Do Not Run the Configuration Wizard. Entire Configuration handled through the SharePoint Content Pack which comes next)

    Go ahead and Run the SharePoint Installer.

    clip_image051

    Leave the File Locations Default here.

    clip_image052

    Give it a moment. It will take about 5-8 Minutes Depends on your Resources.

    clip_image053

    Now to Install the SharePoint Configuration Content Pack. Here also I’ll be using tool but you may use PowerShell which is more reliable.

    As Usual browse and point to the ‘SP2013ConfigDemo15.2.6.zip’ file and click ‘Install’ to start

    clip_image054

    URL Must be Default here as well.

    clip_image055

    It will automatically Launch the PowerShell and run below script. If everything is green you are cool.

    clip_image056

    Give it a Moment. This will take about 15-25 minutes Depends on your Resources.

    clip_image057

    Keep Monitoring !. You will notice the success through below lines on the Shell. User Access Control will prompt for PowerShell sometimes during this process so keep monitoring to click yes each time.

    clip_image058

    When Everything Completed it will open up the log file through WordPad.

    clip_image059

    Go Ahead and explore IIS. You will see the Web Applications has been created for each Purposes.

    clip_image060

    And Yes all three sites (Central Admin, MySite, Intranet) will be opened up automatically as well.

    clip_image061

    clip_image062

    Step 5 – Install PeoplePack Content Package

    Not yet Impressive this guy. So Next is People Pack to deploy which will upload all the nice profile images to Active Directory and Syncing Automatically to SharePoint through User profile Service App.

    Prerequisites:

    1. AD, DNS Configured
    2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)
    3. Execution Policy Modified (Set-ExecutionPolicy RemoteSigned)
    4. SQL2012SP1Demo15.2.5 Content Pack Installed
    5. SharePoint Content Pack Installed
    6. ‘PeoplePack’ (PeoplePackDemo15.2.8) Content pack Copied to VM
    7. Logged in to the VM as ‘Contoso\spadmin’ (Password is same for all the Accounts in this setup which is – pass@word1)

    As Usual browse and point to the ‘PeoplePackDemo15.2.8.zip’ file and click ‘Install’ to start

    Reminding Again that You don’t have to use Tool each time, just execute PowerShell lines instead if you are comfortable with it.

    clip_image063

    Leave the Path Default (It will refer to MySite URL Automatically here so do not change). Simply click ‘OK’ to start

    clip_image064

    Before You Start Make Sure the Forefront Identity Manager Service is up (coz this happens all the time when you setup a new SharePoint Environment). If it’s Not Go ahead and set it to Automatic and Start it Manually. (But the Identity manager Synchronization must be already Running else you may got a different issue with Service Account Permissions etc..)

    clip_image065

    Give it a Moment here. It will take probably 15-25 Minutes to Complete the Profile Updates and Synchronization. And keep monitoring as it’s really cool to see how each profile Being Updated through Shell during the Process !!.

    clip_image066

    clip_image067

    clip_image068

    Once Completed it will generate the report as usual.

    If you browse the MySite through a general user you will see the updates. Or else just type a user Name like ‘Paul’ in Search Box, if everything went well you may see all the picture has been updated.

    clip_image069

    Step 6 – Install Self Service BI Pack

    Next is to run the BI Pack. This Script configures the SharePoint BI Site with Samples which is the main module of this Demo. During this process it restores few Databases from backups located inside the package Folder.

    Prerequisites:

    1. AD, DNS Configured
    2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)
    3. Execution Policy Modified (Set-ExecutionPolicy RemoteSigned)
    4. SQL2012SP1Demo15.2.5 Content Pack Installed
    5. SharePoint Content Pack Installed
    6. ‘PeoplePack’ (PeoplePackDemo15.2.8) Installed
    7. ‘Self-ServiceBIDemo15.2.7’ Package Copied to VM
    8. Logged in to the VM as ‘Contoso\spadmin’ (Password is same for all the Accounts in this setup which is – pass@word1)

    Launch the tool and Locate the Self-ServiceBIDemo15.2.7.zip then add it as usual. Then Install

    clip_image070

    Leave the Path Default here. This will take about 6-10 Minutes depends on your resources. Once completed you will be able to see the Result opened automatically on WordPad.

    clip_image071

    Step 7 – Configure PowerPivot for SharePoint 2013

    Prerequisites:

    1. AD, DNS Configured
    2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)
    3. Execution Policy Modified (Set-ExecutionPolicy RemoteSigned)
    4. SQL2012SP1Demo15.2.5 Content Pack Installed
    5. SharePoint Content Pack Installed
    6. ‘PeoplePack’ (PeoplePackDemo15.2.8) Installed
    7. ‘Self-ServiceBIDemo15.2.7’ Installed
    8. Logged in to the VM as ‘Contoso\spadmin’ (Password is same for all the Accounts in this setup which is – pass@word1)

    To do this, Launch the PowerPivot for SharePoint 2013 Tool from the Start menu of the VM.

    clip_image072

    No option here except the First one so click ‘ok’ and go ahead

    clip_image073

    Give it a Moment

    clip_image074

    Once the validation Complete you will get below Interface. Fill the Parameters by Providing the account Details as below and hit ‘Validate’

    – Default Account UserName – contoso\spservice

    – Default Account Password – pass@word1

    – Default Server – DEMO2013A

    – PowerPivot Server – DEMO2013A\POWERPIVOT

    clip_image075

    If the validation Succeeded, you are getting this pop up. So click ‘ok’ to proceed.

    clip_image076

    Once Validation Successful, just Run It by saying YES here.

    clip_image077

    Give it a while. Probably 5-8 Minutes.

    clip_image078

    It’ll update the status like below In the Middle of the Process so keep monitoring.

    clip_image079

    Finally the Success massage is here.

    clip_image080

    This step completes the Self Service BI Model so now go ahead and Install the Silverlight on VM then try to Explore the Self Service BI Site, It will display the Site like a charm. Have a look below.

    clip_image081

    Step 8 – Install Visio Service Content Pack

    So the final Step is to get the Visio Service Demo Installed. This step Completes the whole setup of our Demo.

    Prerequisites:

    1. AD, DNS Configured
    2. Security Settings Configured (Firewall, IE Enhanced Security, Remote Management etc..)
    3. Execution Policy Modified (Set-ExecutionPolicy RemoteSigned)
    4. SQL2012SP1Demo15.2.5 Content Pack Installed
    5. SharePoint Content Pack Installed
    6. ‘PeoplePack’ (PeoplePackDemo15.2.8) Installed
    7. ‘Self-ServiceBIDemo15.2.7’ Installed
    8. VisioServiceDemo Package Copied to VM
    9. Logged in to the VM as ‘Contoso\spadmin’ (Password is same for all the Accounts in this setup which is – pass@word1)

    Browse and Point to the ‘Visio Service Demo Zip’ File. And Install

    clip_image082

    Install path should be the Default as below.

    clip_image083

    It will launch the Result on WordPad Once it completed as usual so This completes the entire Configuration of the Demo.

    Go ahead and Open up http://Intranet.contoso.com/Sites/Visio

    Known Issues:

    1. You will get below Error on PowerShell if you haven’t run the – Set-ExecutionPolicy RemoteSigned

    .\Install.ps1 : File C:\SharePointBIDemoBuilderWithSQL2012\PeoplePack\PeoplePackDemo15.2.8\Install.ps1 cannot be

    loaded. The file C:\SharePointBIDemoBuilderWithSQL2012\PeoplePack\PeoplePackDemo15.2.8\Install.ps1 is not digitally signed. You cannot run this script on the current system. For more information about running scripts and setting

    execution policy, see about_Execution_Policies at http://go.microsoft.com/fwlink/?LinkID=135170.

    At line:1 char:1

    1. The-term ‘Get-SPServiceApplication’ is not recognized as the name of a cmdlet, function, script file, or operatable program.

    clip_image084

    For this issue you have to Put below highlighted line in the Install.ps1 script in each Package

    clip_image085

    Also Make Sure the Relevant Files are Unblocked through their Properties

    clip_image086

    1. Sorry This Site Hasn’t been Shared With you. http://Intranet.contoso.com/Sites/Visio Site Isn’t accessible.

    This is simply because Spadmin doesn’t have rights on Particular Site Collection. To resolve this open up Central Administration and Add SPADMIN as the Site Collection Admin for Visio Site Collection under Intranet.Contoso.com Web Application.

     

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s