Executive Dashboard — The Tip of the Iceberg
Most of our projects have consistent interim deliverables — a progress report on a system implementation, a prototype of a new system, and findings uncovered to date. Clients can see results as projects progress.
One type of project, however, is quite different — a business intelligence project, typically consisting of a data warehouse, an Online Analytical Processing (OLAP) cube, and one or more reporting components such as an executive dashboard. It's hard for a client to see results until the entire data warehouse has been built and some significant percentage of the total data has been loaded. While we develop a project plan and report our progress against it, there is so much technical work going on "behind the scenes" that completed tasks have virtually no meaning to clients — they have little basis for comparing tasks against progress on the overall project.
Is 99% Conformance Good Enough?
To make matters worse, all the data coming into the data warehouse must conform to the business rules defined in the loading routines. It is not enough to have 99% conformance to specifications — any data not successfully loaded will inevitably result in report inconsistencies. Furthermore, the 1% non-conforming data is typically discovered near the end of the project, as loading exceptions are researched. The frequent outcome of such research? Someone says, "Oh, yes, I remember now — the West Region changed how they entered that information about four years ago to conform with the East and Central Regions. Might that be our problem?" Yes, it likely is. Those three oldest years of data will remain in the holding queue until the data consistency issues have been resolved. The end result — users will have seven years of clean, vetted, comparable data across all regions, enabling accurate analysis and reporting.
Getting from 99% to 100% data accuracy is often time-consuming and frustrating. While reports and flashier presentations such as an "executive dashboard" can provide some value until the underlying data is complete and accurate, any interim release always carries several caveats with it.
The data warehouse by itself is typically not the end of a business intelligence project — the warehouse's content must be made available in some way. Busy executives focusing on strategic and major operational issues don't have time to learn the lower-level tools, such as spreadsheet add-in modules, for obtaining warehoused data. Such persons need to have a tool much more easily understood and used, yet a tool flexible enough to answer questions guiding major revenue and expense decisions.
Benefits of an Executive Dashboard
Such a tool is an executive dashboard, software that allows creation of graphs, trend lines, and summarized reports for key performance indicators (KPI) and other business metrics. To ensure executives have a good experience with a dashboard, it must have very fast performance — calculations done at the dashboard level must produce results quickly. How is this done? The dashboard draws from common summaries pre-aggregated by, and stored in, the OLAP cube, which also stores any computation-intensive calculated results.
The implication of the preceding discussion is this — the metrics, calculations, and presentations desired in the final visual deliverable, the executive dashboard, must be designed from the get-go in those prerequisite invisible deliverables, the data warehouse and OLAP cube.
This month's e-newsletter describes the executive dashboard we developed for a client, as the capstone of the overall business intelligence project. Just as only about 10% of an iceberg is visible, with the bulk of its great volume underwater, so an executive dashboard is the tip showing above the base — the data warehouse and OLAP cube — for the overall business intelligence project. The executive dashboard is easy to develop, once the base can provide it the desired pre-aggregated data and pre-stored calculation results.
Todd L. Herman
Read more about getting an Executive Dashboard for your business.
Case Study: Delivering Meaningful Information to Executives
This real estate client had a large quantity of high-quality operational data in its business systems. These systems had been in use for many years, and major upgrades — such as from the DOS to the Windows versions of the systems — had been well-managed to fully convert and preserve historical information. Information from these systems were routinely compiled and analyzed for periodic reviews of each property.
Several business systems, addressing different aspects of this client's complex operations, were in use, and the reporting tools these systems provided were not sufficient to produce many of the complex trend and other analyses routinely used to review their properties. Calculating these analyses in electronic spreadsheets was both time-consuming and error-prone.
To help executives and managers in these periodic property reviews, as well as in day-to-day decisions, an executive dashboard was developed to concisely and visually present key metrics in the various business areas, including:
Performance of the dashboard was optimized by storing pre-aggregated results in the underlying Online Analytical Processing (OLAP) cube, and by optimized data structures in the related data warehouse. The dashboard then draws information from the OLAP cube and data warehouse for calculations and presentations.
Should an executive or manager desire more information about a metric of interest, extensive drill-down and drill-around capabilities were developed to more fully tap the rich treasure trove of information stored in the data warehouse and OLAP cube.
Below, this major report demonstrates the flexibility provided users. Date, Center Type (Drive By, Destination, Local), Region, and Center can all be specified. Since the overall report is on merchandise category, the user can also select how the drill-down outline is organized through the "Drill Down By" picklist. This screen shows the Category -> Sub-Category -> Region progression.
These two boxes, below, show exactly the same information. The first table below accurately presents data, but does little to add meaning. The second chart below quickly shows a major lease change in 2011.
Of interest to both our client and their tenants is store performance by location, compared to averages for the property and the merchandise category — illustrated by the first chart, below. Similarly, a tenant's sales trend across all centers identifies how our client and their tenant might jointly help boost sales at a particular low-performing store — shown in the second image, below.
Results & Benefits...
While the dashboard project was undertaken to benefit the executive group, many areas are benefitting:
- Executives and managers now have ready access to information previously available only on an ad hoc basis through extensive queries and report writing.
- The Information Systems group now focuses less on the presentation of information and more on the quality of information in the underlying systems.
- The Leasing group is able to use the dashboard to answer substantive questions from and about tenants, enabling faster and better responses to lease opportunities.
The routines loading data into the data warehouse:
- Have also increased confidence in the underlying business systems, because they scrutinize data, marking erroneous data for correction and re-submission.
- Draw information directly from the business systems, virtually eliminating maintenance of the data warehouse. As new properties are added, the loading routines recognize this and automatically populate the required data warehouse tables.
The design, development, and deployment of an executive dashboard was the culmination of a large data warehouse and OLAP cube project, giving persons in many areas of the business meaningful information never readily available before.
For More Information...
To discuss how technology usage and business process improvements could be applied to the issues facing your business, call us at 336.297.4200 to schedule a no-obligation consultation, or click here to contact us online.
Read about more benefits of an Executive Dashboard and Data Warehouse.