SAP NetWeaver BW query tools answer questions such as what happened, how many, where the problem is, how often, and what actions are needed. However, business executives seek additional insight, such as what will happen next, what is the best that can happen, and how can I use this information. That is the unique value of a business dashboard as opposed to a query tool. The objective of a dashboard is to derive a future state based on past information.

However, the accuracy and the value of the information in a dashboard depend on how efficiently you use dashboard development tools. Xcelsius is no exception. The development process I present is a consolidation of common practices I’ve come across while designing dashboards using Xcelsius. This information is for dashboard designers and developers. After outlining an end-to-end design process, I explain how you can map it into Xcelsius.

Approach

My approach includes a set of activities I describe as process blocks. Figure 1 is a high-level view of those process blocks.

Figure 1

Dashboard development process

I use a dashboard design sheet to document dashboard development process progress. You record design facts and evaluate them in each process block. The result becomes input for subsequent process blocks. The content largely focuses on dashboard functionality, visuals, and data source definitions. You can download a template for the design sheet via this link.

Dashboard Description

The success of any dashboard design depends on understanding what business executives and managers expect from it for measurement and monitoring of business elements. Often, requirements are articulated as follows: “I would like to see a competitive sales comparison dashboard” or “I need to know the demand in the last quarter. What is the quarterly growth in sales? What is the product market share?”

The challenge is to transform these requirements into dashboard functionalities that provide answers in an at-a-glance screen. More elaboration on requirements provides a better description of the dashboard. You can achieve this by:

• Understanding the executive’s mental model of a dashboard 
• Carrying out a detailed analysis of current business reporting systems and gaps
• Studying the manual information analysis process

Carry out the requirements to a level that describes dashboard functionality in terms of necessary business alerts, comparisons of facts, drilldowns of figures based on specific measures, and filters on business element categories. Find out how users would interact with the dashboard.

With these guidelines, you can prepare the dashboard design sheet with a list of all the functionalities, possible visuals involved, and the user interactions that operate those functionalities. By reading the guidelines, executives should get a better picture of the functions of the end product. Figure 2 contains an example dashboard design sheet that lists the functionality of a competitive comparison dashboard.

Figure 2

Sample dashboard description

Integrating dashboard business functionalities in an easy-to-understand graphical format that can yield actionable insights is no easy task. The most complex and challenging part is the layout of the visual components and navigation patterns. Different relationships among dashboard elements can create different conclusions. You can handle the development of the dashboard layout with a wireframe. It is a fast and easy way to create a conceptual prototype on which people can comment. A wireframe helps you understand the relationship among pages and the end-user experience.

Sketch the Dashboard Layout

The wireframe creation process consists of these three tasks:

• Logically group dashboard functionalities and related visuals
• Decide the base of visual components

• Outline the wireframe

Logically Group Dashboard Functionalities and Related Visuals

The end user can quickly analyze dashboard information if it is provided with the help of logical groups of information. Classify dashboard functionalities that you can logically group together. Functionality groups help to recognize a set of visuals required for that group and the interrelationship between those visuals.

For example, you could logically group quarterly revenue comparison for all competitors, quarterly revenue percentage, and the previous four quarters’ revenue together with the name Growth. Also, you can logically group functionalities that are connected with each other. For example, gross sales, sales return and allowances, cost of goods sold, and selling expenses influence overall income, so you could group these functionalities as profitability analysis.

Decide the Base of Visual Components

Most dashboards display information using graphs, but that is not a requirement. In some situations, textual information is more efficient. Mapping a suitable visual component to a dashboard function depends on what type of information you expect from an individual dashboard function as well as from a dashboard functionality group.

Xcelsius visual components include primary visual components (e.g., charts, tables, dials, gauges, and maps) and secondary visual components (e.g., sliders, combo boxes, radio buttons, check boxes, and panels). Primary visual components handle the dashboard functionality. However, the interrelationship between functionalities and user interaction requires primary as well as secondary components. Figure 3 shows a set of visual components and logical groups defined from the sample competitive comparison dashboard.

Figure 3

Dashboard functionality logical group and involved visuals

Outline the Wireframe

After defining a logical grouping of dashboard functionality and base visual components, you are ready to prepare the wireframe of the dashboard, which defines how and which information is displayed. You can build a wireframe using any simple tool such as a Microsoft PowerPoint presentation or even paper. Pay attention to the following aspects:

