Import Database

When you choose Data > Import Data > Database you can import data from a SQL database that is accessed using JDBC. JDBC drivers are available that can connect to all major databases. These drivers are available from your database vendor or third party developers. Please contact your database administrator or database software vendor for information on driver availability.

If you choose to import from a Database, you need to select and enter information related to the database source such as the database type, database connection parameters or URL and database driver. Connection parameters may differ depending on the database driver, and what other selections you have made. If you name and save the database configuration, you'll be able to use it with other models. You can also delete a saved configuration.

 

To select a saved Database connection, click the down arrow next to "Connect to" and choose the saved connection: the information about the database connection displays. Saving a Database connection allows you to easily reuse rather than re-enter the connection information, in the same or in other models, using the Data Browser feature.

 

Quantrix includes the H2 database and the Scrollable Emulation Driver, but you can import from other SQL databases that can be used with JDBC. The H2 database installed with Quantrix is an open source Java SQL database that may be useful to test with.

 

Data types in Quantrix database import:

Data Item – A column or set of columns used to populate the cell data.
Primary Key – The column in the database that is the primary key.  
Primary Key Label – A descriptive label that can be used as the item name label instead of the actual primary key label itself.
Foreign Key – A foreign key is a referential constraint between two tables. In Quantrix terms, a category could serve as a foreign key to a primary key category located in a different matrix. 

 

Note: You can use the Add new database link to add new database driver and database information (shown in the image below), and you can add new database driver information using Database Settings.

 

 

 

 

 

Using Import Database

After entering or selecting a data source and clicking Connect, the database tables are shown in a Database data tree. You can click the plus sign(s) to expand, and you can filter using the filter box to display only what you want to see.

 

Note: Database structure is shown with icons. Databases with only one instance show the database icon and tables icons. Databases with multiple instances include an instance icon.

Database Icon

Database Table Icon

Database Instance Icon

 

Data Source Name - This defaults to Database, and you can change it to a more meaningful name. The Data Source Name shows in the Data Import Matrix's data source pane. Once you have entered or selected database configuration information, connected and selected a table, the OK button at the bottom of the screen becomes enabled. When you click OK any changes to the Data Source Name will be saved.

Connect to - Use the drop-down to select the database type to configure, or to Add a new database. Once you save a database configuration it displays in this drop-down for selection.

Save - Once Save is enabled you can save this database connection information, so that you can use it again.

Manage - Click Manage to Rename, Remove or Load a saved database connection.

Authenticate - Click Authenticate to show the authentication dialog for this database connection, depending on how your authorization is set up. You can change the Username and Password, and the Save credentials setting. If you save credentials, the encrypted credentials are saved on your computer.  See also Credentials Manager.

Connect - Click Connect to connect to the database and show the imported database information (tables and fields) in a database structure tree format.

Select a table from the list that displays after tables display (see below about searching the tables). 

When you click to select a table, the Max rows option appears. If you select the option, you can enter a number in Max rows to limit the data source to that number of rows. 
This can help you set up or configure/prototype a data import more quickly when the source is slow or large. Note that Max Rows remains in effect when you click to exit the data import configuration. When your prototyping is complete, enter data import configuration again to remove or adjust Max Rows if desired.
Note that if the database type is not supported (if it is not a standard SQL database supported by Modeler), Quantrix will not be able to issue the necessary SQL to perform the Max rows query, so the Max rows field will be disabled.
As noted in the tooltip next to Max rows, the Max rows setting treats the source table as though it had fewer rows. It does this before generating the query, which means it affects the size of the table being queried rather than the maximum number of returned results. This can result in missing items and incomplete data for your import.
Changing Max rows will cause an update if you selected live preview. If you change Max rows on the configure source pane and the source is shared between multiple import matrices, you are prompted to edit all sources or split the sources to segregate the Max rows setting.

Note the option to Discover table joins automatically.  Per the tooltip, if you choose Discover table joins automatically, it may take some time for Quantrix to inspect the table(s) and attempt to make table joins automatically. Quantrix will look for foreign key constraints in the database, and base its automatic table joins on them. You can also manually add table joins later.

Click the plus sign next to a table to expand the table and view its fields.

Note: Although you can view the table fields for informational purposes, you can't select the fields here: You must select a table.  

Select the table from the Database Structure pane that you want to include in the import. If you have many tables, you can search on the metadata in the box below the text "Select a table from the database structure". For example, you can search on part of the name for a table and the table will display so you can easily select it. As soon as you click on a table, the Max rows field displays to the right of the Connect button as described above. 

As described below, after you click OK, the data is imported, and a Data tree displays the table and fields you chose in the left pane with All Rows selected by default, and an All Rows category and structure displayed in the right pane. You can remove All Rows (you can delete the All Rows category or remove the All Rows check mark) and choose the fields manually. To choose fields manually, select and drag the table fields to the matrix import pane on the right to see the data for the fields.

 

Within the data tree, which displays the table fields from the table you selected, you can right-click on a field for additional options that depend on the data source. For example, you may have only the right-click option to Make Data Item for a matrix data source, but for a database data source you might see the options shown below.

 

 

Add Custom Field - You can add a virtual column in a data source. An example would be to take two columns and perform a mathematical operation (average, multiply, addition). Doing this allows you to use the database as the processing engine for the column and then use the results in a matrix using the aggregated data source.

  • If you choose Add Custom Field after right-clicking, the Custom Field dialog displays:

Enter the name of the new field in Custom Field Name.

Click the column to use, and then enter the SQL statement for the new virtual column.

  • Remove from View removes the selected field from the data structure tree display.

  • Make Data Item creates an Item category and populates the related data.

Primary Keys

As mentioned in the Configure Data Source topic, if a SQL or Salesforce data base data source has a primary key, Data Import will recognize it and bring it in as a primary key. A primary key icon shows on the field in the data source tree, and, if you drag that field to an import matrix as a category, the import matrix category also shows a primary key.

If a SQL data source has multiple fields that the import detects as primary keys, they do not show the primary key icon: only a single "virtual" primary key field appears with the primary key icon, with a comma separated list of the component field names.  The virtual key values are a pipe-delimited concatenation of the values of the component field values.

Dragging Fields from the Data Tree to the Matrix area

The images below illustrate the way you can drag a field from the data tree to become a category, item and/or filter.

Note that you can't create an import Data Item Category by dragging: to create a Data Item Category, use the Right-Click on the field within the data source and choose Make Data Item.

 

 

 

 

Using Data Tools and Filters

When you click OK after selecting a table, the window refreshes showing the imported database fields on the left, and a Filters pane with All Rows beneath it on the right.

You can remove the All Rows option, or delete the All Rows category, and work with fields selectively.

Use the button icons above the table fields and to the right of the Quick Find box to view/edit the data source configuration as described in the overview.

Note: In order to refresh the pick list from a database data source, you must either Refresh the local data source or close and reopen the model. To refresh a database data source, you need to have the Permission to Modify Data Sources.

 

To create Filters, drag fields into the filters section and choose operators in conjunction with Values, Fields and Expressions.

 

See Using Filters on Imported Data

At the bottom right of the matrix pane, there is a Finish icon. If you click Finish, the Data Import Matrix displays, with a Data Import Configuration icon with which you can return to the configure pane showing the imported text fields. You can toggle between the two displays by clicking the icons as many times as you need to.

 

Query Using SQL

Query Using SQL - After you first click Connect on the Configure Database pane, the database tables display. If you want to build a SQL query to pull the data you want (rather than by selecting tables by clicking on them), click the Query Using SQL link.  The custom SQL you enter creates a SQL View directly from the database.  See also the topic Query Using SQL and Validation.

Note: Some databases (such as Oracle 11 and earlier) will not support a SQL row limit query in Data Import.

You can select tables to use in the SQL query pane by double clicking on a table to bring the table into the SQL query pane.

When you click the Query Using SQL link, the link changes to Select Tables so that you can easily return to the table select pane.  You can toggle between the Select Tables and Query using SQL links.

Tip: You can execute stored procedures by calling  the stored procedure name using "execute <procedure_name>" in the SQL pane.

Once you are satisfied with your custom SQL query, click OK and the data source pane shows the data source type as SQL View of (followed by the name of the table you chose).

 

Add New Database

If you choose Add new database, another set of panels display in which you can add (or remove) a database driver (JAR file) for a selected database type.

 

 

Table Joins

Discover table joins automatically - This option is located below the table selection pane and tells Quantrix to try to determine table relationships automatically. This can take time because Quantrix has to analyze and search for potential table relationships. Note that primary keys are not displayed in the table join.

If you choose Join Tables, you can click the plus sign to expand and view the join tables. When you click the plus sign, the join tables show, with the relationship shown in parentheses:

 

When you expand the join tables and drag fields into the Filter pane, the table name is shown first for all but the base table. In the illustration below, Discount Id is not prefaced with the table name because it comes from the base table.

Query Using SQL and Validation