Forum Stats

  • 3,817,308 Users
  • 2,259,308 Discussions
  • 7,893,742 Comments

Discussions

Complex What-If Scenario Analysis

hiddenonearth
hiddenonearth Member Posts: 91 Red Ribbon
edited Nov 15, 2020 2:05PM in SQL & PL/SQL

Hello,

i am facing an complex issue where when event changes on a time series (year basis) it has an minor or mayor affect on the other following events and finally on the defined target. Meaning that when one event changes on the monetary status or any other status then to following events needs to be changed afterwards since all the events are connected and dependend (and are in relations) and belong to a bigger picture.

 I am talking here about running some what-if analysis on changing data.

Simple example: for a dependency graph with chain of dependencies and effects:

Does anyone know a workaround with PL/SQL or can recommend a way to start or tackle this problem since I need faced such issue.

Any help is appreciated. Thanks.

Tagged:

Answers

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond

    If this is specifically an ApEx question, you posted it in the wrong "category" - you should find the ApEx category, this is the (plain) SQL and PL/SQL space.

    For example, you talk about "a chart with bar ranges and target lines". I have no idea what that means, although I think I am moderately experienced with SQL and PL/SQL. I never heard of "bar ranges" or "target lines" etc.

    Whatever your question is, though, I hope your triggers don't, in fact, have side effects as you depicted in your diagram. You have a cycle in it (B -> C -> D -> E ->B), which means that whenever an event affects one of them (either directly or indirectly - for example because something changes in A) you will enter an infinite loop of dependencies.

  • hiddenonearth
    hiddenonearth Member Posts: 91 Red Ribbon

    In general its an PL/SQL question. Anways, i changed and modified the description so it become more clear.

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond

    Sorry but no - it is not a PL/SQL question. I already explained - there are no "bar ranges" and "target lines" etc. in PL/SQL.

    Please explain the problem in SQL and PL/SQL terms, if you want a (PL/)SQL solution. Otherwise I, and perhaps other participants in this category, will not be able to help you.

  • Mike Kutz
    Mike Kutz Member Posts: 6,191 Silver Crown

    can recommend a way to start or tackle this problem

    I'd start by trying to explain the problem so that someone else can understand. Right now, I have no clue what you are talking about. I have no idea what a "good solution" for your problem would be.

    Looking at the chart (alone), My first two thoughts for possible solutions:

    • Job Chains
    • series of AQ queues

    MK

    Frank Kulash
  • hiddenonearth
    hiddenonearth Member Posts: 91 Red Ribbon

    Thanks for the response. What I basically try to achieve are multiple what-if analysis for different scenarios like a simulation of different outcomes. I hope this makes it clearier.

  • Mike Kutz
    Mike Kutz Member Posts: 6,191 Silver Crown

    Now, it sounds like you need the Advanced Analytics ($$$) add-on.

    If you are trying to generate that mapping also, then you definitely need that add-on.

  • mathguy
    mathguy Member Posts: 10,494 Blue Diamond

    Uh-oh. You changed the thread title and the content of your original question, making some answers seem like they have nothing to do with your question. That is totally uncool. Good luck getting help from others - I'm done with you.

  • Paulzip
    Paulzip Member Posts: 8,678 Blue Diamond

    I've still absolutely no idea what you want. No example, no context, no clue.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,807 Red Diamond

    There are two basic approaches for this type of problem.

    Use SQL analytical functions, common table expressions, recursion, hierarchical query, and so on.

    Use a PL/SQL pipeline via SQL.

    But without details such as test data, what-if analysis requirements, and expected output, it is not possible to provide any real assistance.

  • SBJ
    SBJ Member Posts: 1,751 Silver Trophy

    Analysing multiple scenario's based on one single version of your data is one of the reasons Oracle Workspace Manager was developed: https://www.oracle.com/database/technologies/enterprise-edition-workspace-manager.html

    Sounds to me like that is what you are looking for.