Hyperion(r) SQL Interface Guide Information Map
Show | Previous | Next
Hyperion(r) SQL Interface Guide Information Map
Hide | Previous | Next
Hyperion(r) SQL Interface Guide Information Map
Show | Previous | Next  
Hyperion(r) SQL Interface Guide Information Map
Hide | Previous | Next  



Loading SQL Data

This chapter describes how to load SQL data into OLAP Server using Application Manager:

Examples in this section are based on samples provided with your installation:

For more information about the sample applications, see Sample Applications.

Understanding Data Loading

After you have configured the data source and prepared the multiple-table data for loading, load the data using this process:

  1. If you plan to use substitution variables, create them. See Using Substitution Variables for instructions.
  2. Create a rules file. See Creating a Data Load Rules File.
  3. Load data into the Essbase database. See Loading the Data.

Using Substitution Variables

Similar to report scripts and calculation scripts, you can use substitution variables in SQL strings. You can use substitution variables that apply across the entire OLAP Server, or specify them for a particular application or database.

Understand the rules for substitution variable use in SQL Interface before you create and use them:

Understanding the Rules for Substitution Variables

Remember these rules when you create substitution variables:

Creating Substitution Variables

To create and use a substitution variable, use this procedure:

  1. Create the substitution variable using the instructions in the Essbase Database Administrator's Guide section Creating Applications and Databases.
  2. Open the rules file you plan to use, then select File > Open SQL from the menu. The Define SQL dialog box appears.
  3. In the Define SQL dialog box, 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.
  4. Click OK/Retrieve to retrieve the data using the rules file.

For example, if you want to create a substitution variable to change the value of MONTH or STATE without having to create a new data rules file for each change, you could create substitution variables this_month and State:

Then, in the Define SQL dialog box, you can specify the substitution variable:

When you click OK/Retrieve, the data load rules file is created, and you can save it. When you need to change the value for state or month, simply change the value of the substitution variables and open the same data load rules file.

Creating a Data Load Rules File

To load SQL data into the Essbase database, you must create and use a data load rules file.

Data load rules are a set of operations that Essbase performs on data when it loads it into an Essbase database. The operations are stored in a rules file that tells Essbase how to load the SQL data and map it to your database outline. The rules file can also reject invalid records in the data source.

You can reuse a rules file with any data source that needs the same set of data load rules.

Summary of Steps to Create a Data Load Rules File

To create a data load rules file, follow these steps:

  1. Open the Data Prep Editor. See Step 1: Opening the Data Prep Editor for instructions.
  2. Select the SQL data source, budget.dbf in the example. See Step 2: Selecting the SQL Data Source for instructions. If you plan to create SQL queries in Essbase, see Step 2a: Creating SQL Queries (optional).
  3. Define the data load rules in a rules file. See Step 3: Defining the Data Load Rules for instructions.
  4. Associate the data load rules file with the database outline, Sample Basic in the example. See Step 4: Associating the Rules File with an Outline for instructions.
  5. Validate the data load rules file. See Step 5: Validating the Rules File for instructions.
  6. Save the data load rules file. See Step 6: Saving the Rules File for instructions.

Step 1: Opening the Data Prep Editor

To open the Data Prep Editor:

  1. Start Application Manager and connect to your server. The Application Manager Desktop window is displayed:
  2. From the Applications list box, select Sample.
  3. From the Databases list box, select Basic.
  4. Click the Data Load Rules button, . The existing data load rules are listed in the Desktop Window, and the New button becomes active.
  5. Click New to display the Data Prep Editor:

Step 2: Selecting the SQL Data Source

To select the data source, budget.dbf in the sample screens, use this procedure:

  1. Choose File > Open SQL:
  2. Note: This is the point where SQL Interface is first invoked.

    The Select Server, Application and Database dialog box is displayed. The Sample Basic database is selected:

  3. Click OK to display the Define SQL dialog box:
  4. In the SQL Data Sources list box, dBASE Files is selected. This is the SQL source that you configured in Configuring a Data Source.

    The wildcard character (*) appears in the Select text box. This loads all the fields from the dBASE file.

  5. Type the name of the data source file in the From text box (budget.dbf in this example). You defined the path for this file in Configuring a Data Source, so you do not need to define it here.
  6. Leave the Where text box empty to load all the rows from the table.

  7. Click OK/Retrieve.
  8. Essbase opens the data source file, budget.dbf, in the Data Prep Editor. See Step 3: Defining the Data Load Rules for more information.

    Note these facts about data source files:

