Chapter 3: Case Study: You Cannot Manage What You Cannot Measure

This is an excerpt from a Rough Cut. Rough Cuts are pre-published manuscripts that authors have released on Safari Books Online before the book is available in print. Rough Cuts not only provides you with early access to future best sellers as they are being written, but also the chance to be part of their development, allowing you to post your feedback or respond to comments from users, editors and authors.

In this chapter:

Overview and Business Background

Organizational Structure

Business Situation and Requirements

Where to Start

Proposed Solution Architecture Roadmap

Basic Project Plan

This chapter provides a case study that allows you to follow ACME Company, a fictitious production company, as they work to implement a business intelligence (BI) solution with PPS. Appropriate metrics are at the core of BI and performance management solutions. You will see how ACME first examines what it wants to measure then plans how to measure what they need to manage. You will follow their process as they move from business requirements to the work of gathering and analyzing data, identifying measures, designing KPIs and scorecards, designing reports and final dashboards.

This chapter provides a high-level and end-to-end view of implementing a real-world BI solution along with a blueprint of a basic project plan that you can use as a jumping off point for your own implementation.

Overview and Business Background

ACME Company is a film and television production company located in Los Angeles. The company has been in existence for five years and has 10 employees. Since its inception, the company has created several local television shows. Two years ago, ACME Company expanded into nationwide syndication with a new science fiction action show called Star Quest that has just completed its second season. Star Quest is broadcast in 10 markets across the country: New York, Los Angeles, Chicago, Philadelphia, Boston, San Francisco, Dallas, Washington D.C., Atlanta, and Houston.

The producers have outlined several short-term and long-term goals they would like to achieve with the show and with the company as a whole. They would like to start laying the foundation for these changes now, during the hiatus between the recently completed season two and the upcoming season three. Briefly, these goals relate to market expansion and increasing popularity.

Currently, the production company has negotiated a license fee with 10 affiliate stations in different markets across the country. The fee is broken down into categories for the three types of broadcasts, First Run, Ancillary, and Re-Run.

First Run

First run describes an episode that is being shown for the first time in a specific market. First run shows can run at any time during the week, but each episode plays in all 10 markets within the same week, regardless of which day.

Ancillary

Ancillary describes an episode that already had its first run, and is being rebroadcast within six days of its First Run broadcast. Often referred to as “an encore presentation” by the local affiliates, it does not necessarily air in the same week as a First Run broadcast, but will always air before the next First Run broadcast. For example, if a week is considered to run from Monday to Sunday, an affiliate may broadcast a First Run episode on Saturday night. The Ancillary broadcast may not be until the following Wednesday. It is not uncommon for Ancillary Broadcast to run the hour before a new First Run episode airs.

Re-run

Re-run describes an episode that has already had a First Run and Ancillary broadcast. From that point on, all broadcasts of that episode are referred to as Re-runs.

As a BI expert, you have been brought on board as an associate producer to help the production team meet their goals by analyzing their show data.

Currently all of the data captured by the production team exists in numerous Excel spreadsheets. In addition to unifying the data into a central location, you will be analyzing the data to help the production company meet its new goals.

Organizational Structure

Figure 3.1 is a high-level organizational chart of ACME Company. Haik, the developer, has interviewed the management team and a summary of the interviews can be found in the in the Problem Domain (Needs) section of this chapter.

FIGURE 3.1
The organizational chart for ACME Company.

Business Situation and Requirements

Market Expansion

As part of their overall strategy, the company is planning to expand into new markets on several fronts.

Short-Term Goals

Currently in the top 10 television markets, producers wish to expand to the top 30 for next season. As a result, they will need to negotiate license fees in the 20 new markets. These should be based on number of viewers in the market and comparable to the existing 10 markets.

Note - CEO - “We should be able to quickly glance and see a list of the top 30 markets in order of viewers.”

Affiliates make a profit by selling advertising time during the broadcast, usually to local companies. ACME Company would like to land a national advertising partner. Placing a national ad into the broadcast would mean less advertising time available for the local affiliates and so the License Fee will need to be renegotiated in existing markets. To understand these negotiations better, the CFO needs to have answers to the following types of questions:

How much do we need to drop the license fee to compensate for the loss of one minute of local advertising time?
What is the minimum we must charge for a national commercial to offset the potential drop in revenue if we discount the license fee?

