Dodeca PBCS/EPM Cloud Connector

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 environments. 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.15

  • Retrieves are now more robust to try to avoid an index out of bounds error. #4050

Version 1.7.14

  • Fixed issue where columns would be arranged incorrectly when retrieving a grid containing members that the user does not have access to. Note: cells that a user does not have access to appear as #Missing, as there is presently no way for the connector to differentiate between #NoAccess and #Missing. #4046

  • Finding members (using a selector) has been changed to default to case-insensitive search (underlying PBJ library updated). #4027

  • Member search (such as performed in a selector list) now searches aliases in addition to member names. #4028

Version 1.7.13

  • Fixed issue where member selection on a "top"/col dimension would throw exception. #4009

  • Fixed issue where extra members would be added to a retrieve/zoom when suppress missing is turned on. #4025

  • Fixed issue when navigate without data option is enabled. #4026

Version 1.7.12

  • Fixed issue when using label/invalid rows and suppress missing is turned on causing additional rows to get cleared

  • Fixed issue when using suppress missing when use both member/alias is turned on

Version 1.7.11

  • Fix support for member selection dialog. #3993

  • Provide support for basic member selection rules in member selection dialog (children, children including member, descendants, descendants including member). The "subset" rule does not work.

  • Add support for Preview button in member selection dialog

  • Support for dynamic logging level (Log Level for Current Session option in client)

  • Using Find inside a selector list now works (to search for members in by name)

  • Add additional logging to zoom in (enable debug or higher to generate)

  • Fixed issue preventing with data audit logging updates not being sent to Dodeca server

Version 1.7.10

  • Fix issue where incomplete rows (e.g. a row meant to be a label) were being dropped from the resulting retrieve

  • Enable compression on all responses

  • Turn off some debug grid printing in logs

  • Fix issue when left axis starts with blank rows causing the connector to think that use both member names/aliases was turned on when it wasn’t, resulting in an error

  • Fix issues with blank result grids with suppress missing is turned on

  • Don’t mark label rows as invalid members

  • Fixed issue with how cell counts are calculated for purposes of breaking extremely large retrieves into multiple, smaller retrieves (cell count was not taking into account row member cells, only data cells)

Version 1.7.9

  • Added optimization to prevent Member Selection dialog from taking a long time to open. #3875

  • Allow blank columns in the beginning of the "top" axis. #3982

  • Allow invalid member in first row of left axes. #3987

  • Enhanced support for keep/remove only operations so that they will work with multiple selections/ranges (however, these operations are currently based on row indices, not member names/groups)

  • Added clearCache=true parameter to cube/plan settings to force a complete cache clear (should only be used temporarily, e.g. set this parameter, then test the connection, then change it back)

  • Outline XML now indicates when members are shared. #3984

  • Outline XML now indicates attribute dimensions

  • Outline XML now supports the Default alias for members. #3971

  • Outline XML no longer shows additional member of same name as dimension under the dimension (e.g. Scenario had a child named Scenario, which has Actual, Budget, etc.). #3972

  • Fix where selector list might be missing some hierarchies (was due to level computation issue). #3869

  • Enhanced retrieve/ad hoc operations to avoid the 500,000 cell limit in EPM Cloud by creating multiple requests and stitching them together

  • Requests to EPM Cloud will now use the current suppress missing option, which may reduce response time for retrieve operations

Version 1.7.8

  • Blank rows are now allowed at the top of the "left" axes

  • Fixed issue where blank columns/rows would return "null" as cell value

  • Drill to all levels now honors ancestor on top option

  • Fixes to unknown member handling, support strict mode option

  • Fix zoom issue on horizontal/top axis

  • Drill to all levels returns members in correct order (parents on bottom as subtotals)

  • Fix issue with repeat member names option

  • Fix issue with Shares() DQE erroneously including parents

  • Introduce AllShares() DQE to expand all shares (can also nest Shares() calls)

  • Fix issue with using remove only on a top axis

  • Support Within Selected group ad hoc option

  • Support grids that result in no data (doesn’t cause error)

  • Support for Empty Grid Error option (to explicitly cause error when data grid is empty)

Version 1.7.7

  • Reworked Zoom In to more closely mimic conventional zoom behavior

  • Fixes to "use both members and aliases" option

  • Adjustments to unknown member handling

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+

  • Fixed issue with zoom operations returning members that aren’t in outline order

  • Added support for strict mode (Dodeca add-in option)

  • Added support for unknown members (list of unknown members is returned, retrieves skip invalid intersections unless strict mode is on)

  • Fixed a zoom issue when zooming on POV members

  • Added support for "use both members and aliases" option. Incoming grids may now provide a grid where each dimension appears twice (e.g. a row containing Jan, January) and the use both option will be honored in response grids

  • Adjusted indentation to only apply to rows but not members in the POV or headers

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