1. Using Oracle Essbase
  2. Work with Sources

9 Work with Sources

Using saved connections and Datasources, you can set up cubes to interact easily with a variety of source data.

For example, you can set up a partition between a cube and RDBMS tables, share data between a cube and Oracle Database, develop security filters using variables to fetch members or user names from outside source data, and load data from REST API endpoints.

Many cube operations require connection information, such as login details, to access remote source data or hosts. You can define these as connections and Datasources once, and reuse them in various operations, so that you do not have to specify the details each time you perform a task.

You can implement saved connections and Datasources either globally or per application. These abstractions facilitate the following operations:

  • Loading dimensions and data

  • Importing cubes

  • Defining variable security filters

  • Connecting cubes using partitions, and accessing real-time data

  • Drilling through to remote sources of data

Topics in this chapter:

About Connections and Datasources

Many operations call for connecting to source data external to the cube. Connections and Datasources, which you create and save as reusable objects in Oracle Essbase, provide a way to do this efficiently.

If you have network connectivity between an external source of data and Essbase, you can define connections and Datasources in Essbase to easily "pull" data from the external source. If you have no network connectivity between Essbase and the external source of data, then you should stream data loads or dimension builds using the CLI tool, first creating a local connection, and then issuing the dataload or dimbuild command with the stream option.

A connection stores information about an external server and the login credentials that are required to access it. By defining one connection that can be used by multiple processes and artifacts, you can simplify many aspects of your analytics. For example, when it’s time to change a system password, you only need to update one connection.


Sources > Connections interface in the application inspector for Sample Basic block storage application. The following connections have been created: 1) Name: OracleDB, Type: Oracle Database, Description: Connection to Oracle Database. 2) Name: Essbase2, Type: Essbase, Description: Connection to Essbase instance 2. 3) Name: UserDetails, Type: File, Description: CSV file of user details

A Datasource is another object that you can define once and reuse, to help you manage data flow into and out of your cubes. You can define a Datasource to represent any external source of data, whether a relational system, a table, a file, or another cube.


Sources > Datasources interface in the application inspector for Sample Basic block storage application. The following Datasources have been created: 1) Name: UserDetails_DS, Connection: SAMPLE.UserDetails, Description: User details repository. 2) Name: Essbase2_DS, Connection: SAMPLE.Essbase2, Description: Connection to Essbase instance 2. 3) Name: OracleDB_DS, Connection: SAMPLE.OracleDB, Description: SAMPLE_BASIC_TABLE on Oracle Database

You can define one connection and use it to access multiple Datasources. For example, consider an external Oracle Database server that has separate tables for products, resellers, and sales territories. You need only one connection to access Oracle Database, but you might want to create unique Datasources to access each of the tables.

One use case in which you might define multiple Datasources per connection is as follows: if you use separate load rules to build each dimension in a cube, each rules file can be set up to access the relevant table in Oracle Database. For example, assume your cube has a Market dimension, and you regularly build dimensions using a Dim_Market load rule to populate the Market dimension from a SALES_TERRITORIES table. Likewise, you use a Dim_Product load rule to populate the Product dimension from a PRODUCT table. Both load rules can use the same connection, but because they draw from separate tables, you defined two different Datasources.

Historically, you needed to hard code connection and source data details into Essbase artifacts such as rule files, location aliases, and partitions. While hard coded information is still supported in these artifacts, you can work more efficiently if you define connections and Datasources globally (or, at the application level).

Create Connections and Datasources

Before you can create connections to external source data from Essbase, you must get the connection details such as host names, user names, passwords, and any other service credentials from your system administrator.

Create a Global Connection

To create a global connection (accessible to multiple applications),

  1. Log in to the Essbase web interface as a service administrator.

  2. Click Sources.


    Sources icon in Essbase web interface

  3. Click Create Connection and select the source you need to connect to. Sources and versions included with Essbase are listed in the Database section of the certification matrix (see the Platform SQL table). If you want to use your own preferred JDBC driver that you will upload, refer for details to Create Connections and Datasources for Generic JDBC Drivers.


    Create Connection button with connection choices: Oracle Database, Essbase, File, Spark, DB2, SQL Server, MySQL, and JDBC

  4. Complete the connection details and save the connection. Next, create one or more Datasources that use the connection. The input details vary depending on the source type.