Note - CFO – “It would be great to see some kind of sliding/adjustable scale so that you can fiddle with the numbers for how much you are charging for a national spot and how much you are charging the affiliates to hit the perfect sweet spot.”

To get the maximum amount from a national advertiser, the show needs to have the best ratings possible. As the show is on at different times on different nights in the various markets, ACME Company would like to analyze ratings data, and try to figure out the best nights and spots in each market. They then plan to offer a license discount if affiliates are willing to move the show to the more popular slots. Again, to understand their negotiation position better, they need to have answers to these questions:

How much can we offer while still increasing revenue?
How can the ratings from multiple markets be aggregated into an average for a national sponsor to use as a gauge?

Note - Marketing – “This key performance indication would track ratings for the upcoming third season and compare it to the previous two seasons with the goal of increasing ratings by 10% over the previous two seasons (average).”

Long Term Goals

Looking further into the future, ACME Company will be expanding its show offerings. First, they will be creating a spin-off from Star Quest. They need to decide what night and spots the new show should take.

Note - Marketing – “By analyzing our ratings from the previous two seasons, we should be able to determine what the first and second best slots are for the show. We could then decide to move the current show with its loyal fans to the second best slot, and put the new spin-off into the best possible slot to attract as many viewers as possible.”

Increase Popularity

In addition to increasing ratings by improving the timeslots in various markets, the production team would also like to target the show to fit the audience’s tastes. This means analyzing the episodes with the highest ratings to try to spot trends in style, cast, and guest stars so that the most popular elements can be worked into more episodes.

Note - CFO – “This is an extension of the lower costs initiative. By analyzing the show types and ratings we should be able to strike a balance of lowering costs while satisfying fans.

We want to analyze show demographics to determine if there are elements that increase viewership in our lower demographic categories.

We need to break our ratings down into demographics to see if any show elements are more or less appealing to others than our core group of fans.”

Where to Start

In order to define its expansion strategies better, the company needs to understand current performance of its show as well as market performance. Producers and managers need to be able to answer the following types of questions quickly:

How is the show performing now?
How is the show performing relative to market indicators?
How is the market performing?
How can we better understand the market to position the show in the best way possible?
Based on the analysis what targets do we want to set for the show?
Based on the analysis, what factors can help us meet advertising and rating targets for the show?

Asking questions like this is a good starting point for ACME Company and for any business wanting to implement a business monitoring solution. From this point, you can move to examining what you want to measure and how you plan to measure it.

Tip - First, analyze your organization and ask what business problems you need to solve.

Second, ask how your organization can quantify or measure these problems.

Third, go to your Information Technology group and ask how technology can support the metrics you have defined.

Proposed Solution Architecture Roadmap

After consulting with senior management and key stakeholders, the IT group has decided the best strategy is to start with a small implementation of PerformancePoint Services. Business monitoring is new to ACME Company so one of the goals of this strategy is to provide the company with a manageable start that they can build on. Their plan is to start with two servers, as shown in Figure 3.2, and create a deployment that can scale for rapid growth. At this stage, PPS and Excel Services are the only services that will be enabled on the SharePoint server.

FIGURE 3.2
IT proposes a two-server deployment of PPS that can scale easily for growth.

All the data that will be used is stored currently in individual spreadsheets. The plan is to load the data once using SQL Server Integration Services (SSIS), and then continue to update it directly in the OLTP database. To make the data as accessible and flexible as possible, the IT group has also decided to build a dimensional data warehouse and an OLAP cube. Structuring the data in this way will make it easier to build analytics dashboards. It will also provide the producers with the business information they need to make timely, well-informed decisions.

Basic Project Plan

To build monitoring solutions, organizations can begin with an examination of data or by examining existing business issues and decisions. Beginning at either end of the spectrum is appropriate and provides great flexibility. However, it is important to keep in mind that the data and business decisions must be integrated eventually.

Appropriate metrics are at the core of BI and performance management solutions. For example, first you need to examine what you want to measure and how you plan to measure it. Then you need to understand what data you currently have available to build your metrics.

A basic project plan for building a BI solution includes the following steps:

Gather data
Analyze data and identify measures
Design KPIs and scorecards
Design reports
Design final dashboard

Gather Data

Data must be set up so it can be used as a measure of performance. This is true regardless of the data source or storage solution. PPS can work with different types of data sources whether your organization stores data in Analysis Services cubes or in Excel spreadsheets such as ACME Company (see Figure 3.3).

