SQL Data Sources

Unknown macro: {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 excecuted on the databases where the information is located, while the cube is being updated.

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

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

Nota
For information on the availability of JDBC or ODBC drivers for your database system, please contact your supplier.
See tutorial JDBC Drivers

SQL Data Sources

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. When the condition is met the other data sources in the connection are excecuted, otherwise the build process is interrupted.

Defining SQL Data Sources

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

Add and define the connection to the database.
Add and define guards.
Specify the queries to extract data from the database using SQL.

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

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.

To add a connection to a database:

1. Select the Sources branch in the Design Tree.
2. Select the SQL Queries branch.
3. Add a new connection to the database:
  - To add a new connection at the end of the list choose the Add Connection command
    The connection is added and the Properties Pane for the connection 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.
4. 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.

The SQL Connection Properties Pane

The SQL Connection properties pane includes 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 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.

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

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

Select the Sources branch in the Design Tree.
Select the SQL Queries branch.
Select the connection branch.
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.

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:

Select the Sources branch in the Design Tree.
Select the SQL Queries branch.
Select the connection branch.
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.
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.

Select the Sources branch in the Design Tree.
Select the SQL Queries branch.
Select the connection branch.
Select the SQL Query branch.
Choose the Fields tab of the Properties pane.
To autofill the fields, choose the Autofill button. The fields populate and you can edit them if you wish.
To add a field, choose the New Field button. A new row adds to the table.
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.

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

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

Unknown macro: {scrollbar}