5 Manage Database Connections for Semantic Models

Administrators create and manage cloud database connections for Data Modeler. Your business data doesn't have to be in one place. Connect to multiple cloud databases so business modelers and analysts can analyze company data wherever it is stored.

Connect to an Essbase Cube

Connect to an Essbase database so that you can model and visualize data from Essbase cubes.

Before you start, deploy Data Gateway with your Oracle Analytics instance and configure a Data Gateway agent to communicate with the remote Essbase deployment.
  1. In the Physical Layer, create a new database:
    1. Right-click in the Physical Layer pane and select Create New Database.
    2. At the Database dialog, specify a name to identify your database within Oracle Analytics.
    3. Select the Database Type (for example, Essbase 11), then click OK.
  2. In the Physical Layer, create a new connection pool:
    1. Right-click the new database, click Create New Object, then select Database Connection Pool.
    2. At the Connection Pool dialog, specify a name to identify the database pool within Oracle Analytics.
    3. In Essbase Server, specify the connection string for your Essbase server.
      For example, http://<IP address>:<port number>/essbase/agent.
    4. Select Use Data Gateway.
    5. Enter the User name and Password for the Essbase deployment.
    6. If prompted, re-enter the password for the Essbase deployment.
  3. In the Physical Layer, import the Essbase metadata:
    1. Right-click the Essbase connection and select Import Metadata.
    2. At the Select Data Source page, click Next.
    3. At the Select Metadata Objects page, expand the database in the Data source box, select the cube you want, then click Import selected.

      For a large cube, the import can take two to three minutes.

    4. When the import is complete, expand the database in the Repository View to display the imported Essbase cube.
    5. Click Finish.
  4. Using the physical layer that you've just created, create your business model and mapping layer, and your presentation layer.
  5. Click File, Cloud, then Publish.
  6. Create a dashboard or visualization workbook based on your Essbase cube.
    The new Essbase cube is now available as a subject area in Oracle Analytics.
    For example, in Oracle Analytics, create an analysis and in the Select Subject Area dialog you can access the new Essbase subject area. In Oracle Analytics, create a new workbook and in the Add Dataset dialog, click Subject Areas to access the new Essbase subject area.

Create Semantic Model Connections to Snowflake Data Warehouse

Configure your on-premises environment so that you can model data in a Snowflake database.

Both local and remote connections to Snowflake from a semantic model require an installation of Data Gateway in order for the Model Administration Tool to import and model tables from a Snowflake data source. This Data Gateway agent needs to be configured with a suitable driver to connect to Snowflake. For remote connections, Data Gateway must continue to be available when queries are run. However, once the Snowflake tables are modeled and the semantic model is published to Oracle Analytics, for local connections, Data Gateway can be disabled or removed as it isn't used when queries are run from Oracle Analytics.

You can create either a local or a remote connection to Snowflake from the semantic model. When creating a local (not remote) connection, the connection pool in the semantic model uses a JDBC connection.

Before you start, install Data Gateway and Model Administration Tool on the same Windows computer in your on-premises environment.
  1. Configure a local Data Gateway agent to facilitate a connection from the Developer Client Tool to Snowflake.
    1. Download the latest Snowflake JDBC driver (for example, in file snowflake-jdbc-3.9.0.jar).
    2. Copy the downloaded JAR file to the Data Gateway installation folder.
      In a server deployment, copy the JAR file into:
      <Data Gateway folder>/domain/jettybase/lib/ext
      In a personal deployment, copy the JAR file into:
      <install directory>\war\datagateway\WEB-INF\lib
    3. Re-start Data Gateway.
  2. Configure a connection to your Snowflake database.
You can now model your data using this connection.

Create a Local Semantic Model Connection to Snowflake

Connect to a local Snowflake database so that you can model Snowflake data.

  1. In Model Administration Tool, enable the JDBC connection pool functionality by loading Java data sources. See Step 3 in Configure and Register Data Gateway for Reporting.
  2. In Model Administration Tool, create a new database and set the type to Snowflake.
  3. Add a Connection Pools and specify these details on the General tab:
    • Call Interface: JDBC(Direct Driver).
    • Require fully qualified table names: Yes.
    • Data source name: Enter the connection string, for example: jdbc:snowflake://xxxx.snowflakecomputing.com?db=ODEV&warehouse=xxxxxx&schema=xxxxxx
    • RDC Version: Leave this field blank.
  4. On the Miscellaneous tab, specify these details:
    • JDS Server URL: Leave this field blank (remove any entry in this field).
    • Driver Class: net.snowflake.client.jdbc.SnowflakeDriver.
    • Use SQL over HTTP: false.
  5. Model your data using this connection.
  6. Upload or publish your semantic model to Oracle Analytics Cloud when you have completed your model.
    Note: Oracle Analytics Cloud connects to the Snowflake database without using Data Gateway.
You can now model your data using this connection.

Create a Remote Semantic Model Connection to Snowflake

Connect to a local Snowflake database so that you can model Snowflake data.

  1. In Model Administration Tool, enable the JDBC connection pool functionality by loading Java data sources. See Step 3 in Configure and Register Data Gateway for Reporting.
  2. In Model Administration Tool, create a new database and set the type to Snowflake.
  3. Add a Connection Pools and specify these details on the General tab:
    • Call Interface: JDBC(Direct Driver).
    • Require fully qualified table names: Yes.
    • Data source name: Enter the connection string, for example: jdbc:snowflake://xxxx.snowflakecomputing.com?db=ODEV&warehouse=xxxxxx&schema=xxxxxx
    • RDC Version: Set to 2.
  4. On the Miscellaneous tab, specify these details:
    • JDS Server URL: Leave this field blank (remove any entry in this field).
    • Driver Class: net.snowflake.client.jdbc.SnowflakeDriver.
    • Use SQL over HTTP: true.
  5. Model your data using this connection.
  6. Upload or publish the semantic model to Oracle Analytics Cloud when you have completed your model.
    Note: Oracle Analytics Cloud connects to Snowflake using any configured Data Gateway agent.
  7. Copy the Snowflake driver file to each Data Gateway agent installation folder.
    • In a server deployment, copy the JAR file into: <Data Gateway install_location>/domain/jettybase/thirdpartyDrivers.
    • In a personal deployment on Windows, copy the JAR file into: <Data Gateway_extract_path>\thirdpartyDrivers.
    • In a personal deployment on MacOS, copy the JAR file into: <Application->Show Package Contents>Resources->app.nw-> thirdpartyDrivers.
  8. Re-start Data Gateway. See Maintain Data Gateway.

DSN Formats for Specifying Data Sources

In Oracle Analytics you can model your on-premises data for many database types. Oracle Analytics supports direct access to some on-premises data sources through the semantic model. When you create the database connection using Model Administration Tool, in the Data source name field on the Connection Pool dialog (General tab) you use the appropriate DSN format for the database type you're connecting to.

Amazon Redshift:
       DRIVER=Oracle 7.1 Amazon Redshift Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
      SSL: DRIVER=Oracle 7.1 Amazon Redshift Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"];EM=6;CPV=TLSv1.2,TLSv1.1,TLSv1, SSLv3, SSLv2;VSC=0
Apache Drill:
       DRIVER=MapR Drill ODBC Driver;Host=["host-name"];Port=["port"];CastAnyToVarchar=true;ExcludedSchemas=sys,INFORMATION_SCHEMA;AuthenticationType=Basic               Authentication;ConnectionType=Direct
Aster:
       DRIVER=Aster ODBC Driver;SERVER=["host-name"];PORT=["port"];DATABASE=["service-name"]
DB2:
      DRIVER=Oracle 7.1 DB2 Wire Protocol;IpAddress=["host-name"];PORT=["port"];DB=["service-name"]
      SSL:  DRIVER=Oracle 7.1 DB2 Wire Protocol;IpAddress=["host-name"];PORT=["port"];DB=["service-name"];EM=1;VSC=0
Greenplum:
       DRIVER=Oracle 7.1 Greenplum Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
Hive:
       DRIVER=Oracle 8.0 Apache Hive Wire Protocol;HOST=["host-name"];PORT=["port"]
      SSL: DRIVER=Oracle 8.0 Apache Hive Wire Protocol;HOST=["host-name"];PORT=["port"];EM=1;VSC=0
Impala:
       DRIVER=Oracle 7.1 Impala Wire Protocol;HOST=["host-name"];PORT=["port"]
     SSL:  DRIVER=Oracle 7.1 Impala Wire Protocol;HOST=["host-name"];PORT=["port"];EM=1;VSC=0
Informix:
       DRIVER=Oracle 7.1 Informix Wire Protocol;HOSTNAME=["host-name"];PORTNUMBER=["port"];DATABASE=["service-name"]
MongoDB:
       DRIVER=Oracle 8.0 MongoDB;HOST=["host-name"];PORT=["port"];DB=["service-name"]
MySQL:
      DRIVER=Oracle 7.1 MySQL Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
PostgresSql:
       DRIVER=Oracle 7.1 PostgreSQL Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
Spark:
      DRIVER=Oracle 8.0 Apache Spark SQL;HOST=["host-name"];PORT=["port"]
      SSL:  DRIVER=Oracle 8.0 Apache Spark SQL;HOST=["host-name"];PORT=["port"];EM=1;VSC=0
SQL Server:
     DRIVER=Oracle 7.1 SQL Server Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"]
     SSL:  DRIVER=Oracle 7.1 SQL Server Wire Protocol;HOST=["host-name"];PORT=["port"];DB=["service-name"];EM=1;VSC=0;CryptoProtocolVersion=TLSv1.2,TLSv1.1,TLSv1,SSLv3,SSLv2
Sybase:
       DRIVER=Oracle 7.1 Sybase Wire Protocol;NA=["host-name"], ["port"];DB=["service-name"]
Teradata:
       DRIVER=Oracle 7.1 Teradata;DBCName=["host-name"];port_name=["port"]