March BI – Finally, I Got Some Swag

Oh…that didn’t come out right. If you’ve read some other posts you’ll know that I don’t ever have my name drawn for anything at these user groups, well except for maybe a water bottle or a…uhmmm…flask. This time I landed me an autographed copy of Business Intelligence with Microsoft Office PerformancePoint Server 2007. Talk about an easy-to-use BI front-end. PerformancePoint Server puts KPIs, scorecards, and dashboards right into your users’ hands. Sure, you need to manage and govern the back-ends, but moving business functions to the business space could not be easier.

For me, it’s all about the people. Cincinnati has such a strong base of people in the technology space. If we keep moving forward there really isn’t anything we can’t do together. Tonight was one of the most valuable nights of relationship building I’ve experienced in a long time. The Business Intelligence SIG brought in the author Craig Utley, a mentor for Solid Quality Learning, from Louisville. Craig has seen some of the largest Microsoft BI implementations in the world and has participated in SQLCAT. The BI SIG handed us a great learning opportunity.

Walking into Max, I bumped into WorkflowOne’s David Lawton. Knowing the importance of the subject matter, David traveled down from Dayton to attend. Whenever I’m looking for this one particular kind of person David always seems to have a connection, and we’ve exchanged some good email over the last few months. I got to say hello to iSqFt’s Mike Langdon. Jennifer Griffin, an analyst at Quality Gold, also attended. I met Jennifer at the February .NET user group where Clarence Klopfstein, a good friend of mine and peer of Jennifer’s at Quality Gold, introduced me. All-in-all, about 25 people attended – a pretty good crowd for the second meeting.

Craig started into the evening’s talk describing the monitoring, analytics, and planning platforms available in PerformancePoint Server. Any one of these platforms in and of themselves might create a long presentation. I guess that’s why he wrote a book. Being the experienced instructor, his first words to us posed a question: “Who is currently implementing BI?” A couple of semi-raised hands and grunts later and Craig repeats the question. Only louder. And expecting an answer. And I guess that’s what we get for our “Monday morning” welcome.

So what is PerformancePoint Server? Microsoft released the monitoring / analytics / planning product in the October-’07-ish time frame. As one would expect, the monitoring piece and the analytics piece integrate tightly with each other. The planning piece can standalone. All three pieces tell a nice story about why Microsoft released PPS as one integrated product.

What does PPS do? Bottom line, PPS puts drop-dead-easy-to-use BI tools in the hands of business users. In the most basic sense, PPS provides monitoring for a business through scorecards and dashboards, enables a business to analyze data with powerful slicing and dicing tools, and offers a planning and budgeting framework whose output feeds directly into the analytics tools to monitor and gauge progress for this period against the plan as well as provide a basis for the next period’s planning efforts. To this end, PPS generates a cycle of activity around the planning process and the execution of a business plan. With metrics and reporting tools in place, a business can begin to ask, “What happened? Why?” and, “What do I want to happen?”

The monitoring platform replaces Business Scorecard Manager 2005. As one would expect in a monitoring platform, reporting is all about the scorecard. Scorecards, which reside in SharePoint-hosted dashboards, consist of KPIs and link to analytical reports. Obviously, reports is a loose term used to describe drill down, chart, report or whatever type of formatted data is presented to a user. KPIs drive business behavior (hopefully), and reporting generally centers around the KPIs. What’s important to your business? Just ask yourself what triggers executive bonuses and you’ve pretty much discovered your company’s KPIs. Some examples may be sales, margin, headcount, turnover rates, or assembly line rates. More than a number, a KPI should be a meaningful goal, indicator, or trend. Some of the accepted protocols found on scorecards include easy to visualize colors and symbols, such as a green, yellow, or red light, or a color-coded trend arrow. Business decisions drive goals. And with a scorecard, boundaries for scoring and comparison generate KPI scores that hopefully deliver at-a-glance understanding of the data.

Diving a bit deeper, PPS allows fairly easy configuration of scorecards. Users can roll-up KPIs into objectives and weight them in perspectives that affect overall scores. Once the process of creation and configuration is understood, creating both traditional scorecards and scorecards that measure some interesting perspective is straightforward. A traditional scorecard might contain measurements of financial performance. With simple mouse clicks, financial performance measurements will drill down into increased revenue numbers, year-over-year revenue growth, new product revenue, and spending control. Perhaps a non-traditional scorecard will rank salespeople comparing actual vs. target goals with accompanying status and trends.

That’s all great on the front end, but where does the source data come from? The most common source is an MSAS cube, but PPS does not constrain formats for source data. In fact, any ODBC data source *can* feed a scorecard. And as expected, PPS easily reads SQL Server and Excel. Interesting configurations allow multiple data sources to feed one scorecard. For instance, actuals may reside in a cube while goals originate from a spreadsheet. In the end PPS requires SQL Server for the metadata, but the source data could come from anywhere.

So analysts have identified data sources, they know the KPIs, and they have an idea of what the scorecard should look like. How does an analyst create a dashboard to display all this? PPS includes a Dashboard Designer, a .NET WinForms application that allows users to configure scorecards and create dashboards. The Dashboard Designer places all the scorecard elements at the user’s fingertips. We’ll find out later that an analyst will use this same familiar tool for analytics.

