business intelligence consulting
 

Create Dashboards using PerformancePoint Dashboard Designer (SharePoint 2013)

by Hans Esquivel 17. April 2013 07:26

In this article we'll show you how to create a Dashboard using PerformancePoint Dashboard Designer for SharePoint 2013. We will connect to the "Sales Summary" cube of AdventureWorksDW2012, using an Analysis Services data source connection.

This article assumes you have the following:

  1. Implemented SharePoint Server 2013 Enterprise
  2. Experience working with SharePoint 2013.
  3. Have created a Business Intelligence site in SharePoint 2013.
  4. Exposure to PerformancePoint Dashboard Designer.
  5. General know-how of report design and development.
  6. Have extracted and deployed the AdventureWorks Multidimensional Models SQL Server 2012 analysis services project files for Microsoft Business Intelligence products. You can find it here.

Steps

  1. Execute PerformancePoint Dashboard Designer from SharePoint
  2. Configure SharePoint URL to Business Intelligence Center site.
  3. Creating a Data Source Connection
    1. Configure Data Source Time Settings for Time Intelligence
  4. Create a Filter(s)
    1. Member Selection
    2. Time Intelligence
  5. Create Report
    1. Analytic Grid
    2. Analytic Chart
    3. KPI Details
  6. Create Scorecard
  7. Create Dashboard
    1. Connect Filters
    2. Add Reports
    3. Add Scorecard
    4. Connect KPI Details
  8. Deploy Dashboard
  9. Run Report in SharePoint 2013 Business Intelligence Center

Note: An Analytic Grid and Chart may only use an Analysis Services data source.

Execute PerformancePoint Dashboard Designer from SharePoint

1. If you already have installed the Dashboard Designer, you can navigate to Start >> All Programs >> SharePoint >> PerformancePoint Dashboard Designer; if not, simply follow the steps below:

  1. Navigate to your SharePoint Business Intelligence Center site. For example, https://sharepoint/bi
  2. From the Quicklaunch, click on PerformancePoint Content link. You should see something similar to below. 

 

3. When the PerformancePoint Content page renders, click on  from the middle pane. This will install/load PerformancePoint Dashboard Designer.

 

Configure SharePoint URL

Note: This step may have already been configured for you. It's a good idea to review the configuration to familiarize yourself with Server Settings for PerformancePoint Dashboard Designer.

1. Click the upper left button of the Dashboard Designer window.

2. Click the Designer Options button.


3. Click the Server tab.

4. Change the SharePoint URL value to your desired location, if it's not already included.

For example: https://sharepoint/sites/bi

Create New Data Source

1. Right click on Data Connections and select New Data Source.

2. The Select a Data Source Template dialog will prompt you for a Category and Template. From the Template pane, select Analysis Services and click OK.

3. The Connection Setting pane renders. Click the Editor tab and select the Use Standard Connection option.

4. Type the name of your database server name in the Server: textbox. For example, MyDBServer

5. Below the Server textbox, select the "AdventureWorksDW2012Multidimensional-EE" from the Database dropdown list. This is an Analysis Services (AS) database. Note: your name of the AS database may differ.

6. Leave Roles textbox empty.

7. Select the 'Sales Summary' item from the Cube dropdown list. You may keep the default settings in the Data Source Settings pane. Click Test Data Source to validate the connection. So far, your configuration should look similar to below.

 

8. Click the Properties tab. Give the data source a name. For example, "AdventureWorksDW2012".

9. Click the Time tab. Here, we will configure our data source to work with Time Intelligence by specifying values that determine how time will be interpreted for our data source.

10. Select Date.Date.Fiscal from the Time Dimension dropdown list.

11. Under Reference Member section, select Browse...  button and choose December 31, 2010.

 

12. Under the Hierarchy level section, select Day. The Reference Date section should automatically choose the present day - keep this default value.

13. Configure the Time Member Associations like the image below.

 

14. Your final Time configuration should look similar to below.

15. From the Workspace Browser pane, right click the AdventureWorksDW2012 data source and click Save. 

Create New Filters 

We will be creating two Filters for this demo - Date and Sales Territory. The Date filter will allow us to capture yeartodate, quarter, current year and the previous 1-6 years. Let's begin.

Date Filter

1. From within the Workspace Browser, right click PerformancePoint Content folder and select New >> Filter from the list. This will open the Select Filter Template window.

 

2. From within the Select Filter Template window, select the Time Intelligence template item and click OK.


