Dodeca PBCS/EPM Cloud Connector - Version {pbcs-connector-version}

Background

The Dodeca PBCS Connector provides integration between Dodeca views and a PBCS/EPM Cloud server. It uses the PBCS REST API to perform data and metadata operations, such as grid retrieves and fetching member information.

In this document, the names "PBCS Connector" and "EPM Cloud Connector" are used interchangeably. This connector can also be used to provide support in Dodeca for FCCS (Financial Consolidation and Close), PCM (Profitability and Cost Management), and other EPM Cloud modules that use the same core Planning REST APIs.

Connector Settings

Proxy Configuration

The Dodeca PBCS Connector can be configured to use a proxy for communications between itself and your PBCS (EPM Cloud) server. In order to do so, two settings will need to be added to your wrapper.conf configuration file:

wrapper.java.additional.4=-Dhttps.proxyHost=localhost
wrapper.java.additional.5=-Dhttps.proxyPort=8080

Configure the name and port for your proxy server instead of localhost and 8080. If you already have additional settings in the configuration file, use the next available number if it is not 4 (so that you end up with a continuous set of settings numbers).

Enable Additional Logging

More fine-grained output/debug logging information can be shown in the log by adjusting to a higher level such as DEBUG or TRACE. These settings should not be used long-term in production. To enable additional logging for certain modules, the following lines can be used:

wrapper.java.additional.X=-Dlogging.level.com.appliedolap=TRACE
wrapper.java.additional.X=-Dlogging.level.com.jasonwjones=TRACE

Replace X with the next incremental setting number in your wrapper.conf configuration file. In the previous example, the first line indicates the logging level for the connector, while the second line specifically enables additional logging for the Java PBCS module.

Connection Configuration

The PBCS Connector currently only supports using credentials from a native PBCS account (this is a limitation in the PBCS REST API). A future release will add additional support for other user types.

In order to create a PBCS connection in Dodeca, create an Essbase connection as usual. The fields that need to be configured are the following:

Server

The PBCS/EPM Cloud server. This will typically be a server name such as companyname.epm.us-phoenix-1.ocs.oraclecloud.com

Application

The name of the PBCS application, such as Vision

Database

The name of the plan type (cube). The database setting is also used to explicitly define the names of the dimensions in the cube. In order to specify the dimensions, the value of the Database parameter should be in the following format: Plan1?dimensions=Account;Currency;Entity;Period;Scenario;Version;Years. In other words: the name of the plan type/cube, followed by a question mark, the word dimensions, an = symbol, and then the semicolon delimited list of dimensions in the plan. The dimension list is important as it allows the connector to understand the dimensions available without having to reach into other Oracle APIs that may have permissions issues or report the wrong dimension list.

ServletPath

Address to the server running the connector, including protocol and port, such as http://pbcs-connector.example.com:9440.

Username

You will likely want to include the username of a fixed admin or otherwise native user to use for connecting.

Password

You will likely want to include the password of a fixed admin or otherwise native user to use for connecting.

Once the connection details are filled in, save and test the connection. You may now build views that use the PBCS data in a retrieve range.

Outline Caching

Dodeca PBCS/EPM Connector 1.6 and higher provided advanced caching capabilities. Whenever the connector needs to resolve member metadata (such as part of the retrieve process for a grid), the member information will be cached for subsequent operations. The member cache can be preloaded ahead of time to improve performance without having to query members first. The outline preload process can be accomplished using the conncli.jar tool. This tool is shipped in the /tools folder of the connector.

This tool requires Java 8 or newer to run. Additionally, before running any commands, a context and login information must be defined. The context information contains connection information such as the location of the connector server, application name, database name, dimension list, and more. Parameters are as follows:

Connector Server

the URL to the PBCS connector, including protocol and port (if using a non-standard port). For example: http://pbcs-connector.example.com:9440

Server

the Oracle PBCS server address, such as yourcompany-test-yourcompany.epm.us-phoenix-1.ocs.oraclecloud.com

Application

the name of the EPM application, such as Vision.

Database

the name of the cube/database/plan in the application, such as Plan1

Dimensions

list of dimensions in the cube, such as Account, Currency, Entity, Period, Product, Scenario, Version, Year

Dimensional Query Expressions

EPM Cloud does not support traditional report scripts or member queries. The EPM Cloud Connector provides a custom expression language called Dimensional Query Expressions to support many of these use cases for populating selector lists.

Examples

Shows that % is alias for intersection function

Expression:

(Children('Q1') % Regex(Descendants('YearTotal'), 'J.*'))

Result:

[Jan]

Subtract one set from another

Expression:

Descendants('YearTotal') - Leaves(Member('YearTotal'))

Result:

[YearTotal, Q1, Q2, Q3, Q4]

Combination of descendants and regex

Expression:

Descendants('YearTotal', 0) - Regex(Descendants('YearTotal', 0), 'J.*')

Result:

[Feb, Mar, Apr, May, Aug, Sep, Oct, Nov, Dec]

Expand shared members to include the descendants of the original member

Expression:

Shares(Descendants('Administrative VP'))

Result:

[Administrative VP, Administrative Director 1, 730, 740, 750, 760, 770, 830, 840, Administrative Director 2, 600, 710, 720, 810, 815, CEO, CFO]

Regex to return only items that start with 1-5

Expression:

Regex(Leaves('Entity'), '^[1-5].*')

Result:

[110, 111, 112, 120, 130, 140, 210, 220, 230, 110, 111, 112, 120, 130, 140, 210, 220, 230, 240, 410, 420, 421, 422, 423, 430, 440, 450, 501, 509, 510, 511, 519, 520, 530, 535, 540, 550, 555, 560, 570, 575, 580, 585, 590, 595, 240, 410, 420, 421, 422, 423, 430, 440, 450, 501, 509, 510, 511, 519, 570, 575, 580, 585, 590, 595, 520, 530, 535, 540, 550, 555, 560]

The children function returns the children of the given member (shows function names are case-insensitive)

Expression:

CHILDREN('Q1')

Result:

[Jan, Feb, Mar]

Return just the level-0 descendants (leaves) of the given member/set

Expression:

Leaves('YearTotal')

Result:

[Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec]

Descendants with a generation (positive values)/level (negative values and zero) filter results in only descendants with the given level

Expression:

Descendants('YearTotal', -1)

Result:

[Q1, Q2, Q3, Q4]

Explicit reference to a member

Expression:

Member('Q1')

Result:

[Q1]

Children of the children results in all descendants that are two levels below the original member

Expression:

Children(Children('YearTotal'))

Result:

[Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec]

Quarters the hard way

Expression:

Descendants('YearTotal') - Children(Children('YearTotal')) - Member('YearTotal')

Result:

[Q1, Q2, Q3, Q4]

Simple string concatenation

Expression:

Children('Year' + 'Total')

Result:

[Q1, Q2, Q3, Q4]

Adding two member sets results in a single member set with all items

Expression:

Children('Q1') + Children('Q1')

Result:

[Jan, Feb, Mar, Jan, Feb, Mar]

Adding two member sets results in a single list with orders preserved

Expression:

Children('Q2') + Children('Q1')

Result:

[Apr, May, Jun, Jan, Feb, Mar]

All descendants including the original member

Expression:

Descendants('YearTotal')

Result:

[YearTotal, Q1, Jan, Feb, Mar, Q2, Apr, May, Jun, Q3, Jul, Aug, Sep, Q4, Oct, Nov, Dec]

Filter out items NOT starting with 1-5

Expression:

Regex(Leaves('Entity'), '^[^1-5].*')

Result:

[No Entity, Unspecified Entity, Enterprise Global, 000, 000, 600, 710, 720, 730, 740, 750, 760, 770, CEO, CFO, 810, 815, 830, 840, 730, 740, 750, 760, 770, 830, 840, 600, 710, 720, 810, 815, CEO, CFO]

null

Expression:

(Children('Q1') ^ Children('Q2')) ^ Regex(Descendants('YearTotal'), 'J.*')

Result:

[Feb, Mar, Apr, May, Jul]

Apply a regular expression (regex) so that only members from the given set that match are returned (regex J.* matches a J followed by anything

Expression:

Regex(Descendants('YearTotal'), 'J.*')

Result:

[Jan, Jun, Jul]

Return intersection of Q1 children as well as members that start with J

Expression:

Intersection(Children('Q1'), Regex(Descendants('YearTotal'), 'J.*'))

Result:

[Jan]

Converting a member set to a distinct list of members

Expression:

Distinct(Children('Q1') + Children('Q1'))

Result:

[Jan, Feb, Mar]

Create member set with the literal member names

Expression:

Member('Q1', 'Q2')

Result:

[Q1, Q2]

Already level-0, returns member itself

Expression:

Leaves(Member('Jan'))

Result:

[Jan]

Fetch descendants without expanding shares

Expression:

Descendants('Manufacturing VP')

Result:

[Manufacturing VP, Operations Director, 501, 509, 510, 511, 519, 570, 575, 580, 585, 590, 595, Plant Director, 520, 530, 535, 540, 550, 555, 560]

Returns members that are in one set or the other, not both

Expression:

(Children('Q1') ^ Children('Q2'))

Result:

[Jan, Feb, Mar, Apr, May, Jun]

The children function returns the children of the given member

Expression:

Children('Q1')

Result:

[Jan, Feb, Mar]

Removes one set of members from another (but easier to use the minus (-) operator

Expression:

Difference(Descendants('YearTotal'), Leaves(Member('YearTotal')))

Result:

[YearTotal, Q1, Q2, Q3, Q4]

Running the Connection CLI Tool

It is easiest to run the tool if Java exists on your system path, enabling you to simply type java to execute. From the /tools folder, run the CLI with the following command:

java -jar conncli-jar-with-dependencies.jar

You should see output such as the following:

Missing required subcommand
Usage: <main class> [COMMAND]
Commands:
  cache       Cache an outline
  calc        Run a calc script
  connect     Connect to a cube
  context     Context commands
  dimensions  Get dimensions
  login       Login to an Essbase connector server
  logout      Logout (remove persistent credentials)

This indicates that the tool successfully ran (but didn’t do anything, since no commands were given). You can list the available contexts with the following command:

java -jar conncli.jar context list

Resulting in output such as the following:

  • default

This output is showing that there is one context available, and the asterisk indicates that it is the currently selected context.

Configuring a Context

Contexts are helpful to delineate between development and production enviroments. You may wish to create a context for each, with names such as DEV and PRD. If you only have one environment, you may choose to use the default context, which is named default.

You can update some or all of the context settings (such as its server, application, and database) using the relevant parameters to the context update command. For example, to update the current context’s server, application, and database settings, run the following command:

java -jar conncli.jar context update --server example.com --application Vision --database Plan1

To describe the current context to view its values:

java -jar conncli.jar context describe

Which may yield the following:

INFO > Connector Server: http://localhost:9440
INFO > Server: yourcompany-test-yourcompany.epm.us-phoenix-1.ocs.oraclecloud.com
INFO > Application: Vision
INFO > Database: Plan1
INFO > Dimensions: [Account, Currency, Entity, Period, Product, Scenario, Version, Year]

After you have defined the server, connector server, application, and database, you can have the CLI tool automatically populate the dimension list for you:

java -jar conncli.jar context update --fetch-dimensions

With the following output:

INFO > Fetching dimensions

Update Connector Outline Cache

Once the context is properly defined with a connector server, server, application, database, and dimension list, you can launch the command to update the outline cache:

java -jar conncli.jar cache

This will cause the connector server to communicate with your PBCS/EPM application using the context information, fetch all outline information, and add it to the persistent outline cache in the connector (which will speed up various retrieve and other operations).

Release Notes

Version 1.7.6

  • Ships with updated Java Service Wrapper files, and includes support for macOS

  • Connector now returns connection properties to connection testing/troubleshooting in Dodeca 8.4+

Version 1.7.5

  • Fixed issue where selector lists may be missing first item in a hierarchy (such as Jan missing under Qtr1)

  • Performance optimization when querying a member that is a dimension

  • The "Ancestor on Top" option setting now defaults to false (the normal historical value), and is also now honored if set in retrieve options

  • Fixed issue where operations (such as zoom in) on a member that also has a share may result in incorrect results

  • Known issue: there is a potential issue with certain drill-to-bottom operations where an incomplete grid is coming back from the export data slice service

Version 1.7.4

  • Member levels and generations are now included as part of the outline cache. This greatly speeds up indenting, zoom out, and other operations. If a member changes level/generation then the cache may need to be refreshed to reflect the change

  • Attribute dimensions may now be specified using the attributeDimensions key on a connection string (they may still be specified in the dimensions key). This allows the connector to exclude attribute dimensions from certain operations such as the default retrieve

  • Added basic support for remove only (rows)

  • Fixed issue when zooming when using a vertically stacked POV

Version 1.7.3

  • Fixed issue where numeric values are being rounded to the nearest tenth of a dollar (e.g. the value 1000.65 may get rounded to 1000.70)

  • Fixed issue with dimensional query expressions (DQE) not working. DQEs now work correctly when used in a selector list with type EssbaseLegacyMemberQuery

  • Fixed issue when using Member Select dialog box

  • Fixed issue when a cached member selector referred to an alias name

  • Fixed issue when zooming to bottom or all levels

  • Added basic zoom out support

Version 1.7.2

  • Retrieve operations now support SmartList values in the response grid

Version 1.7.1

  • Fixed issue where querying for children/descendants of a member may return only the member itself if the member has a share in the outline

Version 1.7.0

  • Added dimension query expression script feature to allow querying outline metadata for use in selectors and other contexts

  • Support for blank rows and columns in retrieve ranges

  • POV may start in top left corner of a retrieve range (POV must be horizontal)

  • Added support for launching a business rule using run time prompt variables

  • Fix various issues with grid metrics that were affecting drill-through and member selection

  • Support for indentation styles on grids

  • Support suppress missing and suppress zero in grids

  • Support navigate without data

  • Better support for using aliases in grids

  • Enhance zoom in, zoom to bottom, and multi-axis zoom

  • Fixed issue with member names starting with numbers in the POV

Version 1.6.3

  • Fixed issue with caching member aliases and retrieving grid with aliases turned on

Version 1.6.2

  • Fixed issue where cube/plan object was not being reused between requests

  • Member cache is now case-insensitive for server and member name Application and plan name are still case-sensitive: care should be taken to ensure consistent casing when defining connections in Dodeca and connections in the conncli.jar tool

  • Fixed issue where member was being looked up twice

  • Fix Windows service description

  • Added searchThreads parameter to database connection string to use the specified number of threads when performing member/alias search

  • Fix grid issue where additional requests to lookup member names was not going through cache.

Version 1.6.1

  • Enhance conncli.jar to report more details on exceptions

Version 1.6

  • The connector now includes an advanced caching architecture that is persistent between restarts and can be pre-populated using the conncli.jar tool.

  • You can now launch a business rule (calc) with a given name (uses the CalculateFromFile connector method)

  • Attribute dimensions are now properly supported in retrieve grids (make sure to add attribute dimension name to connection string, such as Basic?dimensions=Scenario,Account,…​,Market Size)

Version 1.5.16

  • Added support for the connector to use a proxy when communicating with an Oracle EPM Cloud server

  • Upgraded version of PBJ library to 2.0.0