RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Building Dashboards with Microsoft BI Tools : Page 2

PerformancePoint Server, the newest product in the Microsoft Business Intelligence stack, offers tremendous productivity gains for building dashboards to display key performance indicators as well as many other reports and charts.

Tip 1: Presenting the End Result
It's always good to define the end result up front, especially when you're dealing with visual requirements. Figures 1-5 show the following:

  • Figure 1: A scorecard that shows the six standard performance measures (filtered by Date, Sales Geography, and Employee), along with a "hot list" of top 10 selling stores for the Date Selection.
  • Figure 2: A pie chart from Excel Services that shows sales broken down by Product Category.
  • Figure 3: A line chart that shows sales for the most recent 12 months of activity.
  • Figure 4: A sales cube that allows users to drill down into sales for a time period and sales/geography dimension definition.
  • Figure 5: A sales report from Reporting Services that shows the user of report parameters.
Figure 1. Dashboard page 1 of 5: A Scorecard and Top 10 stores.
Figure 2. Dashboard page 2 of 5: Pie chart from Excel Services.

Figure 3. Dashboard page 3 of 5: Sales line chart for most recent twelve months.
Figure 4. Dashboard page 4 of 5: Analytic grid with drill-down capability.
Figure 5. Dashboard page 5 of 5: SSRS report, user can set report parameters.

Along the way, you'll see how to build the dashboard and pages using a combination of PPS, Excel, and SSRS. You'll also see the role that MDX plays in customizing output and building filter pick-lists.

Tip 2: Understanding the OLAP Database
Figure 6. The JewelDestiny Cube
Figure 6 shows the diagram of the JewelDestiny OLAP database, which consists of the following:

  • Fact tables for Daily Sales and Repairs, along with counts for Extended Service Plans, Payment Protection Plans, and Credit Applications.
  • Dimension hierarchies for Date, Sales Area (two separate hierarchies for sales by geography and sales by business), and Product.
  • A Visual Studio 2005 Analysis Services Solution (see Figure 7).
  • MDX Custom Named Sets and Custom Member calculations from Analysis Services (see Figure 8).
As you read through the article you'll see that these MDX code pieces play important roles in customizing output.

Figure 7. The JewelDestiny Analysis Services Solution
Figure 8. Named Sets and Calculations in Analysis Services

Tip 3: Getting Started with the PPS Designer
Launch the PPS designer again from http://localhost:40000/WebService/PmService.asmx.

Author's Note: Before going any further, Tips 3-5 create the first dashboard page. As usual, the first one is always the hardest! The end of Tip 5 contains a numbered list of the steps, so if you find yourself lost at any point, or just want to see a recap, check that list.

Figure 9. Creating a Data Source
After you launch the designer, you'll need to define a data source for the dashboard. From the Workspace Browser pane on the far left, right-click on Data Sources and select "New Data Source." The PPS designer will prompt you for the type of Data Source; select "Analysis Services," and then name the Data Source JewelDestiny. Finally, as shown in Figure 9, select the server, database, and cube.

Also note in Figure 9 that the JewelDestiny data source icon (in the Data Source Workspace Browser) has a small pencil symbol inside. The pencil symbol indicates that you've made a change to a local element of the workspace, and you need to "publish" it to the server. So right-click on the JewelDestiny item in the Workspace Browser and select "Publish."

Figure 10. Creating a Dashboard and a Dashboard Page
Now that you've defined a Data Source, you can get down to the business of creating the scorecard for the six sales factors. You can actually create all five web pages for the entire dashboard and then fill them in as you go.

To create the dashboard, right-click on the dashboard item in the Workspace Browser and select the option to create a new dashboard. You'll see a screen to create a new dashboard page (see Figure 10). This allows you to split each page into one or more zones. If you need to display more than one report on a page, you can select a template that best suits the page. (You can select a single zone for the entire page, and then add zones/columns later). In the case of the first page you'll place the sales factor scorecard in the upper left, the filter selections for the scorecard in the upper right, and the top 10 store sales report on the bottom.

Next, Figure 11 shows how you can create multiple pages for the dashboard. You can specify a specific name for each page that will appear as a tag page when you go to deploy (or preview) the dashboard.

Figure 11. Pages in a Dashboard
Figure 12. Workspace Browser

Throughout the article, you'll create KPIs (Key Performance Indicators), Scorecards, and Reports: Figure 12 shows how the PPS designer arranges these elements.

Finally, you can save your work as the workbench file (an XML file with a BSWX extension).

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date