Defining a Data Source

Data Columns

When you define a data source, part of the information that you specify is the display attributes for each data column that is retrieved from the data source by a query or an information map.

Here is an explanation of the fields:

Column Name

The name of the data column. You cannot modify this value.

In each type of display, Column Name is used as the default value for Category Label, Label, and Property Alias name.

Category Label

The data column that contains the name that is displayed on a KPI gauge or graph.

Here is how Category Label appears in each type of display:

Display Type	Appearance of Category Label
Bar and trend	Not used.
Gauged graph	For line and bar graphs, displayed as the category for a given value.
Graph	Displayed above each gauge.
Interactive Summary Chart and Detail Chart	Used as the label for the horizontal axis, under the bars. A Category Label is required for the field used for the Bar Value property in the indicator.
Interactive Summary Chart and Detail Plot	Used as the label that goes next to each data point on the plot. A Category Label is required for the field used for the Y Value property in the indicator.
KPI	Displayed above each gauge.
KPI table	Not used.
Range map	Displayed as a tooltip.

Label

The data column that contains the formatted value that is displayed. This format can be a simple numeric value of a label.

An SQL query does not require this field. To create a formatted label at the bottom of a gauge, create the label as a formatted string in the SQL query, and then define that formatted string as the Label attribute.

Here is how Label appears in each type of display:

Display Type	Appearance of Label
Bar and trend	Displayed to the right of each gauge.
Gauged graph	For line and bar graphs, displayed as the category for a given value.
Graph	Displayed below each gauge.
Interactive Summary Chart and Detail Chart	Displayed in the tooltip for each bar.
Interactive Summary Chart and Detail Plot	Displayed in the tooltip for each point.
KPI	Displayed below each gauge.
KPI table	Displayed in the value column.
Range map	Displayed next to each data point.

Hyperlink

The data column that contains hyperlinks.

In each type of display, hyperlinks are embedded in gauges or individual data points in graphs.

Property Alias

The text that replaces the value retrieved for Category Label.

Here is how Property Alias appears in each type of display:

Display Type	Appearance of Property Alias
Bar and trend	Displayed as the column heading.
Gauged graph	Displayed as the axis label.
Graph	Not used.
Interactive Summary Chart and Detail Chart	Displayed as the axis label and tooltip label
Interactive Summary Chart and Detail Plot	Displayed as the axis label and tooltip labels.
KPI	Not used.
KPI table	Displayed as the column heading.
Range map	Displayed as the axis label.

When you define a SAS Data Set data source or a scorecard data source, you create a query that retrieves data from the data source. After you create the query, you submit the query for validation. If the query is correct, data source column names appear in the Available list.

If the query is incorrect, the list is empty. If the query is incomplete or incorrect, it is stored so that you can edit it later.

SAS Information Map Data Source

Overview

A SAS Information Map data source easily aggregates data in such a flexible manner that a single SAS Information Map can drive several different dashboard indicators, such as the example shown here:

Example of several different dashboard indicators

This example dashboard contains four indicators:

Overall Product Net Profit And Profit Target shows a single gauge that represents the aggregate of all profit and profit target data across all product lines and sales channels.
Overall Net Profit And Profit Target By Product shows three gauges, each of which is a pair of aggregated values for each product.
Net Profit And Profit Target By Channel shows two gauges, each one representing aggregated profit and profit target for a specific sales channel.
Net Profit By Channel And Country Vs. Goal shows 120 data values: 60 net profit values for each country and product, as well as targets for specific sales channels.

This example demonstrates how a single SAS Information Map can be configured to drive different indicators by taking advantage of aggregation at the SAS Intelligent Query Services level. The information map for this example is a simple relational SAS Information Map that returns approximately 60 rows of data. Here is a sample of the data:

The data columns Country, Channel, and Product contain data that is repeated. Repeated data is a great candidate for grouping.

The first indicator (Overall Product Net Profit And Profit Target) requires only a single row of data. To specify a single row of data in SAS Information Map Studio, select the numeric values and specify that SAS Information Map Studio aggregate them. In the BI Dashboard, the KPI display type shows one gauge per row of data. To create a single gauge, you must aggregate the data so that only a single row of data is returned from the SAS Information Map. The one row of data returns the value by aggregating the data columns according to the aggregation method for the data item. In this example the method is average. The default aggregation method, sum, usually is not desirable for a dashboard, so you must specify the aggregation method on the Classification/Formats tab of the Data Item Properties dialog box in SAS Information Map Studio.

Here are the settings for the data model that drives the Overall Product Net Profit And Profit Target indicator:

Notice that the Selected list contains only the numeric data columns.

For the Overall Net Profit And Profit Target By Product indicator, the data columns are different. This indicator displays three gauges, one for each product. For this data model, Product is selected, but Channel and Country are not. (There are also some numeric data columns concerning market share in this SAS Information Map that are not pertinent to this discussion.) For each numeric data column, Product is selected in the Category label field so that the gauge is labeled with the appropriate name. Here are the settings for this data model:

Settings for the Overall Net Profit And Profit Target By Product indicator

