Preparing Data For Dashboards

Dashboards are only as good as the data that supports them. The greatest graphics in the world are useless without accurate, quality, flexible data sources.

Preparing data for use with a dashboard requires configuration of data sources. This is not a complex process, rather using as simple systematic approach can ensure that quality data is made available from virtually any contemporary database that supports both the current data needs and any future data requirements without refactoring the dashboard or underlying data structures.

The following method uses a ROLAP type approach [see note 1] and works best using non-aggregated data.

The basic process includes:

• Define and document the dashboard goals
• Create data entities [views] describing the data
• Identify measures and store in the database
• Identify dimensions and store in the database
• Create parameterized queries

Defintions

KPI - key performance indicator representated by a single element [portlet] on a dashboard.

Measure - a single value or a set of numeric values displayed in the KPI. For example - time, #units, #actions, dollars. They are represented in the dashboard as numerical content or graphical symbols.

Dimension - indicates how data should be grouped or distributed. This may be by business function, by person, by day etc

Filter - allows the user to narrow the data to be displayed. Most dashboards employ several configurable filters such as Date, Owner, Process Stage etc.

Step 1: Documenting the Dashboard Goals

Dashboard projects generally remain active to evolve over time as user adoption grows and new requirements are identified. Identifying these requirements should provide answers to such questions as:

Marketing Goals

• What is the revenue?
• What is the revenue to forecast?
• What is the overall profit?
• Is peformance improving or declining based on last week, last month, last year?
• What is the effectiveness of marketing campaigns?
• Which products are selling?
• What is the market share of each product?
• What is the portfolio share of each product?
• Which channels are selling the most?
• Which channels are underperforming?

Step 2 – Create the Data Entities

Developer identifies which databases and data tables contain the most appropriate and timely information for each KPI

Identify is each data set is defined by the same attributes - do not assume they do

Where practical, all database tables related to a particular set of questions should be joined using common ID fields into a database view. Each set of joined tables becomes a Data Entity. Creating a single Data Entity with a set of related data simplifies the creation of flexible dashboard elements and allows the developer to add derived fields for grouping and filtering.

Step 3 – Identify and Store Measures

Identify the Meauses - these are typically based on one numeric column in the DataEntity.

For example, two numeric measures, Actual and Budget are commonly displayed in the dashboard as:

• Sum (Actual)
• Sum (Actual) by Dimension (e.g. Location, Product, Cost Type, etc)
• Comparing Budget to Actual
• Comparing Budget to Actual by Dimension

Other measures may include:

• Number of Transactions
• Variance
• Percent of budget
• Largest value transaction
• Average transaction value
• Age of transaction

Store the Measures

Measures are stored in a database table, making them available for a stored procedure lookup and providing an easy source for navigational menus.

Each measure needs at least three elements:

1. ID
2. Label
3. SQL Fragment representing the measure

Step 4. Identify and Store Dimension Data

Dimension data is characterized by those columns, or derived columns, by which data should be grouped. Dimensions are also used to filter data, for example, by Product Type, AND “California” location.

Identify Dimensions

Identify from business requirements all dimensions required:

For example:

• Product
• Location
• Product Category
• Channel
• Cost Group

Some dimensions may need to be sources from 'derived columns' configured in the Data Entity. This is a combination of two columns, such as

Month/Year (e.g. Month(TransactionDate)+’-‘Year(TransactionDate))

Cost Range (e.g. case when Actual < 1000000 then “Small Deal” else “Large Deal” end)

Store the Dimensions

Dimensions are stores in a database table in the same way as the Measure table to provide the same availability for stored procedure lookups and navigational menus.

Step 5. Create the Parameterized Queries

Three parameterized queries suffice for most of a dashboard’s data requirements.

Discrete Data Query

A discrete data query brings back one or more related measures with no dimensionality/grouping. This type of data is normally represented in a data table, a Dial/Gauge chart, or a Bar/Meter chart.

The minimum input to a Discrete query includes:

1. A list of Measure indexes corresponding to those in the Measure table
2. The database name of the Data Entity
3. Filter indexes corresponding to the Dimension table, conditions, and member values

QUERY

(variables highlighted)

SELECT MEASURE
FROM DATAENTITY
WHERECLAUSE

DATA RESULTS

One Dimensional Data Query

A one dimensional data set displays one or more related measures, grouped by one Dimension. This data can be displayed using various chart types: Bar charts, Pareto charts, Pie charts, Line charts, etc.

The minimal inputs to a One Dimensional query include:

1. A list of Measure indexes corresponding to those in the Measure table
2. The database name of the Data Entity
3. The dimension index to group on
4. Filter indexes corresponding to the Dimension table, conditions, and member values

QUERY

SELECT DIMENSION, MEASURES
FROM DATAENTITY
WHERECLAUSE
GROUP BY DIMENSION

Two Dimensional Data Query

A two dimensional data set provides one or more related measures, grouped by two Dimensions, usually displayed in a number of different chart types.

The minimum inputs to a Two Dimensional query include:

1. A single Measure index corresponding to one in the Measure table
2. The database name of the Data Entity
3. Two dimension indexes to group on
4. Filter indexes corresponding to the Dimension table, conditions, and member values

QUERY

SELECT DIMENSION,DIMENSION2, MEASURE
FROM DATAENTITY
WHERECLAUSE
GROUP BY DIMENSION, DIMENSION2

RESULTS

[using pivot function]

Options FOr Building Dynamic Queries

There are different options for building a dynamic query:

Create a query within a Named Data Set file, and pass along MEASURE, DIMENSION, and filter information. Although this is quick to implement it can raise a security risk as the SQL fragment is passed directly into the NDS file and a single query cannot easily handle a variable number of measures or a variable number of filters.

The preferred method is to use a database stored procedure to accept input from the Named Data Set file. The stored procedure dynamically builds the query and retrieves data based on ID’s, preventing exposure of the actual database column or table names.

Drill Downs

Many dashboards provide drilldown features that depend on ID fields being passed alongside readable content. This is best achieved by passing along two fields for the dimension to keep consistency between different dimension types and to supply the ID if necessary.

Raw Data Displays

Raw data displays are where dashboard charts or tables are based on data with no SUM or other aggregation applied. This can be achieved by either:

• Creating a “Raw measures” table and a “raw” data query
• Factoring the aggregation function out of the measure
• Using a unique ID field as the Dimension “group”

Time Range Filters

Some filters fall outside what would normally be considered a dimension, for instance, time range. To provide these kinds of filters, create a separate table to hold the appropriate values. The stored procedure will then convert the ID into the actual dates.

Pre-aggregated Data

A Data Entity works best with non-aggregated data. Data should not mix aggregation records with “raw” data records.

Once the data is prepared, KPIs and dashboards can be developed with options for end user configuration and personalization.

NOTE 1: ROLAP [Relational On-Line Analytic Processing] typically uses pre-aggregated content, however aggregation is not required in this methodology.

NEXT: Designing Dashboards

Back To Top

Dashboards Index | Types of Dashboards | Developing Dashboards | Preparing Data | Design Guidelines | Dashboard Products |