Oracle Analytics Cloud and Server Idea Lab

Products Banner

DV Parameters - usage/reference in data set definition (reference in physical SQL definition)

Under Oracle Review
374
Views
14
Comments

Recent launch of parameters in DV was highly awaited and appreciated and there are definitely other areas of usage.

One of them is (we are using in similar way session repository variables, which can be set for one-time usage via request variables in UI in metadata for some more complex and dynamic "modeling" scenarios) is following:

Allow reference of parameter values in definition of data sets - particularly referencing in definition of SQL statement for data set table (when table is based on SQL statement).

This would allow us to cover complex/dynamic reporting scenarios we currently cannot achieve in DV (and the only way is to use metadata modeling with combination of classical UI - dashboard prompt setting request variables + analysis)

16
16 votes

Under Oracle Review · Last Updated

Comments

  • Michal -- can you give an example? You can use logical sql to initialize a parameter. That logical sql can reference a dataset. Would that work?

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    @Bret Grinslade - Oracle Analytics-Oracle Hi Bret, not this will not work - reason is following:

    Physical SQL for particular report is realy very complex and cannot be rewritten into logical SQL - I am attaching real example of physical SQL, which we are using in physical layer in metadata (table definition using SQL)

    In this SQL 2 session variables are used as "parameters":

    NQ_SESSION.MKT4HT_CAS_KROK_CHAR (this variable is used in multiple "places" in SQL)

    NQ_SESSION.MKT4HT_ISIN

    And those session variables are then "set" on dashboard via dashboard prompt (setting request variable) for report/analysis execution.

    If you look at the physical SQL you will easily recognize, that complexity is so big, that this cannot be definitely "transferred" (overwritten) into logical SQL (thus execution by BI Server). If you can to that, you are my hero.

    And we have couple of such similar cases.

    So this idea is about having same ability for constructing such complex report (know realized with combination of metadata and classical tools-Answers/Dashboards) also in Data Visualization.

    Thanks

  • Michal ... might be worth to discuss. With this example, you could create a specific dataset or dataset table with the complex calculation (or even a DB view) ... making the logical SQL simple where you are just grabbing a simple item in LSQL.

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    Bret, nothing against discussion. But from your answer it seems, that you have not get the logic behind the report and especially complexity of query and numerous places in query, where “parameters” are passed. If you think, that you can somehow make it simplier and transform it into logical sql, i am more than curious.

  • Michal, currently it will not work. Even with changing the parameter/variable the caching will not catch the change and you will not be able to achieve the intended outcome. DV parameters are only in their initial state and there will be more capabilities coming in upcoming updates, we are reviewing these type of use cases as well but there is no current spec to how we will resolve it.

    We made a decision to push parameters as a rolling feature as even with limited use case coverage having parameters vs. not having them is night and day. We do not assume that it is "done" but from here our progress will fork into several threads, 1) additional use cases coverage, 2) ease of use improvements for common use cases, 3) closing identified gaps in already delivered use cases.

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    Gabby, thanks for explanation, i understand, that parameters are "just at the beginning of their journey" and will be further developed/extended. My idea, I think, falls under first bullet: additional use cases coverage. Anyway, good to have them in OAS 2023 - thanks for that.

  • I have many client with this exact use case --> use a parameter to set the value of a session variable, like the old report variable overriding the value of a session variable. So useful for metrics/measures!

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    @Michael Murray Yes, this is exactly what is needed (to cover some complex scenario, which cannot be handled by passing just simple filters or custom calculations in DV UI). Would be fine to know (since this Idea is in "Under Oracle Review" status) what is the status of this review - will this feature be potentially placed on product roadmap ? @Bret Grinslade - Oracle Analytics-Oracle , @Gabby Rubin-Oracle would you be so kind to commend, pls ?

  • We are looking at adding a parameters' ability to set session variables, there are several key issues that will need to be resolved so we are still figuring out the feasibility of it.

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    @Gabby Rubin-Oracle Hi Gabby, thanks for your reply. Being able to set session variables (from parameter's functionality) would be just one part of the puzzle (required functionality for us). The second one is then ability to refer session variables in SQL query (manual query) within data set definition ? Will this be possible ? Is this planned as well ?

    Thanks and rgds

    Michal

  • We will start with this one to improve the 'cooperability' of the workbook experience with the RPD. As for the second one, I assume that a pre-req for that will be Datasets based on physical SQL and not logical SQL, at least for the example that you had before. This is currently not planned, but why not keep these types of use cases in the RPD domain? Subject Areas are our primary data artifact for advanced enterprise use cases; Datasets are not meant to do everything Subject Areas can. Some parameters/variables support for Dataset definition is valuable but will likely remain at the logical SQL level.

  • @Gabby Rubin-Oracle Your update is appreciated! Logical SQL is definitely the primary hurdle to overcome with respect to replicating the 'OAC Classic' Report Variable-Session Variable interoperability that's currently lacking in the OAC (i.e. DV interface).


    The secondary request --> the ability @Michal Zima references above to reference parameters for dynamic filtering of SQL-based datasets is something we could do in 'Classic' with session variables in the physical layer of the RPD using an 'Opaque View', so technically it could be done in OAC if the primary requested Idea is implemented.

    FAW users, or other users without access to the RPD, would not be able to leverage this functionality, though.

    Passing parameters to filter the SQL used to generate OAC datasets is useful functionality as well, especially given performance implications of returning large amounts of data to the OAC application stack, rather than optimizing/pruning the SQL prior to sending the physical SQL to the database. This can be especially critical if OAC cannot access the database directly (e.g. due to firewalls, lack of drivers, etc.)

    Thanks again for taking this on!

  • Given the differences in the solution architecture, we are trying to see how these types of use cases will be addressed using DV concepts and artifacts, which might differ from how Classic addresses them. We are exploring a project that will allow workbook authors more flexibility along these lines, but it is a bit early to discuss. It goes beyond the use of parameters in the query; the main driver is to allow increased query complexity by the workbook author beyond what the SA/Dataset provides using the basic visualization query generation. Parameters and 'Opaque Views' like concepts will likely be a part of it, but it is still on the drawing board.

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    @Gabby Rubin-Oracle Thanks Gabby - looking forward to hear in future where this project, you mentioned, is heading.

    And you are right, combination of setting/overwriting session variable from DV UI with RPD model (SQL query, referencing session variables defined in physical layer) would be sufficient option.