Introducing SQL Interface
Use this chapter to understand SQL Interface and prepare for data loading with it:
New Features
Use this section to identify new features or changes since Release 5:
New for Release 6.5
Release 6.5 provides these improvements:
- SQL Interface is installed automatically with every OLAP Server installation.
- You can use substitution variables in SQL strings in a manner similar to substitution variable use in report scripts and calculation scripts. For details, see Using Substitution Variables.
- Release 6.2 supports MERANT DataDirect ODBC drivers Version 4.0 on Windows NT and UNIX, except for AIX, which uses Version 3.6.
New for Release 6.2
Release 6.2 includes these improvements:
- Sample SQL Interface data source files are now located in /app/SQL_Samp of your Essbase installation instead of in the same directory as the Sample application, /app/Sample.
- Release 6.2 supports MERANT DataDirect ODBC drivers Version 3.6 on Windows NT and UNIX.
If you do not have the ODBC components but you have already installed SQL Interface, you can install the ODBC components by navigating to the ARBORPATH\odbc\merant40\mdac directory (Windows) or $ARBORPATH\setup (UNIX) in your Essbase installation, and double-clicking mdac_typ.exe.
New for Release 6.1
Release 6.1 includes this improvement:
As of Release 6.1, the SQL Interface installation program on Windows no longer installs the ODBC Data Source components in the /bin directory. You will need these components to configure your data sources.
To verify whether you have these components installed, and if not, how to install them, use this procedure:
- Open your Windows control panel, and perform either of these steps:
- Check for an ODBC Data Sources icon in Windows NT.
- Double-click the Administration Services icon in the Control Panel, double-click the Data Sources icon and check for ODBC drivers.
- If you do not have the ODBC components, you can download Microsoft Data Access Components (MDAC) from the following Web site:
http://www.microsoft.com/data/MDAC21info/manifest_intro.htm
New for Release 6.0
Release 6.0 includes these improvements:
- SQL Interface runs on HP-UX and Windows 2000, as well as on AIX, Solaris, and Windows NT.
- As of Release 6, Essbase SQL Drill-Through is no longer provided, so you may need to use a previous release of SQL Drill-Through.
To use a previous release of SQL Drill-Through with Release 6.x, do the following:
- Install Essbase Spreadsheet Add-in, Release 6.x.
- Install Essbase SQL Drill-Through, Release 5.0.2 Patch 9 (or later), in the same directory.
For information on the data sources qualified for use with Essbase on Windows NT and UNIX platforms, see the Essbase Installation Guide, file essinst.pdf in the /docs/pdf directory of your Essbase installation.
Note: You can also load data from spreadsheets and text files directly, using Application Manager. See the Essbase Database Administrator's Guide.
Understanding SQL Interface
Use SQL Interface to load data directly from a number of popular SQL, relational, and flat-file database servers. SQL Interface provides the connection you need to accomplish these tasks:
- You can execute SQL statements on data before loading. For example, this allows you to load summary data instead of the entire contents of your source database.
- Optionally, you can build Essbase dimensions dynamically as you load data.
- You can load data from flat files that are in ASCII text format, as well as connecting directly to your relational source database.
Note: You can load data from spreadsheets or text files using Application Manager without the use of SQL Interface. See the Essbase Database Administrator's Guide.
SQL Interface works with Application Manager to retrieve data:
- You write a SELECT statement in SQL using Application Manager.
- SQL Interface passes it to the SQL or relational database server. For non-SQL data sources, SQL Interface converts SQL statements to requests for data that the source understands.
- SQL Interface interprets the records received from the SQL, relational, or flat-file database server using the rules defined in the data load rules file. (For more information on data load rules files, see Loading SQL Data.)
- SQL Interface loads the interpreted, summary-level data into the database.
Preparing for Data Loading
To prepare to load SQL, relational, or flat-file data use this procedure:
- SQL Interface is installed during your OLAP Server installation. See the Essbase Installation Guide for information about initial configuration tasks.
- Choose your ODBC driver and point it to its data source. See Configuring a Data Source.
- If your data is in multiple tables, prepare the data for loading by doing either of these actions:
- Check that you can connect to the data source by using the Data Prep Editor in Application Manager to open the SQL source file. See Loading SQL Data.
- Create a data load rules file to tell SQL Interface how to interpret and load the SQL data into your Hyperion Essbase database. See Loading SQL Data.
After these steps are complete, you can load your data. See Loading SQL Data for instructions.