Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

These data sources are defined as the declaration of SQL sentences executed on the databases where the information is located, while the cube is being created. Therefore, access to the data through JDBC/ODBC drivers is vital for extracting the data through this source type. For performance reasons, JDBC access drivers are preferable to ODBC drivers.
Notes

  • For information on the availability of JDBC or ODBC drivers for your database system, please contact your supplier.

Querys for SQL Data Sources
They are the SQL sentences of the databases where the information we need to build the cube is located.
Guards for SQL Data Sources
To ensure that information is available at the SQL data source when you build a cube, you can define a guard for the source. The guard definition is a SQL query with a condition. The condition is based on a returned record count. If the condition is not met, the cube build process is interrupted.
Defining SQL Data Sources
Defining a SQL Data Source is a two-step process:

  1. Add and define the connection to the database.
  2. Specify the query to extract data from the database in SQL language.

This allows you to generate several SQL Data Sources (Queries) that share the same connection to the database.
Adding a Connection to a SQL Data Source
Use the Add Connection command or the Insert Connection command to add a new connection to a SQL data source through JDBC/ODBC.
You must be on the SQL Queries branch within Sources, or on any of the SQL Queries already defined.
To add a connection to a SQL data source:

  1. Select the Sources branch in the Design tree.
  2. Select the SQL Queries branch.
  3. To add a connection after the existing connections, choose the Add Connection command. The connection is added and the Properties pane for the connection displays.

OR
To insert a connection after an existing connection, select the connection and choose the Insert Connection command. The connection is added and the Properties pane for the connection displays.

  1. Complete the Description tab and the General tab on the Properties pane.


Notes

  • Changes made in the Property pane apply when you leave the pane or perform another operation in O3 Designer.
  • After completing the connection definition, you can define the queries. You can define one or more queries for each connection.

Defining SQL Queries
Use the Add Query command or the Insert Query command to define a SQL query for a connection.
To define a query, you must be on the Connection branch within SQL Queries/Sources, or on an already-defined query.
To define a SQL query:

  1. Select the Sources branch in the Design tree.
  2. Select the SQL Queries branch.
  3. Select the connection branch.
  4. To add a SQL query after the existing SQL queries, choose the Add Query command. The query is added and the Properties pane for the query displays.

OR
To insert a SQL query after an existing query, select the query and choose the Insert Query command. The query is added and the Properties pane for the query displays.

  1. Complete the Description, Code and Input Sets tabs of the Properties pane.


Notes

  • Changes made in the Property pane apply when you leave the pane or perform another operation in O3 Designer.
  • After you define the SQL query, you must add fields to the definition.

Adding Fields to a SQL Query
After you have added a SQL query to a connection, you can define the fields the query supplies for the model.
To add fields to a SQL query:

  1. Select the Sources branch in the Design tree.
  2. Select the SQL Queries branch.
  3. Select the connection branch.
  4. Select the SQL Query branch.
  5. Choose the Fields tab of the Properties pane.
  6. To autofill the fields, choose the Autofill button. The fields populate and you can edit them if you wish.
  7. To add a field, choose the New Field button. A new row adds to the table.
  8. Complete the Name, Field Number and Type fields.
  9. To continue adding fields, repeat steps 7 and 8.


Notes

  • Changes made in the Property pane apply when you leave the pane or perform another operation in O3 Designer.

Defining Guards for SQL Data Sources
Use the Add Guard command or the Insert Guard command to define a guard for a SQL data source.
To define a Guard

  1. Select the Sources branch in the Design tree.
  2. Select the SQL Queries branch.
  3. Select the connection branch.
  4. To add a guard after the existing guards, choose the Add Guard command. The guard is added and the Properties pane for the guard displays.

OR
To insert a guard after an existing guard, select the guard and choose the Insert Guard command. The guard is added and the Properties pane for the guard displays.

  1. Complete the Description, Code and Input Sets tabs of the Properties pane.

Notes

  • Changes made in the Property pane apply when you leave the pane or perform another operation in O3 Designer.

The SQL Connection Property Pane
The SQL Connection property pane includes twos tabs:

  • Description
  • General

The Description Tab

Property

Description

Name

Enter the name of the connection.

Description

Enter a description of the connection.



A Sample of the Description Tab in the Connection Properties pane
The General Tab

Property

Description

Verify Before Construction

Turn this checkbox on to verify a connection to the database before proceeding with the cube build. If verification fails, the build process is interrupted with the following build completion status: UNAVAILABLE SOURCE: <source name>.You can define build actions for this status. See the Building Action Tab.

Login and Password

You must specify the user extracting the data through the connection with the database. The user must have appropriate permissions on the database.

Driver

Enter the name of the driver being used. If you are using a JDBC communication protocol, enter the full name of the driver class. Refer to the driver documentation for further information. If you are using an ODBC connection, use the default value in the field (sun.jdbc.odbc.JdbcOdbcDriver). This corresponds to the JDBC/ODBC bridge.

Protocol

Select the connection protocol for the database. When using JDBC connections, the JDBC driver provider must give the protocol name. For ODBC connections, choose the odbc option.

Parameters

