WIXLIB

Configuring a Data Source2In This ChapterConfiguring a Data Source on Windows . 7Configuring a Data Source on UNIX . 7Before you can access data using Essbase SQL Interface, you must configure the operating systemfor each data source and the driver it requires. Essbase installation provides DataDirect ODBCdrivers.This chapter describes procedures for configuring Windows and UNIX. For information aboutconfiguring non-DataDirect ODBC drivers, see Chapter 5, “Using Non-DataDirect ODBCDrivers .” For a complete list of supported ODBC drivers, see Hyperion Essbase - System 9Installation Guide.Note:For detailed, driver-specific information on each of the DataDirect drivers, see the DataDirectConnect for ODBC Reference. The location of this reference varies depending on the platform.Typically it is within the \ HYPERION HOME\common\ . \books\odbc\odbcref\directory.Configuring a Data Source on WindowsOn Windows, use the ODBC Administrator to configure the data source. To configure the ODBC Administrator:1 Start the ODBC Administrator by opening the Windows Control Panel and double-clicking the AdministrativeTools icon, then double-clicking the Data Sources icon.2 Choose a data source, or add one, and then enter the required information about the driver.For detailed instructions, see the documentation from the ODBC provider.Configuring a Data Source on UNIXAfter installing Essbase, execute the inst-sql.sh file to enable Essbase SQL Interface. Theodbc.ini file is created in HYPERION HOME/common/ODBC/Merant/x.x/odbc.ini.Configuring a Data Source on Windows7

To configure a data source on UNIX:1 In the UNIX user home directory, ensure the .odbc.ini link is set to HYPERION HOME/common/ODBC/Merant/x.x/odbc.ini. and remove the link if it is to a different location. Correct the link if it is to a different location. Create the link if it is missing.2 Edit the odbc.ini file to add a description of the data source. See “Editing the odbc.ini File for Changes”on page 8 and “Example: Editing odbc.ini for DB2” on page 8.Editing the odbc.ini File for ChangesIf you add data sources or change driver products or data sources you may need to edit theodbc.ini file because the odbc.ini file contains ODBC connection and configurationinformation such as data source name and driver product name. Specific information varies perplatform.Example: Editing odbc.ini for DB2The following example illustrates how you might edit the .odbc.ini file on an Essbase runningon AIX, to connect to a DB2 8.2 database named “tbc data,” using an ODBC data source nameddb2data that invokes the DataDirect 5.2 Wire Protocol driver.Use the vi command to edit the .odbc.ini file and insert the following statements:[ODBC Data Sources]tbcdata DB2 Source Data on AIX.[tbcdata]Driver /ARdb222.soDatabase tbcdataIpAddress isaix7TcpPort 500008Configuring a Data Source

Preparing Multiple-Table DataSources3In This ChapterUnderstanding Multiple-Table Preparation . 9Preparing Data from Multiple Tables .11This chapter describes how to prepare relational data in multiple tables for loading into Essbase.Understanding Multiple-Table PreparationUse this section to understand the two methods available for preparing multi-table data: “Understanding Two Methods for Multiple-Table Data Preparation” on page 9 “Understanding Security Requirements” on page 11 “Choosing a Preparation Method” on page 11Understanding Two Methods for Multiple-Table DataPreparationIf data is in multiple tables, create a single table or a view of the data either by: Creating one table or view in the SQL database before using Essbase SQL Interface:Understanding Multiple-Table Preparation9

See “Choosing a Preparation Method” on page 11 for instructions. Joining the tables by entering a SELECT statement in Administration Services Consoleduring the data load-dimension build task:See “Preparing Data from Multiple Tables” on page 11 for instructions.10Preparing Multiple-Table Data Sources