Create an Application-level Connection

To create an application-level connection (accessible to only that application),

  1. Log in to the Essbase web interface as an application manager, or as a power user with application management permission to the specified application.

  2. On the Applications page, click the Actions menu to the right of the application name, and click Inspect.

  3. Click the Sources tab.


    Sources tab in the application inspector for application named Sample. The Connections area is showing, with two connections already created: one named OracleDB and one named Essbase2.

  4. Click Create Connection and select the source you need to connect to. Supported sources and versions included with Essbase are listed in the Database section of the certification matrix (see the Platform SQL table). If you want to use your own preferred JDBC driver that you will upload, refer for details to Create Connections and Datasources for Generic JDBC Drivers.

  5. Complete the connection details and save the connection. Next, create one or more Datasources that use the connection. The input details vary depending on the source type.

Source-Specific Workflows

For some source-specific workflows, see the following topics:

Create a Connection and Datasource to Access Oracle Database

Define a connection and Datasource between Essbase and Oracle Database.

If applicable, use one of the following subtopics instead of this one:

  1. In Essbase, on the Sources page, click Connections.
    Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources. The example used in this topic is an application level connection defined on Sample Basic.
  2. Click Create Connection and select Oracle Database.
  3. Enter a connection name, host, port number, user name, and password. When you enter the User name, enter the Oracle Database user name, without the role. Select SID (server ID) or Service, and enter server details.

    Create connection dialog for creating connection to Oracle Database. Name: OracleDB. Host: 198.51.100.165. Port: 1521. User: essbase. Password: (obscured). SID is checked and the value is orcl. Description: Connection to Oracle Database

  4. Click Test to validate the connection, and if successful, click Create.
  5. Verify that the connection was created successfully and appears in the list of connections.
    Next, you will create a Datasource for the Oracle Database connection.
  6. Click Datasources, and click Create Datasource.
  7. From the Connection drop-down box, select the name of the connection you just created; for example, SAMPLE.OracleDB. Application-level connections are prefixed with the application name, in the format appName.connectionName.
  8. Provide a name for the Datasource; for example, OracleDB_DS.
  9. Optionally enter a description of the Datasource; for example, SAMPLE_BASIC_TABLE on Oracle Database.
  10. In the Query field, provide the appropriate SQL query that selects the Oracle Database data you want to make available in this Datasource.

    Create Datasource dialog, General step, for creating Datasource to Oracle Database. Connection: SAMPLE.OracleDB, Name: OracleDB_DS, Description: SAMPLE_BASIC_TABLE on Oracle Database, Query: select * from SAMPLE_BASIC_TABLE

  11. Click Next. If the SQL statement was correct to query an Oracle Database area, you should see the queried columns populated.

    Create Datasource dialog, Columns step, for creating Datasource to Oracle Database. Index 1: Name: DIMENSION_PRODUCT, Type: String. Several more columns with other names are listed in order, some of type String, and some of type Double.

  12. Change any numeric columns to Double, and click Next.
  13. Change any additional source-specific parameters, if applicable, and click Next. For information about parameter use, see Implement Parameters for Datasources.
  14. Review the preview panel. You should see the results of the SQL query fetching columns of data from Oracle Database.

    Create Datasource dialog, Preview step, for creating Datasource to Oracle Database. Preview data from Oracle Database is shown, with values under the column names.

  15. If the preview looks correct, click Create to finish creating the Datasource.
Create a Connection and Datasource for Oracle Autonomous Data Warehouse

Define a connection and Datasource between Essbase and Autonomous Data Warehouse.

If you will create a federated partition between Essbase and Autonomous Data Warehouse on Shared Infrastructure, use the following topic instead of this one: Create a Connection for Federated Partitions.

