Guidelines for Defining Indicator Data :: SAS(R) BI Dashboard 4.31: User's Guide, Second Edition

Define the Data to Suit the Indicator

Use the following guidelines to define data that suits the indicator:

Determine the indicator types to use with the indicator data and the required appearance of the resulting indicator. Then, plan the indicator data to meet your needs.

For example, gauges in a KPI indicator appear best with indicator data that produces summarized data with a limited number of data rows. If you use indicator data with significant amounts of data to drive a KPI indicator, SAS BI Dashboard attempts to produce one KPI gauge representation for each row of data in the indicator data. For significant amounts of data an indicator type that automatically summarizes the data is preferable. Bar charts are an example of this type of indicator.
To display a single gauge in a KPI indicator, the indicator data must return a single row of data.
To display multiple gauges in a KPI indicator, each numeric value should be associated with a descriptive name. After you define indicator data, you select the data fields to include in the indicator.
An indicator type might support only certain date formats. Make sure that your data uses only the date formats that are supported by your selected indicator type. If the data uses a format that the indicator does not support, the date might not display correctly in the indicator.

To determine the supported date formats for an indicator type, open an indicator of the selected type in the dashboard designer. In the Display Settings section of the Properties pane, check the available values in any format lists (such as the X-axis format and Y-axis format lists).

Note: Not all indicator types use date format settings.

Design the Data for Efficient and Responsive Dashboards

Create indicator data so that it executes quickly.

A single slow set of indicator data can slow the entire dashboard. Even when indicator data returns only a single row of data, if it must first join tables that each contain thousands of rows of data, the indicator data will not perform well.
Information Maps are useful because you can apply filters to the data. However, if you do not require a filter, you can point your indicator data directly to the table registered in the metadata.
(Information Map data sources) When you open an indicator data object, the Query Results tab is populated with data. If it takes awhile to for the query results to appear, it is a good indication that you should consider summarizing the data or changing the information map to be more efficient. If the query is slow when you are defining the indicator data object, it will also be slow when executing from a dashboard.
(Information Map data sources) If you have an information map that was built specifically for a SAS Web Report Studio report, consider building an information map specifically intended for a dashboard. Doing so enables you to optimize the information map for the dashboard.

Aggregate or Summarize the Data

It is a best practice to aggregate the data as much as possible. Doing so ensures the best performance in a dashboard and avoids unexpected results, such as too many rows or gauges, or incorrect summaries.

CAUTION:

The default maximum number of rows returned by any data source is 10,000.

If the source data contains more than 10,000 rows, an error message about making an adjustment appears. If possible, aggregate the data to reduce the number of rows to return.

If the source data has too many rows, summarize the data.

Summarize data at the indicator data or in the source data. Do not rely on the indicator to perform the summary.

Tip
For an SQL query, you can summarize data using aggregation functions such as AVG() and the distinct keyword.

Another summarization strategy is to summarize the data into a summary table. The summary table is then used as the basis for the SQL query or as the base table for the information map. In the following figure, the first illustration shows the SAS BI Dashboard indicator data using a query that combines data from multiple tables. The second illustration shows the same two tables being summarized by an ETL program. The SAS BI Dashboard indicator data then uses this summarized table.

Alternative Methods for Summarizing Data

The first method is easier to set up and might result in more timely data, but the second method is more flexible and is probably more scalable. You can use a similar strategy when creating information maps that will be used by the SAS BI Dashboard indicator data.
(Information Map data sources) If you choose fewer columns in the query, it is possible to avoid the row limit or an inefficiently large number of rows.
(Information Map data sources) An information map data source easily aggregates data in such a flexible manner that a single information map can drive several dashboard indicators.
Some indicator types support summarizing or averaging data. For a list of these indicator types, see Indicator Types that Aggregate Data.

Miscellaneous Guidelines

(SQL query data sources) The underlying LIBNAME must be pre-assigned.
(SQL query data sources) When you define an SQL query data source, you create a query that retrieves data from the data source. After you create the query, submit the query for validation. If the query is incorrect, the list is empty, and an error message is displayed.
(OLAP Information Map data sources) The data returned from an OLAP information map is flattened into a two-dimensional table structure. You assign a role for each dimension when you define the data source. The roles are Column, Row, and Slicer.

Note: You must assign the role of Row to at least one data column.

For dimensions with the role of Row, the levels of each hierarchy are the data fields. The corresponding members of the level are the values of the data field, where the field name is the label for the level. The other data fields are the columns that are typically seen in an OLAP viewer. The field names for the data fields are the comma-delimited levels of the dimensions with the role of Column, in the order of the dimensions with the role of Row.

Tip
You can use the grouping feature of the bar chart with reference lines indicator with an OLAP information map to create a multi-dimensional display of data.