Choosing a Preparation MethodAn SQL database server reads from a single table and maintains a single view more efficientlythan it processes a multiple-table SELECT statement. Therefore, creating a single table or viewbefore you use Essbase SQL Interface greatly reduces the amount of processing time the SQLserver.Understanding Security RequirementsData load-dimension build preparation requires specific permissions: (For either preparation method) SELECT access privileges to all tables containing therelevant data. (For creating a single table or view in the relational database source) CREATE accessprivileges in the SQL database to create a single table or view. Ask the SQL DatabaseAdministrator for the appropriate CREATE privileges.If you cannot obtain CREATE privileges, use Administration Services to join tables during thedata load.Preparing Data from Multiple Tables To prepare data from multiple tables:1 Obtain the required privileges: SELECT access to tables where relevant data is stored CREATE access to create a single table or view to join multiple-table data before usingEssbase SQL Interface2 Use one of these methods to prepare data from multiple tables: Create a single physical table or virtual table (view) based on data from multiple tables inthe source database. Create a SELECT statement in Administration Services Console that joins the tables:a.Identify the tables and columns (or fields) containing the data that you want to load intoEssbase.b.Select File Open SQL in Administration Services Console to display Open SQL DataSources. See “Opening an SQL Database” in Essbase Administration Services Online Help.c.Write a SELECT statement that joins the tables. See “ Selecting the SQL Data Source” onpage 15 and “Creating SQL Queries (optional)” on page 15.Note:Essbase passes the SELECT statement to the database without verifying the syntax.Preparing Data from Multiple Tables11

12Preparing Multiple-Table Data Sources

Loading SQL Data4In This ChapterUnderstanding Data Loading .13Using Substitution Variables .13Loading Relational Data Using Essbase SQL Interface .14Loading the Data.16This chapter describes how to use Oracle's Essbase Administration Services to load data andbuild dimensions from SQL data sources.Understanding Data LoadingYou can load the data or build outline dimensions after you have configured the data sourceand prepared the multiple-table data for loading. To load the data:1 If you plan to use substitution variables, create them. See “Using Substitution Variables” on page 13 forinstructions.2 Create a rules file. See “Loading Relational Data Using Essbase SQL Interface” on page 14.3 Load data into the Essbase database. See “Loading the Data” on page 16.Using Substitution VariablesSubstitution variables enable you to use the same rules file for different data sources. You canuse substitution variables in SQL strings and data source names. A substitution variable canapply to all applications and databases on Essbase Server, or a substitution variable can be usedfor a particular application or database.You can also define a substitution variable for the data source name (DSN) and specify in therules file the substitution variable name.Understand the rules for substitution variable use in Essbase SQL Interface before you createand use them:Understanding Data Loading13

Understanding the Rules for Substitution VariablesRemember these rules when you create substitution variables: The value of the substitution variable should be valid and appropriate SQL. Essbase doesnot validate the value. Be especially careful when you use quotation marks or single quotes. Different sourcedatabases have different conventions about when to use them. Whether or not you use a substitution variable, be sure that no regular SQL operators inSELECT, FROM, or WHERE clauses start with the ampersand (&), because that is theEssbase identifier for substitution variables.Creating and Using Substitution Variables To create and use a substitution variable, use the following procedure:1 Create the substitution variable using the instructions in “Managing Substitution Variables” in EssbaseAdministration Services Online Help.2 To open the SQL data source when you edit the rules file select File Open SQL from the menu. (See“Creating a Rules File” in Essbase Administration Services Online Help.) Specify substitution variables inthe resulting Open SQL Data Sources dialog box. To specify a substitution variable for the DSN, select Substitution Variables and select thesubstitution variable from the list. To specify substitution variables in the query, use the substitution variable instead of a“field value” string in the Select, From, or Where box. Remember to type the ampersand(&) in front of the substitution variable name.3 Click OK/Retrieve to retrieve the data for the rules file.Note:Be sure to set the desired value for the substitution variables before you use the rules file for adata load or dimension build.Loading Relational Data Using Essbase SQL InterfaceTo load SQL data into the Essbase database, you must create and use a data load rules file.Data load and dimension build rules are a set of operations that Essbase performs on data itloads as values in an Essbase database or builds as members in an Essbase outline. The operationsare stored in a rules file. The rules file can also reject invalid records in the data source.Summary of Steps1. Create a data load rules file.14Loading SQL Data