To create a global connection, you need to have the service administrator role. To create an application level connection, you need to have user role, plus application manager permission on the application.

  1. In Essbase, on the Sources page, click Connections.
    To define the connection and Datasource at application level, instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources.
  2. Click Create Connection and select Oracle Database.
  3. Select Autonomous using the toggle switch.

    Image of the Create Connection dialog box, showing how to create a connection from Essbase to Oracle Autonomous Data Warehouse

  4. Enter a connection name.
  5. Select a service name.
  6. If needed, drag and drop a wallet file, or click the Wallet File field to upload one.

    If you are using a connection which has already been made available to you (a repository connection), you do not need to upload a wallet, because it should already be in the repository. Select the Repository Database option.


    Repository Database option selected

    If you need to upload a wallet, obtain a wallet file by selecting Download Client Credentials (Wallet) from your Autonomous Data Warehouse Administration page in Oracle Cloud Infrastructure.

  7. Enter your Autonomous Data Warehouse username, password, and optionally, a description.
  8. Click Test to validate the connection, and if successful, click Create.

    If you get connection errors, you may need to expand Advanced Options to adjust the minimum and maximum connection pool sizes.


    Advanced Options with Min Pool Size 5, Max Pool Size 50

    See About Controlling the Pool Size in UCP in Universal Connection Pool Developer's Guide.

  9. Verify that the connection was created successfully and appears in the list of connections.
  10. Next, you will create a Datasource for the Autonomous Data Warehouse connection. Click Datasources, and click Create Datasource.
  11. From the Connection drop-down box, select the name of the connection you just created; for example, EssbaseADW. For application-level Datasources, select the application-level connection name, in the format appName.connectionName.
  12. Provide a name for the Datasource; for example, ADW_DS.
  13. Optionally enter a description of the Datasource; for example, Autonomous Data Warehouse Datasource.
  14. In the Query field, provide the appropriate SQL query that selects the Autonomous Data Warehouse data you want to make available in this Datasource.
  15. Click Next. If the SQL statement was correct to query an Autonomous Data Warehouse area, you should see the queried columns populated.
  16. Change any additional source-specific parameters, if applicable, and click Next.
  17. Review the preview panel. You should see the results of the SQL query fetching columns of data from Autonomous Data Warehouse.
  18. If the preview looks correct, click Create to finish creating the Datasource.

Create a Connection and Datasource to Access Another Cube

Define a connection and Datasource between two Essbase cubes (on different instances).

  1. In Essbase, on the Sources page, click Connections.
    Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources. The example used in this topic is an application level connection defined on Sample Basic.
  2. Click Create Connection and select Essbase.
  3. Enter a connection name; for example, Essbase2.
  4. Either enter the Host and Port information, or check the box to Use URL. Connection information can be provided by your Service Administrator.

    Create connection dialog for creating a connection to another Essbase instance. Name: Essbase2. Use URL is checked. URL is https://192.0.2.1:443/essbase/agent. Host and Port are blank because Use URL is checked. User: admin. Password (obscured). Description: Connection to Essbase instance 2

    If you are using the URL, use the discovery URL format. A discovery URL is the URL provided by your Service Administrator, with /agent appended to the end. For example: 

    https://192.0.2.1:443/essbase/agent
  5. Click Test to validate the connection, and if successful, click Create.
  6. Verify that the connection was created successfully and appears in the list of connections.
    Next, you will create a Datasource for the Essbase connection.
  7. Click Datasources, and click Create Datasource.
  8. From the Connection drop-down box, select the name of the connection you just created.
  9. Enter a name for the Datasource, and an optional description.
  10. Select the application and database that will be used for this Datasource.
  11. Provide a valid MDX query that selects the cube data you want to make available in this Datasource.

    Create Datasource dialog, General step, for creating Datasource to another Essbase instance. Connection: SAMPLE.Essbase2, Name: Essbase2_DS, Description: Connection to Essbase instance 2, Application: Sample, Database: Basic, MDX Query: Select {Market} on columns, {Product} on rows from Sample.basic

  12. Click Next. If the MDX syntax was correct to query the remote cube, you should see the queried columns populated.
  13. Change any numeric columns to Double, and click Next.

    Create Datasource dialog, Columns step, for creating Datasource to another Essbase instance. Index 1: Name: Product, Type: String. Index 2: Name: Market, Type: String

  14. Change any additional source-specific parameters, if applicable, and click Next.
  15. Review the preview panel. You should see the results of the MDX query fetching columns of data from the other cube.

    Create Datasource dialog, Preview step, for creating Datasource to another Essbase instance. Preview data from Essbase is shown.

  16. If the preview looks correct, click Create to finish creating the Datasource.

Create a Connection and Datasource to Access a Data File

Define a connection and Datasource between Essbase and a source data file.

  1. Upload the source data file to the file catalog on Essbase.
    If you need a sample source data file for this task flow, you can copy and paste UserDetails.csv from the gallery section of the file catalog to your application's file catalog (or reference it without moving it). It represents a data repository of 22 users, with their associated countries, cost centers, currency, managers, company, business units, and offices.
  2. In Essbase, on the Sources page, click Connections.
    Or, to define the connection and Datasource at application level instead of globally, start on the Applications page instead of the Sources page. From the Actions menu to the right of an application name, launch the inspector and click Sources. The example used in this topic is an application level connection defined on Sample Basic.
  3. Click Create Connection and select File.
  4. Enter a name for the connection; for example, UserDetails.
  5. Provide the catalog path to the source data file.
  6. Enter an optional description; for example, CSV file of user details

    Create connection dialog for creating connnection to a file in the Essbase server catalog. Name: UserDetails, Path: /gallery/Technical/Filters/UserDetails.csv, Description: CSV file of user details

  7. Click Test to validate the connection, and if successful, click Create.
  8. Verify that the connection was created successfully and appears in the list of connections.
    Next, you will create a Datasource for the file connection.
  9. Click Datasources, and click Create Datasource.
  10. From the Connection drop-down box, select the name of the connection you just created; for example, UserDetails.
  11. Enter a name for the Datasource, and an optional description.
  12. Essbase detects and enters details about the source data; for example, whether it has a header row, and is comma-delimited. Click Next.

    Create Datasource dialog, General step, for creating Datasource to a file on the Essbase server. Connection: SAMPLE.UserDetails, Name: userDetails_DS, Description: User details repository. Header Row is checked, because Essbase detected that the CSV has a header record. Delimiter is Comma, because Essbase detected that the file is comma delimited

  13. You should see the columns populated from the file source. Change any numeric columns to Double, and click Next.
  14. If the preview looks correct, click Create to finish creating the Datasource.
If you update the source file metadata (for example, to add columns), you must recreate the Datasource.

Create Connections and Datasources for Generic JDBC Drivers

Use this workflow to enable Essbase to connect to any JDBC source of data, using drivers you upload to the Essbase Server.

If you are the Essbase deployment administrator, you can configure Essbase to use your preferred drivers you upload to the Essbase server machine. Oracle has tested Essbase JDBC connectivity using Oracle drivers. To use JDBC drivers from other vendors, check the driver documentation for requirements on specifying the URL and credentials of your JDBC data source. For any performance-related steps, refer to the vendor JDBC documentation.

Note:

Ensure that the JDBC driver you use with Essbase honors the setFetchSize method for controlling memory used while processing the result set. For optimal performance of data load and dimension build processes, Essbase fetches 1000 records per network call.

To configure Essbase to use generic JDBC drivers,

  1. Connect to the Essbase server machine using SSH.

  2. Manually create a drivers directory in <Essbase Product Home> on the server instance.

    Ensure drivers is all lower case, as the path is case sensitive.

  3. From your vendor site, download the JDBC driver JARs you want to use.

    The Oracle Database JDBC driver supported by Essbase is ojdbc8.jar.

    If you use Autonomous Data Warehouse, you need to download the full archive (ojdbc8-full.tar.gz) containing the Oracle JDBC Thin driver and companion JARs.

  4. Upload the JDBC driver jars to the drivers directory on the Essbase instance.

    Upload only one version of each database driver to the drivers directory. For example, do not upload both sqljdbc41.jar and sqljdbc42.jar, or else Essbase will use the older one (as it appears first in CLASSPATH).

    If you use Autonomous Data Warehouse, extract the archive (ojdbc8-full.tar.gz) and move all of the contents directly into the drivers directory (not a subfolder).

  5. Create connections to the JDBC drivers.

    1. In the Essbase web interface,

      • to create a global connection: on the Sources page, click Connections.
      • to create an application-level connection: from the Actions menu to the right of an application name, launch the inspector and click Sources.

    2. Click Create Connection and select JDBC.

      To find the JDBC driver, Essbase looks in the drivers folder. If no jar files are found, Essbase returns a Class Not Found (or failure to load driver) error when you test the connection.

    3. In the Create Connection screen,

      1. Provide a name for the JDBC connection. For example, OrclJDBC.

      2. In the URL field, provide the JDBC connection string. For example, jdbc:oracle:thin:@myserver:1521:orcl. Obtain the JDBC connection string from the JDBC provider.

      3. For User and Password fields, enter the credentials for a user who is authorized to access the database.

      4. In the Driver field, provide the fully qualified class name of the JDBC driver. For example, oracle.jdbc.driver.OracleDriver.


      Create Connection dialog for JDBC driver connection. Name: OrclJDBC, URL: jdbc:oracle:thin:@myserver:1521:orcl, User: essbase, Password: (obscured), Driver: oracle.jdbc.driver.OracleDriver, Description: My Oracle JDBC 8 connection

      For Oracle drivers, specify the URL using the following syntax guidelines:

      • To use the Oracle System ID (SID) that uniquely identifies the database, use the syntax jdbc:oracle:thin:@<host>:<port>:<SID>. For example, 

        jdbc:oracle:thin:@myhost:1521:orcl

      • If Oracle Database is registered with a listener, you can use Service Name in the URL instead of the SID, using short syntax jdbc:oracle:thin:@<host>:<port>/<servicename>. Example: 

        jdbc:oracle:thin:@myhost.example.com:1521/orcl.esscs.myhost.example.com

      • The following example uses Service Name with long syntax.

        jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(host=myhost.example.com)(protocol=tcp)(port=1521))(CONNECT_DATA=(SERVICE_NAME=orcl.example.com)))

      • If you are using Autonomous Data Warehouse, in the URL syntax, you must include the TNS_ADMIN environment variable specifying the path to the wallet. The wallet can be anywhere on the Essbase server machine, but you must provide the full path, using syntax like jdbc:oracle:thin:@<database_service_name>?TNS_ADMIN=<walletpath>.

        Linux Example

        jdbc:oracle:thin:@adwsql_low?TNS_ADMIN=/scratch/oracle_home/dist/essbase/drivers/adwConn

        Windows Example

        jdbc:oracle:thin:@adwsql_low?TNS_ADMIN="C:\\Oracle123\\Middleware\\Oracle_Home\\essbase\\drivers\\adwConn"

        Example on an OCI Deployment

        jdbc:oracle:thin:@adwsql_low?TNS_ADMIN=/u01/data/essbase/catalog/users/firstname.lastname@example.com/adwconn

    4. Click Test to validate the connection, and if successful, click Create.

    5. Verify that the connection was created successfully and appears in the list of connections.

  6. Create Datasources over the generic JDBC driver connections.

    1. Click Datasources, and click Create Datasource.

    2. From the Connection drop-down box, select the name of the connection you just created; for example, SAMPLE.OrclJDBC. Application-level connections are prefixed with the application name, in the format appName.connectionName.

    3. Provide a name for the Datasource; for example, OrclJDBC_DS.

    4. Optionally enter a description of the Datasource; for example, SAMPLE_BASIC_TABLE on Oracle Database.

    5. In the Query field, provide the appropriate SQL query that selects the data you want to make available in this Datasource.

    6. Click Next. If the SQL statement was correct to query a table, you should see the queried columns populated.

    7. Change any numeric columns to Double, and click Next.

    8. Change any additional source-specific parameters, if applicable, and click Next. For information about parameter use, see Implement Parameters for Datasources.

    9. Review the preview panel. You should see the results of the query fetching columns of data from the external source.

    10. If the preview looks correct, click Create to finish creating the Datasource.

Implement Parameters for Datasources

To make Datasources more flexible, you can implement runtime parameters in the query to allow the Datasource to use variables.

These may be substitution variables defined in Essbase, runtime parameters defined by the grid context when Smart View users drill through to external data, or user defined functions written in an external source system.

Whenever you plan to use variables in Essbase Datasources, you first need to

  1. Include variable syntax in the Datasource query. For example, the Datasource query must include ? in its syntax, where the ? is a placeholder for some variable to be defined at runtime.

  2. Do one of the following:

    • Set a fixed, default parameter value in the Datasource for Essbase to use as a fallback in case the variable has an invalid context at runtime, OR

    • Set a substitution variable to be used by the Datasource

    • Pass an external, user-defined function (or stored procedure) as a parameter

To define Datasources and implement parameters for them, you must be an application manager or higher.

Enabling the use of variables in Essbase Datasources helps you streamline operations by requiring fewer Datasources to maintain. Implementing variables to Datasources enables you to specify a runtime query context that will be applied whenever a user accesses a Datasource associated with an Essbase cube.

For example, assume the following use cases.

  • A database manager oversees a recurring data load job that loads data to the cube on a monthly basis. The database manager can now use a substitution variable to load data for the current month, instead of maintaining a load rule for each month.

  • An application manager maintains drill through report definitions for different business use cases. The application manager implements variables in the underlying Datasource that Smart View users pull from in their drill through operations. As a result, the application manager has fewer drill through report definitions to maintain and debug.

Set a Default Parameter in a Datasource

Set a default parameter in a Datasource if you want to enable the use of variables in the queries Essbase generates when it works with data stored outside the cube.

To set the default parameter,

  1. Obtain or create a connection to the external source of data (for example, create a connection to Oracle Database).

    1. You can use a global connection, if one already exists in the Sources page of the Essbase web interface, OR,

    2. You can create an application level connection:


      Image of an application level connection to Oracle Database, named oracledb_conn.

  2. Create a Datasource over the connection you will use to access Oracle Database.

    Again, note that you can define a Datasource globally if it should be available to all applications, or you can define it at the application level.

    1. In the General step, for Connection, select the Oracle Database connection you created.

    2. For Name, give a name to the Datasource.

    3. For the Query, provide a query (this example uses SQL). To make it a parameterized query, you must include a filter condition (WHERE clause) that maps a relational column in your source to a placeholder. You indicate the position of the variable by using a placeholder, ?, in the query syntax. The placeholder is for a parameter you will pass in a later step. 

      select * from SB_DT where DIMENSION_YEAR=?

      For example, assume your relational database has the following table, named SB_DT. The table has DIMENSION_YEAR column with months as values:
      Image of a relational database table with the DIMENSION_YEAR column selected. All its values are months: Jul, Aug, Aug, Sep, Sep, Oct, etc

      To use a variable for the selection of month values from the DIMENSION_YEAR column, apply the following filter syntax in the query: where DIMENSION_YEAR=?
      Image of the General tab in the create Datasource wizard. Connection: SAMPLE.oracledb_conn, Name: oracledb_ds, Description: Datasource for Oracle DB, Query: select * from SB_DT where dimension_year=?

    4. Click Next.

    5. In the Columns step, apply the appropriate data type that Essbase should associate with each column from your relational source data.

      For example, set the numeric columns to type Double, and leave the alphanumeric columns as type String.


      Image of the Columns tab in the Create Datasource wizard, with Type selections, double and string.

    6. Click Next.

    7. In the Parameters step, Param1 is created – this parameter exists because you used a ? in the query on the General step.

      Leave Use Variables unchecked, double-click the text field under Value, and type in a default value for the runtime parameter. The purpose of this default value is for Essbase to use as a fallback in case the parameter has an invalid context at runtime. This step is important if you intend to use runtime parameters as part of drill through report definitions.

      You can also rename Param1 to a name that is meaningful for your use case. For example, you can rename it to param_G_month to indicate that the parameter uses a global variable for the current month, or you can rename it to param_<appName>_month to indicate that the parameter uses an application-level variable for the current month. Customizing the parameter names can be helpful when debugging parameters using Essbase server log files.


      Image of the Parameters tab in the create Datasource wizard. Use Variables is unchecked, and the Value entered is Jan.

      If you want to customize the parameter to reference a substitution variable, then you do not have to provide a default value. See Use Substitution Variables in a Datasource instead of this topic.

    8. Click Next.

    9. In the Preview, notice that the default parameter has been applied to your query. As a result, the preview is populated only with external source records in which the value of DIMENSION_YEAR column is Jan.


      Image of the Preview tab in the create Datasource wizard, showing only records where DIMENSION_YEAR = Jan.

      Although the preview only displayed values with the default parameter applied, later, when you implement runtime parameters for drill through report definition, you will have access to more external data than what was visible in the preview.

    10. Click Create to create the Datasource based on this query of your external source data. The Datasource is enabled for implementation of runtime parameters.

