Database Adapters: HP Reporter, MS SCOM, SAP ERP, SAS EV, and VMware

What Are the Database Adapters?

Preparing a Database Adapter for Staging

Duplicate-Data Checking for Database Adapters

Enabling Duplicate-Data Checking for Database Adapters

Subsetting Input Data By Using Duplicate-Data Control Data Sets

Backloading Data By Using Macro Variables

What Are the Database Adapters?

Several adapters extract raw data from databases. The following adapters are database adapters:

HP Reporter
MS SCOM
SAP ERP
SAS EV
VMware

Preparing a Database Adapter for Staging

SAP ERP and SAS EV Adapters: The raw data for the SAP ERP and SAS EV adapters must be in SAS data sets. If raw data does not exist, the staging job ends with an error message.

HP Reporter, MS SCOM, and VMware adapters: The data sources for the HP Reporter, MS SCOM, and VMware adapters add performance data to relational databases as follows:

For the HP Reporter adapter, the relational database can be one of the following:
- an ORACLE database on a UNIX or Windows operating system
- an SQL database
- a Microsoft Data Engine (MSDE) on a Windows operating system
For the MS SCOM adapter, the relational database must be a Microsoft SQL Server database.
For the VMware adapter, the relational database can be one of the following:
- an ORACLE, Microsoft SQL Server or, for VMware vCenter releases prior to 5.5, an IBM DB2 database on a UNIX or Windows operating system
- a Structured Query Language (SQL) database

If an ODBC server definition must be created that points to that database, see the “ODBC Sources” topic in the “Overview of Common Data Sources” chapter in the SAS 9.4 Intelligence Platform: Data Administration Guide. This document can be found at this location:http://support.sas.com/documentation/onlinedoc/intellplatform/index.html. Find the section that corresponds to the type of database that contains your data, and follow the instructions for that section.

These instructions include the steps required to perform the following tasks:

define the ODBC driver on your system, if necessary
register the database server
create and register the database library that points to your database

If you want to establish connectivity to a SQL Server Database by using ODBC, follow the instructions for “Establishing Connectivity to an Oracle Database by Using ODBC.” (The descriptions of ODBC connections to Oracle and Microsoft Access databases can be helpful when you connect to similar data sources.)

When creating and registering the database library that points to your database, you are prompted to specify several fields, including the Database Schema Name. Use the following values for that field:

VMware – schema=dbo
MS SCOM – schema is left blank
HP Reporter – schema is left blank

The data from one of these databases is input directly into the Adapter Setup wizard. You can set up the library that points to the database in SAS Management Console. After the library is defined, it can be specified as the raw data input to the appropriate staging transformation.

Duplicate-Data Checking for Database Adapters

Enabling Duplicate-Data Checking for Database Adapters

The staging transformations for the HP Reporter, and MS SCOM adapters do not provide a parameter for duplicate-data checking. Duplicate-data checking is always enabled for these adapters. (Duplicate-data checking is not automatically enabled for the VMware adapter.)

For more information, see Appendix 5, Duplicate-Data Checking.

Subsetting Input Data By Using Duplicate-Data Control Data Sets

The input data from all adapters can be subset by using duplicate-data control data sets.

Subsetting means that only newly identified records can be processed and written to staged tables. (The ranges in the duplicate-data control data sets are not used to determine what data is to be processed.)

Note: If you want to backload data, you must delete the duplicate-data control data sets. The staging job can then process all the data that is available in the database or in the raw data tables.

Backloading Data By Using Macro Variables

You can backload data for HP Reporter, MS SCOM, SAS EV, and VMware staged tables that are added to the process flow diagram after the staging job has already run once. Backloading data can be accomplished by overriding the default action of the duplicate-data control data sets that subset the incoming data. To do so, set the macro variables ITRM_LoadFromDate and ITRM_LoadToDate to valid start and end datetime values. Then, this datetime range is used to subset the data from the database instead of the ranges in the duplicate-data control data sets.

%let ITRM_loadFromDate=14FEB2010:00:00:00; 
%let ITRM_loadToDate=15FEB2010:23:59:00;

Note: When these macro variables are used with the VMware adapter, you must specify the values for these macro variables in Coordinated Universal Time (UTC). (UTC time is the same as Greenwich Mean Time (GMT).)

The following list explains when the data is subset:

For the HP Reporter adapter, the data is subset as it is extracted from the raw data tables.
For the MS SCOM adapter, the data is subset while extracting from the database.
For the SAS EV adapter, the data is subset after it is initially extracted from the raw data tables.
For the VMware adapter, the data is subset after it is initially extracted from the raw data tables.

For information about backloading, see How to Backload Raw Data.