Create a database connection

Note:

Database connections are supported in Insights in ArcGIS Enterprise and Insights desktop.

A database connection allows you to add data from a supported database. Available database connections are listed in the Add to page window when you click the Databases option or on the Connections tab Connections on the home page. For more information about using database data in Insights, see Understanding data in relational databases.

Before you create a database connection, the following prerequisites must be met:

Create a new database connection

Database connections are saved as relational database connection items. You can view relational database connection items from the Connections tab on the home page or, if you are using Insights in ArcGIS Enterprise, in your ArcGIS Enterprise portal. Connections can be created from the home page or before you add data to your workbook.

Home page

Use the following steps to create a database connection from the home page:

  1. Click the Connections tab Connections.
  2. Click New connection and choose Database from the menu.

    The New connection window opens.

  3. Choose the database type you want to connect to. Supported databases include SAP HANA, Oracle, Microsoft SQL Server, and PostgreSQL.
  4. Provide the following required connection properties:
    • Name—The alias that will be assigned to the database connection. The Name property does not have to correspond to the database name.
    • Type—The database type. For example, Oracle or SQL Server.
    • Username—The username for the database you are connecting to.
    • Password—The password that corresponds to the username you are using.
    • Server Name—The fully qualified domain name (OS (Windows) authentication or SQL Server authentication) or database host name (SQL Server authentication only).
    • For SQL Server connections, choose either SQL Server or Windows as the Authentication method.
    • For SAP HANA connections, check Connect using SSL if you are connecting to a database with Secure Sockets Layer configured.
    • Other connection properties, such as Default Spatial Type, Database Name, Port Number and Instance Name, vary by database type.
  5. Click Add.

    Insights uses the connection properties to try to connect to the database. Once successful, the connection will appear on the Connections page.

    If Insights can't connect to the database, an error message appears. See Troubleshoot a database connection for more information about the reasons why a connection could not be established.

A relational database connection is created. The connection can be updated from the Connections tab on the home page. For more information, see Update a database connection.

As the owner of the database connection in Insights in ArcGIS Enterprise, you can share the item with team members so that they can use the data from the database connection in their analysis.

Workbook

Use the following steps to create a database connection from the Add to page window:

  1. Open the Add to page window using one of the following options:
    • Create a workbook. The Add to page window opens when the workbook is created.
    • Click the Add button Add above the data pane in an existing workbook.
    • Create a page in an existing workbook. The Add to page window opens when the page is created.
  2. In the Add to page window, choose Database.
  3. Click New Connection.
  4. Provide the following required connection properties:
    • Name—The alias that will be assigned to the database connection. The Name property does not have to correspond to the database name.
    • Type—The database type. For example, Oracle or SQL Server.
    • Username—The username for the database you are connecting to.
    • Password—The password that corresponds to the username you are using.
    • Server Name—The fully qualified domain name (OS (Windows) authentication or SQL Server authentication) or database host name (SQL Server authentication only).
    • For SQL Server connections, choose either SQL Server or Windows as the Authentication method.
    • For SAP HANA connections, check Connect using SSL if you are connecting to a database with Secure Sockets Layer configured.
    • Other connection properties, such as Default Spatial Type, Database Name, Port Number and Instance Name, vary by database type.
  5. Click Ok.

    Insights uses the connection properties to try to connect to the database. If connecting to the database is possible, the connection is created, and datasets from the database are listed in the middle pane. You can choose datasets from your new connection to add to your workbook page.

A relational database connection is created. The connection can be updated from the Connections tab on the home page. For more information, see Update a database connection.

As the owner of the database connection in Insights in ArcGIS Enterprise, you can share the item with team members so that they can use the data from the database connection in their analysis.

Index columns

Insights will create an index on database tables for certain functions using Oracle, SQL Server, or PostgreSQL databases to improve performance. For Oracle and SQL Server databases, indexing can only take place if the user who creates the database connection has the required database privileges. Once the connection is created, indexes will be created on the database tables regardless of who is using them in Insights.

If the user who creates the database connection does not have the necessary privileges, indexing will not take place on the database tables.

The following table describes how indexing will be performed for each database type and the privileges required to create a connection where indexing takes place:

DatabaseDescriptionIndex typeRequired privileges
SAP HANA

Indexing is not completed in Insights for SAP HANA databases.

Not applicable

Not applicable

Oracle

Indexing occurs in the following situations:

  • A field is calculated on the dataset.
  • A relationship is created with the dataset.
  • Enrich Data is used on the dataset.
  • Location is enabled on the dataset, including through results from spatial analysis.
  • The data is projected to a different geographic coordinate system. Data will be projected if the coordinate system of the dataset does not match the coordinate system of the basemap.

Non-unique

Connect to and browse contents with data caching.

One of the following:

  • The user is the owner of the table.
  • The owner of the table granted the CREATE INDEX ON <table name> privilege to the user.
SQL Server

Indexing occurs in the following situations:

  • A filter is applied to the dataset.
  • A relationship is created with the dataset.
  • Enrich Data is used on the dataset.
  • Location is enabled on the dataset, including through results from spatial analysis.

Non-unique

Connect to and browse contents with data caching.

PostgreSQL

Indexing occurs in the following situations:

  • A filter is applied to the dataset.
  • A relationship is created with the dataset.
  • A time series graph is created.

Non-unique

Connect to and browse contents with data caching.

Spatial types

The default spatial type is used to determine what spatial type will be used when a location field is added to a database dataset using enable location. The following table describes the default spatial types that are available for each database type:

DatabaseSupported default spatial types
SAP HANA
  • ST_Geometry
Oracle
  • Oracle Spatial
  • Esri Geodatabase
SQL Server
  • Geometry
PostgreSQL
  • PostGIS Geometry
  • Esri Geodatabase
  • None
Note:

SDELOB spatial types are not supported.

Resources

For more on creating and troubleshooting enterprise geodatabases in Oracle, see Geodatabases in Oracle.

For more on creating and troubleshooting enterprise geodatabases in Microsoft SQL Server, see Geodatabases in Microsoft SQL Server.

For more on creating and troubleshooting enterprise geodatabases in PostgreSQL, see Geodatabases in PostgreSQL.

Next steps

Now that you've created a database connection, you're ready to access data from your database connections in Insights.