3. The Create Filter window renders and you should see the Select a data source page. Click Add Data Source from the available menu items.

4. You should see your previously create data source under the Workspace tab. Double click on the AdventureWorksDW2012 data source item from the list. This will add the data source the Select a data source page and place a check in the box right of the AdventureWorksDW2012. Click Next.


5. The next page of the wizard is Enter time formula. Add the following formulas, as presented in the image below. After you have completed adding the formulas, click the Preview button to validate the member values exist. Click Close after previewing the members.

 

6. Click Next.

7. From the Select Display Method page, select Multi-Select Tree style from the list and click Finish to complete the Date Filter configuration.

8. Select the Properties tab and name the Filter Date.

9. For the Display Folder textbox, enter Retail\Filter.

10. Right click the Date filter from the Workspace Browser and click Save

Sales Territory Filter

1. Repeat steps 1-4 from above (Date Filter), but for step 2. select the Member Selection template.

2. After completing step 4. and clicking Next, you will navigate to the Select Member page. Here, you will configure which Filter Dimension, Members and Filter Measure to use. Make the following configurations:

Filter Dimension = Sales Territory.Sales Territory

Filter Members = All Sales Territories, All descendants of "All Sales Territories"

Filter Measure = Default Measure (Sales Amount)

Note: The default measure may differ from your Cube configuration.

3. Click Next to navigate to the Select Display Method page.

4. From the Select Display Method, select the Multi-Select Tree style item.

5. Click Finish to close the window.

6. Click the Properties tab and name the Filter  Sales Territory.

7. For the Display Folder textbox, enter Retail\Filter.

8. Right click the Sales Territory filter from the Workspace Browser and click Save

Your should see the following Filters under the Workspace Browser.

 

Create Report

Analytic Grid

This report will present Internet Sales Summary (i.e., Sales Amount, Gross Profit, Gross Profit Margin, Average Sales Amount and Order Quantity) by Product Categories hierarchy and Fiscal Year dimensions. We will include background filtering for Date and Sales Territory.

1. From within the Workspace Browser, right click the Retail folder and select New >> Report.

 

2.The Select a Report Template window renders. Choose the Analytic Grid template from the list and click OK.

3. The Select a Data Source page renders - Make sure the Workspace tab is selected; choose the AdventureWorksDW2012 data source from the list and click Finish. The Analytic Grid design area will render.

You will notice three different sections on the bottom that allow you to drag items to Rows, Columns (bottom axis) and Background (optional). The Background serves as a hidden filter. You may add an item to the Background as either a static filter, or attach a custom Filter to a Background item when you define your Dashboard. Note: we will perform the latter for this demo.

The far right pane Details provides you with existing Measures, Dimensions and Named Sets available to drag to the Rows, Columns and/or Background sections.

4. Expand the Measures node and drag the following items to the Columns section. Collapse the Measures node when you have finished adding the items below.

1. Sales Amount

2. Gross Profit

3. Gross Profit Margin

4. Average Sales Amount

5. Order Quantity

5. Expand the Dimensions >> Product node and drag the following item to the Rows section. Collapse the Dimensions node when you have finished adding the items below.

1. Categories

6. Expand the Date >> Fiscal node and drag the following item to the Background section. Collapse the Date node when you have finished adding the items below.

1. Fiscal Year

7. Expand the Sales Territory node and drag the following item the Background section. Collapse the Sales Territory node when you have finished adding the items below.

1. Sales Territory

Try expanding the All Products node under the Product Categories column of the Analytic Grid. You should see something similar to below.

 

8. Click the Properties tab and name the report "Sales Summary".

9. Rename the Display Folder to "Retail\Reports".

10. Right click the report from under the Workspace Browser and select Save.

 Analytic Chart

This chart will present Sales Amount by Calendar Year (CY) and Product Category. We will include background filtering for Date and Sales Territory.

1. Follow steps 1-3 from the Analytic Grid above, except this time, for step 2., use the Analytic Chart template. You should see the same empty Report like the image in found in the previous step 3. above.

2. Expand the Measures node and drag the following items to the Columns section. Collapse the Measures node when you have finished adding the items below.

1. Sales Amount

3. Expand the Dimensions >> Product node and drag the following item to the Rows section. Collapse the Dimensions node when you have finished adding the items below.

1. Product Category

4. Expand the Fiscal node under the Date dimension and drag the following to the Bottom Axis section. Collapse the Date node when you have finished adding the items below.

1. Fiscal Year

