Out-of-the-Box Features: Profitability and Cost Management Cloud Service (PCMCS) – Intelligence and Dashboarding: Queries

Published September 11 2019 by Alecs Mlynarzek
Back to insights

Welcome back to the Profitability and Cost Management out-of-the-box features series!

Here, you’ll gain insight to fully leverage the features bundled with an Enterprise Cloud Subscription which includes Profitability and Cost Management. The focus of this post is: PCM queries – artifacts that represent or extract data in an easily consumable format.

At the end of this blog post, the below topics should be familiar to the reader:

  1. Define PCM Queries
  2. Queries Use Cases
  3. How to Launch Queries in PCM
  4. Query Options
  5. Data Extract Format
  6. Common Errors and Warnings
  7. Alternate Uses of PCM Queries

*The contents of this blog post are based on the standard Bikes (BkML30) application. Deploying the PCM Demo Bikes application can be achieved via the PCM landing page — “Creating a Sample application button” (from version 19.06 onwards).

1. What is a PCM Query? 

PCM Queries are predefined statements with execution mechanics like Smart View retrievals.

Queries can be launched in one of three ways:

  1. Via the PCM Graphical User Interface (GUI)
  2. Automatically through EPM Automate/REST API commands
  3. Within Dashboards and Intelligence analysis reports (covered in greater detail in a this previous post).

2. Queries Use Cases 

Queries are versatile artifacts that have a list of use cases limited only by the user’s imagination. The most common use cases are:

  1. Data Validation – leveraged both for input as well as post-allocated results. Queries can be created and stored in a PCMCS instance. Their definition is similar to a Smart View query, with Columns, Rows, and Point of View (POV) selections. More details are found in the queries options section. PCM Queries have drill-through capability – applicable only to base level queries, leveraging the Cloud Data Management functionality.
  2. Driver/Adjustment Data Entry Template – while queries do not rise to the capabilities of a PBCS/EPBCS web data entry form, they manage to solve the issue of “directional intersections” in an elegant manner. By defining the base level intersections where driver data should reside and storing that query definition, users avoid the need for offline sheets for data entry.
  3. Refined Data Clear Selection – queries can be leveraged to trigger narrow or specific data clears aimed at replacing partial data sets. During a clear POV action, users can select a predefined query to restrict the clear scope. This feature optimizes data loads enabling users to restrict the replacement of input data to only those intersections that are required to be replaced. Think of it as a predefined FIX statement or a predefined tuple.
  4. Simplified Task Lists  – An example of this capability is explained in detail within the “Alternative uses” section of this blog.
  5. Journal Entry or Data Warehouse export – formatted data exports that can be leveraged as Journals within a GL submission process, in .csv extracts, without any custom formatting functionality like header, footer, record count, date/time stamp, etc.

3. How to Launch PCM Queries 

There are several Graphical User Interfaces (GUI) as well as automation options to launch queries.

1.  Intelligence Menu Section – clicking the query name opens a Smart View connection within an Excel session, prompting users to enter their Cloud credentials. If the Excel session is not terminated, credentials will persist for all subsequent query launches.
Blog Post.Alec Intelligence Menu Section Picture

From the Actions button, users can also launch a direct .csv export of each query. The exported file will be placed within the File Explorer section and is available for download. Users can define the number of decimals they choose to extract – up to a maximum of 7 – and whether or not they choose to perform a base-level export or an aggregated data export.

2.  Manage Queries Section – this menu includes all the capabilities found within the Intelligence menu section along with the ability to edit, delete, or create new queries.

3.  EPM Automate Command – if the desire is to launch a query and generate a .csv file in an automated manner, the requirement can be achieved by executing the following command: epmautomate exportqueryresults APPLICATION_NAME fileName = FILE_NAME [queryName = QUERY_NAME] [exportOnlyLevel0Flg=true]The .csv file generate is very similar to a data warehouse extract file with all dimensions displayed in columns and separated by a space delimiter.

By omitting the queryName parameter, the automation will execute a full base-level data extract of the PCM application in the native ASO format. (non-columnar, optimized for native ASO data load).

Alternatively, if the query must be used in a targeted data clear, the request can be launched via automation:  epmautomate clearpov APPLICATION_NAME POV_NAME [QUERY_NAME] PARAMETER = VALUEstringDelimiter = “DELIMITER”

Example:

epmautomate clearpov BksML 2019_Jan_Actual queryName=BksML_2019_Jan_clear_query isManageRule=false is InputData=false isAllocatedValuses=fasle is AjustmentValues=false stringDelimiter

When uisng targeted data clears, no other parameters can be enabled, such as isManagerRule, isInputData, isAllocatedValue, or isAdjustmentValues.

4.  Rest API Command – just like EPM Automate, REST API is used for automation (lights-out processing). EPM Automate leverages REST API in the background. The difference between REST API and EPM Automate is not the scope of this post; however, one of the main differences between the two is the enhanced logging level available with REST API, which is why implementation partners may favor REST vs EPM Automate.

https://<SERVICE_NAME>-<TENANT_NAME>.<SERVICE_TYPE>.<dcX>.oraclecloud.com/epm/rest/v1/applications/Ex3F3/jobs/exportQueryResultsJob

{“queryName”: “Proftiability – Product”,”fileName”: “ProfitabilityProduct2019.txt”,”exportOnlyLevel0Flg”:”true”}

The syntax for a targeted data clear is the following:

https://<SERVICE_NAME>-<TENANT_NAME>.<SERVICE_TYPE>.<dcX>.oraclecloud.com/epm/rest/{api_version}/applications/{application}/povs/{povGroupMember}/jobs/clearPOVJob

{“isInputData”:”true”,”queryName”:”myQueryName”,”stringDelimiter”:”_”}

4. Query Options 

PCM has a few displays and data extract options that can be stored with the query, and more that can be selected during run time via the GUI or through automation scripts. The settings can be separated into two categories:

1. Optional Query-Store Settings

  • Option 1: Use Aliases: If not deselected, the member name will be used instead.
  • Option 2: Suppress Missing data during execution. If not selected,
  • Option 3: Include Attribute Dimensions
  • Option 4: Order of columns (ignored during granularity override selected at run-time)

2. Mandatory Query-Stored Settings

Option 5: Column/Row Selection

Each dimension reference must indicate whether it is to be used in the Row, Column, or Point of View (POV). It is possible to save queries with no POV reference, the only mandatory selections being those of Columns and Rows.

Any dimension member selection marked as POV will be displayed either in the POV menu/floating box within Smart View or as the header record content if the POV box is disabled in Smart View.

Blog Post.Alec Mandatory Query-Stored Settings

This is a screenshot with the alternative of the POV box disabled.  Users will be able to see a representation of all dimensions that were a part of the toggle POV box when the POV was enabled.

Blog Post.Alec Mandatory Query-Stored Settings2

The selection of Row, Column, and POV will be bypassed during data extracts, whether launched through the menu or via the EPM Automate or REST API commands. All data extracts will list out the members referenced in the selection for each dimension followed by a single data column.

3. Optional GUI Run – time Settings:

Option 6: Export only level-0 data. This will force the query to produce base-level data intersections for all members where a base level has not already been selected in the query. Depending on the size and granularity of data, the query can take anything between 30 seconds up to several hours. Create multiple queries to support the larger data extracts or define the right level of granularity required for the target system to avoid slow extracts or even failures when exceeding the 5-mil records limit.

Option 7:  Rounding precision – extends to a maximum of 7 decimals.

Blog Post.Alec Optional GUI Run-Time Settings.png

Generating data with an increased number of decimals should be paired up with the Application setup of decimals detail as there is no point in generating a data extract with 5 decimals when the application is configured to only support up to 2. This configuration option is available in the Application menu and can be revisited and updated at any point in time.

If neither of these two optional GUI run-time settings are selected, the report will pull the level of granularity established within the query, whether setup at base level or at aggregated level intersections.

4. Optional Automation Settings: 

Option 8: Changing the precision of data extracts when launching queries via EPM Automate or REST API can be achieved via the parameter roundingPrecision with values ranging from (-6) to 7. By default, the EPM Automate exportqueryresults will extract data values with 2 decimal characters. Consider whether or not extracting data with multiple decimals is required, especially if the application Allocation Precision parameter has not been set to higher than the standard value of 2 decimals.

Blog Post.Alec Option8

5. Data Extract Format 

The Smart View query extract format will stay constant regardless of the choice of menu where it is launched.  The .csv file format; however, has a few variances depending on the options selected either during build or during run time.

The .csv format file generated will lose references to POV/Column/Row. As mentioned previously, the resulting file will look like a data warehouse extract – very similar to what can be achieved via an export script within a Planning Cloud Business Rule or Essbase export calc script.

If end users choose to perform a base-level extract override during run time or through Automation commands, the .csv extract will lose the predefined order of the dimensions setup in the query definition.

Regardless of the level of the data extracted (upper or base level), all members within a .csv file extract will be enclosed in double quotes. The delimiter will be “tab” and cannot be overridden or replaced from within the PCM GUI.

This is an example of the query “Profitability – MultiDimension” that is available with the Demo Bikes model. The query extract was launched via the GUI with 7 decimals and with no granularity override (no base level extract option selected at run-time):

Blog Post.Alec Data Extract Format

This is an example of the same query “Profitability – MultiDimension”– launched with 7 decimals and base-level members selection/override at run-time:

Blog Post.Alec Data Extract Format2

When comparing the above screenshots, the order of the columns was clearly altered. This is due to the base-level override selected during run time, and it is an important detail in case the .csv file must be used as a data feed to an external system.

6. Common Warnings and Errors 

Although this section does not represent an exhaustive list of errors, it covers the most common query-related issues a user may encounter along with the corresponding solution.

Warning message: “Query has invalid members. Save the Query to permanently remove the invalid references. To validate the entire Point of View, go to Model Validation without saving.”

Blog Post.Alec Common Warning and Errors

This is a generic message that can indicate either a warning or a true error.

Potential causes for this warning message: if row selections represent top-of-the-house (or so called Generation 0) members; in other words, the Dimension name, while queries may run and produce results, the warning will pop up every time the query is launched via the GUI.

In order to fix this warning, the row selection must be made on any other member or subset of members that is not referencing the Generation 0 / top-of-the-house member.

Second cause for the same warning message: a true error resulting from a member referenced in a query that has either been renamed or removed from the application. In this case, the query is pointing to a Generation 0 / top-of-the-house member, but that selection was not intentional. In most cases when this warning occurs, the obsolete member name reference was automatically removed and either replaced with the top level of the corresponding dimension or simply left blank:

Blog Post.Alec Second Cause for the same warning message

If the user wants to validate which reference was removed due to a metadata update, there is an option to run Model Validation* reports on queries to find out more details:

Blog Post Alec Model Validation 1

*More details on the Model Validation tabs and options will be covered in a future blog post.

In this example, the member STAT1201 has been renamed as STAT120. Because the query references STAT1201, the user is prompted to renew the Account reference selection within the query.

CAUTION: if the user receives this warning and saves the query before launching the Model Validation report, the previous member selection reference (which is now obsolete) is removed and replaced with the Dimension top member. This means that short of restoring the query from a prior snapshot, the user will no longer have a prior reference of the member that has been removed.

The number of query result cells exceeds the limit set by the QUERYRESULTLIMIT.

Blog Post.Alec Errors

Reference the advice given in Option6 (Optional GUI Run time settings section) to reduce the size of your query. This query limit cannot be manually updated by end users or administrators of the Profitability application.

7. Alternate Uses of PCM Queries 

One of the long-awaited features in PCM is the ability to create forms, menus, and task lists like those within Planning and Budgeting Cloud applications. In the absence of such features (which should be coming in future updates), queries can become an easy-to-use alternative. In the prior sections, we explored how queries can be leveraged as data entry guidance mechanisms like forms, data extract tool, and narrow-scope data removal tool. The one feature not discussed yet is the Task List alternative.

By creating predefined queries and listing them in a specific sequence, administrators can dictate the order of operations for either the setup/data load/pre-allocated values or the validation of a PCM model after the allocation process was completed. The listing below is very similar to the concept of Task Lists in PBCS/ EPBCS, and while PCM administrators cannot customize this list by user ID, it still offers that step-by-step guidance that an end user may find useful.

Blog Post.Alec Alternate Usues of PCM Queries

A few tips to keep in mind when leveraging queries in a Task list format:

  1. There are a limited number of characters for each query name. The limit is clearly enforced when editing the name of an existing query. Users can go beyond the limit of number of characters when building a new query from scratch, but this results in an ADF interface error message, and it is most likely a bug which will be addressed in future releases.
  2. The order of the queries is based on the name – descending or ascending. There is no option to customize query order (similar to up/down arrows that allow us to move tasks in Planning or PBCS). This restriction forces the naming convention to be similar to the above example.
  3. There is no option to restrict access at the query level. There are security restrictions/data grants that can be set up for each user to restrict access to the data within the PCM app, but there is no restriction to disable or not display a query or list of queries.

8. Conclusion on PCMCS Queries 

PCM Queries offer a wide array of functionalities that enable users to interact with PCM data throughout the entire processing cycle.

Queries can be used as Data Entry Form to define data entry templates for drivers, or for adjustments when launched via Smart View. By storing the intersections where data is expected to be entered in a query, end users don’t have to worry that they are sending values to the incorrect intersection. Once the query is saved, it can be leveraged multiple times. The references via formulas or hierarchy relationships (Parent, Descendant, Level 0, etc.) will dynamically build the latest metadata selection with each query launch, eliminating the risk of not submitting a driver value because an intersection was not displayed in a Smart View selection.

Queries can also be leveraged as data export mechanism for target systems. The .csv format extract at base-level or upper-level, in column format, can be consumed by most, if not all ETL tools.  There are certain restrictions with queries for data export such as number of records that can be extracted at one time as well as considerations with using dynamic member references. In such cases, the ASO Essbase reporting leading practices must be dusted off and put to good use. What is common sense in the ASO Essbase world should be a good benchmark in the PCM world as well.

Predefined queries are also a good use of resources when troubleshooting allocation results or analyzing data. While PCM queries do not have prebuilt intelligence capability to call out differences month-on-month – a feature that is present in Account Reconciliation Cloud Solution – they can support validation and troubleshooting efforts after the PCMCS allocation process is completed.

The “Out-of-the-Box” series is slowly closing the list of items available in the PCM Intelligence screens, but we are not done yet with all that PCM has to offer!

Keep a close eye on this space for future posts on Cloud Data Management, Model Validation, and Backup and Restore features within PCMCS.

For comments, questions or suggestions for future topics, please reach out to us at infosolutions@alithya.com Subscribe to receive notifications about new posts about Cloud updates and other Oracle Cloud Services such as Planning and Budgeting, Financial Consolidation, Account Reconciliation, and Enterprise Data Management.  Follow Alithya on social media for the latest information about EPM, ERP, and Analytics solutions to meet your business needs.

Contact us