SharePoint 2010 Performance Point Service Configuration With SSAS
PerformancePoint used to be an independent product with the earlier version of SharePoint. In SharePoint 2010, it has been integrated in the form of PPS. But in order to effectively use PerformancePoint Services in SharePoint 2010, it should be properly configured.
Below are the steps to confgire Perfomance Point service in Sharepoitn 2010 and how to display Report and Score Card on SharePoint Site.
- Have the necessary database or content i.e the data from where you want to display the report/chart on SharePoint site, such as SSAS, SQL report server. This is mainly provided by the SQL database team or team that handles the external content where the data is stored. In this example I ma going to use AdventureWorks2008 sample Database provided by Microsft. You can download it depending on your SQL version
As I am going to use SQL Analysis service from SQL server we need to create Cube from the Sample database in SSAS. Check this to create it http://www.accelebrate.com/sql_training/ssas_2008_tutorial.htm.
If you have all the above things ready or you the content ready to be display the report on SharePoint 2010 performance point, please proceed to the next steps.
Start the secure store service and PerformancePoint service from the Central Admin services
Create a secure store service and set encryption key
Associate the Secure Store Service to the web application
Create the PerformancePoint service application
Once the Performance Point Service is created, we need to set up an unattended service account so that we can use a single shared account to access an external data source. To do so, select the newly created Performance Point Service and click on "Manage" on the ribbon.
Next click on PerformancePoint Service Application Settings. By default the Secure Store Service name will be pre-populated. Provide the unattended username and password of the account. Check this for more info on unattended service account http://technet.microsoft.com/en-us/library/ee836145.aspx
Next Associate PerformancePoint service to the Web application.
Next activate following features on site collection: 1) PerformancePoint Services Site Collection Features ( from Site collection Administrator) 2) PerformancePoint Services Site Features (Manage Site features)
Create a new Library of type "DataConnection Library For Performance Point". Name this as ‘PPS Connection’ Or any name you like
Create a List of type "Performance Point Content List".Name this as ‘PPS Configuration’ or any name you like
Open the List PPS Configuration and click Add item to it. This should open a Dashboard Designer. If you are using it for first time it may take little time.
Right click on PPS Connection and Add ‘New Data Source’.
Here various types of data sources are available. I will be using Analytical service.
Next Screen you need to provide your SQL server name/instance and click on down arrow in the Database field. It will populate all the SSAS database/cube. Remember you need to have SSAS database created in SQL. Select the SSAS Cube from the Cube drop down. Click on ‘Test Data Source’ Button to check the database connectivity. You can rename the Data Source as per your wish.
Note: If you don’t see the database then you need to check your database connection. Also check for Event Viewer Log and ULS log for errors.
Next, Create a Scorecard, right click on PPS Configuration list in Dashboard Designer
Select the Analysis Services template from Microsoft for Scorecard. We will use a Wizard option for our Scorecard. This wizard will guide us to select a data source. The next step is to add a new KPI in our Scorecard or select the existing KPIs from our data source, if there is KPI in your database.
Choose ‘Create new KPIs from SQL Server Analysis Services Measures’ and click the Next button.
Create KPI as Show below. This may differ as per your database. You need to select what you want from your SSAS server
Now click the Next button until you reach the Finish wizard button. Keep all the default settings. Rename your Scorecard as ‘Sales Scorecard’. You are now done with a basic scorecard with three KPIs. It should look like the one shown below:
Now let’s modify the Scorecard by adding our products and timeline. You will see a ‘Details’ window on your right hand side screen, as shown below. Drag from right and Drop at the main screen. See the red line in the image below
Add Country on Actual and Target and selected the desired country as shown below:
Then click on Edit and Update from top ribbon. Your Scorecard value will change as :
Similarly, add Calendar Year from right side on Scorecard.
And Select desired members as shown below:
Again Edit and Update from top ribbon. Your Scorecard value will change as shown below:
This is it. You have created the Score Card.
Next, we will create Report using Dashboard Designer. In Dashboard Designer, right click on PPS Configuration List.
In the popup wizard, select Report Template. Here we select Analytical Chart.
In the next step it will ask to select Data Source. Select the data source that we created earlier
On clicking Finish, it will create a Report Window. To configure the report, we have to put dimension data
Let’s put Product data in Series as shown below, Drag and Drop
On Adding the product or what you choose, it shows as below:
Click on the dropdown and select the desired products.
Then Add Fiscal Year from right side detail window into Bottom series as shown below:
And that’s all. Report is ready as shown below:
Save your work from Dashboard Designer
Next, we will display the Report and Score Card on SharePoint site
SharePoint provides OOB Web parts. Those are PerformancePoint Scorecard/report.
For displaying Scorecard, use Scorecard web part and provide the path of your newly created Scorecard in the Location property. The same is applicable for reports as well. Copy and paste the relative path. You will see the report and score card