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 |
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.
After you have configured the data source and prepared the multiple-table data for loading, load the data using this process:
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:
Remember these rules when you create substitution variables:
To create and use a substitution variable, use this procedure:
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.
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.
To create a data load rules file, follow these steps:
To select the data source, budget.dbf in the sample screens, use this procedure:
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:
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.
Leave the Where text box empty to load all the rows from the table.
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:
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:
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,
budget.dbf
MONTH = "Jan"
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:
TABLE1.DBF,TABLE2.DBF
TABLE1.Key_No = TABLE2.Key_Code
This SQL statement will query all columns from the relational source, and will sort the results in month order:
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:
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:
Version |
Prodno |
State |
Account |
Month |
Measure |
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.
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.
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:
To change the field names to match the dimension names in the Essbase outline (Sample Basic in the sample screens), use this procedure:
The Field Properties dialog box is displayed:
The data load properties for Field Number 1 are displayed.
The data load properties for Field Number 2 are displayed.
The data load properties for Field Number 3 are displayed.
The data load properties for Field Number 4 are displayed.
The data load properties for Field Number 5 are displayed.
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.
The Data Prep Editor is displayed again with the fields renamed. Notice that the last column has a temporary name of field 6:
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:
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.
To associate the rules file with the Sample Basic outline, use this procedure:
The Associate Server Outline Object dialog box is displayed:
To validate the rules file against the Sample Basic outline:
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.
Use this procedure to save the rules file:
The Save Server Object dialog box is displayed:
This gives the rules file the name BUDGET.RUL.
You can now use the BUDGET rules file to load the data from the dBASE file into the Sample Basic database.
To load the data into Sample Basic:
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:
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 |