ExoInsight Configuration Guide

 

Table of Contents:

• Managing ExoInsight Environments
  • Oracle Essbase Environments
  • Oracle PBCS and FCCS Environments
  • Oracle HFM Environments
  • Oracle Fusion Cloud ERP Environments
• Managing ExoInsight Administrators
• Proxy Server Configuration
• Managing ExoInsight Relational Database Targets
  • Common JDBC Entries
• Managing Configuration Settings Manually

Managing ExoInsight Environments

ExoInsight environments are named connection references to the source systems. One ExoInsight environment is set up per source system and is then referenced when connecting ExoInsight to that source system. 

Oracle Essbase Environments

To set up Essbase environments, click the Essbase Environments tab on the ExoInsight homepage:

If you have not already logged into ExoInsight, you’ll be prompted for your username and password (note: please see the Managing ExoInsight Administrators section for more information):

Once you’ve successfully logged in, you’ll be able to add additional Oracle Essbase environments. To add a new environment, click the Add New button:

Fill out the required information:

Name: The name you’ll use to reference the Essbase connection. Note: ExoInsight is a reserved word and should not be used as the environment name. Additionally, do not use a space or other special character in the name. Underscores ( _ ) are allowed.

Server: Enter the Essbase server name. Server IP address can also be used. If Essbase is running on the default port then it can be omitted; otherwise include the port number in the format servername:port. Note: If using APS as the Provider Services URL, then the Server can be the Essbase Cluster name.

Provider Services URL: Use “embedded” unless you want to explicitly list your provider services URL. The Provider Services URL should be in the format

http(s)://ServerName:Port/aps/JAPI

Please note that JAPI is case-sensitive. If using the JAPI URL as the Provider Services URL, the Essbase Cluster name can be used as the Server.

Allow User Impersonation: If set to false, the user will need to log in using their Essbase username and password. If set to true, the username and password supplied on the OLAP Environment entry are used to pull data from Essbase, but the username from the users is used to apply security. This is primarily used for single sign-on enabled applications.

Single Sign On: Check to enable SSO for this environment. SSO must be configured for ExoInsight before this can be used.

Admin-Only: If checked, then only users who have been configured as an ExoInsight admin can access this environment. See Managing ExoInsight Administrators for more information about creating ExoInsight admins.

Username and Password: (Optional) These values are only required to test the connectivity of the environment, or if Single Sign On is checked. If Single Sign On is not checked, they can be removed from the entry once connectivity is confirmed.

Row Limits: (Optional) Set the maximum row limits per query for each application.

Once the information has been entered, either click the Save button to save the environment, or click the Validate Connection to Selected Environment to validate connectivity.

Oracle Cloud EPM Environments

To set up a Cloud EPM environment, click the Cloud EPM Environments tab on the ExoInsight homepage. The Cloud EPM Environments tab is used to set up PBCS, FCCS, PCMCS, Tax Reporting, and Freeform Planning environments.

If you have not already logged into ExoInsight, you’ll be prompted for your username and password (note: please see the Managing ExoInsight Administrators section for more information):

Once you’ve successfully logged in, you’ll be able to add additional Oracle Cloud EPM environments. To add a new environment, click the Add New button:

Fill out the required information:

Name: The name you’ll use to reference the Cloud EPM connection. Note: ExoInsight is a reserved word and should not be used as the environment name. Additionally, do not use a space or other special character in the name. Underscores ( _ ) are allowed.

Base URL: Enter the Cloud EPM URL. For example, if your PBCS URL is

https://planning1-a111111.pbcs.us2.oraclecloud.com/HyperionPlanning

then use

planning1-a111111.pbcs.us2.oraclecloud.com 

for the Base URL. 

Domain: In the example above, the domain would be

a111111

Username and Password: These values are only required to test the connectivity of the environment. Once connectivity is confirmed, they can be removed from the entry.

Once the information has been entered, either click the Save button to save the environment, or click the Validate Connection to Selected Environment to validate connectivity.

Managing ExoInsight Administrators

ExoInsight admins are set up to control access to the following areas of ExoInsight:

• Add, edit, or delete Oracle Essbase environments
• Add, edit, or delete Oracle Cloud EPM environments
• Add, edit, or delete Oracle HFM environments
• Add, edit, or delete Oracle Cloud ERP environments
• Add, edit, or delete relational database destinations
• Add, edit, or delete admin users
• Add, edit, or delete power users
• Add, edit, or delete Saved POVs
• Add, edit, or delete ExoInsight Variables
• Access the ExoInsight log
• Access the Query Monitor

The ExoInsight admin does not need to be an administrator of the source system. ExoInsight will use the user entered as the ExoInsight admin to authenticate against the source system to grant access to the ExoInsight functions above. Granting a user ExoInsight admin access does not give the user the ability to modify or access anything in the source system they would not normally be able to.

ExoInsight uses a user that is specified during the installation as the initial admin. The ExoInsight admin must be able to be validated with a username and password against the source the admin user is assigned to.

To change, remove, or alter ExoInsight administrators, click on the Admin Users tab:

You’ll be prompted to log into ExoInsight if you haven’t already. Note: The username that was entered as the administrator during the installation must be used the first time, and the environment will be EB for Essbase, PBCS for any Cloud EPM source, HFM for HFM, and ERP for any Fusion Cloud ERP source.

To add or remove admin users, click the Add or Delete buttons at the top. If adding a new ExoInsight administrator, simply select the environment the user will be authenticated against and enter the new admin user:

Please note that ExoInsight is case-sensitive, so if a username is entered here in uppercase, then you will need to enter the username as uppercase when logging into areas of ExoInsight that require ExoInsight admin access.

Proxy Server Configuration

If the source you're attempting to connect ExoInsight to sits behind a proxy server (typically seen with Oracle Cloud EPM or Oracle Fusion Cloud ERP connections), you can instruct ExoInsight to communicate with the proxy server instead of directly to the source.

1. To update the ExoInsight proxy settings, open the <installation directory>/casabase/keystore/keystore.properties file.
2. Set the proxySet flag to true to make ExoInsight proxy aware. 
3. If you would like to use the system proxy settings of the server ExoInsight is installed on, set the java.net.useSystemProxies flag to true. Do not fill out any of the other settings.
4. If you would like to manually set the proxy settings, fill out the *.proxyHost and *.proxyPort with the hostname and port number of the proxy server. Make sure to fill out the appropriate http or https version of the setting depending on if your instance of ExoInsight has SSL enabled or not.
5. Always set the *.nonProxyHosts setting. In most cases this should be set to localhost.
6. Restart ExoInsight.

proxySet=false
java.net.useSystemProxies=false

http.proxyHost=
http.proxyPort=
http.nonProxyHosts=
https.proxyHost=
https.proxyPort=
https.nonProxyHosts=

Managing ExoInsight Relational Database Targets

ExoInsight enables the pushing of Essbase, PBCS, and FCCS data and hierarchies directly to most any widely available relational database. To set up relational database destinations, you must first have the correct JDBC driver installed with ExoInsight. ExoInsight ships with the following connection options by default:

• SQL Server
• Oracle
• SQLite

To enable another relational database destination, place the database’s JDBC driver in the following ExoInsight directory:

<installation directory>/casabase/JDBC_drivers

Once the JDBC jar file has been placed in the directory, the JDBC.groovy file must be updated to include the version, URL, jar file name, etc. A list of the most commonly used entries can be found at the end of this article. If you do not see the database you’re attempting to add, please contact [e-mail] for the required information.

After the JDBC.groovy file has been updated and ExoInsight has been restarted, you may now add your connection information to ExoInsight. Click the Database Environments tab in ExoInsight:

You’ll be asked to log in if you haven’t already:

To add a new connection, click the “Add New” button and fill out the required information:

Name: The name that you will reference this connection with.

Database Type: A drop-down list of available databases for your ExoInsight instance. If the database type you need is not listed, refer to the instructions at the beginning of this section to add it.

Server: The database server name.

Database: The database name. (Note: This value is not necessary for Snowflake connections)

Port: The database port. (Note: This value is not necessary for Snowflake connections)

User: The user that will be connecting to the database. Please note the user MUST have rights to create and drop tables. (Note: This value is not necessary for Snowflake connections)

Password: The password for the database user. (Note: This value is not necessary for Snowflake connections)

Once the required information has been entered, click the Validate Connection to Selected Environment button to validate your connection information (Note: the Validate Connection button is not available for Snowflake connections):

Common JDBC Entries

SQL Server 2016+

SQLServer {
        sql_driverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
        jdbcJarFileName='mssql-jdbc-9.2.0.jre8.jar'
        jdbcUrl='jdbc:sqlserver://SERVERNAME;databaseName=DATABASENAME'
        nvarchar='nvarchar(max)'
        columnNameWithSpaceCharStart='['
        columnNameWithSpaceCharEnd=']'
        timestampField='DATETIME'
        timestampFunction="none"
        dropTableStatement='drop table if exists [TABLENAME]'
        batchSize=5000
}

SQL Server 2014-

SQLServer {
        sql_driverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
        jdbcJarFileName='mssql-jdbc-9.2.0.jre8.jar'
        jdbcUrl='jdbc:sqlserver://SERVERNAME;databaseName=DATABASENAME'
        nvarchar='nvarchar(max)'
        columnNameWithSpaceCharStart='['
        columnNameWithSpaceCharEnd=']'
        timestampField='DATETIME'
        timestampFunction="none"
        dropTableStatement="IF OBJECT_ID('[TABLENAME]', 'U') IS NOT NULL DROP TABLE [TABLENAME];"
        batchSize=5000
}

Oracle

Oracle {
        sql_driverClassName='oracle.jdbc.driver.OracleDriver'
        jdbcJarFileName='ojdbc6.jar'
        jdbcUrl='jdbc:oracle:thin:@SERVERNAME:PORTNUMBER:DATABASENAME'
        nvarchar='nvarchar2(500)'
        columnNameWithSpaceCharStart='"'
        columnNameWithSpaceCharEnd='"'
        timestampField='TIMESTAMP'
        timestampFunction="to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss.ff3')"
        dropTableStatement="""
        BEGIN
           EXECUTE IMMEDIATE 'DROP TABLE "TABLENAME"';
        EXCEPTION
           WHEN OTHERS THEN NULL;
        END;
        """
}

PostgreSQL

PostgreSQL {
        sql_driverClassName='org.postgresql.Driver'
        jdbcJarFileName='postgresql-42.2.5.jre6.jar'
        jdbcUrl='jdbc:postgresql://SERVERNAME/DATABASENAME'
        nvarchar='varchar(500)'
        columnNameWithSpaceCharStart='"'
        columnNameWithSpaceCharEnd='"'
        timestampField='TIMESTAMP'
        timestampFunction="to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss.ff3')"
        dropTableStatement='drop table if exists "TABLENAME"'
}

MySQL

MySQL {
        sql_driverClassName='com.mysql.jdbc.Driver'
        jdbcJarFileName='mysql-connector-java-5.1.47.jar'
        jdbcUrl='jdbc:mysql://SERVERNAME/DATABASENAME'
        nvarchar='varchar(500) charset utf8'
        columnNameWithSpaceCharStart='`'
        columnNameWithSpaceCharEnd='`'
        timestampField='TIMESTAMP'
        timestampFunction="to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss.ff3')"
        dropTableStatement='drop table if exists `TABLENAME`'
}

SQLite

SQLite {
        sql_driverClassName='org.sqlite.JDBC'
        jdbcJarFileName='sqlite-jdbc-3.30.1.jar'
        jdbcUrl='jdbc:sqlite:DATABASENAME'
        nvarchar='nvarchar'
        columnNameWithSpaceCharStart='"'
        columnNameWithSpaceCharEnd='"'
        timestampField='DATETIME'
        timestampFunction="none"
        dropTableStatement='drop table if exists "TABLENAME"'
}

SAP HANA

SAP_HANA {
        sql_driverClassName='org.postgresql.Driver'
        jdbcJarFileName='postgresql-42.2.5.jre6.jar'
        jdbcUrl='jdbc:postgresql://SERVERNAME/DATABASENAME'
        nvarchar='varchar(500)'
        columnNameWithSpaceCharStart='"'
        columnNameWithSpaceCharEnd='"'
        timestampField='TIMESTAMP'
        timestampFunction="to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss.ff3')"
        dropTableStatement='drop table if exists "TABLENAME"'
}

Amazon Redshift

AmazonRedshift {
       sql_driverClassName='com.amazon.redshift.jdbc42.Driver'
       jdbcJarFileName='RedshiftJDBC42-1.2.43.1067.jar'
       jdbcUrl='jdbc:redshift://SERVERNAME:PORTNUMBER/DATABASENAME'
       nvarchar='nvarchar(200)'
       columnNameWithSpaceCharStart='"'
       columnNameWithSpaceCharEnd='"'
       timestampField='TIMESTAMP'
       timestampFunction="to_timestamp (?, 'YYYY-MM-DD HH24:MI:SS')"
       dropTableStatement='drop table if exists "TABLENAME"'
}

Azure SQL

AzureSQL {
        sql_driverClassName='com.microsoft.sqlserver.jdbc.SQLServerDriver'
        jdbcJarFileName='sqljdbc4.jar'        
        jdbcUrl='jdbc:sqlserver://SERVERNAME;database=DATABASENAME;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net'
        nvarchar='nvarchar(500)'
        columnNameWithSpaceCharStart='['
        columnNameWithSpaceCharEnd=']'
        dropTableStatement='drop table if exists [TABLENAME]'
}

DB2

DB2 {
        sql_driverClassName='com.ibm.db2.jcc.DB2Driver'
        jdbcJarFileName='db2jcc.jar'
        jdbcUrl='jdbc:db2://SERVERNAME:PORTNUMBER/DATABASENAME'
        nvarchar='varchar(80)'
        columnNameWithSpaceCharStart='"'
        columnNameWithSpaceCharEnd='"'
        timestampField='TIMESTAMP'
        timestampFunction="TIMESTAMP_FORMAT(?, 'YYYY-MM-DD HH24:MI:SS')"
        dropTableStatement='DROP TABLE TABLENAME'
}

Snowflake

Snowflake { 
        sql_driverClassName='net.snowflake.client.jdbc.SnowflakeDriver'
        jdbcJarFileName='snowflake-jdbc-3.13.29.jar'
        jdbcUrl='jdbc:snowflake://SERVERNAME.snowflakecomputing.com'
}

Managing Configuration Settings Manually

When modifying the configuration settings above, the ExoInsight configuration file located at

<installation directory>/casabase/config/ExoInsight.groovy

is updated. Sometimes it's easier to inspect or modify the file directly in a text editor. When modifying the file, take care to make sure the syntax is correct before saving. After manually modifying the file ExoInsight must be restarted for the changes to take effect.