FIGURE 3.3
Currently the data exists in a series of spreadsheets collected by the production team.

At ACME Company, the spreadsheets contain data about Actors, Affiliates, Regions, Broadcast, Characters, Episode, License Fee, and Slots. This information is gathered from various data sources such as Nielsen ratings. Next, the information is consolidated manually into the spreadsheet. Once the data warehouse, cube, and dashboards are built, the plan is to automate the data gathering process as well.

Since the data in the spreadsheet is already fairly denormalized, the process of structuring it into a dataset that can be useful for an Analysis Services cube should be a straightforward process.

Note - Keep in mind that when you import data from Excel, you often have pay attention to data types. Frequently, data that you want to treat as Ints, is interpreted by SSIS as Double or String. For this reason, make sure you do the proper data type conversions in SSIS.

Build Dimensional Data Warehouse

The first step is to load the Excel spreadsheet data into SQL Server. You need to do this so that we can build a multi dimensional data warehouse that is better suited to use with Analysis Services than Excel.

The most straightforward way to load data from Excel into SQL Server is to use (SSIS). The IT group has decided to load all the spreadsheet data as is into the staging area. This means they will not be doing any transformations or data conversions. The dimensional transformation will take place from the staging area. The final dimensional schema is depicted in Figure 3.4.

FIGURE 3.4
The final dimensional schema built from the data loaded from the Excel spreadsheets.

Based on the data warehouse, the IT group will build a cube that will be used to build KPIs, scorecards, reports, and dashboards. The initial structure of the cube is depicted in Figure 3.5. This cube will allow ACME Company to analyze episode and viewer data.

FIGURE 3.5

Tip - This is the initial structure of the cube that will be used to analyze episode and viewer data.

Here are some points to keep in mind:

It usually pays to start small. Do not try to implement all measures and dimensions at once.

It is easier to focus on a few measures and dimensions and make sure these are done right. Then you can add other measures and dimensions later.

Once you have a solid foundation, adding more measures and dimensions is a straightforward process.

Analyze Data and Identify Measures

You need to answer the following questions to determine which KPIs ACME Company needs

Which type of broadcast brings in the most viewers for Star Quest?
What are the top markets in terms of viewers?

Listed below are the KPIs that might assist in answering these questions:

KPI – Number of viewers for different types of broadcasts
KPI – Advertising time for different types of markets and broadcasts. KPI displays market by advertising time.
KPI – Ratings by night and spot in each market. KPI displays market by ratings.
KPI – Most popular night. KPI displays audience numbers by day of the week.
KPI – Most popular spot. KPI displays audience numbers by spot and day of the week.
KPI – Most popular episode. KPI displays audience numbers by episode.
KPI – Most popular character in the show. KPI displays audience numbers by character appearance.
KPI - Most popular guest star. KPI displays audience numbers by guest star appearance.

Design KPIs and Scorecards

During the functional specification design process, basic concepts such as what you want to see on a scorecard and what KPIs to use can be modeled using tools such as Excel or Visio. The next four figures include examples of scorecard mockups that were created using Excel.

This first example in Figure 3.6 is a Ratings Scorecard that measures the numbers of viewers by each location and for each broadcast types. The final scorecard will include Actual and Target values.

FIGURE 3.6
The Ratings Scorecard measures numbers of viewers by each location and for each broadcast types.

In the second example in Figure 3.7, the data for each location and broadcast type in the sample Ratings Scorecard has been rolled up to display objectives only.

FIGURE 3.7
Roll up the data to the objective level to view overall values for each location.

In the third example in Figure 3.8, a Character Scorecard measures the number of viewers by each episode. The final scorecard will include Actual and Target values. This will allow ACME to determine which characters are the most popular.

FIGURE 3.8
The Character Scorecard measures the number of viewers by each character and for each episode.

In the fourth example in Figure 3.9, the data for each character in the sample Character Scorecard has been rolled up to display objectives only.

Alexis Jarr verified figure 12/22/2009.

FIGURE 3.9
Roll up the data to the objective level to view overall values for each character.

The first and most important KPI that ACME Company wants to analyze is Viewers. As with the cube, the IT group has decided to begin with a small and simply designed scorecard that allows ACME to measure viewership against a target (see Figure 3.10).