For JDBC connections, enter any additional parameters the driver requires. Refer to the driver documentation for more information. These parameters represent the third part of the driver's URL, defined by the driver provider. If you use ODBC, specify the name of the ODBC data source defined in the system. (Windows 95/98/NT: Control Panel, 32bit ODBC).

Test

When you complete the connection properties fields, use the Test button to test the connection. Designer attempts to open a connection with the specified database and then indicates if the operation was successful or not.

Data

Use the Data button to test the access to the tables and fields available in the database for creation of queries dependent on this connection. This option may not be supported with some ODBC drivers.


To use a manufacturer-provided JDBC driver, you must make it visible to O3. There are two ways to do this:

  • Copy the complete and decompressed directory structures from the Java classes of the driver to the classes directory under the O3 installation directory.
  • Copy the .jar containing the Java classes under the classes directory under the O3 installation directory. Modify the CLASSPATH in the Designer.lax and Builder.lax files in the installation directory to include this .jar.



A Sample of the General Tab in the Connection Properties pane
The SQL Query Property Pane
The property pane for SQL queries includes four tabs:

  • Description
  • Code
  • Fields
  • Input Sets

The Description Tab

Property

Description

Name

Enter the name of the query.

Description

Enter a description of the query.



A Sample of the Description Tab in the SQL Query Properties pane
The Code Tab

Property

Description

Code

Enter the SQL command for retrieving the information from the database.
Use the Clear button to remove all code from the code text area.

Assist

Choose the Assist button to access the Assistant. You can use the Assistant to help create the SQL statements, and select tables and fields to include in it. The Assistant displays the database accessed in the connection in tree form. Use the Add button to include tables or fields. Note: to avoid duplicate field names in tables in the query you can include the table name as a prefix to field names. Some ODBC drivers may not support the Assistant.



A Sample of the Code Tab in the SQL Query Properties pane
The Fields Tabs

Property

Description

Dimension

Turn this checkbox on to indicate that the Data Source contains information for use in creating Dimensions (or Building Metada phase). The cube creation module, O3 Builder, uses this information to decide whether the source is relevant for Dimension creation or if it is only a Measures source.

Verify Before Construction

Turn this checkbox on to verify the existence of the data source at the specified connection before proceeding with the cube build. If verification fails, the building process is interrupted with the following build completion status: UNAVAILABLE SOURCE: <source name>You can define a build action for this status. See the Building Action Tab.

Fields

Use the New Field button to add a field, indicating the result set columns from the query; that is, the record fields to use later on in the definition of Dimensions and Measures. For each field, declare the field name, the column number within the result set and the field's data type. When choosing a field name, bear in mind that all references to the field made in the definition of the model take place through the name you declare here. Data types are: String, Integer, Double, and Date.
You can also delete a field by selecting it and choosing the Delete button.

Autofill

Use the Autofill option to automatically fill in the fields with their corresponding column numbers. The field names are the same as the specified in the result set. The data types are also obtained from the result set information Once you autofill the fields, you can further customize the Data Source configuration by changing names, and revising data types.

Preview

Use the Preview button to preview the contents of the query result set. This does not display all records in it.

Local Table

If you turn on the Local Table check box, the property pane updates to include the Key and Include Field columns for each field. You can enter the table name. For each field it should be indicated if it's part of the table and if it's part of its key.
See About Local Tables.

Tuples by record

It indicates that a number of "n" tuples must be generated in the cube from each data source record. The default value is 1. See About Tuples by Record.



A Sample of the Field Tab in the SQL Query Properties pane
The Input Sets Tab

Property

Description

Input Set

A list of input sets displays. Turn on the check box for each input set to include the data source in. See About Input Sets.



A Sample of the Input Sets Tab in the SQL Query Properties pane
The Guard Property Pane
The property pane for a guard includes three tabs:

  • Description
  • Code
  • Input Sets

The Description Tab

Property

Description

Name

Enter the name of the guard.

Description

Enter a description of the guard.



A Sample of the Description Tab in the Guard Properties pane
The Code Tab

Property

Description

Build If Number of Records

Define the condition to verify for the query. Choose from > (greater than), < (less than) and = (equal to).
Define the quantity of records required to meet the condition.
Each guard is verified before the build process begins. If the condition of the guard is not met, the building process is interrupted with the following build completion status: INVALID GUARD: <guard name>
You can define a build action for this status.

Code

Enter the SQL command for retrieving the information from the database.
Use the Clear button to remove all code from the code text box.

Preview

Use the Preview button to preview the contents of the query result set. This does not display all records in it.

Assist

Choose the Assist button to access the Assistant. You can use the Assistant to help create the SQL statements, and select tables and fields to include in it. The Assistant displays the database accessed in the connection in tree form. Use the Add button to include tables or fields. Note: to avoid duplicate field names in tables in the query you can include the table name as a prefix to field names. Some ODBC drivers may not support the Assistant.



A Sample of the Code Tab in the Guard pane
The Input Sets Tab

Property

Description

Input Set

A list of input sets displays. Turn on the check box for each input set to include the data source in. See About Input Sets.



A Sample of the Input Sets Tab in the Guard pane

  • No labels