A JDBC Universe allows data to be read from a relational database using a JDBC driver. If your
data is defined in a relational database, and a JDBC driver is available, then JDBC Universe is
recommended. Before getting started, add the JDBC driver into /opt/elixir/lib/, to make it available to
all Java Virtual Machines.
Right-click the Tables node, and select one of the following options:
Configure connection pool...: JDBC allows access to data
through SQL statements. To configure the connection pool, click the 
icon.
For example, you can install MySQL Workbench and use com.mysql.jdbc.Driver
for the JDBC Universe. Modify the host and dbname in the URL field,
enter the user name and password, and click Test Connection.
If the connection test succeeds, you have the option to define the Connection Pool Parameters on the next page. On the Connection Pool JDBC Properties page, you can set any customized properties for your JDBC driver by setting the keys and values. After you complete the settings, click Finish.
Add tables...: Click the Add Tables 
icon.
Tables from the relational datasource
will display in the Add Tables window. You can choose from these tables, or invert your
selection. Click OK.
Add join table: Click the Add join table 
icon. Join SQL combines records from two
or more tables from the relational database. Join SQL combines columns from two tables by using
values common to each. There are often two columns with the same name, and the Universe
will add the table name as a prefix to ensure the columns are unique. Click Finish,
and the corresponding SQL will be calculated and executed to determine the schema.
Add custom table: Click the Add custom table 
icon. Enter your SQL expression into the
Custom SQL field. If you have entered some expression in the SQL panel, it will be the default
content when you create a new custom table. Dynamic parameters are supported, and you can
use ${substitutions}. Click Finish and the schema will be automatically
inferred. If parameters are involved, you will be prompted to enter a parameter value.
Add folder...: This option helps you categorize tables, which is extremely useful when you have a large number of tables to work on. Fill in the folder name and click OK.
Edit: This option is available for each table under the
Tables tree. Right-click a table, and select this option. The
Edit Table window opens. Select from the Generated SQL list to show the entire table, or
hide certain columns. You can create several Universe tables that are backed by the same physical
table, but expose different columns. This is much more efficient than fetching all columns and
filtering locally.
Show SQL: This option is available for each table under the Tables tree. Right-click a table, and select this option. It will display the SQL that will be executed for the table. Click the Load Data blue button to load the table contents.
Paste: This option enables you to copy tables from another JDBC Universe and paste here.
A Repository Universe is similar to a small repository, which may include hundreds of tables. Each table maps to a datasource, which is a full copy of the original datasource. If you change either the table or the mapping datasource, the other one remains unchanged.
A Repository Universe supports all kinds of datasources except Composite, which is replaced by transforms.
Right-click the Tables node, and select one of the following options:
Add table...: This option enables you to create a new table. Choose a datasource type and fill in datasource parameters. Click Finish.
Add folder...: This option helps you categorize tables. Fill in the folder name and click OK.
Import datasource...: This option enables you to import from an existing datasource. Choose from the repository and click OK.
Paste: This option enables you to copy tables from another Repository Universe and paste here.
The Settings tab displays table settings, including schema, parameters, table attributes and column attributes. The Settings tab also enables you to add transforms for data manipulation.
The Schema tab displays column names and data types. The PrimaryKey column is highlighted with a key icon. If a primary key is not defined, you can add one using the column attributes.
The following types of attributes are supported:
Enumeration: By defining the attributes of individual fields, Repertoire Data Designer can then validate the record values using the cleansing process. For example, we might specify that the field "Gender" is a nominal enumeration of "M" and "F". The cleansing process would then warn us of any records containing "m" or "Female". Ordinal enumeration attributes allow us to specify the order in which to display data, while retaining the original order. For example, we might specify that the field "Fruit" is an ordinal enumeration of "Apple" and "Orange". Only "Apple" and "Orange" records can pass the validation in cleansing process.
ForeignKey: This type of attributes is created by JDBC DataSources, and therefore cannot be edited.
Format: Define the format.
Nullable: Specify whether the data can be Null values.
PrimaryKey: A PrimaryKey is necessary for all tables in the Repository Universe. Ensure that each table has a unique PrimaryKey column. This is important for Ad Hoc Dashboard. For most cases, a table has only one PrimaryKey, therefore the sequence number is 0. If you are working on a datasource without an obvious PrimaryKey - for example -
FruitSales.ds, add a Sequence Transform to create a new ID column, and add a Column Attribute to mark it as a PrimaryKey. SinceFruitSales.dsuses a composite PrimaryKey, an alternative approach is to add a Column Attribute to mark “Company” as “PrimaryKey:0”, and add another Column Attribute to mark “Fruit” as “PrimaryKey:1”.Range: Specify the start value and end value.
Comments: Enter text for comments.
RegExp: Define and test syntax.
The Transforms tab enables you to create and edit
transforms to a schema. Click the plus sign, and the Transform Wizard will be invoked. For detailed
information on transform categories, refer to the Elixir Transform User Manual.
This restricts access to rows where a specified Credentials Field matches the user credentials. You have the option to specify users or groups who can access all records.
User credentials refer to the user name and all group names to which the user belongs. In credentials, users and groups are not distinguished. A blank string matches no credential, while "*" matches any credential.
The following table shows an example of the input (Credentials Field: Field 3):
| Field 1 | Field 2 | Field 3 |
|---|---|---|
| 87 | 1348.50 | Sales |
| 75 | 1162.50 | Sales |
| 64 | 3628.80 | Marketing |
| 51 | 2891.70 | Marketing |
| 107 | 6473.50 | * |
The following table shows the output for users and group with the "Sales" credential:
| Field 1 | Field 2 | Field 3 |
|---|---|---|
| 87 | 1348.50 | Sales |
| 75 | 1162.50 | Sales |
| 107 | 6473.50 | * |
The following table shows the output for users and group with the "Marketing" credential:
| Field 1 | Field 2 | Field 3 |
|---|---|---|
| 64 | 3628.80 | Marketing |
| 51 | 2891.70 | Marketing |
| 107 | 6473.50 | * |
This enables the filtering of Dates and Timestamps. You can filter by the current day of month, month and year. You also have the option to filter by offsets, either earlier or later, from the current day of month, month and year.
The following table shows an example of the input:
| HireDate | EmployeeName |
|---|---|
| 2011-05-17 | Chad Mattson |
| 2009-03-25 | Richard Davis |
| 2012-01-17 | Amy Franks |
| 2009-05-09 | Willie Costa |
| 2010-05-30 | Rebecca Smith |
When you set the Filter Field to HireDate,
choose to keep records with
Offset Month, and set the Offset value
to -1 (assume the current month is June), the following table shows the output:
| HireDate | EmployeeName |
|---|---|
| 2011-05-17 | Chad Mattson |
| 2009-05-09 | Willie Costa |
| 2010-05-30 | Rebecca Smith |
This provides a variety of functions which can calculate a new field from your formula. The types of functions include Numeric, String, System and TimeDate. For more information on how to write a formula with functions, refer to the online documentation for your chosen database and version. These documentation are easily searchable online but too numerous and the links are too likely to change to be included here.
The following shows an example of using the CONCAT function in this formula:
CONCAT ([Name], " joined the company on ", [HireDate])
| Name | HireDate | Output |
|---|---|---|
| Richard Davis | 2009-03-25 | Richard Davis joined the company on 2009-03-25 |
| Amy Franks | 2012-01-17 | Amy Franks joined the company on 2012-01-17 |
| Willie Costa | 2009-05-09 | Willie Costa joined the company on 2009-05-09 |
The following shows an example of using the SUBSTRING function in this formula:
SUBSTRING ([Name], 1, 3)
| Name | HireDate | Output |
|---|---|---|
| Richard Davis | 2009-03-25 | Ric |
| Amy Franks | 2012-01-17 | Amy |
| Willie Costa | 2009-05-09 | Wil |
The Rows tab displays either the original data of a table, or the output of column data if there are transforms created. Click the Load Data blue button to refresh.
The Inspector tab enables you to review the contents of a column. It displays column information, value frequency based on the data type, as well as the chart type and plot.