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

[tweetmeme source=”denglishbi” only_single=”false”]

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:

• Mapping data with SSRS 2008 R2
• Mapping data with Report Builder 3.0 (SSRS 2008 R2) Case Sensitive

Download:

Feel free to download the PPS Workspace file (ddwx) and the SSRS report (RDL) file from my SkyDrive which I have included in a zip file.

 

You might find this posting useful if you want to reuse the workspace file – Migrating PerformancePoint 2010 Content to New Server.

Enjoy and Happy New Year!

PerformancePoint Services 2010 Analytical Grid Filter Fix, Sort of

Back in September there was a forum posting that was looking for a solution to resolving the filtering issue with Analytical Grids in PerformancePoint – filter value function not working on analytic grid.  The filtering functionality was working fine with Analytical Charts, but for Grids there seemed to be a slight bug.  When the Top filter was applied to an Analytical Grid report you would received messages like the following:

There is no data to display.

Drag measures, dimensions, or named sets to Rows and Columns to lay out the report.

there are no data rows to display

So what was going on, because if you switched the Report Type from Grid to say Bar Chart it worked just fine…

If you set this up in a Chart and then flipped back to Grid then it would retain the setting.  If you modified the filter at this point then you would get what you see in the first screenshot above.

Well just this month a new Cumulative Update was released for SQL Server 2008 R2 – Cumulative Update 5.  It just so happens that one of the fixes in this was related to PerformancePoint Services! 

FIX: An analytic grid that is connected to SSAS 2008 R2 returns incorrect data when you apply a filter to the analytic grid in PerformancePoint Dashboard Designer

I typically wait until a Service Pack is released, but I always review the fixes that are included in the Cumulative Updates, because you just never know when you might uncover an item that could resolve an issue that you have been having or might not have been aware of.

In order to get this fix you need to request the hotfix from the main page – Cumulative Update package 5 for SQL Server 2008 R2.  Once you do this you will get an email with the download link and also a password to unzip the actual hotfix package that you can install.

Here are some screenshots of the installation process which took approximately 10 minutes to install on my machine:

 

Once you get this installed you will be on SQL Server 2008 R2 version number 10.50.1753.0.  After I performed the install it did require a reboot in my situation.

Now we go back and test out our filter functionality on the Analytical Grid and the results are…

Well we didn’t get an error like the one in the first screenshot, but it doesn’t apply the filter properly like it does if we switch over to an Analytical Chart:

It appears that they fixed part of the issue, but it is still not handling the user defined hierarchy filtering.  If we use just a attribute hierarchy then it works just fine.

So at this point it will take a little more investigation and a follow-up reply to the original forum posting.

TDWI Minneapolis Chapter Event: The Human Side of Data Modeling – December 14

The next TDWI Minneapolis Chapter event is scheduled and the agenda has been posted.  This month they will be talking about data modeling and techniques to use this to communicate with everyone that is involved with the project.

http://tdwichapters.org/Minneapolis

When: Tuesday, December 14, 2010, 8:00 a.m. – 12:00 p.m.

Where: Target Corporation, 1000 Nicollet Mall, Minneapolis, MN 55402

Directions please click here

We are pleased that Target Corp will host our December meeting. Please check in at the Skyway level Security Desk.

Click here to register for the next upcoming event

Agenda:

• 7:45 – 8:15 a.m. – Networking and Registration
• 8:15 – 8:30 a.m. – Introduction and Chapter Business
• 8:30 – 9:45 a.m. – “The Human Side of Data Modeling – Improving Communication with Mere Mortals” by Alec Sharp
• 9:45 – 10:00 a.m. – Break and Networking
• 9:45 – 10:45 a.m. – “The Human Side of Data Modeling – Improving Communication with Mere Mortals” by Alec Sharp
• 11:30 – 11:45 p.m. – Innovation Roundtable, Q&A and Collaboration
• 11:45 – 12:00 p.m. – Closing Comments & Wrap-Up

Click here to register for the next upcoming event

Free SQL Server Community December Training

Head over to Pragmatic Works site and take a look at some training that is available this month.  This month you can listen to some more great business intelligence content presented by Chris Webb, Brian Knight, Mike Davis, and Tim Moolic.  The topics covered with be Analysis Services, Data Mining, SSIS, and BI careers.

• December 21, Chris Webb – Introduction to Performance Tuning Analysis Services 2008
• December 23, Brian Knight – Introduction to Data Mining in SQL Server Analysis Services
• December 28, Mike Davis – Using Script Task and Components in SQL Server Integration Services
• December 29, Brian Knight & Tim Moolic – Building a career with SQL Server business intelligence

If you miss a session they record them and you can check them out later on-demand in the webinar resource area.

Enjoy!

PASS DW & BI Virtual Chapter December Lineup

