SharePoint 2010 BI

Analysis Services was one of the primary data sources with the previous version of PerformancePoint to get the most functionality out of the product.  Now with the release of the 2010 product we have some new options and one of them is PowerPivot.  PowerPivot is also a multi-dimensional data source and in this post I will talk about what this source provides for functionality and what it does not compared to Analysis Services.

First of all you will need to have an environment that has PerformancePoint Services configured.  If you are not familiar with how to do that you can take a look at this great posting on the product team’s web site – Deploying PerformancePoint 2010 Soup to Nuts.  Once you have that setup then the other thing that you are going to need to do is have PowerPivot setup with SharePoint so that we will be able to utilize the files as data sources.  There are plenty of articles and material available in regards to this configuration, here is one of them – How to: Install PowerPivot for SharePoint on a New SharePoint Server.

Once you have your environment setup and ready the next thing will need to do is create your PowerPivot data source that you are going to leverage to create your dashboard with.  In this example we are not going to be creating the file and leveraging the PowerPivot PivotCharts and PivotTables, we are simply going to be setting up a file that will be used to provide the data and measures needed for analysis.  I will be using it in place of having to implement an Analysis Services solution (except for the fact that PowerPivot is really an Analysis Services database under the hood).

Some of the reasons that we might want to use PowerPivot in a PPS dashboard would be because:

• ability to leverage PPS analytical features like analytical grids & charts and decomposition tree
• quickly integrate disparate data for analysis into a single source
• leverage existing Excel Power User expertise, data, and files

For this example I will be leveraging the Contoso Retail BI Demo data – Microsoft Contoso BI Demo Dataset for Retail Industry.  I will also be using the following components to put this example together – Excel 2010 with PowerPivot add-in (this can be downloaded from here – PowerPivot Download), PowerPivot for SharePoint 2010, PerformancePoint Services, and SharePoint 2010 Enterprise.

Warning: This example does not include each step-by-step instruction and expects that you have some basic knowledge of PowerPivot, DAX, and PerformancePoint.

First step is to launch PowerPivot Window from Excel 2010 to load data	Now from the PowerPivot Window we will select the option to load data from a SQL Server database
Select the ContosoRetailDW SQL Server database	Choose the option to select from a list of database tables
In this example we will select DimDate, DimGeography, DimProduct, DimProductCategory, DimProductSubcategory, Dim Store, FactSales, and FactSalesQuota	For the two fact tables we will remove a few columns that are not needed for analysis during the load using the Preview & Filter option (remove initial fact key column and last three ETL columns).  Also add a filter for FactSalesQuota on ScenarioKey to only include values of 2 (for Budget)
Now you can click finish and watch the data load – over 5 millions rows of data!	During the load process the relationships that were defined in the SQL database were retained, so we will not need to setup any additional relationships between the tables
We might simplify the snowflaked model and pull the Product Category and Subcategory information into the Product table using DAX and the RELATED function	To simplify the model a bit more we can remove columns and tables that are exposed to the PivotTable in Excel – remove the surrogate key, foreign key, ETL columns, and in the related tables remove all columns
Now the creation of the PivotTable data (measures) which will be needed in PerformancePoint to reference – I will comment on this more down below after the example	Measures have been added for PerformancePoint, so now the file can be published to SharePoint to be referenced
File –> Save & Send –> Save to SharePoint –> Save As (this will be saved to a Trusted Excel Services location in SharePoint)	In Dashboard Designer a new Data Source will be created – go into the Data Connections library and add a new PerformancePoint Data Source (this will launch Dashboard Designer)
Create a Multi-Dimensional Analysis Services data source and use a connection sting pointing to the Excel file in SharePoint – “DATA SOURCE = http://mybi10/Workbooks/ContosoSalesAnalysis.xlsx” and the cube name is “Sandbox”	If you receive this message when setting up the data source to pick the cube you will need to verify the connection string URL and/or verify that the SQL Server Browser service is running
Now create a couple of KPIs for Sales and Gross Margin information (you will need to use the Add Lists option in the Workspace portion of the Ribbon to add the PerformancePoint Content list). Make sure to change the Calculation option from Default to Data	Setup a Scorecard and add a dimension column (ProductCategory) to analyze
Create an Analytical Chart (you need to save the data source if you haven’t done so yet to create the analytical reports). To apply formatting to the measures switch to Grid report type and change formatting to $ and %.  Also filter empty axis and series	Add another Analytical Chart. Apply appropriate formatting and filter to axis and series
Add an Analytical Grid. Adjust Settings so that the column and row headings are not truncated in the View options in the Ribbon	Create a couple of Filters, one for the FiscalYear and another for RegionCountry (an MDX Query was used along with the Tree view setup for each filter)
Create and setup the Dashboard	Connect the filters to the Dashboard items
Save all of the content and then deploy the Dashboard to SharePoint	View and test the dashboard page
View the Decomposition Tree (requires Silverlight)

A couple of items that I wanted to discuss briefly about in regards to this example was the reasoning for setting up measures in the PowerPivot PivotTable and the other was for setting up the KPIs Calculation as Data Value.  External tools require that you setup measures in the PowerPivot file in our to reference.  Whether you did this in PerformancePoint or Reporting Services, you would need to have measures defined to be able to reference to utilize for analysis.  PowerPivot doesn’t have the ability to setup formatting either to pass through, so you also have to perform this in the tools referencing it currently.  You can reference items placed in the PivotTables in the Values section, but you can’t guarantee these will always exist, so I recommend setting up measures for every metric that you want to evaluate and utilize with external tools.

The other items that I wanted to mention was the KPI calculation setting.  This was changed to Data value so that we would be able to leverage the Decomposition tree option in the right-click context menu.  If this was left as Default value this would have been disabled.  You currently cannot use the Show Details option since drillthrough is not an option in the current PowerPivot release, but you can use the Decomposition tree for analysis.

For the most part PowerPivot provides very similar functionality when compared to Analysis Services.  A few items that you don’t get will be Show Details (Drillthrough), Named Set references, measure formatting (along with formatting configurations – colors), and user-defined hierarchies.

Using Excel Services Reports with PerformancePoint Server (PPS)

Posted by denglishbi on January 24, 2010

UPDATE (10/1/2010): I corrected the image of the screenshot for configuring the Products parameter in the Excel file.  I originally had one displayed with DateFiscal for the setup instead of the one referencing Products.  I apologize for that.  If you wanted to do one for DateFiscal you would need to setup the named range for Sales!$B$4.

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.

One of the nice features about creating dashboards with PerformancePoint Server (PPS) is the ability to incorporate different types of reporting types like Analytical Grids/Charts, SQL Reporting Services Reports (SSRS), ProClarity Analytical Views, and Strategy Maps.  Another one is being able to leverage Excel Services.  As we all know Excel is one of the most widely used tools, so it only makes sense that at some point we will probably want to be able to integrate an existing Excel file within the organization.

Some of the reasons that you might want to use Excel as a report in a PPS dashboard could be:

• use an Excel report that has already been developed by end-user
• leverage some of the nice Excel 2007 conditional formatting options – data bars, color scales, and icon sets
• want more control over the style and format of the chart or table
• need to incorporate a pie, bar (not column), area, scatter, radar, or bubble chart into your dashboard

The first thing that you will need to do ahead of time before you are able to do this is enable Excel Services on SharePoint 2007 and configure it.  If you are unfamiliar with this process you can check out this reference – Demo: Enable Excel Services and data connections for a SharePoint team site and check the video and download it for reference.  NOTE: Excel Services is an Enterprise feature within SharePoint Server.

Once you have Excel Services configured you will then need to create the Excel file with the items that you would like to include in your dashboard.  In this example I will be using Analysis Services as the data source (the ever famous Adventure Works DW) and will create an Excel PivotTable and PivotChart.  These two Excel items will then be made available in SharePoint (Excel Services) for reference by the PPS dashboard that will then be connected to dashboard filters.  NOTE: this demonstration is using a virtual environment that is setup in a standalone configuration (meaning everything is installed on the server – SQL Server, PPS, SharePoint, etc.).

(You can click on the images below to view a larger image – the flow goes from left to right)

Open up a new Excel workbook and create the data connection to the Analysis Services database	Connecting to a local instance of Analysis Services
Select the AdventureWorksDW database and the Adventure Works cube	Enter a name for the Office Data Connection (odc) file
Going to create a PivotChart and Pivot table	Select ‘Reseller Sales Amount’, ‘Date.Fiscal’, and ‘Geography.Geography’
Only select FY 2004 in the column labels and change the chart type from Column to Pie	Remove Chart Title and add Data Bars conditional formatting to the PivotTable
Click in the PivotTable and in the Options enter a meaningful name and deselect the grand total options since we are only going to show one time period in this example	Now select the pie and go into the Format Data Labels options and choose the options you would like to include
Select the PivotChart and in the Properties in the Layout portion of the Ribbon give the Chart a meaningful name	Changed the worksheet name and add an additional hierarchy from the field list in the Report Filter section – Product.Product Categories
Go into the data connection properties and export the data connection to the data connection library in SharePoint (just like in the demo link provided above)	Publish the Excel workbook to Excel Services
Specify the path to the document library, provide a meaningful file name, and specify the Excel Services options (I just selected items in the workbook and then added the parameter in to be used with PPS)	Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotTable
You can click on the View button to see a preview of the item you are referencing	Setup the reference in Dashboard Designer to the Excel Services workbook and the PivotChart (you can copy and paste the previous report, change the name in properties, and change the item name being referenced)
Create the dashboard page and link the filters to the reports	Deploy the dashboard page
Test the dashboard page filter and drill into the data within the table for further analysis

NOTE: If you don’t see the Excel Services parameter in Dashboard Designer it is most likely because in the Excel filter you have multiple values selected.  Go back into the file and make sure only one item is selected so that it will be recognized and pulled into the parameters section in Dashboard Designer.

Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)

Posted by denglishbi on December 31, 2010

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.

Back in January I did a posting in regards to Using Excel Services Reports with PerformancePoint Server (PPS).  This has been a very popular posting and I thought I would add another one in regards to using Reporting Services (SSRS) reports with PerformancePoint (in PPS 2007 this type of report was called SQL Server Report).

Some of the reason that you might want to include a SSRS report in your PPS dashboard would be because:

• leverage an existing report created by an end-user
• incorporate existing operational reports
• use additional charting options – map, area, range, scatter, polar, bar (not column), funnel, 3D, sparklines, data bars
• need more flexibility and control over reports, styles, colors, scales, etc.
• join multiple data sources into a single report
• combine relational and OLAP data into a single report

The example that I will be showing is using SSRS in SharePoint Integrated Mode, but you can also do this in Native Mode as well, you would just see a different setup screen when you are configuring the report in Dashboard Designer (a tad bit easier in my opinion configuring these in Native Mode – which is labeled as ‘Report Center’ mode in Dashboard Designer, confusing I know…).  I will also be using Report Builder 3.0 to create and deploy the report to the SharePoint site.

Go to Report Library in SharePoint site, select Documents from Ribbon, select New Document, and pick Report Builder Report	This will either launch Report Builder or ask you if you want to run and install the application if you haven’t done so yet
Report Builder is a ClickOnce application and by clicking Run you will install the application	Once installed the Report Builder application will start up
In this example we will build a Map	Reposition the map up a bit so it appears above the legends
A Bubble Map will be used to be able to analyze two metrics	A new data set will need to be added that contains the spatial data
A new data source will be added connecting to the Contoso Retail DW SSAS database	Use the Sale cube, filter for the United States, setup the Fiscal YQM as a Parameter, pick State Province Name, Sales Amount, and Sales Total Cost
Use STATENAME and map this to the State Province Name field from the data set	Pick a theme for the style, setup the bubble size to visualize Sales Amount, and polygon color for the Sales Total Cost
Setup Chart and Legend titles, polygon tooltip, remove color legend, resize/reposition map, and remove default marker size	Save report to SharePoint library
Now we are going to add a new Report to our existing PerformancePoint Content library	This will launch Dashboard Designer and like the Report Builder you may be prompted to install it (this is also a ClickOnce application)
If nothing launches then you need to make a small adjustment in your IE security settings to Enable ‘Automatic prompting for file downloads’	Now we will create the new PerformancePoint Report
Use the SharePoint Integrated mode, specify the URLs for the Report Server and the RDL file, uncheck the Show toolbar, and specify a name for the PPS report	Next we will create a filter that we can use with the report once it is displayed in the dashboard page
The filter we will create will be for the Fiscal YQM and we will remove periods that don’t have any Sales Amount	We will use a Tree style display and only allow a single selection
Name the filter and get ready to create the dashboard	Add a new Dashboard item
Name the dashboard item, page, add the filter, add the report, and remove the extra column (zone) on the page	Create a Connection (formerly link in PPS 2007) between the filter and the report
The filter will connect to the DateFiscalYQM parameter on the report and will pass the Member Unique Name (an SSAS member value to the report)	Save the PPS content items and deploy the dashboard to the Dashboards library
Select the Master Page and whether or not you want to include the page navigation or not	Test out the filter and view the results with the deployed PPS dashboard