• Estimate available dashboard real estate per dashboard functions or functionality group
• Avoid overpacking or scattering the visual components. Focus on complete use of the dashboard real estate.
• Highlight an area or piece of information the user needs to read first. It can be a single bar of the chart, cell of a table, or a highlighter icon on table figures.
• Navigation pattern between functionality groups and their visual components design using minimal mouse clicks
• Define drilldown clearly. Drilldown is the ability to click data that shows related data in the same area or in another dashboard.
• Define a filter clearly. A filter allows users to look at the data differently by allowing them to select the report parameters.
• Define dynamic variation of charts elements (X and Y axes) or tables (rows and columns). This is important when various data matrices are mapped to a single chart or table visual component and are controlled by the selector visual component.

Visual components in Xcelsius need a data provider that derives data directly or indirectly from a data source. You need to define boundaries and the level of the data source to deliver information for the visual components of the wireframe.

Define Data Behind the Scenes

The base data source of a dashboard is predominantly derived from its functionalities. You can simplify its definition by applying dimensions and measures derived from wireframe functionality groups, dashboard functionality interrelationships, and visual design.

Raw data information flowing from the source data is transformed for the Xcelsius dashboard visuals. The results of the data transformation are intermediate data matrices to which visual components connect. You start by defining and consolidating the data matrices required for dashboard visuals. The data matrices evaluate the required key measures and dimensions to derive the final data sources.

Describe Data Matrices for the Visual Components

The Xcelsius visual components use more than one dimension and multiple measures, with the exception of pie charts, which use multiple dimensions. Maps, text, dials, and gauges require a single value as the data measure or key figure. The first task is to list the data sets with multiple dimensions and measures and then look for single data values. Define the data sets in terms of dimensions and measures spread across rows and columns of the Xcelsius Excel sheet.

Consolidate Data Matrices

Select distinctive dimensions and measures from all the data sets listed in the previous section. While doing so follow these best practices:

• For a given measure, identify all the time dimensions and select the lower one as part of the target data source. For example, you use yearly data for yearly sold quantities, but monthly quantity figures to project sales trends. Therefore, you would select monthly quantity figures for the base data source.
• Select more granular information for dimensions. However, you still need to develop the aggregate as part of the data model process. For example, total sales is a summary of all regions, but for regional sales you require all the regional data, which becomes part of the base data source.
• Give preference to base measures such as revenue, sales, and overhead costs. Evaluated measures such as net profit need to be calculated whenever they are required as part of the data model.
• Derive dashboard base data sources from data sets as part of consolidation process. These data sources are the primary information base of the Xcelsius dashboard that will be available to visual and non-visual components. It is suitable to logically group base data as part of the consolidation process. For example, the sales performance dashboard can have a data source for historical facts and another for forecast figures. The dashboard and dashboard functionality groups largely influence base data source groups.
• A small number of base data sources for the dashboard demand a more organized data model. The data model maintains the interrelations of functionalities, reduces redundant information, reduces application maintenance, generates a light weight application, and provides more opportunity to get insight into information to analyze it further.  

Base data source is nothing but a data matrix table in which records are often arranged by spreading measures and dimensions on Excel columns and rows. Figure 4 shows an individual data provider for a given functionality. The definition of the target data source is listed.

Figure 4

Definition of visual component data set and data sources

You have gathered all the essential parts of a dashboard including the wireframe, base data source, and data sets definitions. Now you assemble them. 

Dashboard Construction

Assembly of an Xcelsius dashboard consists of activities performed in sequence. First, position the visuals on the dashboard screen as portrayed in the wireframe design. Then position the source data in an Excel component and, finally, model the data to generate sources for visuals.

Construct the Dashboard Screen

Although wireframe design makes the dashboard layout job simple, you should avoid pitfalls by following these practices:

• Plan to use the full real estate of the dashboard screen without introducing scrolling or panning
• Select or generate elements such as the theme and color scheme required for the dashboard. You can use themes and color to convey essential information more efficiently.
• Avoid overlap of visuals, unless it is intentional
• Properly estimate the size of the visuals. This is especially important when the data volume dynamically changes in large visuals such as charts, tables, or lists.
• Choose visual display elements (such as charts, title, subtitle, and axis labels) that fit into the must-have category. Eliminate others that might distract attention and waste space.
• Plan enough space on the visual for a mouse-click wherever user interaction is required. This becomes more challenging when a chart has many data series bars or data points.

