Customize the Rule File for an ODBC Source of Data

If your source data is network accessible and configured using ODBC, you can configure Essbase rules to access the source data using SQL query results.

Example 1 - DB2

Here is an example of the SQL Properties for a load rule for accessing a relational source of data in DB2 that is not defined as Datasource in Essbase.

The Name field must match the DSN name defined in odbc.ini (Linux), or the Data Source Name configured in Windows. A SQL query you write pulls the data from DB2 to Essbase.

SQL Properties dialog. Properties option selected is SQL Data Sources (DSN). Name is TBC_DB2. A SQL query is in the Query field. User name and password for DB2 are entered.

If you want to use one load rule for more than one ODBC source of data, you can use a substitution variable for the DSN name (and for elements in SQL clauses). For example, if the &dsn and &month variables are defined in the cube associated with the rule file,

Variables section of the cube inspector for Sample Basic shows two substitution variables defined. One is named "dsn" with a value of TBC_DB2, and one is named "month" with a value of Dec.

then the load rule can reference these variables, as shown in the following rule example:

SQL Properties section of a rule defined on Sample Basic references two substitution variables. One reference, &dsn, is in the Name field. Another reference, &month, is in the SQL query in the Query field. The SQL query reads: select a.SKU, b.STATE, &month, c.CHILD, SUM(d.AMOUNT) from Product a, Market b, Measures c, Sales d where d.STATEID = b.STATEID AND d.PRODUCTID = a.PRODUCTID AND d.MEASURESID = c.MEASURESID GROUP BY a.SKU, b.STATE, &month, c.CHILD

Example 2 - Teradata

Here is an example of the SQL Properties for a load rule for accessing a relational source of data in Teradata.

Source Properties > SQL Properties tab in a load rule. Properties selection is ODBC (DSN-less), Server entry is teradata://192.0.2.110:/1025/TBC, and Query entry is select * from TABLENAME.

The example above corresponds to this configuration in odbc.ini (on Linux):

[Teradata]
Driver=/opt/teradata/client/16.20/lib64/tdataodbc_sb64.so
Description=NCR 3600 running Teradata V2R6.2
DBCName=192.0.2.110
astUser=
Username=xxxxxx
Password=xxxxxx
Database=TBC
DefaultDatabase=TBC
NoScan=no
MechanismName=TD2

Here is another example of the SQL Properties for a load rule for accessing a relational source of data in Teradata.

Source Properties > SQL Properties tab in a load rule. Properties selection is SQL Data Sources (DSN), Name entry is $TELAPI$192.0.2.110, and Query entry is select * from TABLENAME.

The example above corresponds to this configuration in odbc.ini (on Linux):

[$TELAPI$192.0.2.110]
Driver=/opt/teradata/client/16.20/lib64/tdataodbc_sb64.so
Description=NCR 3600 running Teradata V2R6.2
DBCName=192.0.2.110
astUser=
Username=xxxxxx
Password=xxxxxx
Database=TBC
DefaultDatabase=TBC
NoScan=no
MechanismName=TD2