My example here used the Contoso Retail DW sample data which is available from the Microsoft downloads here – Microsoft Contoso BI Demo Dataset for Retail Industry.  This is also using Reporting Services 2008 R2 which includes the new Map report item, Report Builder 3.0, PerformancePoint Services, and SharePoint 2010 Enterprise.

I have two other postings that I did earlier in the year in regards to the new Map report item here that you can check out if you have questions in regards to that:

Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)

Posted by denglishbi on December 31, 2010

UPDATE (6/21/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.
Back in January I did a posting in regards to Using Excel Services Reports with PerformancePoint Server (PPS).  This has been a very popular posting and I thought I would add another one in regards to using Reporting Services (SSRS) reports with PerformancePoint (in PPS 2007 this type of report was called SQL Server Report).
Some of the reason that you might want to include a SSRS report in your PPS dashboard would be because:

• leverage an existing report created by an end-user
• incorporate existing operational reports
• use additional charting options – map, area, range, scatter, polar, bar (not column), funnel, 3D, sparklines, data bars
• need more flexibility and control over reports, styles, colors, scales, etc.
• join multiple data sources into a single report
• combine relational and OLAP data into a single report

The example that I will be showing is using SSRS in SharePoint Integrated Mode, but you can also do this in Native Mode as well, you would just see a different setup screen when you are configuring the report in Dashboard Designer (a tad bit easier in my opinion configuring these in Native Mode – which is labeled as ‘Report Center’ mode in Dashboard Designer, confusing I know…).  I will also be using Report Builder 3.0 to create and deploy the report to the SharePoint site.

Go to Report Library in SharePoint site, select Documents from Ribbon, select New Document, and pick Report Builder Report	This will either launch Report Builder or ask you if you want to run and install the application if you haven’t done so yet
Report Builder is a ClickOnce application and by clicking Run you will install the application	Once installed the Report Builder application will start up
In this example we will build a Map	Reposition the map up a bit so it appears above the legends
A Bubble Map will be used to be able to analyze two metrics	A new data set will need to be added that contains the spatial data
A new data source will be added connecting to the Contoso Retail DW SSAS database	Use the Sale cube, filter for the United States, setup the Fiscal YQM as a Parameter, pick State Province Name, Sales Amount, and Sales Total Cost
Use STATENAME and map this to the State Province Name field from the data set	Pick a theme for the style, setup the bubble size to visualize Sales Amount, and polygon color for the Sales Total Cost
Setup Chart and Legend titles, polygon tooltip, remove color legend, resize/reposition map, and remove default marker size	Save report to SharePoint library
Now we are going to add a new Report to our existing PerformancePoint Content library	This will launch Dashboard Designer and like the Report Builder you may be prompted to install it (this is also a ClickOnce application)
If nothing launches then you need to make a small adjustment in your IE security settings to Enable ‘Automatic prompting for file downloads’	Now we will create the new PerformancePoint Report
Use the SharePoint Integrated mode, specify the URLs for the Report Server and the RDL file, uncheck the Show toolbar, and specify a name for the PPS report	Next we will create a filter that we can use with the report once it is displayed in the dashboard page
The filter we will create will be for the Fiscal YQM and we will remove periods that don’t have any Sales Amount	We will use a Tree style display and only allow a single selection
Name the filter and get ready to create the dashboard	Add a new Dashboard item
Name the dashboard item, page, add the filter, add the report, and remove the extra column (zone) on the page	Create a Connection (formerly link in PPS 2007) between the filter and the report
The filter will connect to the DateFiscalYQM parameter on the report and will pass the Member Unique Name (an SSAS member value to the report)	Save the PPS content items and deploy the dashboard to the Dashboards library
Select the Master Page and whether or not you want to include the page navigation or not	Test out the filter and view the results with the deployed PPS dashboard

My example here used the Contoso Retail DW sample data which is available from the Microsoft downloads here – Microsoft Contoso BI Demo Dataset for Retail Industry.  This is also using Reporting Services 2008 R2 which includes the new Map report item, Report Builder 3.0, PerformancePoint Services, and SharePoint 2010 Enterprise.

I have two other postings that I did earlier in the year in regards to the new Map report item here that you can check out if you have questions in regards to that: