Data Connectors

The Data Connectors page allows user to

  • manage the list of connectors

  • select individual items from these connectors to be visible in Data Model and Reports

Note

For the Reporting Databases:
- The connection string user should have permissions to read schema; to select on all tables, views, store procedures and functions that will be used as data sources; to execute those store procedures and functions.
- The user should also have permissions to create temp tables.

Add connector and select visible data sources

In this step user adds a connector and selects data sources to be visible in reports.

Warning

Please use caution when adding stored procedures to the visible data source list. All stored procedures are executed when added to visible (input parameters are set to NULL) to obtain the resulting fields returned. Some stored procedures are created to do things like delete tables, add data to tables, etc. If these are added to the visible data sources, they will be executed in the database.

  1. In browser, log in to AgilePoint NX as a user with Data Connectors permission.

  2. Click Settings, then Data Setup then Data Connectors in the left menu.

    ../_images/Connector_Add_Connector.png

    Fig. 7 Add Connector

  3. Select the Setting Level: either System or a specific tenant.

  4. Click on Add Connector in the middle panel.

    ../_images/Connector_Server_Type.png

    Fig. 8 Connector Data Server Type

  5. Select the data server type from the popup.

  6. The Database Connection popup appears for configuring the connector.

    ../_images/Connector_Builder.png

    Fig. 9 Database Connection Builder

    1. Fill in the Server Name, e.g. “yourdbserver.com”.

    2. Fill in the Database name, e.g. “Northwind”.

    3. Select the Authentication type from the drop-down box.

    4. Fill in the Login and Password if necessary.

    5. Optional. Fill in the additional connection options specific to the selected data server.

    These steps can be bypassed when user already knows the connection string. In this case, it can be copied and pasted straight into the Connection String box.

  7. Click OK button to verify the connection and go to the next step after all required fields are filled in.

    Note

    • Unless the Connection String has been verified successfully, user will not be able to move next.

  8. The connector name will be automatically populated from the database name. User can edit to give it a more suitable name.

    ../_images/Connector_Name.png

    Fig. 10 Connector Name

  9. Expand the listed user schemas and object types to see the data sources.
    The data sources can be quickly filtered by typing a partial name in the Search box.

  10. Click on the data sources to move them between the two lists. User can quickly move all data sources in a group (Table, View, Stored Procedure or Function) by clicking on that group name.

    ../_images/Connector_Data_Sources.png

    Fig. 11 Data Sources

  11. Click Save button at the top to save the connector and the visible data sources.

Connector Permissions

Analytics Center needs permissions to view the database schema and read from selected tables and views.

If using stored procedures as data source, Analytics Center needs execute permission on these stored procedures as well as create table and delete table permissions.

Note

The create table permission will be used to create temporary tables to store the output of stored procedures, for joining to other data sources. And the delete table permission will be used to clean up these temporary tables afterwards.

Delete connector

  1. Click the delete icon (x) on the right of a connector to delete it.

  2. Click OK in the confirmation pop-up.

Make a conector hidden

All data sources from a connector can be hidden quickly by making that connector hidden.

  1. Click the visibility icon on the right of that connector.

    ../_images/Connector_Visible_Invisible.png

    Fig. 12 Connector Visible/Invisible

  2. Click OK in the confirmation pop-up.

    All data sources from this connector is hidden from Data Model and Reports. The right pane is disabled and the connector’s visibility icon is changed to a hidden one.

    ../_images/Connector_Visible_Confirmation.png

    Fig. 13 Confirmation pop-up

To restore the visibility of the data sources:

  1. Click the “hidden visibility” icon on the right of that connector.

  2. Click OK in the confirmation pop-up.

    The visibility of all data sources from this connector is restored back to the time before being hidden. The right pane is enabled and the connector’s visibility icon is changed back to normal.

Refresh the list of available data sources

When there is a remote change in a connector, it will not be automatically reflected in Analytics Center. The Reconnect button needs to be manually clicked on to detect that.

  1. Click on the connector.

  2. Click the Reconnect button.

  3. The remote changes in the data sources will be marked as either New data source or Changed data source.

    The Data Setup, Data Connectors and Data Model menu items will also be marked with Changed data source icon (!).

    ../_images/Connector_New_And_Changed_Data_Sources.png

    Fig. 14 New and changed Data Sources

  4. Go to Data Model page to resolve the changes.

Filter the connector list

The connector list can be quickly filtered by typing a partial connector name in the Search box.

Cancel the changes

To cancel any changes without saving:

  1. Click the Cancel button at the top.

  2. Click OK in the confirmation pop-up.

Connection String Examples

  • Oracle:

    • Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.45.37)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=user;Password=password;

    • Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.45.37)(PORT=1521))(CONNECT_DATA=(SID=xe)));User Id=user;Password=password;

  • Microsoft SQL Server:

    • Server=192.168.45.37,1433;Database=testdatabase;User ID=user;Password=password

    • Server=HOST-PC;Database=testdatabase;User ID=user;Password=password

  • MySQL:

    • Server=MY-PC;Port=3306;Database=testdatabase;User ID=user;Password=password

  • PostgreSQL:

    • Server=mydomainname;Port=5432;Database=testdatabase;User ID=user;Password=password

    • Server=mydomainname;Port=5432;Database=testdatabase;User ID=user;Password=password;SslMode=Require;Trust Server Certificate=true;

    Note

    • If using a PostgreSQL connection string with “SslMode=Require”, the “Trust Server Certificate=true;” parameter will also need to be added.

  • Elasticsearch :doc:’/intro/elastic’:

    • server=https://xxxxxxxx.us-east-1.aws.found.io;Port=9243;User=user;Password=password;

  • MongoDB :doc:’/intro/mongo’:

    • Server=localhost;Port=27017;Database=admin;User=user;Password=password;

    • User=user;Password=password;Server=atlas-host1;Port=27017;Database=testdatabase;AuthDatabase=admin;AuthMechanism=SCRAM-SHA-1;ReplicaSet=cluster0-shard-00-01-u49p2.mongodb.net:27017,cluster0-shard-00-02-u49p2.mongodb.net:27017;UseSSL=true;SlaveOK=true;