For the Net Profit And Profit Target By Channel indicator, the data model uses only the relevant numeric data columns and the Channel data column. For each numeric data column, Channel is selected in the Category label field so that the gauge is labeled with the appropriate name.

For the small-multiples gauged graph Net Profit By Channel And Country Vs. Goal, all of the details appear in the indicator, including Product, Channel, and Country. Here are the settings for this data model:

Settings for the gauged graph Net Profit By Channel And Country Vs. Goal

Because the labels for the individual bars in the indicator should denote the country, the data column selected in the Category label field for the numeric data column is Country Abbrv.

OLAP Information Maps

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.

You can use the grouping feature of the gauged graph display with an OLAP information map to create a multi-dimensional display of data.

Define a SAS Information Map Data Source

You define a SAS Information Map data source when you create or edit a data model, or create or edit an indicator. For more information, see Create or Edit a Data Model and Create or Edit an Indicator.

To define a SAS Information Map data source:

In the Name field on the New Data Model page or the Data Model Properties page, type the name of the data source that you want to appear in the list of data models.
Next to the Data source field, click Select and select SAS Information Map.

The SAS Information Map field appears.
Next to the SAS Information Map field, click Select.

The list of repositories appears.
Expand a repository, select a SAS Information Map, and then click OK.

The fields to define a SAS Information Map data source appear.
From the Available list of data columns and filters, move items between the Available list and the Selected list by selecting one or more items and clicking the arrows between the lists.

A filter is moved under the Filters node. By default, a data column is moved under the Column node. If the SAS Information Map is based on an OLAP cube, the Selected list includes the nodes Row and Slicer. To use a data column in either of these roles, select Row or Slicer before moving the data column from the Available list.

Note: To make available a filter that contains a user prompt, the prompt must have a default value specified in SAS Information Map Studio.
To order the data columns, click the name of a data column in the Selected list, and then move it up or down by clicking one of the arrows that are next to the list.
When the data columns are set, click Apply Query Changes.
To associate a data point property with data columns in the data source, do the following for each data point property:
1. From the Column Name column, select the check box next to each data point property that you want to include in the display.
2. From the Category Label drop-down list, select a data column in the data source that contains the category label or group variable.
  
  If the data source contains a Name data column, this data column is selected automatically for all data point properties.
3. From the Label drop-down list, select a data column in the data source that contains the label for the data point property.
  
  If you need a complicated label, such as one that concatenates data fields and static text, create a data column in the data source to store the concatenation. Then type the name to display for the data point property in the Property Alias field.
4. From the Hyperlink drop-down list, select a data column in the data source that contains the hyperlink for the data point property.
5. In the Property Alias field, type the name to display for the data point property in the column heading in the KPI table display.
  
  Note: The KPI display ignores this value.
To view the first twenty rows of data that are included in the display, click the Data View tab.
To preview the KPI display or Graph display, click the KPI Preview tab or the Graph Preview tab.

Define a SAS Data Set Data Source

You define a SAS Data Set data source when you create or edit a data model, or create or edit an indicator. For more information, see Create or Edit a Data Model and Create or Edit an Indicator.

To define a SAS Data Set data source:

In the Name field on the New Data Model page or the Data Model Properties page, type the name of the data source that you want to appear in the list of data models.
Next to the Data source field, click Select and select SAS Data Set.

The Query field appears.
In the Query field, type the query code.
Click Submit.

The fields to define a SAS Data Set data source appear.
To associate a data point property with data columns in the data source, do the following for each data point property:
1. From the Column Name column, select the check box next to each data point property that you want to include in the display.
2. From the Category Label drop-down list, select a data column in the data source that contains the category label or group variable.
3. From the Label drop-down list, select a data column in the data source that contains the label for the data point property.
  
  If you need a complicated label, such as one that concatenates data fields and static text, create a data column in the data source to store the concatenation. Then select that data column from the Property Alias drop-down list.
4. From the Hyperlink drop-down list, select a data column in the data source that contains the hyperlink for the data point property.
5. In the Property Label field, type the name to display for the data point property in the column heading in the KPI table display.
  
  Note: The KPI display ignores this value.
To view the first twenty rows of data that are included in the display, click the Data View tab.
To preview the KPI display or Graph display, click the KPI Preview tab or the Graph Preview tab.

Define a Metadata Table Data Source

You define a SAS metadata table data source when you create or edit a data model, or create or edit an indicator. For more information, see Create or Edit a Data Model and Create or Edit an Indicator.

To define a SAS metadata table data source:

In the Name field on the New Data Model page or the Data Model Properties page, type the name of the data source that you want to appear in the list of data models.
Next to the Data source field, click Select and select SAS Metadata Table.

The Select Table tree and the Available Columns table appear.

The Select Table tree includes all registered libraries that contain tables.
From the Select Table tree, select a table.

The data columns in the table appear in the Available Columns table.
From the Available Columns table, select the data columns to include in the query by selecting the check box at the end of a row.

Note: You can select all data columns by selecting the check box at the top of the last column.
For each selected data column, select a method to summarize the data from the Summary column.
For each selected data column, choose whether to group the data by selecting the check box in the Group by column.
To specify additional filters, type the filter (using SQL syntax) into the Filter field.
Click Apply Query Changes.

More fields to define a SAS metadata table data source appear.
To associate a data point property with data columns in the data source, do the following for each data point property:
1. From the Column Name column, select the check box next to each data point property that you want to include in the display.
2. From the Category Label drop-down list, select a data column in the data source that contains the category label or group variable.
3. From the Label drop-down list, select a data column in the data source that contains the label for the data point property.
  
  If you need a complicated label, such as one that concatenates data fields and static text, create a data column in the data source to store the concatenation. Then select that data column from the Property Label drop-down list.
4. From the Hyperlink drop-down list, select a data column in the data source that contains the hyperlink for the data point property.
5. In the Property Alias field, type the name to display for the data point property in the column heading in the KPI table display.
  
  Note: The KPI display ignores this value.
To view the first twenty rows of data that are included in the display, click the Data View tab.
To preview the KPI display or Graph display, click the KPI Preview tab or the Graph Preview tab.

Define a SAS Strategic Performance Management Data Source

You define a SAS Strategic Performance Management data source when you create or edit a data model, or create or edit an indicator. For more information, see Create or Edit a Data Model and Create or Edit an Indicator.

To define a SAS Strategic Performance Management data source:

In the Name field on the New Data Model page or the Data Model Properties page, type the name of the data source that you want to appear in the list of data models.
Next to the Data source field, click Select and select SAS Strategic Performance Management.

Several fields appear.
In the Scorecard server URL field, type the URL to the location of SAS Strategic Performance Management in the form http://<server>:<port>/.
From the Date format drop-down list, select a date format.

When you create the query, all dates must be in this format.
In the Name format field, type the name to appear above the indicator in a dashboard.

You can type any text. However, keywords supply certain information from SAS Strategic Performance Management. When the query is run, the keywords are replaced with values from SAS Strategic Performance Management for the cell specified in the query. These are the keywords:

TEMPLATE

is the name of the template on which the scorecard that is associated with the queried cell is based.

PROJECT

is the name of the project in which the scorecard that is associated with the queried cell is located.

SCORECARD

is the name of the scorecard that is associated with the queried cell.

ELEMENTTYPE

is the name of the type of element that is associated with the queried cell.

ELEMENT

is the name of the element that is associated with the queried cell.

METRICATTRIBUTE

is the name of the metric attribute that is associated with the queried cell.

DATE

is the date on which the metric attribute value is based. To specify the current date, type the pound sign (#).

METRIC_VALUE

is the value of the metric attribute that is associated with the queried cell.

The keywords are case-insensitive.

Use a plus (+) between a literal and a keyword and between keywords. For example, if the queried cell is located in the scorecard Shoe Sales in the project East Coast, SCORECARD + for the + PROJECT + region produces Shoe Sales for the East Coast region.

You can apply a SAS format such as dollar8.2(METRIC_VALUE) to any text.
In the Value format field, type the value to appear below the indicator in a dashboard.

This field accepts the same text, keywords, and formats as the Name format field.
In the Query field, type the query that specifies a cell within SAS Strategic Performance Management from which to retrieve values, and then click Submit.

Use a vertical bar (|) to separate the parts of the query that specify a cell. Here is the general format to specify a cell:

template | project | scorecard | element_type | element | metric_attribute | date | URL

URL specifies the hyperlink that appears in the indicator in a dashboard. URL is optional.

If you don't include URL, one is automatically generated from SAS Strategic Performance Management. The target of the automatically generated URL is either the directive for the queried cell or the scorecard that contains the queried cell.

If you do include URL, the string is used exactly as you type it.
Use two vertical bars (||) to separate individual queries as in the following example:
```
tmplt1 | prj1 | score1 | elemtype1 | elem1 | metattr1 | date1 | <url> || 
tmplt2 | prj2 | score2 | elemtype2 | elem2 | metattr2 | date2
```
Click Submit.

More fields to define a scorecard data source appear.
To associate a data point property with data columns in the data source, do the following for each data point property:
1. From the Column Name column, select the check box next to each data point property that you want to include in the display.
2. From the Category Label drop-down list, select a data column in the data source that contains the category label or group variable.
  
  If the data source contains a Name data column, this data column is selected automatically for all data point properties.
3. From the Label drop-down list, select a data column in the data source that contains the label for the data point property.
  
  If you need a complicated label, such as one that concatenates data fields and static text, create a data column in the data source to store the concatenation. Then select that data column from the Property Alias drop-down list.
4. From the Hyperlink drop-down list, select a data column in the data source that contains the hyperlink for the data point property.
5. In the Property Alias field, type the name to display for the data point property in the column heading in the KPI table display.
  
  Note: The KPI display ignores this value.
To view the first twenty rows of data that are included in the display, click the Data View tab.
To preview the KPI display or Graph display, click the KPI Preview tab or the Graph Preview tab.