2. Select your SQL data source. See “ Selecting the SQL Data Source” on page 15 forinstructions.3. If you plan to create SQL queries in Essbase, see “Creating SQL Queries (optional)” on page15.Creating the Data Load Rules File To create a data load rules file, see Creating a Data Load Rules File in Essbase AdministrationServices Online Help.Selecting the SQL Data SourceWhen you create a rules file you must specify a data source. To select a SQL data source:1 In Administration Services Console, open Data Prep Editor or an existing rules file.2 Choose File Open SQL.3 In Select Database, enter the Essbase Server, application, and database. and click OK.4 In Open SQL Data Sources, select the data source or select the substitution variable and enter requiredinformation. (See “Opening an SQL Database” in Essbase Administration Services Online Help for details.)5 Click OK/Retrieve.6 In SQL Connect, enter the user name and password for the source database and click OK.Note the following facts about data source files: The data source file must be configured on the server computer. On UNIX platforms, you have defined the path for the SQL data source file inthe .odbc.ini file. On Windows, if you have not defined the path for the SQL source file in the ODBCAdministrator, you can type the path in the Database text box of the Define SQL dialog box. If you do not define a path, Essbase looks for the data source file in the directory from whichyou are running Essbase Server.Creating SQL Queries (optional)Instead of creating a table or view to select data for the retrieval, you can write a SELECTstatement when you perform the data load.Note:Creating a SELECT statement in Essbase is usually slower than creating a table or view in thesource database.Loading Relational Data Using Essbase SQL Interface15

The SQL Statement text box in the Open SQL Data Sources dialog box has Select, From, andWhere text boxes to help you write SQL queries. You can specify multiple data sources, filterthe display of records, and specify the desired order and grouping of records that will appear inData Prep Editor.Loading the Data To use SQL or relational data with an Essbase database, see Performing a Data Load or DimensionBuild in Essbase Administration Services Online Help.16Loading SQL Data

Using Non-DataDirect ODBCDrivers5In This ChapterConfiguring a Non-DataDirect Driver .17Creating a Configuration File for Non-DataDirect Drivers.18Configuring Non-DataDirect Data Sources .21Support for Non-DataDirect Drivers .21You can use drivers other than the DataDirect drivers provided with Essbase. Some nonDataDirect ODBC drivers are tested with Essbase. For detailed information on tested andsupported drivers and data sources, see the Hyperion Essbase - System 9 Installation Guide. To use a non-DataDirect driver:1 Configure the driver for all drivers and data sources.2 Configure the data source if you are using Teradata.This chapter contains the following topics that describe how to use non-DataDirect ODBCdrivers:Configuring a Non-DataDirect DriverWhen you configure a DataDirect driver, Essbase recognizes the basic configuration informationfor the driver, especially the name of the driver and whether the name and password are casesensitive. However, when you use a non-DataDirect driver, you must give Essbase theconfiguration information for the driver that you use.You give Essbase the configuration information for your non-DataDirect driver by creating adriver configuration file called esssql.cfg, which is a text file. Place this file in yourARBORPATH\BIN directory on Essbase Server. This is c:\Hyperion\AnalyticServices\ ifyou installed Essbase in the default directory (/home/hyperion/analyticservices/ onUNIX platforms).Note:If you do not create a configuration file, Essbase uses the default values. You may not be able toconnect to the SQL database using the default values.Configuring a Non-DataDirect Driver17