At design time, when more and more visuals gather on the dashboard design screen, management becomes difficult. Xcelsius Object Browser can assist you in organizing visuals effectively. You can select, hide, show, cut, copy, paste, delete, bring forward, move back, group, ungroup, lock, unlock, and access the Properties panels of components.

Build the Source Data

Source data can technically be placed anywhere in the Excel component. However, a dedicated spreadsheet would be more appropriate. To organize final data sources as per dashboard design sheet definition, revisit all the raw data sources as potential contributors that you might consider at the requirement gathering phase.

Take a closer look at each of those raw data sources and identify gaps such as missing data between the target base data source and available raw data. Request these missing data pieces for the final build of the dashboard data source. For example, if the dashboard functionality displays the maximum profit based on the adjustment of a related contributor such as sales and expenses, then you could use the possible maximum and minimum value of sales or expenses to set visual component limits of such contributors.

Avoid picking more granular information from raw data that might not influence dashboard functionality directly or indirectly. For example, if sales figures are based on sales order value, then consider only sales header-level information and avoid sales order item information. However, summaries of information from the same raw data may be needed. If so, you can extract high-level information from the low-level data by applying aggregation formulas.

If the dashboard is designed to consume an online data source (generally fed by Web services or SAP NetWeaver BW queries), then make sure the online query output table or data matrix matches the target data source column and rows definition derived earlier.

Next you apply suitable formats to measures and dimensions. Keep the level of numerical precision as needed. Use appropriate currency symbols, units, and data alignment. Scan data for consistency of dimension values and measures.

Dashboard Data Model

Dashboard data modeling in Xcelsius is a process to organize base data sources. You need to develop a mechanism that produces efficient data sets that, when consumed by dashboard visuals, provide insight into business information. The fundamental challenge of a dashboard data model is to achieve flexibility while optimizing the use of data sets from base data sources.

Data sets in an Xcelsius dashboard consist of Excel cells or a range of cells to which visual and non-visual components of the dashboard are bound. It could be a single value, a single record, or a set of records that are part of the source data or that are transformed from source data. Xcelsius provides numerous options to ease the data modeling process.

Single-Value or Single-Row Data Sets from the Source Data

You can use Excel data functions such as VLOOKUP, HLOOKUP, INDEX, and MATCH to return single values from a source data based on the lookup value. The VLOOKUP and HLOOKUP search abilities are restricted to the first column or first row of a data matrix. However, an alternative approach of the INDEX function used with the MATCH data function allows a lookup value search in a specified column index. Although the lookup value is a single string of characters, you can search on multiple values by introducing a composite key column to the base data source. This composite key column contains unique keys generated for each source data record with concatenation of dimension values involved in the search criteria.

To obtain a complete result row that matches the selection criteria, replicate the lookup formula with the appropriate formula column index into target cells. Although the activity is backbreaking, you get flexibility and are able to arrange the result record values as per the target data set.

Similar to Excel data functions, the majority of Xcelsius visual and non-visual components are capable of filtering single values or single records from the source data at run time. You can use the Insertion type function to enable components to filter a single value, a single column, and a single row of source data, and put this data into destination cells.

A non-visual component, such as SOURCE DATA, is more useful when filter records of source data are based on a row or column index.

Build a Data Set with Multiple Records from the Source Data

Excel lookup data functions and the drill-down ability of Xcelsius components are useful when a data set requires a single value or single record. Imagine scenarios in which a data set needs a collection of multiple records. A workaround uses Excel RANK, MATCH, and INDEX data functions. Working together, these data functions identify row indexes that match selection criteria. Figure 5 illustrates a typical use of these data functions arranged in columns.

Figure 5

Excel data function that filters multiple records from a given source

This approach meets the goal of filtering more than one record from a data source. However, if the source data is huge, the target data set consumes more space than the source. A massive effort is required to write formulas in all the target data set cells. With no other alternative available, use this approach.

Xcelsius provides a set of visual and non-visual components that can replicate source data to the target area. For example, Xcelsius selector components — such as LIST BOX, COMBO BOX, ACCORDIAN MENU, and LIST BASE MENU — can extract multiple records (with the insertion type Filtered Rows) from source data and insert them into the destination data set.

