Microsoft Business Intelligence Blog : Monitoring

PushBI for Microsoft TechED 2009

Patrick Husting — Thu, 07 May 2009 08:26:00 GMT

We developed a PushBI solution to track the business intelligence sessions and ratings at TechED 2009 next week. Download and give it a tour next week when the session speakers scores start coming in.

http://www.PUSHBI.com/TechED.aspx

Displaying Dates in a PPS Scorecard

Patrick Husting — Mon, 09 Feb 2009 10:07:00 GMT

You know, Alyson just rocks, but I wish I knew of this last summer....

"It frequently comes up that you may want to show a date in your scorecard, either as an actual value, a target value or just as an extra piece of information such as when was this KPI last updated in the source. One way that you can display dates in your scorecard is to make use of the MDX "Properties" expression. Note, that this will only work for data being sourced from an Analysis Services cube. See the simplistic example below. "

more at source

Extended Analytics 2008 for use with QuickBooks

Patrick Husting — Fri, 24 Oct 2008 13:03:00 GMT

It has been a REALLY busy year at Extended Results our consulting company. We have had some tremendous growth in regards to customers and employees along with generating some new and interesting intellectual property. I have been using QuickBooks for years for my different companies and I found it to be a great program for managing my ERP needs on a small/mid scale. But this year with all the growth, we had a need for some business intelligence functionality to run the business better. Since there is nothing on the market for QuickBooks for BI, we decided to write it ourselves and start marketing it. It is called Extended Analytics 2008 and you can find out more at http://www.ExtendedAnalytics.com.

We just got it all approved through the QuickBooks licensing/developer program and have begun marketing it to QuickBooks ProAdvisors. We have been surpised with the response and purchases since the release. Very exciting. The solution is totally drag and drop and meant to be brain dead easy to use...

The following screenshot is an example of a Scorecard you can drag and drop right onto your dashboard along with some rich Dundas charts showing your revenue vs. expenses.

You can have multiple analytic charts that can be dragged and dropped into different zones.

Lots of screenshots and feature lists can be found at the product web site which can be visited at http://www.ExtendedAnalytics.com

Using SharePoint Webparts in PPS

Patrick Husting — Thu, 09 Oct 2008 21:35:00 GMT

Had a question come up about how to use a SharePoint document library within PPS. Use the following steps to create it.

Open SharePoint Designer and open your SPS site
Create an ASP.NET page
Drag and drop your document library onto the page
Attach the SPS stylesheet so it looks pretty
Save it to the document library on the site
In PPS Designer, create a Web Page Report view and link to that page URL in the SPS document library
Drag and drop onto your dashboard and publish

Let me know if you have any questions

Maxing out the Treeview or Multi-Select Control for Filters

Patrick Husting — Sat, 20 Sep 2008 03:32:00 GMT

When using PPS M&A Treeview Filters on a scorecard, it has a default maximum values of about 200 members and then it doesn't show anymore. You can increase the number of members by adding the following tag in the SharePoint web.config file.

Where "5000" is the maximum number of members to show. Just remember this will have an impact on rendering performance.

Patrick

Creating a Free Form Text Search filters for Performance Point

mjwilson — Tue, 16 Sep 2008 13:02:00 GMT

While Performance Point offers a wide array of "Out of the box" filter solutions, one of the holes that we found was the inability to perform a user input search. While working on a project, we encountered a situation where the members we wanted to list blew way past the upper limit of the PPS filters. While one solution would be to increase the upper limit, this leads to performance hits on the pages as the reports wait until all of the selected members are loaded.

Our solution: A free form text box that would perform a search against dimensions / members in our cube, and in turn apply the searched item as a filter to the dashboard report.

I used a blog post by Joe Hayes of the PerfomancePoint Product team as an initial stepping stone (as well as the inspiration), as well as the PPS SDK found here.

The solution requires a two part development effort, on one hand you need to create a PerformancePoint Dashboard Designer plug-in in order to create the filter and add it to your dashboards. The second part requires that you build a custom web server control.

The screenshot above has 3 projects included in one solution, the third being an installer to be used to help automate the rollout of the filter to multiple machines, but that is a topic for a different post.

The creation of the PerformancePoint Dashboard Designer plugin is detailed by Joe Hayes, and in the SDK, but more than that, Joe gives you a solid code base to begin building your own filters from. When everything works right and your plug-in loads, you will have a new filter template available to you in Dashboard Designer.

One of my biggest sticking points was understanding what exactly the GetDisplayData and the GetMessageData methods in the DataProvider did, and how they facilitated the communication between not only Dashboard Designer and SharePoint, but the fitler and the report item itself.

