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 3

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 4: Creating the KPIs and the Scorecard
Finally, with all these "housekeeping" steps out of the way, you can create the scorecard. Scorecards are comprised of KPIs (Key Performance Indicators). If you're not already familiar with the concept of KPIs, you can easily find definitions in blogs, web searches, and books. Suffice it to say that the six sales factors for JewelDestiny are KPIs: financial measurements to evaluate performance. The goal is to create a dashboard that displays how an employee or store or other geographic sales area is performing, according to the six sales factors.

To create each KPI, right-click on the KPI item in the Workspace Browser to add a new KPI. With each new KPI, you need to specify the actual value and the target value as the Sale Amount measure and the Sales Goal measure from the OLAP cube (see Figure 13). You can then select the threshold scoring indicator: the range of values to measure the actual data against the target data (see Figure 14). In the example for actual sales versus sales goal, anything at or above 100 percent is good (green light), anything below that but greater than 85 percent is a mild warning (yellow light), and anything below that is a red flag (red light).

Figure 13. Creating KPI, Part 1 (Defining the Actual/Target Values)
Figure 14. Creating KPI, Part 2 (Defining the Threshold Scoring Indicators)

After you create the first KPI, the remaining ones are easy: just create a KPI with a name, select the actual and target values and an indicator range. Note that in the case of the percentages, the target values are stored as a fixed value (e.g. 35 percent for Payment Protection Plans). Obviously you could also create a measure column in the OLAP database to handle this.

Finally, if you want to create labels that drill down (for instance, the Scorecard in Figure 1 shows Net Sales Objectives, Percentages, etc.), you can create a special kind of KPI called an Objective KPI. (You select the KPI type when you first create the KPI-either as an Objective KPI or a Blank KPI).

Figure 15. Scorecard with KPIs
After you finish creating the KPIs (and you also publish them), you can create the Scorecard that will display the KPIs by right-clicking on the Scorecard item in the Workspace Browser and selecting "New Scorecard." You can give the new Scorecard a name and then drag the KPIs from the Details list on the far right (see Figure 15). After you publish the scorecard, you can go back to the dashboard and drag the scorecard to the appropriate dashboard page.

Tip 5: Linking the Scorecard with Filters
You're almost there! Now you need to define filters for the scorecard so that the end user can view the scorecard by Sales Hierarchy or Employee, and by Date Hierarchy.

On the dashboard page, you can go to the third tab marked "Filter" and add a new filter. The PPS Designer gives you options to define a new filter by a custom MDX query, a simple Member Selection, or a Named Set. In this instance, you want to display the entire Geography hierarchy. Essentially, the code will amount to the following, which uses the MDX DESCENDANTS function:

    [Store Master].[Geography Hierarchy].[All],
    [Store Master].[Geography Hierarchy].
    [Store Master],
While you could just enter a custom MDX query, you may find that you'll want to use the query elsewhere. In this case, it's better to define the MDX query as a Named Set in the OLAP database, for reusability. Listings 1 and 2 show the MDX code for calculated named sets in the OLAP database. You'll find the code from these listings in the calculations tab of the JewelDestiny OLAP cube (in the Analysis Services project, see Figure 8).

Figure 16. Creating Filters
Once you've defined these MDX Named Sets (which you can now use anywhere), you can select them when you create your filters (see Figure 16).

One more step! You may be asked, "OK, I created my scorecard….I created my filter….how does the scorecard recognize the filter?" Good question! The answer-filter links! You can either right-click on the scorecard that you dropped onto the page, or you can click the Create Links toolbar option at the top of the PPS designer to create filter links.

You create filter links in two steps on the two-tab dialog boxes in Figure 17 and Figure 18. First (on the Link Items tab), you select the filter that you wish to associate with the scorecard, so select the Geography Hierarchy filter first (you're going to do this three times, for each of the three filters). Second (on the Link Options tab), you select the Endpoint Item Filters, and select Member UniqueName as the Source Value.

Figure 17. Creating Filter Links, Tab 1
Figure 18. Creating Filter Links, Tab 2

That's it! OK, that was quite a bit of work. So before I go any further, let me stop and review what you've done so far:

  1. You created a Data Source to the Analysis Services OLAP database (see Figure 9).
  2. You created a dashboard and five dashboard pages, each with a particular zone layout (see Figure 10 and Figure 11).
  3. You created KPIs (that you'll eventually display on the scorecard) where you defined an actual value (Net Sales Dollars), the target value (Sales Goal), and then you defined a threshold scoring pattern (see Figure 13 and Figure 14).
  4. You designed the scorecard by dragging and dropping the KPIs from the list of details onto the scorecard area (see Figure 15).
  5. You dragged the scorecard into the relevant dashboard page.
  6. You defined dashboard filters on Geography (from the OLAP database Named Set FullGeographySet in the first code example below), on Date in Figure 16 (from the Named Set FullSetOfDates in the second code example below), and Employees (just a basic Member Selection of the Employee dimension).
  7.    CREATE SET CURRENTCUBE.[FullGeographySet]
       AS DESCENDANTS (   
            [Store Master].[Geography Hierarchy].[All],
            [Store Master].[Geography Hierarchy].[Store Master],
       SELF_AND_BEFORE ) ;       
       AS DESCENDANTS (   
        [Date Calendar].
            [Business Year - Quarter Desc - Month Desc - Full Date].
        [Date Calendar].
            [Business Year - Quarter Desc - Month Desc -  Full Date].
            [Full Date],
       SELF_AND_BEFORE ) ;          
  8. You created filter links between the filters and the elements (reports, scorecards, etc.) that you dropped into a dashboard page (See Figure 17 and Figure 18). Note that a single report/scorecard can have multiple filter links.
  9. You previewed the dashboard.

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