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

Michal Zima

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)

Bret Grinslade - Oracle Analytics-Oracle

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

@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)

Physical_Parametrized_Report_SQL.txt

Uploaded Apr 5, 20235.48 KB

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

Bret Grinslade - Oracle Analytics-Oracle

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

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.

Gabby Rubin-Oracle

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

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.

Michael Murray

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

@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 ?

Gabby Rubin-Oracle

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

@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