You can use this option to develop advanced specialized custom components that perform the necessary data extraction and transformation from the source data to the destination data sets. Figures 6, 7, and 8 illustrate the structure of a data set with a single value, a single record, and a set of records. 

Figure 6

Data set structure to pick single value that link to the UI component

Figure 7

Data set structure to pick multiple values that link to the UI component

Figure 8

Data set structure to pick data record that links to the UI component

Organize Data Sets

Apply the most suitable filter option to generate those data sets. If you do not have a dedicated worksheet for the source data, you can identify a place for all data sets in the Excel component so that they appear on the top of all data sources.

Static source data makes implementation fairly simple because of the immediate reflection of results into the data set. If you have dynamic dashboard source data, you can implement data functions by taking a snapshot of the dynamic source data at design time.

Testing

Immediately after build, the application design should fully test any application to ensure that the objectives are achieved before publishing to the user community. A dashboard is not an exception. Unlike a software application unit test, most dashboard application tests are conducted with complete business data. The scope of dashboard testing often includes examination of business and technical aspects.

In the testing phase, the dashboard must be able to demonstrate all business functionalities planned in the design phase and defined in the dashboard design sheet. All visuals (individually or jointly) must quickly present information that the user can easily consume and analyze. Alerts and notifications should actively respond for given business scenarios.

You can distribute dashboards via email or publish them on a central corporate Web site or portal. In both cases, the dashboard is converted into an Adobe Flex object, Adobe Flash object, embedded into a Microsoft Word document, or embedded into a Microsoft PowerPoint document. Though you are free to select any options for the application output format, make sure that the application is not so heavy that it becomes difficult for mass distribution via email. It should be light enough to execute quickly on a Web page. Xcelsius allows you to generate standalone executable dashboard files (such as in Flash), which users can download from a Web page and run independently on a local machine. If you are using this approach, ensure that the files are properly sized so that the download from the Web page is easy and quick.

Business functionality-related discrepancies in a dashboard largely point to source data completeness and correctness. With appropriate measures taken on source data you can eliminate most of the root causes of discrepancies. You can address user interface alerts as well as notification and dashboard performance problems by revisiting the property settings of the data model and visuals. With necessary corrections in the wireframe design, the color scheme and theme can handle usability issues.

When the dashboard designer is confident of a successful execution, you can offer the dashboard to power users as a trial version. Designers can expect suggestions or corrections in the trial phase that you can incorporate into the design. Before you label it as final, provide the necessary documentation.

Document and Make Public

An Xcelsius dashboard is a software application that is intended for repetitive use. No matter how frequently an organization plans to use the dashboard, it is important to document processes in a step-by-step guide. Include information about the direction of source data updates as well as configuration of dashboard functionalities. In addition, the process consists of information about base data preparation activities, which is essential before it becomes a source for the dashboard. You should document the information in a dedicated Excel worksheet.

Documentation of the data model design is essential when the dashboard application might be enhanced or modified later. For example, the company might introduce new KPIs or want to display a customer satisfaction index for a new product line. Documentation is related to the data model technique used and its relationship with dashboard functionalities that build on top of it. It consists of comments, scripts, data binding areas of visual components, and special data rules that are implemented using either Excel data functions or Xcelsius components. You can record design-related information in the spreadsheet in which the data model is implemented. Figure 9 illustrates a sample documentation maintained for the competitive comparison dashboard.

Figure 9

Sample document for competitive comparison dashboard

At the end of process, you are ready to publish the final dashboard. Normally an Xcelsius dashboard is published to the end-user community as a finished product in the form of Flash-compiled code embedded into a PDF, Word document, or PowerPoint file. However, a source dashboard component is an XLF file that you can only preserve and maintain by using Xcelsius. As a part of the recommended best practices, you should preserve the XLF file and dashboard design sheet as discussed earlier.

Sandesh Darne

Sandesh Darne is a senior lead for the Consulting ERP Practice at L&T Infotech, India. He leads a group of senior consultants whose primarily focus is on SAP upgrades and SAP usability consulting services. He also directs the SAP NetWeaver Portal and the SAP Center of Excellence, which focuses on excellent practices in organizations. He is certified in SAP NetWeaver Business Warehouse 3.5. He has also worked in the product development department of L&T Infotech where products such as ZoomUP, eALPS, and CodeReview were built and used as accelerators for SAP upgrade projects.

You may contact the author at sandesh.darne@lntinfotech.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.