Overview

I could not find a reference for getting the Contoso sample database working in PerformancePoint. Here are the steps to set up Business Intelligence using OOTB SharePoint functionality. There are other resources for consuming such data, and I may follow this up with another post with more detail.

Prerequisites and Preparation

• I use SharePoint 2010 Enterprise against SQL Server 2008 Enterprise. These steps should work against the standard version of SQL Server 2010 or 2012. SharePoint 2010 Enterprise version is required for PerformancePoint services.
• Determine Pass Phrase for Secure Store key. This may be entered three times. A "strong" password is recommended.
• Determine and password for the unattended service account will be used for the PerformancePoint Service. This can be the same account as used in other places. Since I intend to use this for demo purposes, I will use a managed account that is already set up. This account must have DBOwner for the content database of the site collection where the BI tools will be used.
• If it is not already set up, determine which application pool the PerformancePoint Service should use, and consider whether you need a new account if it is to be a new application pool.
• The identity of the application pool must have db_owner membership for the content database(s) of the site collection(s) where you want to use PerformancePoint.
• Download both ContosoBIdemoBAK.exe and ContosoBIdemoABF.exe from http://www.microsoft.com/en-us/download/details.aspx?id=18279.

Create Database Steps

• Execute both files from the download using the same directory for the unzip output. For whatever reason, SQL Server did not like restoring from the default temporary directory. I created a new directory under c:\projects and moved the files there.
• Create ContosoRetailDW database in MS SQL server in the database engine.
  • If this is a demo environment, the "Simple" recovery model in Options is suggested. See Gigantic SharePoint SQL Server LDF Files.
• Create Contoso_Retail database in SSAS
• Restore ContosoRetailDW.bak to ContosoRetailDW.
  • In Options, select Overwrite the existing database
• Restore ContosoRetailDW.bak.abf to Contoso_Retail database
  • In Options, check "Overwrite database" box

SharePoint Central Administration Setup

Secure Service Store

• Central Admin > Application Management - Manage Service applications > Secure Store Service (either one)
• Generate new Secure Store key?
  • If you see "Before creating a new Secure Store Target Application, you must first generate a new key for this Secure Store Service Application from the ribbon".
    • In the ribbon, click Generate New Key.
    • Enter the Pass Phrase for Secure Store key twice and click ok.
    • Click Refresh Key and enter the Pass Phrase for Secure Store key again and click Ok.
  • If you see "There are no Secure Store Target Applications in this Secure Store Service Application. You can create a new Target Application from the Manage Target Applications group in the Edit ribbon group," or if you see a Target application, the key has already been created and does not need to happen again.

Create PerformancePoint Service Application

If you resisted the temptation of creating all the service applications when creating your SharePoint Farm, good move (not sarcasm). To determine whether it exists:

• Go to the Central Admin home page.
• Under "Application Management" click "Manage Service Applications".

If it is not there,

• Click New > PerformancePoint Service Application
• Name: PerformancePoint Service Application
• Check Add this service application's proxy to the farm's default proxy list.
• Either create or use an existing Application Pool.

Then, navigate to Central Admin Home Page > Manage Services on Server > Click Start on the PerformancePoint Service.

Unattended Service Account

In "Manage Service Applications" (Central Admin > Application Management - Manage Service applications),

• Select the PerformancePoint Service Application and click Manage or just click on the link.
• Click the first link: PerformancePoint Service Application Settings.
• In the "Unattended Service Account" section, enter the user name and password to be used for querying data sources.

Optional - Create a New Site Collection with the BI Template

• Central Admin > Application Management – Create site collections > Template – Enterprise > Business Intelligence Center
• As noted above, the account used for the domain service account must have DBOwner rights.
  • Option 1 - Modify using SQL Server Management Studio: Right click [database name] > Security > Users > New User… > Role Members – check db_owner and Owned Schemas check db_owner
  • Option 2 - Using the SharePoint 2010 Management Shell, run the following commands:

$webApp = Get-SPWebApplication("Web Application Name")

$webApp.GrantAccessToProcessIdentity("domain\userName")

• If you do not create the new site, you must enable the BI features on an existing site which is beyond the scope of this post.
• In any event, grant the unattended service account Contribute rights to the various BI lists. You may do this by adding it to the "Members" group of your site collection.

Using Dashboard Designer

Put your BI site into the trusted zone

If your site is not in your local intranet, put it there:

• Open your SharePoint BI site in Internet Explorer.
• Tools > Options > Security Tab > Local Intranet > Sites > Advanced > Add

Run Dashboard Designer

• From your BI site, hover over the "Monitor Key Performance" tab (or any of the tabs) and click "Start Using PerformancePoint Services".
• Click "Run Dashboard Designer".
• Make sure Dashboard Designer is connected to your server: File Button > Designer Options > Server. Enter the URL of your BI Site Collection and click Ok.

Connect to the Contoso_Retail Cube

• Right click "Data Connections" > New > Analysis Services.
• Enter the SQL Server's computer name.
• Choose Contoso_Retail as the database. Note that Dashboard Designer uses the term "database" when some might think "cube."
• Choose a Cube from the database such as Inventory. Note that that Dashboard Designer uses the term "cube" when some might think "perspective."
• Optional: Click Test Data Source.
• Optional: rename "New Data Connection" to something more relevant, like "Inventory."
• Important: before you start consuming the cube in the next section, save the data connection.

Consume the Cube

• In the ribbon, click Create > Reports - Analytic Chart.
• Choose Inventory for the Data Source.

There is a lot more here that you can do, but this gets you started.

Summary

We looked at setting up the sample Contoso retail database for use in Business Intelligence against SharePoint's PerformancePoint Services.