The Dashboard Designer, being part of the Office 2007 Suite, has the Office look and feel. Three main workspaces allow easy customization in either a connected or disconnected environment. In a disconnected mode, publishing happens at the push of a button when ready. The flexibility of the tool allows an analyst to pull down a project from the server and work locally. First, add a project data source using the customary connection settings dialog. Then an analyst can start building. If the designer does not make an appropriate indicator available, an analyst can add their own. Functionality allows creation of new KPIs and objectives. An analyst configures them right on the screen. Scoring allows the setting of arbitrary baselines, so an analyst is not constrained to goals between 0% and 100%. For instance, if two reps produce results beyond quota, where one produces 120% and another 200%, the user can set a best score, say, at 130% to ensure that generating 200% of quota earns the appropriate recognition in light of multiple reps surpassing quota. Craig explained and interesting fact to us. Apparently the ADA does not allow color alone to identify a trend, symbol, or indicator, because of its uselessness to the colorblind, so indicators use shapes as well as colors to indicate intent.

Dashboard Designer includes a number of pre-formatted dashboards, and the interface makes filter modification as easy as drag-and-drop. Then preview dashboard builds in a temporary site. Dashboard Designer does not deploy previews to SharePoint.

The analytics piece of PPS enhances the dashboard. Analytics allows for and is designed for in depth data analysis. In PPS v1, the product comes with ProClarity licenses because PPS, as of v1, has not achieved full ProClarity integration. Expect a fully-integrated environment in PPS v2. We should see PPS v2 about the same time we see the next version of Office. That said, ProClarity’s Analytic Chart and Analytic Grid made it into PPS v1. When do you need to move from PPS to ProClarity to derive the necessary results? The breakpoint: if users need to change dimensions on rows and columns then use ProClarity as PPS will not support the behavior.

The PPS report designer allows drag and drop reporting capability. Reporting provides automatic drill down without code. Reports contain filters to create multiple views. Users can view data as multiple dimensions on a series, columns on a graph, or as cells in spreadsheet view, and users can export reports to excel or powerpoint. Dashboard designer provides a number of report templates out-of-the-box. Some drawbacks include the fact that Analytic Chart and Grid only work against cubes and MSAS. In contrast, users can connect to Excel services where Excel ’07 has finally become a fantastic BI tool. Take note that the Pivot Chart and Pivot Table, as well as Trend Analysis, use Offfice Web Components, and their use will require correct licensing.

The PPS reporting web client does not include functionality to drag and drop heirarchies or drill down across differnt dimensions while tracking the history of where you’ve been. You’ll need ProClarity to do that. PPS v2 should include the functionality.The planning tool allows for enterprise budgeting and forecasting. The planning tool is not selling as well as the other two pieces of PPS, which could be expected, as it’s difficult to change the enterprise culture around forecasting and budgeting. A customizable workflow engine routs and approves planning activities. Centralized application logic, security, and metadata management without the need for IT support all add tremendous value to the platform. Excel is the financial analyst’s tool of choice, and the planning process lets analysts just type in the numbers and submit. At the end of the day the server builds a cube. The planning tool can be used to prepare annual budgets, allocate corporate costs across the organization, merge subsidiary data to view consolidated reports or forecasts, build a strategic plan to communicate key business objective and targets, and analyzing what-if scenarios.

PPS scales very well although licensing may be cost prohibitive for small companies. PPS is $20K and CALs $195 per end user. To share reports outside the organization you’ll need the $30K external connector.

In the end, business development managers will want the scorecard and analysts will use the analysis tools. Knowledge workers need reports with some limited interactivity. PPS covers all these things. You can add reporting services or Excel, where Excel ’07 is finally a good BI tool. The unconfirmed bet is that Excel 14 will contain all the ProClarity functionality.

To effectively design, stick to 8 dimensions max. Don’t over-engineer a solution. In Craig’s words, as technologists, “Well, we’re really bad at a lot of things.” Over-engineering is one of those things. In many cases no one uses the available cubes mostly because people don’t understand them. They quickly become too complex to digest. Throw 30 dimensions at users and you’ll overwhelm them quickly. You can run PPS today with SharePoint services and you don’t need SharePoint server. V2 may require SharePoint server, although hopefully not, as it will double the cost of deployment.

That ended the meeting part of the meeting. Swag today includes a couple of books, some water bottles, a keyboard and thumbprint reader, visual studio express, and copies of MSDN magazine.

After the meeting ended, the usual pizza was served. Well, maybe not the usual. Not like there is this stash of pizza they break out of the closet for our meetings. You know what I mean. Since I have some pretty nasty allergies to milk and wheat I grabbed some Wendy’s chili before the meeting. I thought I would pass on the pizza and head out pretty quickly. Needless to say, I got caught up in all the discussions.

I had the opportunity to introduce myself to Pinnacle Solutions’ Director of Business Intelligence, Jeff Cook. Jeff does a great job heading up the BI group with the help of Matt Rigling, another one of Pinnacle’s consultants and director of the Cincinnati SQL Server User Group. I also took advantage of an opportunity to catch up with ex-LUCRUM-ite, Scott Gordon. Scott and I talked about some friends caught up as casualties of the weak economy and the difficulties they must deal with. Bruce Smith from Cardinal introduced himself as Scott and I talked. Russ McMahon and I talked a bit about the senior design projects at CAS.

And finally I had the opportunity to meet Bill Huth, the lead SQL developer at QBase. QBase and I crossed paths when I completed a gap analysis for the Fine Arts Fund last summer. The FAF contracts QBase to analyze their data in lieu of constructing their own data mart. QBase is working on an interesting fund-raising offering that should make these efforts incredibly cost effective for non-profits. I’m looking forward to reviewing it.

You can review Craig’s PowerPoint deck here. Craig Utley can be reached at



~ by Andy on March 14, 2008.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: