This page is for data modellers who want to connect to an external database, either to load data or to export processed data back to a database.
Overview
A datasource is a modelling object whose properties provide the information needed to connect to an external database. You must have one datasource for each type of database to which you need to connect. The datasource has the information needed to push data to and pull data from the external database. Optionally, you can configure several datasources for the same type of database. This is useful if you want to have several connections to the same database.
How a datasource behaves when you run analysis on the model is affected by:
- the Privilege Allow anonymous updates on datasources. Only a user with this privilege can change the Allow Anonymous Export tick box on the datasource.
- the System Configuration option Allow anonymous export by default. When selected, this system option sets Allow Anonymous Export to be ticked by default on new datasources.
- Most importantly, ensure you have configured at least one datasource instance.
For information about the properties toolbar, and about the sections Parent Details, Analysis Models, Description and Audit Summary, see Common Properties. We recommend you always add a Description that explains the purpose of the item you are creating.
For a full list of all the PhixFlow property tabs and windows, see Properties, Windows, Menus and Toolbars.
The toolbar has the standard icons and 
Show Tables, which opens a list of tables visible for this datasource using the default login details; see the Table Details window.
Basic Settings
| Field | Description |
|---|---|
| Name | Required. Enter the name for this datasource object. You will use this name to specify this datasource object in other collector and exporter objects. |
| Enabled | Error rendering macro 'excerpt-include' : No link could be created for '_check_box_ticked'. when the configuration is complete and the datasource is ready to be used. |
| URL | Enter the URL to connect to the datasource; see Connection URLs below. what |
| Allow Anonymous Export | For datasources connected to an exporter being run by a task, such as a scheduled task. The target database must permit anonymous updates. Error rendering macro 'excerpt-include' : No link could be created for '_check_box_ticked'. to allow an exporter to export data anonymously to a database using the datasource. The datasource uses the anonymous username and password configured ...?where.The datasource can only use the logged in user's credentials (see Use user credentials below). You need the Allow anonymous updates on datasources privilege to change this option. |
| Use User Credentials on Export | For datasources connected to an exporter being run interactively, when a user clicks Error rendering macro 'excerpt-include' : No link could be created for '_check_box_ticked'. to connect to the target database using the PhixFlow username and password of the current logged-in user.The target database must already have the same username and password with the required permissions. Contact the database administrator to do this. |
| Protect during import | Error rendering macro 'excerpt-include' : No link could be created for '_check_box_ticked'.
|
| Password | Password for connecting to the datasource with the Anonymous Username. I've ticked Allow Anonymous, and Use User, but I'm not seeing any fields where I can specify which user credentials to use. This option has gone. |
| Driver Class | Required. Select the driver class for the type of database to which the datasource object connects. This should match the type of datasource you are connecting to, e.g. Oracle; Not all the driver classes listed are configured with the JDBC drivers; see Driver Classes. |
Advanced
| Field | Description |
|---|---|
| Check Query | This is an advanced (optional) setting. Use this field when connections to the target database might go "stale", e.g. if the target database disconnects long running connections. Setting this field causes PhixFlow to run the query against the database just before each use of a "pooled" connection. If the query does not return a row, the connection is discarded and a new connection is created. To avoid any performance impact, the query should execute quickly. See also database collectors for guidance on writing SQL queries in PhixFlow. |
| Datasource Profile | This is an advanced (optional) setting. Use this field when connections to the target database have certain requirements that are not covered by PhixFlow's default external Datasource configuration. The value entered in this field will determine which external datasource connection profile that this datasource instance will use to connect to the target database. |
Connection URLs
A datasource instance must have a name, a URL, and a username and password used to connect to the external datasource. The URL should be in one of the following formats:
Platform | Connection String |
|---|---|
Oracle | jdbc:oracle:thin:@hostname:1521:phixflow |
| Oracle (> 12c with PDB containers) | jdbc:oracle:thin:@hostname:1521/phixflow |
Sql Server | jdbc:sqlserver://hostname\myservice;databaseName=phixflow |
MySQL | jdbc:mysql://hostname/phixflow |
| Big Query | jdbc:bigquery://@hostname:@port;ProjectId=@Project; |