| 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 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.
{quote}
(!) Nota
For information on the availability of JDBC or ODBC drivers for your database system, please contact your supplier.
(i) See tutorial [JDBC Drivers | O3PS:Drivers JDBC más utilizados]
{quote}
h2. 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.
h3. 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.
h3. 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.
h4. To add a connection to a database:
## Select the Sources branch in the Design Tree.
## Select the SQL Queries branch.
## 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.
## 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.
h4. The SQL Connection Properties Pane
The SQL Connection properties pane includes two tabs:
* Description
* General
h5. The Description Tab
|| Property || Description ||
| Name | Enter the name of the connection. |
| Description | Enter a description of the connection. |
h5. 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 |
...
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:
- Select the Sources branch in the Design Tree.
- Select the SQL Queries branch.
- 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.
- To add a new connection at the end of the list choose the Add Connection command
- 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. |
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. |
...
|
...
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.
- To add a guard at the end of the list, choose the Add Guard command.
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). |
Code | Enter the SQL command for retrieving the information from the database. |
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. |
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.
- To add a new query at the end of the list, choose the Add Query command.
- 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. |
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. |
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. |
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 |
...
| Wiki Markup |
|---|
{scrollbar} |
| Child pages (Children Display) |
|---|