Versions Compared

Old Version 1

changes.mady.by.user Nicolás Palombo (Unlicensed)

Saved on

compared with

New Version Current

changes.mady.by.user Nicolás Palombo (Unlicensed)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin
Wiki Markup
{scrollbar}

...

O3 Designer can connect to different types of databases using the appropriate drivers. It can also handle multiple queries to the databases it connects to.

These data sources are defined as the declaration of SQL sentences executed excecuted on the databases where the information is located, while the cube is being createdupdated.

Therefore, access to the data through JDBC/ODBC drivers is vital for extracting the data through this source type. with this kind of sources.

For performance reasons, JDBC access drivers are preferable to ODBC drivers. Notes

...

(warning) Nota
For information on the availability of JDBC or ODBC drivers for your database system, please contact your supplier.

...

(info) See tutorial JDBC Drivers

SQL Data Sources

They are the SQL sentences of the databases where the information we need to build the cube is located.
Anchor_Toc94582473_Toc94582473Guards 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 To extract data from a database, the model needs to define the connection to the database and the set of queries that retrieve de data.

Additionally, an optional set of guards can be defined to control whether the cube is built or not. Guards can be used to verify that there is relevant information in the database.

Guards consist of a SQL query with a boolean condition. The condition is based on a returned record count. If When the condition is not met, the cube met the other data sources in the connection are excecuted, otherwise the build process is interrupted.
anchor_Toc94582474_Toc94582474

Defining SQL Data Sources

Defining a SQL Data Source is a twothree-step process, being the second step optional:

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

This allows you to generate several SQL Data Sources (Queries) that share the same connection to the database. Anchor_Toc94582475_Toc94582475

1. Adding a Connection

...

for SQL Data

...

Sources

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.
Anchor_Toc94582476_Toc94582476

To add a connection to a

...

database:

    1. Select the Sources branch in the Design
    tree
    1. Tree.
    2. Select the SQL Queries branch.
    3. Add a new connection to the database:
      • To add a new connection
    after the existing connections,
      • at the end of the list choose the Add Connection command
    .

      • The connection is added and the Properties
    pane
      • Pane for the connection
    displays
      • is displayed.

...

      • To insert a new connection right after an existing connection, select

...

      • it and choose the Insert Connection command.
        The connection is added and the Properties

...

      • Pane for the connection

...

      • is displayed.
    2. Complete the Description tab and the General tab on the Properties pane.

...

...

After completing the connection definition, you can define the queries. You can define one or more queries for each connection. Anchor_Toc94582477_Toc94582477Defining 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.
Anchor_Toc94582478_Toc94582478To 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.

...

  • 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.

...

  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.

...

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

...

  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 Properties Pane

The SQL Connection property properties pane includes twos two tabs:

  • Description
  • General

...

The Description Tab

Property

Description

Name

Enter the name of the connection.

Description

Enter a description of the connection.

...

...

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. Model Properties

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. O3 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.

...

  • 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.

Image Removed
A Sample of the General Tab in the Connection Properties pane
Anchor_Toc94582486_Toc94582486Image Added

Installing a JDBC Driver
  • Copy the .jar containing the Java classes in the classes/jdbc directory under the O3 installation path.
  • Restart O3 Designer to read the new classes

(info) See the tutorial JDBC Drivers

...

2. 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. Add a new Guard
    • To add a guard at the end of the list, choose the Add Guard command.
      The guard is added and the Properties pane for the guard displays.
    • To add a guard right after an existing guard, choose the Insert Guard command.
      The guard is added and the Properties pane for the guard displays.

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

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.

Image Added

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 Defining Input Sets

...

3. 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. Add a new query to the connection:
    • To add a new query at the end of the list, choose the Add Query command.
      The query is added and the Properties pane is displayed.
    • To insert a new query right after an existing one, select it and choose the Insert Query command.
      The query is added and the Properties pane is displayed.
  5. Complete the Description, Code and Input Sets tabs of the Properties pane.

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 that the query supplies for the model.

  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.

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.

...

...

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.

Image Removed
A Sample of the Code Tab in the SQL Query Properties pane
Anchor_Toc94582489_Toc94582489Image Added

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 Model Properties.

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.

Image Removed
A Sample of the Field Tab in the SQL Query Properties pane
Anchor_Toc94582490_Toc94582490Image Added

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 Defining Input Sets.

...

  • Description
  • Code
  • Input Sets

...

Property

Description

Name

Enter the name of the guard.

Description

Enter a description of the guard.

...

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.

...

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.

...

...

Wiki Markup
{scrollbar}
Child pages (Children Display)