5. Expand the Sales Territory node and drag the following item the Background section. Collapse the Sales Territory node when you have finished adding the items below.

1. Sales Territory

You should see something similar to below.


6. Click the Properties tab and name the report "Product Category Sales".

7. Rename the Display Folder to "Retail\Reports".

8. Right click the report from under the Workspace Browser and select Save.

KPI Details

A KPI Details report is a view type that provides additional information about scorecard values and properties. As dashboard users click cells in a scorecard, the KPI Details report updates to display particular information about that value. For example, when dashboard users click a value in the Target column, the KPI Details report displays details such as how performance is calculated, what banding settings are used, and what kind of indicator is used.

To display information, a KPI Details report must be connected to a scorecard in the dashboard. This means that a KPI Details report is not used in a dashboard without its accompanying scorecard on the same dashboard page. However, you can create and configure a KPI Details report one time, and then reuse it in multiple dashboards, connected to multiple scorecards.

NOTE: We will connect the "KPI Details" report during our Dashboard setup.

1. From within the Workspace Browser pane, right click the Reports folder and select Report.

2. Select the KPI Details template from the Report Template window.

  

3. Click OK. This will open the KPI Details report editor.

4. Keep the default settings in the Editor tab and click the Properties tab.

5. Change the name to "KPI Details" and make sure the Display Folder reads "Retail\Reports" (add it if the value is empty).

6. Right click the report from under the Workspace Browser and select Save.

Create Scorecard 

Scorecards are dashboard items that show performance for one or more metrics. Scorecards compare actual results to specified goals and express the results by using graphical indicators.

For this demo, we will create a scorecard from an existing Analysis Services data source.

1. From within the Workspace Browser, right click the Retail folder and select New >> Scorecard.

2. The "Select a Scorecard Template" window renders. Select Analysis Services template and make sure the "Use wizards to create scorecards" is checked.

3. Click OK.

4. The Create an Analysis Services Scorecard window renders. Select the AdventureWorksDW2012 data source from the list and click Next >.

5. For the KPI Source, choose the Import SQL Server Analysis Services KPI's option. This will query a list of available KPI's from the Analysis Services database. It may take a minute to load the items.

Note: If you get an error, it may be one of the two issues:

1. You may have to grant permissions to access KPI's from your Analysis Services server.

2. You need to install the proper ADOMD, AMO and OLEDB components. You can begin Microsoft® SQL Server® 2008 R2 Feature Pack. You may want to do the same from the SQL Server 2012 Feature Pack.

6. Select all three items from the KPIs to Import list and click Next >.

  

7. We will not set a Measure Filter. Click Next >.

8. We will not set Member Columns. Click Next >.

9. Finally, accept the default value for "Create KPI's in" and click Finish on the Locations page.

10. Rename the Scorecard "Company Health".

11. Click on the first column to highlight the Revenue, Expense to Revenue Ratio and Product Gross Profit Margin rows. 

  

12. From the Details pane, expand the Dimensions folder >> Calendar and drag the Calendar Year member over the very left border of the three highlighted rows, while holding down the Shift Key.

Note: it's a little tricky, but you should see a vertical blue line across all of the three rows. Once you see that, drop the dimension.

13. The Select Members window renders. Select CY 2005 thru 2008 and click OK. You will notice all columns values are blank.

14. Click the Edit tab from the Dashboard Designer Ribbon.

15. From the View section, click Update. Clicking Update with refresh the data values. You should see something similar.

Note: You can format the values from visiting the KPI's that were created earlier and changing the Number Format. We will not cover formatting in this demo.

16. Right click the scorecard from under the Workspace Browser and select Save.


Create Dashboard

We will now design our Dashboard to include the Filter's, Analytic Grid/Chart report and Scorecard we created earlier in this demo. After completing the design, we will save/deploy to SharePoint Business Intelligence Center and test the results.

1. Right the Retail folder and select New >> Dashboard.


2. The Select a Dashboard Page Template window will render. Choose the 2 Rows template from the list and click OK.

 

3. The Dashboard designer view renders. This demo is using a two zones - one header (filters) and one detail (analytic grid). From within the Pages section, change the Page 1 name to "Company Sales".

4. Add another Zone to the bottom row. Place your cursor on the Bottom Row zone and right click to select Add Below. You should have three zones now.

5. From within the Details pane, expand the Filters >> PerformancePoint Content >> Retail >> Filter nodes.

6. Drag both Date and Sales Territory filters to the Top Row zone. Collapse the Filters node when you have completed this step.