Use Substitution Variables in a Datasource

The following workflow illustrates how to create an Essbase Datasource from a query of external source data, using a substitution variable defined in Essbase. The substitution variable adds more flexibility to how you design the query that pulls from your source data.

In this example, you will use a substitution variable in Essbase to declare the current month. Instead of updating Datasources monthly to pull in data for the current month, you can leave the Datasources alone, and only update the substitution variable you defined.

  1. Create a global or application level substitution variable.


    Image of a substitution variable created at the application level. Name: mnth, Value: Aug

  2. Obtain or create a connection to the external source of data (for example, create a connection to Oracle Database).

    1. You can use a global connection, if one already exists in the Sources page of the Essbase web interface, OR,

    2. You can create an application level connection:


      Image of an application level connection to Oracle Database, named oracledb_conn.

  3. Create a Datasource over the connection you will use to access Oracle Database.

    Again, note that you can define a Datasource globally if it should be available to all applications, or you can define it at the application level.

    1. In the General step, for Connection, select the Oracle Database connection you created.

    2. For Name, give a name to the Datasource.

    3. For the Query, provide a query (this example uses SQL). To make it a parameterized query, you must include a filter condition (WHERE clause) that maps a relational column in your source to a placeholder. You indicate the position of the variable by using a placeholder, ?, in the query syntax. The placeholder is for a parameter you will pass in a later step. 

      select * from SB_DT where DIMENSION_YEAR=?

      For example, assume your relational database has the following table, named SB_DT. The table has DIMENSION_YEAR column with months as values:
      Image of a relational database table with the DIMENSION_YEAR column selected. All its values are months: Jul, Aug, Aug, Sep, Sep, Oct, etc

      To use a variable for the selection of month values from the DIMENSION_YEAR column, apply the following filter syntax in the query: where DIMENSION_YEAR=?
      Image of the General tab in the create Datasource wizard. Connection: SAMPLE.oracledb_conn, Name: oracledb_ds, Description: Datasource for Oracle DB, Query: select * from SB_DT where dimension_year=?

    4. Click Next.

    5. In the Columns step, apply the appropriate data type that Essbase should associate with each column from your relational source data.

      For example, set the numeric columns to type Double, and leave the alphanumeric columns as type String.


      Image of the Columns tab in the Create Datasource wizard, with Type selections, double and string.

    6. Click Next.

    7. In the Parameters step, Param1 is created – this parameter exists because you used a ? in the query on the General step. To customize Param1 to reference a substitution variable, click Use Variables, and select a substitution variable from the Value drop-down list.

      If you are creating a Datasource within an application, both global and application-level substitution variables are available to select. The application-level variables are prefixed with the application name. If you are creating a global Datasource, only global substitution variables are available to select.

      You can rename Param1 to a name that is meaningful for your use case. For example, you can rename it to param_G_month to indicate that the parameter uses a global variable for the current month, or you can rename it to param_<appName>_month to indicate that the parameter uses an application-level variable for the current month. Customizing the parameter names can be helpful when debugging parameters using Essbase server log files.


      Image of the Parameters tab in the create Datasource wizard. Use Variables is checked, and the Value is Sample.mnth.

    8. Click Next.

    9. In the Preview, notice that the substitution variable is applied to your query. As a result, the preview is populated only with external source records in which the value of DIMENSION_YEAR column is Aug.


      Image of the Preview tab in the create Datasource wizard, showing only records where DIMENSION_YEAR = Aug.

    10. Click Create to create a Datasource based on this query of your external source data.