Profitability and Cost Management

Get Involved. Join the Conversation.

Comments

  • Evan Leffler

    Here is the response I gave over in the Idea Lab for reference:

    Matt, this functionality already exists. Just change your selection from the following

    <<Attribute Dim Name>> = <<Attribute Dim Member>>

    to instead read 

    <<Attribute Dim Name>> IN <<Attribute Dim Member Hierarchy>>

    this will select any member under that hierarchy

  • Ankit kumar Shrivastava

     Updating dimensions in PCMCS can be achieved using the REST API. Based on how you want to automate the integration process -  FDMEE, Bat scripts, Groovy etc this can be accomplished using the endpoints available here:

    https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/prest/pcmc_update_dimensions_as_a_job.html

    Thanks

    Ankit Shrivastava

  • Alecsandra Mlynarzek

    Hey there Al,

    A couple of thoughts on the custom calc syntax:  if your POV is FY19, May, then you don't need to repeat it at all in any of the formula components.

    The Essbase agent will identify the POV almost like a fix statement or a fixed tuple, and any formula you write in the rules will dynamically read it. So if Period is in POV, and you have the same rule for each POV, just skip mentioning it in the rule, that way you can do copy POV rules and rulesets and not have to edit them,

    You should mention May, FY19 only if you want to override your POV. 

    Now let me ask you a question about the other members, "Real" and "E001". Do you need to specify them or are they already specified in your Target selection? If they are in the target, just go ahead and take them out from the formula. Just like the POV override, the selections in Target tab will be applied to all equation components. So if in your target you have E001, E002, E003, then each will be calculated as Acc1=Acc2*Acc3 , using their corresponding reference. In other words Account 1(for E001)=Account2(forE001) *Account3(for E001). The script generates the same formula for all the Entities you specify in the Target selection.

    I hope this helps.

    Alecs

  • Alecsandra Mlynarzek

    Hi there Edward,

    Are you asking for help to understand why the data distribution by product has a value of "X" when the driver data indicates it should have a different value? 

    If that is the question then please drill into the member "Materials". This is a parent with several children, each having a different distribution %. You cannot and should not add % to a total and use that value as your validation.

    Below is one example for "Frames" under "Materials",and the results are correct.

     I hope this is helpful.

    Kind regards,

    Alecs 

  • Don Bean

    Sandip

    PCMCS uses an ASO database.  The choice of Dynamic, Stored, and Hierarchies Enabled dimensions will have varying impacts on the way data is retrieved and what hierarchy options are available to you.    In general Stored dimensions and hierarchies will aggregate the fastest, dynamic dimensions and hierarchies the slowest.   In addition the use of formulas on specific members causes the database to execute the formula for *each* potential data intersection in order to determine if data is present.  This bypasses the database's ability to skip empty intersections to speed up data retrieve and aggregation.   By using two members with formulae in your query you are compounding that problem.

    In addition the use of Accounts as dynamic slows down any aggregations that need to occur to satisfy your query.   Try to see if the Accounts dimension can be changed to Hierarchies Enabled.  This allows you to segregate your COA hierarchy in a stored hierarchy, and any alternate rollups or members with formula in secondary hierarchies which can be tagged as dynamic.   This will allow queries against the stored hierarchy to run much faster.

    These are not characteristics specific to PCMCS - any Essbase ASO database will have the same requirements.  I'd recommend looking at the Essbase Database administrators guide for more information and guidance on this.

     

  • Evan Leffler

    Hello Sandip,

    I would start by investigating two specific areas which would help in diagnosing the problem at hand

    1. The Member Solve Order on both the RPTG_Actuals and P01T13 will require the ASO database to loop at a bare minimum, 10 times in an effort to solve the equation.  Couple that with the CASE statement in the formula, and it will loop an additional amount of times based on how many CASES are present.  As a starting point could you provide the entire formula that is within the RPTG_Actuals member
    2. In addition, I'd like to see the contents of the P01T13 member to try and understand what is the expected outcome of that particular formula as well

    The comments that Don has made are spot on.  Specific to Accounts being Dynamic, if you have a member formula and/or a Shared member within the same hierarchy that appears twice, such as would be the case when trying to calculate Net Income, for example, this will force you to make this hierarchy Dynamic.  There may be some room for improvement to Don's point by breaking out the hierarchy which may facilitate making the first hierarchy Stored by way of making Accounts Enabled, that would have to be further investigated to ensure your data integrity stays as expected.

    If you can provide the additional information, I can take another look to see if there is room for additional improvements.

  • Alecsandra Mlynarzek

    Hi there John,

    I remember I had a similar issue about 2 years ago and the solution was to set up a data grant for the group on at least one dimension to enable read at top level. By default, the Viewer and User groups cannot see data unless you give them access to some area of the application.

    Kind regards,
    Alecs 

  • Alecsandra Mlynarzek

    Amit, check your browser version against the table in the below link.

    https://docs.oracle.com/en/cloud/get-started/subscriptions-cloud/csgsg/web-browser-requirements.html

    If your default browser is not Internet Explorer, there may be additional add-ons that need to be installed. This is an older reference:

    https://docs.oracle.com/applications/smartview/810/SVICF/browser_add_on_requirement_100xeeb0c08a.htm#SVICF-GUID-4269A432-78F5-46DC-A02A-90301044DDA6

  • Alecsandra Mlynarzek

    Hi there Amit,

    I doubt this is an XLS issue. It looks more like a browser issue. 

    What Internet Explorer version do you have installed and have you checked compatibility with Oracle Cloud? 

  • Alecsandra Mlynarzek

     It could be that the number of intersections you are trying to query is extremely large for that rule that has failed. If you combine this information with a couple more details from other logs, the picture will become clearer.

    Can you check the Execution statistics and share the information corresponding to that rule (number of potential intersections)?

    Are you using any attribute dimensions or UDAs in filters within this rule? If you can share the configuration of the rule from the Program Documentation, we could have a more meaningful discussion, because based on your rule definition there could be one or more issues to solve.

    Thanks!

    Alecs

  • Alecsandra Mlynarzek

    Hi there Amit,

    the image you have attached only shows that there are warnings, indicating the rules are not calculating anything (the rules are pointing to either a source or a driver intersection without data).

    “number of updated cells count is zero” means just that the rules were not able to calculate anything. Unless there is more to this error log, what you have included here does not display any error message reference.

  • Alecsandra Mlynarzek

    Hi there Amit,

    Your issue is related to some larger Smart View query that you may have retrieved in a prior session or .xls sheet. There are sessions in the background running in Essbase which are not yet finalized, and there are insufficient resources for your new query to return results. 

    If you do not remember force-closing any prior smart view session because it ran long then there may be other users on the application that are launching large queries. 

    This is how you get rid of this error message : 

    1. you wait for all queries to run. You can't anticipate how long the wait will be. I, unfortunately, never have the patience for this option.

    2. go to Navigator - > Database - Administration ->Restart. This command will restart the Essbase application, terminating all running queries. 

    3. for a complete rehash of resources,  you can restart services (resetservice) through epmautomate. This option will take a while longer, anything between 10-20 minutes. 

    This is how you prevent this error message in the future:

    1. check what kind of query you are trying to launch. If you are retrieving on top-of-the-house dimension members and most of them are dynamic, and your app is on the larger side ('000 of members in many dimensions) then you must have a "starter" smart view pull to begin with, focusing on a narrower data selection.

    2. revisit the storage settings in your hierarchies. If most members in your hierarchies are Dynamic, then you know you are using up a lot of resources to complete a high-level query.

    3. Start your queries with the Smart View option Navigate without data. That way you can build your query as you need to, and then when you have narrowed down its scope, you can enable the data pull and ensure it can return a result in a decent amount of time, without taking up all the resources.

    Let us know how it goes.

    Alecs

  • Evan Leffler

    There is a limitation, which currently appears to be 1,000 characters.  Historically, I have always broken up calculations logically into two or more and then combine the results in yet another custom calculation to get around this.

     

    Hope that helps

  • Alecsandra Mlynarzek

    Hey there AL,

    Your best bet is to create 12 queries, one for each Period, with the period name included in the query name, and then have the automation call the corresponding Query based on the current month run time variable layered within the query name. 

    The year update in each query can be part of the yearly maintenance processes since it literally takes 5 seconds/ query to make such an update. This way you don't have to build 12 sets of queries for each year. Unless you really want to. :)

    You could write custom scripting to access just 1 query and change the reference of the month &year via run-time variables directly in the .xml file, then import the new ".xml" query content just like you would import an artifact from a snapshot, and then launch the automation jobs. While this is an option, it is also a lot of headache for very little value. Always keep it simple. Combine automation with "Year End Admin" processes to strike a balance between functionality and ease of troubleshooting.

    Alecs

  • Evan Leffler

    Hi Jean-Luc,

    You are correct PCMCS, does not support metadata loads through Cloud Data Management. You essentially have three options:

    1. Manual manipulation in flat file format to generate the PCMCS metadata file
    2. Utilize a tool such as DRM or EDMCS for metadata management
    3. Custom solution, such as SQL to extract from Financial ERP, transform, and generate a flat-file in the prescribed PCMCS format.

    Hope this was helpful