FIGURE 3.10
This scorecard is simple but allows ACME to measure a critical metric[md]the number of viewers.

Design Reports

Presenting properly designed reports is a critical aspect of a successful dashboard. Reports allow users to further investigate data and to apply visualizations that “makes sense” to them. Each person in an organization interprets and consumes information differently and PPS makes is easy to change visualizations on the fly. The five figures included in this section are examples of how you can create a variety of different reports while still using just the single Viewer KPI.

This report allows us to analyze viewers by episode and region (see Figure 3.11). It allows us to understand how the viewership trends over a season. It provides answers to questions such as:

Does the viewership decline or increase during the course of a season?
Is there a pattern that can be identified during the course of a season?
If so, what factors might influence a decline or an increase?

FIGURE 3.11
Use this report to analyze viewership by episode and region.

The stacked bar chart allows you to analyze how the different regions proportionally compare to each other (see Figure 3.12).

FIGURE 3.12
The stacked bar chart presents a different view on the same data showing how different regions compare to each other.

This second line chart, shown in Figure 3.13, allows you to drill into a specific region and look at viewership trends based on time and individual affiliates.

FIGURE 3.13
This line chart displays viewership trends based on time and individual affiliates.

The ability to analyze data in a grid format is useful when we want to be able to drill down into data. The grid format used in Figure 3.14 enables you to analyze data based on date and episodes. You can quickly understand how different seasons or episodes within a season compare to each other.

FIGURE 3.14
With the grid format, you can quickly drill down into data.

Using Visio Strategy Maps allows you to visualize data in many interesting ways. The Visio Strategy Map shown in Figure 3.15 uses a map to inform the viewer how the West, Central, and East regions compare to each other.

FIGURE 3.15
Visio Strategy Maps allows you to present data in highly visual and accessible ways.

Design Dashboard

Fundamentally, the goal of a dashboard is to display valuable business information in the best possible way to foster and strengthen the decision-making process of an organization. The look and feel of the dashboard is critical to the success of a BI solution. PPS is visual decision-making tool. The display of the data is just as important as how the data is stored and organized.

When you design a dashboard, it is important not to add too much information on one page. Users should be able to analyze what is on a dashboard and figure out how the information pertains to them without spending unnecessary time scrolling up and down or trying to understand the meaning of a particular scorecard or report (see Figure 3.16).

FIGURE 3.16
In this example, the dashboard offers the user a selection of pages making it easy for them to navigate the data.

In the dashboard shown in Figure 3.16, several pages have been added including Strategy Map, Show Details, Web Page, and Stacked Bar. This selection allows the user to choose the scorecard or type of report they want to look at and makes it easy for the user to navigate the data.

Another good tool to use is filters, which allow the user to look at high-level information or select values that are more specific(see Figure 3.17).

FIGURE 3.17
Filters allow users to specify the information they want to see.

On the strategy map example, we added a filter that allows the team to analyze viewership based on all seasons, individual seasons, or individual episodes.

Summary

This chapter introduced the ACME Company production company and provided an overview of the process ACME went through to implement a BI solution with PPS.

ACME Company already had all the information necessary for analysis in Excel spreadsheets. The chapter covered the process of moving this data into SQL Server and transforming it into multi-dimensional data using SSIS. Once the IT team built the data warehouse, they were able to build a cube that could be used to create KPIs, scorecards, reports, and dashboards.

This chapter brought you full circle from identifying business requirements to creating a technology solution that supports transparency and clarity in the decision making process.

Later chapters in this book provide additional information and instructions on each of the topics introduced in this case study.

Best Practices

First, analyze your organization and ask what business problems you need to solve.
Second, ask how your organization can quantify or measure its business problems.
Third, go to your IT group and ask how technology can support the metrics defined by the business analysis.
If your company is new to BI, begin with a manageable implementation that you can then build on.
A basic project plan for building a BI solution includes the following steps: gather data, analyze data and identify measures, design KPIs and scorecards, design reports, and final dashboards.
During the functional specification design process, use modeling tools like Excel and Visio to diagram the basic concept for your scorecards and KPIs.
When you design a dashboard, it is important not to add too much information on one page.

Skip Links

Chapter 3: Case Study: You Cannot Manage What You Cannot Measure

Excerpt from Microsoft Office PerformancePoint Services 2010 Unleashed.