7. Expand the Reports >> PerformancePoint Content >> Retail >> Reports nodes.

8. Drag the Product Category Sales report to the Bottom Row zone.

9. Drag the Sales Summary report to the very both zone "Zone 1".

Your Dashboard designer should look similar to below.


10. From within the Date filter, drag the Member Unique Name item to the Drop Fields to Create Connections section of the Product Category Sales report.  

You should see the following Connection window. Accept the default setting and click OK.  

  

11. From within the Sales Territory filter, drag the Member Unique Name item to the Drop Fields to Create Connections section of the Product Category Sales report.  

You should see the following Connection window. Accept the default setting and click OK.  

  

12. Do the same for the Sales Summary report, located on at "Zone 1".

13. Click the Properties tab. 

14. Change the Name value to Company Sales. 

12. Change the Display Folder value to Retail\Dashboard.

13. Click the Editor tab and add a New Page from within the Pages pane.

14. Select the 2 Rows template from the Dashboard Page Template window.

15. Name it Company Scorecard.

16. From within the Details pane on the right, expand the Scorecards node >> PerformancePoint Content >> Retail.

17. Drag the Date filter to the top zone and the Company Health scorecard to the bottom Zone of the page.

You should see something similar.

18. From within the Date filter, drag the Member Unique Name item to the Drop Fields to Create Connections section of the Company Health scorecard.

19. When the Connection window renders, select "Row" for the Connect to: value:

20. Click Save icon from the upper left corner of the Dashboard Designer Ribbon.

Note: Stay on the Company Scorecard page for the next step.

Add KPI Details to Dashboard

1. Create another Zone below the Company Health scorecard. Tip: Right click the border of the zone and select Add Below.

2. In the Details pane on the right side of the window, expand the list of available reports. Drag the KPI Details report to a dashboard zone.

3. Within the KPI Details box that you added to the dashboard, click the down arrow located to the right side of the report name, and then click Create Connection. The Connection dialog box opens, which enables you to configure the connection between the KPI Details report and a scorecard.

4. Click the Items tab. In the Get values from list, select the scorecard that you want to connect to the KPI Details report. (You should see the KPI Details report already listed in the Send values to box.

5. Click the Values tab and locate the Connect to list. In the Connect to list, select Cell.

6. Use the Source value list to select Cell: Context.

7. Click OK to close the Connection dialog box. In the center pane of the workspace, in the Connections box for the KPI Details report, you can see the name of the scorecard that you have connected to it.

8. In the Workspace Browser, right-click the dashboard and then click Save. Deploy the dashboard to view and test the KPI Details report. 

Deploy to SharePoint

1. From within the Workspace Browser pane, right click the Sales Dashboard item and select Deploy to SharePoint...

2. The Deploy window renders. Click the Dashboard folder. Keep the default value under the Master Page dropdown.

3. Check the Include page list for navigation. If you plan to have a dashboard consist of more than one page, select the Include page list for navigation. This will add a simple hyperlink navigation to the master dashboard, while deployed in SharePoint.  

4. Click OK. Deployment process begins... You will now be redirected to SharePoint Business Intelligence Center - Internet Sales. You should see something similar.

 

 

5. Click the Date filter and select Three, Four, Five and Six Years Ago. 

6. Click the Sales Territory filter and check Europe. The values for both the Analytic Grid and Chart should change.

7. Click the Company Scorecard hyperlink from above the filters. You'll notice the Scorecard and the pane below for KPI Details.

8. Click the first value under the Value column. The KPI Details will render details below. 

 

 

You have completed a PerformancePoint 2013 Dashboard! Feel free to modify the dashboard by including additional dimensions and/or measures.  

 

 

Cheers! 

 

 

Reference: PerformancePoint Dashboards 

 

In this article we'll show you how to create a Dashboards using PerformancePoint Dashboard Designer for SharePoint 2013. We will connect to the "AdventureWorksDW2012" cube, using an Analysis Services data source connection.

SharePoint | SQL Server | Analysis Services | Multidimensional Expressions (MDX)

Comments (9) -

hxy0135
hxy0135 United States
6/3/2013 8:47:49 AM #

Hans,
I have installed the proper ADOMD, AMO and OLEDB components from sql 2008 r2 feature pack. I still have trouble to import my KPIs from my cube. the error is below. What do I stll miss?

Thank you for your help!

An unexpected error occurred.  Error 60216.

Exception details:
Microsoft.AnalysisServices.ConnectionException: A connection cannot be made. Ensure that the server is running. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at Microsoft.AnalysisServices.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
   at Microsoft.AnalysisServices.DimeRecord.ReadHeader()
   at Microsoft.AnalysisServices.DimeReader.ReadRecord()
   at Microsoft.AnalysisServices.TcpStream.GetResponseDataType()
   --- End of inner exception stack trace ---
   at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
   at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
   at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(DataSource asDataSource)
   at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)

hesquivel
hesquivel United States
6/3/2013 9:10:53 AM #

Hello,

Please take a look at Event Viewer on the server hosting Analysis Services, after attempting to connect to the KPIs; you may get more details on the missing component/version, if any. What version of SQL Server are you using? I'm assuming the user account has proper access to the cube in question? Did you configure PerformancePoint Services application pool account as an Analysis Services administrator?  

It's hard to diagnose the issue without taking a look at your SharePoint and Analysis Services configuration. I hope some of this helps.

hxy0135
hxy0135 United States
6/3/2013 9:32:33 AM #

HI,
thank you very much for your response.
I am using SP 2013 talking to SQL 2012. Yes, I configured PPS pool account as a SSAS Admin(I have dashboards running). I checked Event viewer on my cube server, there is nothing there. Only error message is from SP application servers listed below:
An unexpected error occurred.  Error 60216.

Exception details:
Microsoft.AnalysisServices.ConnectionException: A connection cannot be made. Ensure that the server is running. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   --- End of inner exception stack trace ---
   at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
   at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
   at Microsoft.AnalysisServices.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
   at Microsoft.AnalysisServices.DimeRecord.ReadHeader()
   at Microsoft.AnalysisServices.DimeReader.ReadRecord()
   at Microsoft.AnalysisServices.TcpStream.GetResponseDataType()
   --- End of inner exception stack trace ---
   at Microsoft.AnalysisServices.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
   at Microsoft.AnalysisServices.Server.Connect(String connectionString, String sessionId)
   at Microsoft.PerformancePoint.Scorecards.Server.ImportExportHelper.GetImportableAsKpis(DataSource asDataSource)
   at Microsoft.PerformancePoint.Scorecards.Server.PmServer.GetImportableAsKpis(DataSource dataSource)

hesquivel
hesquivel United States
6/3/2013 9:55:39 AM #

You mentioned you're using SQL Server 2012. Did you install the correct components from the SQL Server 2012 Feature Pack?

www.microsoft.com/.../details.aspx?id=29065

I think you mentioned you had installed the SQL Server 2008R2 components. Perhaps that's the issue?

hxy0135
hxy0135 United States
6/4/2013 3:59:31 AM #

At first, when I tried to import my KPIs,  I got error "System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91', on my SP Application servers, so I installed SQL 2008 R2 Feature pack. After that, I got the error message I submitted earlier.

thanks

hesquivel
hesquivel United States
6/4/2013 9:19:44 AM #

The user id attempting to connect to AS has proper Role membership/access to the Cube? Also, did you configure your Secure Store for PerformancePoint Service Application? Please ensure there's a reference to the Target Application - PerformancePoint Services Application

hesquivel
hesquivel United States
6/4/2013 9:21:53 AM #

You mentioned in a previous thread you're using SQL Server 2012. Did you install the SQL Server 2012 Feature Pack for ADOMD, AMO and OLEDB components? You need to complete this step if you're PerformancePoint Services are communicating with SQL Server 2012.

kate
kate United Kingdom
7/5/2013 1:51:36 AM #

Hi,
I'm a beginner in PerformancePoint.
I have a problem in "Creating New Data Source" step 4 & 5 :


4. Type the name of your database server name in the Server: textbox. For example, MyDBServer

5. Below the Server textbox, select the "AdventureWorksDW2012Multidimensional-EE" from the Database dropdown list. This is an Analysis Services (AS) database. Note: your name of the AS database may differ.


I have typed my database server name in the server textbox but nothing appeared in the dropdown list.
Could you please help me?
Thanks,

hesquivel
hesquivel United States
7/8/2013 7:37:22 AM #

Hello,

Depending on the version of SQL Server you're using, you'll need to download and install the following components on your server. For example, SQL Server (version) Feature Pack for ADOMD, AMO and OLEDB components. You need to complete this step if you're PerformancePoint Services are communicating with SQL Server 2012, or the version you're using. Here's the link to SQL 2012 Feature Pack www.microsoft.com/.../details.aspx?id=29065 (expand the Details section to find the components).

I hope this helps.

Hans

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud