INFOMAPS Procedure

INSERT DATASOURCE Statement

Makes the data from either a table or cube available to the current information map.

Syntax

Form 1:

INSERT DATASOURCE SASSERVER="application-server-name"
TABLE="library"."table" <options>;

Form 2:

INSERT DATASOURCE SASSERVER="application-server-name"
CUBE=<"schema".>"cube" <options>;

Summary of Optional Arguments

_ALL_

specifies to insert a data item for each physical column or hierarchy as defined in the specified table or cube.

COLUMNS=(column-1 <...column-n>)

specifies one or more physical column names as defined in the specified table.

DESCRIPTION="descriptive-text"

specifies the description of the data source, which can be viewed by the information map consumer.

ID="data-source-ID"

specifies the ID assigned to the data source.

NAME="data-source-name"

enables you to specify a descriptive name for each data source inserted in an information map.

REQUIRED_DATASOURCE=YES | NO

specifies whether the data source is added to the list of required data sources for the information map.

Required Arguments

The INSERT DATASOURCE statement must include the SASSERVER= argument and either the CUBE= or TABLE= argument.

CUBE=<"schema".>"cube"

identifies an OLAP cube as a data source for the current information map.

A cube must be both of the following:

registered in the currently connected metadata server
associated with a schema that is registered in the SAS OLAP Server specified by the SASSERVER= option

Note: A SAS OLAP Server can have only one schema. A schema lists the available cubes.

Restrictions:If you use the CUBE= argument in an INSERT DATASOURCE statement, then you cannot use the TABLE= argument in any INSERT DATASOURCE statement in the same PROC INFOMAPS step. Although you can access either relational data or cube data, you cannot access both types within the same information map.

You can insert only one OLAP cube into an information map.

Cube names are case sensitive.

SASSERVER="application-server-name": identifies the SAS server. The server can be either a SAS application server for relational data (SAS libraries) or a SAS OLAP Server for cube data. The type of server being accessed is identified by the TABLE= option or the CUBE= option.

TABLE="library"."table"

identifies a relational table as a data source for the current information map.

A table must be both of the following:

registered in the currently connected metadata server
associated with a SAS library that is registered in the SAS application server specified by the SASSERVER= option

In order for an information map to use a table, the table must have a unique name in its SAS library (for a SAS table) or database schema (for a table from a different DBMS) in the metadata server. If multiple tables in a SAS library or database schema have the same name, then you must perform one of the following tasks before you can use any of the tables with an information map:

From either SAS Data Integration Studio or the Data Library Manager in SAS Management Console, you can rename a table by changing the value of the Name field in the General tab in the properties window for the table.
From SAS Data Integration Studio, delete the duplicate tables.

Alias:SERVER=

Restrictions:If you use the TABLE= argument in an INSERT DATASOURCE statement, then you cannot use the CUBE= argument in any INSERT DATASOURCE statement in the same PROC INFOMAPS step. Although you can access either relational data or cube data, you cannot access both types within the same information map.

You can use multiple INSERT DATASOURCE statements to add multiple relational tables to the same information map. However, when accessing multiple tables, all tables must be accessed from the same SAS Workspace Server.

Table names are case sensitive.

Optional Arguments

_ALL_: specifies to insert a data item for each physical column or hierarchy as defined in the specified table or cube.

Interaction:If you specify the _ALL_ option, then you cannot specify the COLUMNS= option.

COLUMNS=(column-1 <...column-n>): specifies one or more physical column names as defined in the specified table. The INFOMAPS procedure inserts a data item into the information map for each of these named columns.
The column list can be a single SAS column name or a list of SAS column names separated by at least one blank space and enclosed in parentheses.

Restriction:This option applies only to a relational data source.

Requirement:If you specify the COLUMNS= option, then you must specify it immediately after the TABLE= option.

Interaction:If you specify the COLUMNS= option, then you cannot specify the _ALL_ option.

DESCRIPTION="descriptive-text": specifies the description of the data source, which can be viewed by the information map consumer.

Alias:DESC=

Restriction:Although you can specify more than 256 characters for the data source description, SAS programs can use only the first 256 characters of the description.

ID="data-source-ID"

specifies the ID assigned to the data source. The ID is a value that you can use in an expression to uniquely identify the associated data source in the current information map.

If you do not specify the ID= option, the INFOMAPS procedure generates an ID for the data source based on the specified table or cube name. If the generated ID is different from the table or cube name, then the INFOMAPS procedure prints a note in the SAS log with the generated ID. You can use the LIST statement to display data source IDs.

Restrictions:Nulls and non-blank nonprintable characters are not valid in an ID. The following characters are invalid:

. < > [ ] { } \ / ^ @ ~

If a name contains any of these characters, they are replaced with an underscore (_) when the ID is generated from the name.

Table and cube names are case sensitive.

The first 32 characters of an ID must be unique across an information map. An error occurs if you specify an ID that is the same as an existing ID (data item, data source, filter, or other). An ID that differs only by case from another ID in the current information map is not considered unique.

NAME="data-source-name": enables you to specify a descriptive name for each data source inserted in an information map. If you use the INFOMAPS procedure to insert multiple data sources from the same physical table, the data sources will, by default, all have the same name. When you view the data sources in SAS Information Map Studio, they are indistinguishable because the names are used as identifiers in the graphical user interface. Use the NAME= option to customize the name for each data source.

REQUIRED_DATASOURCE=YES | NO: specifies whether the data source is added to the list of required data sources for the information map. By default, the data source and its assigned filters are included in a query only when a data item that references the data source is explicitly selected for the query. Specify YES if you want the data source and any associated assigned filters to be used in every query that is generated from the information map.

Default:NO

Note:After the data source is created, you can use the REQUIRED_DATASOURCES= option in the UPDATE CURRENT_INFOMAP or UPDATE INFOMAP statements to control whether it is required.

Details

An inserted data source is a logical representation of a table or cube that you can query via the information map. An OLAP data source and the cube that it references have the same set of properties. A relational data source has properties that are not part of the referenced table. You can insert multiple tables as data sources into an information map. A table can be inserted as a data source multiple times in the same information map. Each of these data sources has a unique ID and its own set of properties. To refer to a table data source in an expression, you must use its ID. By default, the ID of a table data source is the same as the table name.

To view a list of all the data sources in the current information map, use the LIST DATASOURCES statement. Even though the data source name and its ID have the same value by default, you can use the ID= option to specify a different ID or use the NAME= option to assign a different name.

Examples

Example 1

/* Insert all the columns from a relational data source. */
insert datasource sasserver="SASMain"
   table="Basic Data"."CUSTOMER" _ALL_
   id="CUSTOMER_US"
   description="Domestic Customers"
   required_datasource=yes;

Example 2

/* Insert only three columns from a relational data source. */
insert datasource sasserver="SASMain"
   table="OrionTables"."CUSTOMER_DIM"
   columns=("Customer_id" "Customer_name" "Customer_age");

Example 3

/* Insert an OLAP data source. */
insert datasource sasserver="SASMain"
   cube="SASMain - OLAP Schema"."class"
   id="Sample_Data";