Profitability and Cost Management

Get Involved. Join the Conversation.

Topic

    Alecsandra Mlynarzek
    PCM Out-of-the-Box (OOTB): 5.Queries
    Topic posted September 24, 2019 by Alecsandra MlynarzekBronze Trophy: 5,000+ Points, tagged Activity Based Costing, Allocations, Costing, Customer Profitability, PCM, Profitability Analysis 
    20 Views
    Title:
    PCM Out-of-the-Box (OOTB): 5.Queries
    Summary:
    Learn how to leverage PCM queries for data extract and data analysis
    Content:

    At the end of this post you should be familiar with the following topics:

    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 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 generated 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”:”_”}

    [...] Continue reading this post on ranzal.blog.

    Version:
    PCM 19.09