Step 2a: Creating SQL Queries (optional)

If you have not created a table or view to retrieve only the information you need from your data source, you can write a SELECT statement to achieve this goal.

Note: Creating a SELECT statement in Application Manager is usually slower than creating a table or view in the source database.

The Define SQL dialog box has Select, From, and Where text boxes to help you write SQL queries. You can specify multiple data sources, filter the display of records, and specify the desired order and grouping of records that will appear in the Data Prep Editor.

The rest of this section contains examples of queries used for data preparation. You can review these examples for help with your site or skip to the next step:

Example: Selecting Fields and Records from a Table

The example in Step 2: Selecting the SQL Data Source uses the wildcard character (*) in the Select text box to load data from all the fields (columns) in the dBASE file. Alternatively, you can type the names of all the fields:

VERSION,PRODNO,STATE,ACCOUNT,MONTH,MEASURE 

Or, you can type only the names of the fields that you want to load, for example:

PRODNO,STATE,MONTH 

The example in Step 2: Selecting the SQL Data Source also uses the name of a single dBASE file (BUDGET.DBF) in the From text box, and leaves the Where text box empty.

You can also use the Where text box to choose a subset of records (rows) to load from the SQL source files.

To load only records (rows) for January from budget.dbf into the Sample Basic database,

  1. Type the following text in the From text box:
  2. budget.dbf 
    

  3. Type the following text in the Where text box:
  4. MONTH = "Jan" 
    

  5. Click OK/Retrieve.

Example: Querying Data from Two Tables

If you are loading data from two relational tables, you can join the tables by specifying both of the table names, separated by a comma (,) in the From text box, and specifying the common (key) fields in the Where text box, separated by an equal sign (=).

To load data from two tables, table1.dbf and table2.dbf, where the Key_No field of table1.dbf contains the same data as the Key_Code field of table2.dbf:

  1. Type the following text in the From text box:
  2. TABLE1.DBF,TABLE2.DBF 
    

  3. Type the following text in the Where text box:
  4. TABLE1.Key_No = TABLE2.Key_Code 
    

  5. Click OK/Retrieve.

Example: Order By with No Where Clause

This SQL statement will query all columns from the relational source, and will sort the results in month order:

Example: Order By with a Where Clause

This SQL statement will return rows from the relational source where the State column is equal to New York, California, or Nevada. It will sort the results in month order:

Example: Group By with No Where Clause (Incorrect)

This SQL statement will return one row per Prodno, State, Account, and Month. It will summarize the metric contained in the Measure column, producing incorrect results:

Now assume that budget.dbf looks like this table:

Table 1: Abbreviated Data Source Example

Version
Prodno
State
Account
Month
Measure

Budget

100-10

New York

Sales

Jan

500

Budget

100-10

New York

Sales

Jan

200

Actual

100-10

New York

Sales

Jan

100



The SQL statement returns these results:

100-10, New York, Sales, Jan, 800 

Budget and Actual data have been added together, which is not the intended result. See the next example for an approach that produces correct results.

Example: Group By with a Where Clause (Correct)

This SQL statement will select only records that have a Version of Budget and a State equal to New York, California, or Nevada:

This SQL Statement will return one row per Prodno, State, Account, and Month. It will summarize the metric contained in the measure column.

Assume that budget.dbf looks like Table 1. The SQL statement returns these results:

100-10, New York, Sales, Jan, 700 

See the MERANT DataDirect Connect ODBC Reference for more information about SQL statements for your specific driver.

Step 3: Defining the Data Load Rules

When you click OK/Retrieve in the Define SQL dialog box, Essbase displays the dBASE fields in the Data Prep Editor. Notice that the field names from the data source differ from the Sample Basic dimension names:

Edit the field names and properties to match:

Changing Field Names