This month the PASS Data Warehousing & Business Intelligence Virtual Chapter has a great lineup of sessions starting today.  The topics are Analysis Services, Data mining, PowerPivot, and Integration Services. 

Date – Time	Topic (Presenter)	Description	Live Meeting URL
Mon, 12/6 – 12pm EST	Add It Up: Analysis Services Aggregations (Craig Utley)	Are you looking to maximize the performance of your Analysis Services queries? This session explains what aggregations are and how they work. You’ll learn how to create aggregations, tune the aggregations based on actual usage, and design custom aggregations when necessary. You’ll discover the benefits and pitfalls of flexible aggregations, when to use rigid aggregations, and the meaning of lazy aggregation processing. You’ll dive into queries to see if aggregations are being used and learn when aggregations aren’t useful. If you thought aggregation design began and ended with the aggregation wizard, you owe it to yourself (and your cubes) to attend this session	https://www.livemeeting.com/cc/usergroups/join?id=BTK6PP&role=attend&pw=d%2CR%25%28%7C%24c9
Fri, 12/10 – 12pm EST	Using OLAP to Optimize and Maintain Predictive Analytics Models (Eugene Asahara)	In complex Predictive Analytics (PA) scenarios where it is being applied to a complex system or the players involved are actually trying to undermine the predictions (ex: credit card fraud), the sophistication of the PA must be taken up a notch or two. In this Webcast I will describe techniques for building sophisticated PA systems on the Microsoft BI Stack by using OLAP to: · Analyze, validate, and optimize PA models. · Manage and Monitor the performance of the PA models in a Performance Management style. · Surface PA results to end users in a manner that allows them to work through the ambiguity that remains around predictions.	https://www.livemeeting.com/cc/usergroups/join?id=4RNB5Z&role=attend&pw=t_M6_%2CMns
Wed, 12/15 – 7pm EST	Self-Service BI with PowerPivot – Examples!!! (Grant Paisley)	PowerPivot is a free add-in to Excel 2010 where you can build a full BI solution with over 100 Million Rows (under the covers it is an “in memory cube” using column compression). In this presentation ,I’ll show you a solution with over 24million ATM transactions with rich visualizations. What do you think the most number of times a person went back to the same ATM on the same day? The most amount of money withdrawn? What type of establishments have what withdrawal patterns? For such simple data we uncover really interesting things…	https://www.livemeeting.com/cc/usergroups/join?id=Q7RDHC&role=attend&pw=w5X*S*f%5Bq
Mon, 12/20 – 12pm EST	SSIS + CDC = SCD (Patrick LeBlanc)	Building dimensions using the Slowly changing dimension wizard in SSIS is simple and quick. However its performance and flexibility is questionable. Even further, when trying to perform incremental loads of your Dimensions using the aforementioned approach or a custom approach prior to Change Data Captured (CDC) offered certain challenges. In this session Patrick will show you how to utilize CDC and SSIS to incrementally load Type I and Type II dimensions ;using features that are all native to SQL Server 2008.	https://www.livemeeting.com/cc/usergroups/join?id=53BGCZ&role=attend&pw=3%3C%2C9%27CDcs

 

These sessions are recorded with Live Meeting, so if you are unable to attend you can check these ones out at a later time or previous ones that have already been done – Presentation Archive.

PASSMN December 14, 2010 Meeting

The next meeting Minnesota SQL Server User Group (PASSMN) is coming up and we are moving it up a week because of the holiday season and are going to join the newly formed Twin Cities PowerShell User Group to hear Nick Weber talk about Practical PowerShell & SQL!

The sponsor for this event is Virteva.

• Meeting sponsor: Virteva
• Location: 8300 Norman Center Dr., Suite 950, Bloomington, MN, 55437
• Live Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=SF7SNR&role=attend&pw=h%5DpF6%3Ch8q (to get the audio you will need to download and install the full Live Meeting client)

Agenda:

4:30 – 5:00 : Welcome Time
5:00 – 5:15 : Announcements
5:15 – 5:25 : Sponsor
5:25 – 6:45 : PRACTICAL POWERSHELL & SQL (Nick)
6:45 – 7:00 : Survey Collection and Giveaways

Presentation:

PRACTICAL POWERSHELL & SQL

PowerShell a new command-line/scripting environment from Microsoft, that developers to administrators alike can use to automate tasks. PowerShell is a command line utility in a GUI world and that tends to frightened many. If you’re still hesitant to dive into PowerShell, you’re not alone. But rest assured a lot of the examples we will go over are easy and practical. We will start out with the PowerShell basics then dive deeper into scripting out objects and finally move into advance scripts.

Nick Weber is a IT Professional with over 14 year of experience.  That time was spent in messaging, network, SAN storage, virtualization and database administration.  Currently a Senior Production DBA but still keeps his hands dirty with all IT’s dark arts.

Click to Attend this event

Hope to see you there!