corecards, test results, report cards, summaries—nearly everyone wants to skip past the details and see the bottom line; managers evaluate professional efforts based on performance. Microsoft's Business Intelligence tools provide developers and power users with the tools and methodologies to produce scorecards and other summaries that represent performance graphically. This article will create a web-based dashboard using several Microsoft products, including the newest product in the BI stack, PerformancePoint Server.
Introducing the Players
If you've been following Business Intelligence lately, chances are good that you've heard the term "Microsoft BI stack." This term refers to the set (or pyramid, as some call it) of products that comprise Microsoft's BI offerings.
First, the foundation for data storage and analysis capabilities is SQL Server 2005 which includes Analysis Services, Integration Services, and Reporting Services: three key pieces of a BI solution.
Second, a collection of Office products work on top of SQL Server 2005 to deliver attractive, customizable output. These products include Microsoft Excel, Reporting Services and ProClarity.
Third, Microsoft PerformancePoint Server works as a portal to integrate output into a series of dashboard pages. PerformancePoint Server includes Scorecard and Dashboard functionality from Business ScoreCard Manager 2005.
Dashboards and Performance
So, you may be asking, what is a dashboard? Generally speaking, a dashboard is an interface (usually web-based) that displays summarized information, so that viewers can analyze it easily. Dashboards usually contain some combination of reports, scorecards, and charts: all designed and arranged so viewers can see information about the data being presented. Dashboards usually contain Key Performance Indicators (KPIs), when there are financial measurements to evaluate performance.
This article presents a fictional jewelry company called "JewelDestiny," with retail stores across the United States. JewelDestiny tracks sales by both a geography-dependent and geography-independent sales hierarchy, as well as by a product brand hierarchy. They measure the performance of each salesperson and store performance by six factors:
- Sales versus pre-defined sales goal.
- Percentage of sales with "add-on" items, such as a chain, polishing cloth, etc. (must be at least 40%).
- Sales made with an Extended Service Plan, as a percent of Total Sales that were eligible for a plan (must be at least 50%).
- Sales made with a Payment Protection Plan, as a percent of Total Sales eligible for a plan (must be at least 35%).
- Repairs/resizing (must be at least 5% of total sales).
- Credit applications (must submit one per full work day).
JewelDestiny evaluates and monitors performance by whether an employee, store, and sales hierarchy meets these six factors; meeting six of six is perfect, five of six is good, and four of six is acceptable. JewelDestiny management wants a web site dashboard that essentially provides four things:
What's on the Menu?
- A web page for managers to view the results of these six factors, for either a single employee or a sales hierarchy, for a specific period of time (a specific day, a month, a quarter, YTD, etc.).
- Standard reports, such as the top ten selling stores for a period of time, a line chart showing sales by Product Category for the last twelve months of available data.
- A drill-down report that allows an analyst to view sales for a high-level hierarchy definition (product category total for the year), and then drill down to subcategories or even individual items, by quarter, month, or even day.
- The ability for "power users" to add new reports as necessary.
This Baker's Dozen article will provide the necessary steps to meet the requirements for JewelDestiny performance monitoring in the following Baker's Dozen tips:
- Presenting the end result, a five page dashboard
- Building the database and the OLAP solution
- Introducing the PerformancePoint designer, and creating the scorecard (dashboard page 1)
- Building a simple dashboard (dashboard page 1)
- Assigning filters for the scorecard (dashboard page 1)
- Building a top 10 sales report (dashboard page 1)
- Creating a pie chart from Excel (dashboard page 2)
- Reading the pie chart from Excel Services, and placing it into a dashboard (dashboard page 2)
- Building an analytic line chart (dashboard page 3)
- Building an analytic, drill-down sales cube report (dashboard page 4)
- Building a report from SSRS that utilizes custom MDX and reporting parameters (dashboard page 5)
- Incorporating the SSRS report into PerformancePoint (dashboard page 5)
- Deploying the solution to JewelDestiny's SharePoint site
Note that some of these tips are "continuation" tips (e.g. Tips 11 and 12 are a set). I'll annotate these where necessary.
Before You Begin
This article covers a complete BI solution; here are the elements available for creating the project:
- The relational database containing all the raw sales data for JewelDestiny
- A SQL Server 2005 Analysis Services project that creates an OLAP database consisting of a sales cube and dimensions for Products, Sales Geography, and Dates
- Separate SQL Server Reporting Services projects and Excel spreadsheets for specific pages of the JewelDestiny dashboard
- A PerformancePoint Workspace project that contains the definition for the JewelDestiny dashboard, which consists of the following: the JewelDestiny scorecard for the six sales performance measures, references to external reports and charts, as well as PerformancePoint analytic reports and charts
- While not required to run the examples, you have the option to deploy the JewelDestiny dashboard to a SharePoint site
A Quick '411' on Using PerformancePoint Server
|Author's Note: normally you would not use all of these tools to design reports and charts for a dashboard. One of the objectives of this article is to show the different available tools and their capabilities. Excel, Reporting Services, ProClarity, and even the PerformancePoint designer all provide nice reporting/charting tools. They are also subsets and supersets of one another—this can initially drive you quite mad! You should learn the capabilities and limitations of each of these. There is no one perfect designer.
You can download a trial version of PerformancePoint Server by doing a web search on "Office PerformancePoint Server 2007 Trial Software." Microsoft offers a trial version for both 32-bit and 64-bit environments. You will only need to install the Monitoring module: the other major module (the Planning Module) is beyond the scope of this article. PerformancePoint has a few prerequisites such as running on a server-based operating system that includes the .NET Framework 2.0, the ASP.NET AJAX extensions, SQL Server 2005, etc. I built these examples using RC1 of Windows Server 2008, though you should be able to run the examples using Windows Server 2003 R2.
Microsoft has designed PerformancePoint Server to install on http://localhost:40000. You launch the PPS designer using the shortcut address http://localhost:40000/DesignerInstall/ (the PPS designer is a .NET 2.0 ClickOnce application). You can use the designer to build dashboards, preview them in a web browser, and then publish them to a SharePoint site.
|Editor's Note: This article was first published in the March/April 2008 issue of CoDe Magazine, and is reprinted here by permission.|