To change the field names to match the dimension names in the Essbase outline (Sample Basic in the sample screens), use this procedure:

  1. After completing Step 3: Defining the Data Load Rules, select the first cell in the first field (VERSION) and choose Field > Properties from the Application Manager menu:
  2. The Field Properties dialog box is displayed:

  3. Select the Data Load Properties tab.
  4. The data load properties for Field Number 1 are displayed.

  5. In the Field Name text box, highlight VERSION and type the dimension name Scenario over it, and click Next.
  6. The data load properties for Field Number 2 are displayed.

  7. Type Product over the existing name (PRODNO) and click Next.
  8. The data load properties for Field Number 3 are displayed.

  9. Type Market over the existing name (STATE) and click Next.
  10. The data load properties for Field Number 4 are displayed.

  11. Type Measures over the existing name (ACCOUNT) and click Next.
  12. The data load properties for Field Number 5 are displayed.

  13. Type Year over the existing name (MONTH) and click Next.
  14. The data load properties for Field Number 6 are displayed. This is the last field in budget.dbf. It contains data values. Because you have fully specified each dimension, you do not match this field to a dimension name.

  15. Delete the existing name (MEASURE) in the Field Name text box and leave the box blank.
  16. Click OK to save all your changes.
  17. The Data Prep Editor is displayed again with the fields renamed. Notice that the last column has a temporary name of field 6:

Changing Field Properties

The fields in the data source file may contain unnecessary leading and trailing white spaces.

To ensure that Essbase deletes these white spaces in each of the fields when it loads the data, use this procedure:

  1. After completing Step 3: Defining the Data Load Rules, select the first cell in the first field (Scenario) and choose Field > Properties from the Application Manager menu:
  2. Click the Global Properties tab:
  3. Select the Drop leading/trailing whitespace check box if it is not already checked.
  4. Click Next, and select Drop leading/trailing whitespace for all fields.
  5. When you reach Field 6, select the Data Field check box as well as Drop leading/trailing whitespace, to tell Essbase that this field contains data values:
  6. Click OK to close the Field Properties dialog box.

You have defined the data load rules for the dBASE file. For more information on creating data load rules files, see the Essbase Database Administrator's Guide.

Step 4: Associating the Rules File with an Outline

To associate the rules file with the Sample Basic outline, use this procedure:

  1. Choose Options > Associate Outline:
  2. The Associate Server Outline Object dialog box is displayed:

  3. From the Objects list box, select BASIC and click OK.

Step 5: Validating the Rules File

To validate the rules file against the Sample Basic outline:

  1. Choose Options > Validate:
  2. If Essbase can validate the rules file, it displays the Verify message box:

    If there are validation problems, Essbase displays the problems in a Validate Rules dialog box. If a field has validation problems, ensure that the field name is correct. For more information on validating rules files, see the Essbase Database Administrator's Guide.

  3. Click OK to close this message box.

Step 6: Saving the Rules File

Use this procedure to save the rules file:

  1. Choose File > Save:
  2. The Save Server Object dialog box is displayed:

  3. In the Object Name text box, type BUDGET.
  4. This gives the rules file the name BUDGET.RUL.

  5. Click OK.
  6. Choose File > Close to close the Data Prep Editor.

You can now use the BUDGET rules file to load the data from the dBASE file into the Sample Basic database.

Loading the Data

To load the data into Sample Basic:

  1. In the Application Desktop Window, select the Sample application and the Basic database.
  2. Choose Database > Load Data to display the Data Load dialog box:
  3. In the Type group of the Data Load dialog box, select the SQL option.
  4. Select the Use Rules check box.
  5. Click Find to display the Open Server Rules Object dialog box:
  6. Ensure that the Sample application and Basic database are selected.
  7. Choose the BUDGET rules file from the Objects list box.
  8. Click OK to display the Data Load dialog box again.
  9. In the Options group, select the Load Data and Abort On Error during dataload check boxes. Ensure that all other options in the group are not selected:
  10. Click OK to load the data.
  11. You may need to type an SQL user name and SQL password when you connect to some SQL databases (for example, when you connect to an Oracle database). You do not need to enter a user name and password when you connect to a dBASE database.

    When Essbase has loaded the data, a message box tells you that the load was successful:

  12. Click OK to close the message box.

Essbase loads the SQL data into the Sample Basic database. The dBASE file contains budget data for the West and the East regions. Essbase loads the data into member combinations for these regions. If you have problems loading the data, see the Essbase Database Administrator's Guide for information on data loading or troubleshooting.




Copyright © 2002 Hyperion Solutions Corporation. All rights reserved.
http://www.hyperion.com