GetDisplayData builds the datatable structure that the filter/server will use and expect, as well as define which columns are in the table are to be displayed in the "Source Value" selection box when you link the filter to the dashboard item. GetMessageData is the actual messenger of the system, it builds the datatable (by calling GetDisplayData) and fills it with the members that are to be passed to the report item. What this means is that all your "search" logic is to be built into or called from this method.

Joe and the SDK give you a good base for the Dashboard Designer part, the web server control is a little more of a challenge. While digging through the SDK I never found a section for "Building your own selection controls", but when I was reading through the Web.Config, I noticed that the inherent PPS selection controls were registered under "CustomReportViews", which Joe verified what how they were implemented. This means that the DLL for the web server control is to be registered under "CustomReportViews" in the Web.Config of the parent SharePoint site.

The web server control is really nothing more than a class that overrides the Render method of a page. This method is passed an HTMLTextWriter from which the raw HTML of our search box is loaded and written to the page. I used as StringBuilder to do this, as it allowed for a logical way to stick together a long string of HTML code, as well as the JavaScript that puts together our search value and triggers the post to the report item. This being said, the only real interaction with the report item comes in the onClick event of my button where it calls:

"PPSMA.DashboardController.get_instance().updateParameter('{0}','{1}',[txtVal]);", base.DashboardItemId, base.BeginPoints[0].ParameterUniqueName);"

where "[txtVal]" is the value from my textbox. It is worth noting that the value passed to the dashboard has to be a JavaScript array, even if it is only one value.

Through the web server control you can load any raw HTML controls, but you are not able to utilize ASP.Net controls because of where in the page lifecycle the control is being inserted.

Development notes and suggestions.

The very first suggestion that I might give is to develop on the server. This sounds a little weird, but I have 2 reasons for this:

Both projects require references to some PerformancePoint DLLs that are loaded on install
Debugging is much easier locally rather than messing with Remote Debugging. It's so much easier just to attach to a local process.

There are a couple of tools out there that will ease development as well, the first being CriTrace which is detailed here. One thing to note, by changing the value from "friendlyfile" as it suggests in the blog post to "spy", SharePoint will print out the results actually on the page versus on a file that you have to dig to find.

Also this blog details how to create an ASPX page that does nothing more than print out the parameters that are passed to the report item. This may sound simple, but it was the best tool I could find for figuring out exactly why my filters were broken, and what the inherent PPS controls were passing that I was not.

And finally download Reflector and read the PerformancePoint DLLs. They can and should be your template / insight into how to build your own filters.

Hope this points you in the right direction, and please feel free to get a hold of us if you have any questions!

PerformancePoint Monitoring Readme for SP 1

Patrick Husting — Thu, 05 Jun 2008 13:24:00 GMT

From the PerformancePoint Server 2007 SP1 Monitoring readme which you can download here

http://office.microsoft.com/download/afile.aspx?AssetID=AM102778591033

PerformancePoint Monitoring Readme

Effective Date: June 6, 2008

Introduction

This file contains important information that was not available when the documentation for the product was written. Workarounds, when they are available, are provided for known issues.

The content in this Readme file applies to the PerformancePoint Monitoring components of Microsoft Office PerformancePoint Server 2007 with Service Pack 1 (SP1). These include:

· PerformancePoint Monitoring Server

· PerformancePoint Dashboard Designer

· PerformancePoint Dashboard Viewer for SharePoint Services.

More PPS M&A SP1 Features/Fixes

Patrick Husting — Thu, 08 May 2008 16:03:00 GMT

PerformancePoint Monitoring Server SP1 includes the following enhancements:

· Support for Microsoft Windows Server 2008

· Add a default display condition report view (see related blog post)

· Multi-select Monitoring server filters passing to multi-select SQL Server Reporting Services report parameters

· Time Intelligence Post Filter formula date corrections

o In some instances filters were passing a one day offset, this is resolved

o Date format of filter will now honor localized formats

· Negative numbers will not cause a text wrap in scorecards

· Dashboard filter performance improvements

· Expand/collapse functions work properly with hidden parent KPI members

· Updated sample data file

· Tabular filter support

· Multi-select filters can now be passed to a Strategy Map report view

· Limit of 1,000 characters in an MDX expression for KPIs has been removed

Â· No longer blocked from using SQL Server 2008 as a data source (NOTE: full support for 2008 is coming in SP2, we just wonâ€™t prevent you from running on it with SP1)

Installing PPS on a Domain Controller

Patrick Husting — Wed, 23 Apr 2008 17:09:00 GMT

I got this from an internal document on how to install PPS on the same machine that has Active Directory installed.

Domain Controllers

While it's not a hard-and-fast rule, most AD administrators will tell you that a domain controller should not run other applications. Since your domain controllers are the "keys" to your network "kingdom", you should do your best to isolate them from attack. By adding additional applications to run on a DC, you are increasing the number of ways that a malicious user can attack that DC. Depending on the size of your network, having dedicated domain controllers may also improve performance in terms of user authentication, and logon times.

Running PerformancePoint Server 2007 on a domain controller is not an advised production scenario and is not a supported scenario. This is why the installer blocks the install of PerformancePoint Server 2007 on domain controllers.

Workaround

There is a command line parameter that will allow the PerformancePoint Server 2007 to install and configure on a domain controller. There are some manual steps to be complete before installation and configuration.

Installing PerformancePoint Planning Server on a Domain Controller

1. You MUST create a new domain account for the Planning Service Identity. You cannot use the domain administrator account as the Service Identity.

2. Using Start\Administrators\Active Directory Users and Computers:

a. Create a new domain account, e.g. DOMAIN\PlanningSI

b. Add it to the Domain Administrators group

c. Add it to the IIS_WPG group

3. Open Start\Administrative Tools\Domain Controller Security Policy

a. Under Local Policies\User Rights Assignment:

i. Grant PlanningSI the â€œLog on as a batch jobâ€ right

ii. Grant PlanningSI the â€œLog on as a serviceâ€ right

4. Install the Planning server MSI using this command line:

a. msiexec /i PPLSrv.msi SKIPOSCHECK=true

5. In the Configuration Wizard, specify the domain account created above as the SI account

Installing PerformancePoint Monitoring Server on a Domain Controller

1. If you are using a domain account, you must add it to the Administrators and IIS_WPG groups before installing the Monitoring Server. You donâ€™t need to do this if using Network Service (recommended).

2. To add a domain account to the appropriate groups: Use Start\Administrators\Active Directory Users and Computers:

a. Add it to the Domain Administrators group

b. Add it to the IIS_WPG group

3. Install the Monitoring server MSI using this command line:

a. msiexec /i PscSrv.msi SKIPOSCHECK=true

Integrate SharePoint Web Parts into a PerformancePoint Server Dashboard

Patrick Husting — Fri, 04 Apr 2008 17:50:00 GMT

I saw this question on an internal Microsoft alias, so I answered it and several people like the answer so I figured I should blog it...

Question: What are the possibilities for combining Dashboard elements (KPIs, scorecards, filters and reports) with SharePoint elements (e.g. as shown below: list of links, announcement and shared documents)? Can this be accomplished through PerformancePoint Server / Dashboard Designer or do we have to construct the page from SharePoint using the different webparts, including Dashboard elements?

You can also do this, we have done this numerous times, works well. I have also presented this "how-to" at the Office and SharePoint developer conferences recently.

Create a blank .ASPX page using SharePoint Designer
Drop your desired "List" onto the page
Link the page with your site style sheet, so it looks pretty
Save the page and remember the URL
Open PPS M&A Designer
Create a NEW Web Page Report View
Enter the URL from above
Go to your Dashboard page and drop that report view where you want it.
Publish ALL and then Publish Page to SharePoint.
Done.

Example Screen

Performance Tuning and Capacity planning for PPS M&A

Patrick Husting — Sat, 29 Mar 2008 00:46:00 GMT

Found this article on PerformancePoint Monitoring and Analytics performance tuning.

http://technet.microsoft.com/en-us/library/bb660539.aspx

PPS Live on the web

Patrick Husting — Thu, 27 Mar 2008 23:37:00 GMT

One of my consultants passed this link onto me with PPS M&A live on the web against web logs. Pretty neat, check it out.

http://richardlees.com.au/sites/Demonstrations/Documents/Web%20Dashboard%20-%20close%20to%20real%20time%20analytics%20of%20this%20internet%20site/Internet%20Activity%20Today.aspx

PerformancePoint Server Certification Update

Patrick Husting — Fri, 01 Feb 2008 14:54:00 GMT

The PerformancePoint Server 2007 exam link has just gone live: http://www.microsoft.com/learning/exams/70-556.mspx

Prep for the exam with self-directed learning courses, which have been designed to give you a quick start on building your performance management solution. Click here to get started.

The link is currently posted on https://partner.microsoft.com/US/program/competencies/iwsolutions/40043177

Migrating PPS Applications and Dashboards - TIPS

Patrick Husting — Mon, 28 Jan 2008 14:04:00 GMT

This is from my BI Manager Kristi Taylor

We recently began the process of migrating a PPS Application from our development environment into test and into production. We utilized the Planning Server Data Migration Wizard to help us accomplish this task. Although this wizard saved us a lot of time we still ran into some problems and I would like to share with you the resolutions.

Problem #1 our inherited application had some â€œmanual editsâ€ renaming the PPS calendar dimension members. When we ran the PSD Import command line the calendar was created with the PPS default labels and when we went to sync the staging db to the app db on the destination, the tool failed because the calendar dimension didnâ€™t match.

Resolution: For auto-generated PPS dims and members such as Time, it is critical that you create the destination application and then go in and rename the members to match those renamed members in the source server.

Problem #2 It turns out when migrating the tables, many dimensions get new Primary Idâ€™s assigned to their members. Your scorecard file by default references these idâ€™s (through your KPI creation). After we finally migrated our application we opened our pre-existing Dashboard Designer file and re-mapped the data source to our new AS cube. Our scorecard would not render any values however we were able to create a â€œnewâ€ report pointed to the Olap cube and could see all values associated with each KPI.

Resolution

Â· If you DONâ€™T have multiple applications running on one PPS site server, reference Database Mirroring on technet for guidance which will enable exact table replication, therefore not running into the problem of â€œre-generatedâ€ primary key. This is not recommended for environments running multiple applications.

· Manual Primary Key Remapping: The tedious way which must be repeated for each KPI every time you migrate: manually delete all filters (dimensions and members) associated with each KPI (and each actual, target and trend indicator associated with each KPI)

· Eliminating the primary key dependency in your Dashboard Designer file. We have to give credit to Srikanth Yalamanchili for pointing us in this direction:

We will likely apply these steps to EVERY Dashboard Designer File we create so that future migrations are not an issue and we have no dependency primary keys that change on every migration. Here are the steps that can be applied prior to or after an application migration:

Â· Open the Tables in your PPS App DB for all dimensions being in your Dashboard Designer KPIâ€™s, in our application this was:

Scorecard Scenario

KPIâ€™s

Measures (Actuals, Target and Trend)

· Identify the primary key # associated with each of the members you are referencing

· Open the Dashboard Designer file with Notepad

Â· Find and replace all primary key strings with the actual â€œLabelâ€ name of the members you are referencing

Example for Scenario Reporting:

Find .&[112]

Replace ALL with .[Reporting]

· Once you have done this save your file and open with dashboard designer.

Â· Your dashboard designer file is now independent of â€œchangingâ€ primary keys

Â· You can â€œseeâ€ what you have done if you Open a KPI, Open the Dimension Filters associated with the KPI, note that â€œnoâ€ member name is actually highlited here (if it were it would again populate the xml with the primary key # instead of the label)

Â· REMEMBER if you add a kpi, or any attribute to a kpi such as trend etc, you will again have a primary key value assigned to that KPI, if you donâ€™t follow the steps above, when you migrate your connection will again break.

Problem #2.B We had some KPIâ€™s that use an & in the name i.e. â€œWindows & Sharepointâ€¦.â€, these KPIâ€™s could not be replaced with the label because they have an & in the label.

Resolution: For these KPIâ€™s apply Manual Primary Key Remapping noted in the first resolution above.

Problem #3 We also ran into an issue when â€œpublishingâ€ these changes to the Sharepoint and M&A Server we were migrating to. We would â€œPublishâ€ our book to the destination M&A server and all seemed well, however any dashboard previously published to this server with the same name was not truly â€œoverwrittenâ€. This was apparent when we attempted to either preview or publish the new dashboard to sharepoint and received an error that the dashboards couldnâ€™t be found. When we refreshed (anticipating to pull down the dashboard we had just published) what we had published moments earlier was overwritten by the pre-existing dashboard with the same nameâ€¦ again resulting in no values showing for the scorecard.

Resolution We refreshed the Dashboard Designer file from the destination M&A server and â€œdeletedâ€ all (to be safe) pre-existing published data on the server regardless of name (Dashboards, KPIâ€™s, Scorecards, Reports and Data Sources). We then published our newly edited book and successfully were able to publish to Preview and Sharepoint seeing all of our changes.

.NET Framework 3.5 replacement for AJAX v1.0

Patrick Husting — Sun, 09 Dec 2007 00:20:00 GMT

PPS M&A requires AJAX v1.0 installed. Well, recently Microsoft released the .NET Framework 3.5 which includes the AJAX. Well, on a couple of my server rebuilds using .NET Framework 3.5, SharePoint runs just fine.

But, when I install PPS M&A, it crashes the SharePoint page on startup. When I uninstalled .NET 3.5 and used the AJAX v1.0 installs, PPS and SPS fired right up.

You have been warned...

Patrick