Creating a Configuration File for Non-DataDirect DriversFor non-DataDirect drivers, you create a configuration file: ARBORPATH/bin/esssql.cfg. The configuration file must contain at least some of the following configurationvalues for each non-DataDirect ODBC driver that you use. Required values may be different fordifferent drivers. See the online help for each driver or see the documentation for specificinformation.This section contains information about what you need to include in the configuration file, andin one case, how to find it: “Including the Minimum Required Contents” on page 18 “Finding a Driver Name on Windows” on page 20 “Finding a Driver Name on UNIX” on page 20Including the Minimum Required ContentsAt a minimum, the configuration file must contain at least the DriverName for every nonDataDirect ODBC driver that you use. Surround all values for each driver with brackets ( [ ] ).Leave one or more spaces between the configuration keyword and its corresponding value.Use this template to help you create a configuration file:[Description"Description of Driver FilesValueSingleConnectionValueUpperCaseConnection ValueIsQEDriverValue]Description of Driver is your description of the driver. Enclose the description in quotation marks(" "). The default value is " ".18Using Non-DataDirect ODBC Drivers

DriverName is the file name of the non-DataDirect ODBC driver and is required.For all other configuration keywords, set Value to be either 1 or 0, according to the followingtable:Configuration InformationValueDescriptionUserId1User ID required0User ID not required Default: 01Password required0Password not required Default: 01Database name required0Database name not required Default: 01Server name required0Server name not required Default: 01Application name required0Application name not required Default: 01Dictionary name required0Dictionary name not required Default: 01Files name required0Files name not required Default: 01Driver not thread-safe One active connection allowed Default: ingleConnectionRecommendation for DataDirect drivers: 10Driver thread-safe Multiple active connections allowedCaution! Specifying 0 (multiple active connections allowed) for DataDirector non-DataDirect drivers may lead to instabilityUpperCaseConnectionIsQEDriver1Driver not case-sensitive Connection information is converted to uppercase0Driver case-sensitive Connection information is unchanged Default: 01Driver is a DataDirect driver You can specify configuration information forDataDirect drivers (for example, if you have a later version of a DataDirectdriver which Essbase does not yet support)0Driver is a non-DataDirect driver Default: 0The defaults apply if you do not specify the value for the driver. These defaults are different fromthe Essbase default values that apply if you do not create an esssql.cfg file.Include configuration information for all the non-DataDirect drivers in one configuration file.Enclose the values for each driver in brackets ( [ ] ).Creating a Configuration File for Non-DataDirect Drivers19

Use this template to help you create a configuration file:[Description "Description of Driver "DriverNameDriverName.][Description "Description of Driver "DriverNameDriverName.]Finding a Driver Name on Windows To display the name of the drive, use this procedure:1 Start the ODBC Administrator using any method from step 1 in “Configuring a Data Source on Windows” onpage 7.The ODBC Data Source Administrator dialog box appears.Data sources that you have configured are in the User Data Sources list box. Drivers that havenot been properly configured may also appear in the User Data Sources list box. Ignore them.2 Select the Drivers tab.3 Obtain the file name of the driver by scrolling to the right. For example, the file name for the Microsoft AccessDriver is ODBCJT32.DLL.4 Select the driver, and click OK.Finding a Driver Name on UNIXOn UNIX, the driver name is in the .odbc.ini file. For more information onthe .odbc.ini file, see “Configuring a Data Source on UNIX” on page 7.20Using Non-DataDirect ODBC Drivers

Configuring Non-DataDirect Data SourcesThe procedure for configuring non-DataDirect data sources is similar to that for configuringDataDirect data sources. See Chapter 2, “Configuring a Data Source.” For specific instructionson configuring a non-DataDirect data source, please reference your ODBC documentation.Note:Before you configure a non-DataDirect data source, you mu

Preparing for Using SQL or Relational Data Sources Essbase SQL Interface is installed during the Essbase Server installation. See the Hyperion Essbase - System 9 Installation Guide for information about initial configuration tasks. To prepare for using SQL or relational data sources: 1 Configure the ODBC driver and point it to its data source. See Chapter 2, "Configuring a Data Source."