Loading SQL Data

In This Section:

About Loading Data and Building Dimensions

Using Substitution Variables

Creating Rules Files and Selecting SQL Data Sources

Performing Multiple SQL Data Loads in Parallel to Aggregate Storage Databases

About Loading Data and Building Dimensions

After configuring one or more SQL data sources and preparing multiple-table data, you can use Oracle Essbase Administration Services to load data and build dimensions.

  To load data and build dimensions:

  1. If you plan to use substitution variables, create them.

    See Using Substitution Variables.

  2. Create rules files and select a data source.

    See:

  3. Load data into the Essbase database.

    See the Oracle Essbase Administration Services Online Help.

Using Substitution Variables

Using substitution variables in SQL strings and data source names enables you to use one rules file for multiple data sources. One substitution variable can apply to all applications and databases on an Essbase server or to a particular application or database.

You can also define substitution variables for data source names (DSNs) and specify in the rules file the substitution variable names.

Rules for Substitution Variables

  • Use only valid and appropriate SQL values. Essbase does not validate values.

  • Be especially careful with quotation marks (single and double). Different databases require different conventions.

  • Because the ampersand (&) is the Essbase identifier for substitution variables, do not begin SQL operators in SELECT, FROM, or WHERE clauses with ampersands.

Creating and Using Substitution Variables

  To create and use substitution variables:

  1. Using the instructions in the Oracle Essbase Administration Services Online Help, create the substitution variable.

  2. As you edit the rule file, open the SQL data source by selecting File, then Open SQL.

    See the Oracle Essbase Administration Services Online Help.

  3. In the Open SQL Data Sources dialog box, perform an action:

    • To specify a substitution variable for the DSN, select Substitution Variables, and select a substitution variable.

    • To specify a substitution variable in the query, in Select, From, or Where, enter the substitution variable (with its preceding ampersand), instead of a “field=value” string.

  4. Click OK/Retrieve to retrieve the data for the rules file.

    Note:

    You must set the values for the substitution variables before you use the rules file for a data load or dimension build.

Creating Rules Files and Selecting SQL Data Sources

  1. Create a data-load rules file; see the Oracle Essbase Administration Services Online Help.

    Data-load and dimension-build rules are sets of operations that Essbase performs on data as the data is loaded into Essbase databases or used to build the dimensions of Essbase outlines. The operations are stored in rules files.

  2. Select a SQL data source.

    See Selecting SQL Data Sources.

  3. If you plan to create SQL queries in Essbase, see Creating SQL Queries (Optional).

Selecting SQL Data Sources

  To select SQL data sources:

  1. In Administration Services Console, open Data Prep Editor or a rules file.

  2. Select File, then Open SQL.

  3. In Select Database, enter the names of the Essbase Server, application, and database, and click OK.

  4. In Open SQL Data Sources, select the data source or the substitution variable, and enter required information.

    See “Opening an SQL Database” in Oracle Essbase Administration Services Online Help.

  5. Click OK/Retrieve.

  6. In SQL Connect, enter the user name and password for the source database, and click OK.

Facts about data source files:

  • The data source file must be configured on the server computer.

  • On UNIX platforms, the path for the SQL data source file is defined in the .odbc.ini file.

  • On Windows, if the path for the SQL source file was not defined in ODBC Administrator, it can be entered in the Database box of the Define SQL dialog box.

  • If a path is not defined, Essbase looks for the data source file in the directory from which Essbase Server is running.

Creating SQL Queries (Optional)

Instead of creating tables or views to select data for retrieval, you can write SELECT statements as you perform data loads.

Note:

Creating SELECT statements in Essbase is usually slower than creating a table or view in the source database.

The SQL Statement box in the Open SQL Data Sources dialog box provides Select, From, and Where text boxes that help you write SQL queries. You can specify multiple data sources, filter the display of records, and specify how records displayed in Data Prep Editor are ordered and grouped.

Performing Multiple SQL Data Loads in Parallel to Aggregate Storage Databases

When loading SQL data into aggregate storage databases, you can use up to eight rules files to load data in parallel. Each rules file must use the same authentication information (SQL user name and password).

Essbase initializes multiple temporary aggregate storage data load buffers (one for each rules file), where data values are sorted and accumulated. When the data is fully loaded into the data load buffers, Essbase commits the contents of all buffers into the database in one operation, which is faster than committing buffers individually.

Note:

This functionality is different than using the import ... data to load_buffer with buffer_id grammar to load data into a buffer, and then using the import ... data from load_buffer with buffer_id grammar to explicitly commit the buffer contents to the database. For more information on aggregate storage data load buffers, see the Oracle Essbase Database Administrator's Guide.

In MaxL, use the import database MaxL statement with the using multiple rules_file grammar. See the Oracle Essbase Technical Reference.

In the following example, SQL data is loaded from two rules files (rule1.rul and rule2.rul):

import database AsoSamp.Sample data 
   connect as TBC identified by 'password' 
   using multiple rules_file 'rule1' , 'rule2' 
   to load_buffer_block starting with buffer_id 100 
   on error write to "error.txt";

In specifying the list of rules files, use a comma-separated string of rules file names (excluding the .rul extension). The file name for rules files must not exceed eight bytes and the rules files must reside on Essbase Server.

In initializing a data load buffer for each rules file, Essbase uses the starting data load buffer ID you specify for the first rules file in the list (for example, ID 100 for rule1) and increments the ID number by one for each subsequent data load buffer (for example, ID 101 for rule2).

By default, SQL Interface disables parallel connections for the DataDirect ODBC drivers that are provided with Essbase. This feature requires parallel SQL connections; therefore, you must create a configuration file (ESSBASEPATH/bin/esssql.cfg) to change the default settings for the ODBC driver you are using. The following example of an esssql.cfg file for the SQL Server Wire Protocol driver provided with Essbase enables parallel SQL connections:

[
Description "SQL Server Wire Protocol"
DriverName ARMSSS
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]

You must restart Essbase Server for the change to take affect.