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.
- 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
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
$webApp = Get-SPWebApplication("Web Application Name")
- 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.
We looked at setting up the sample Contoso retail database for use in Business Intelligence against SharePoint's PerformancePoint Services.