OAS/OAC - using a parameter in a dynamic SQL query.

Paula McMahon

Hi,

I have a workbook containing a table/graph that has been generated by a SQL query, connecting to some Oracle database tables. The SQL query is hardcoded to pull data relating to one variable value only. I would like to dynamically update this table/graph depending on other variable values that I would specify. So, effectively, I would like the SQL query to run each time I change the variable value. Is it possible to do this with parameters? If so, how? The workaround is to create a dataset, via SQL, that contains all values I would need but this dataset would be very large and the refresh times for the table/graph will increase. I have been searching the Community forums, Youtube videos etc. for assistance but I have not found what I am looking for.

Many thanks for any advice offered.

Paula.

Gianni Ceresa

In a DV dataset, you can't have that dynamic component. It is based on a "static" query by design.

If you were using the "classic" part of OAS (analyses and dashboards), you could use a direct database request to build your analyses. There inside you can use parameters, just like in your Discoverer workbooks.

But, as I posted above, the fact that the dataset doesn't have filters in the query, doesn't mean you can't achieve the same result.

You should try once by defining a dataset without any filter (full, and therefore slow).

Then when you use it, set filters and check if the query executed against your database does contains the filters or not: this will make the whole difference, because if the filters are sent to the database in the query, despite having a full dataset, you are practically achieving the exact same result as your Discovered workbooks.

The missing piece is to make sure you do not let the workbook to render its content without a filter. For this you can set the filters as mandatory and set a default value. This will never let the workbook to return the whole dataset. You can even set default values that do not exist, making the default rendering of the workbook empty, and then if you have many filters turn off auto apply and this will let your users set all the filters they need and then click "apply" once to execute the right query directly.

But again, it's something you should try. Look at the generated physical queries and you will probably see that it does behave just like you want it, despite not having set any filter in your dataset.

PS: OAS is not a database, but it can behave like one if you force it, it is just extremely bad at it because it isn't a database (with all the performance optimization for data querying etc.). You actually want to avoid as much as possible OAS to behave like a database, and if it does start doing it, you will easily see it with slow response time (and the logs will also tell you that).

Gianni Ceresa

To be precise: in a dataset you can define filters at the dataset level to select only a subset of rows. The issue is that a parameter doesn't exist at that time, parameters can exists once you have the dataset. Therefore if you try to define the filter to be based on a parameter you get an error that the syntax (the @parameter…) isn't supported.

SteveF-Oracle

Hi @Paula McMahon

To add, Oracle Analytics Cloud has added new features where you can bind a parameter to a filter. However, since Oracle Analytics Server is updated annually, and this feature was released after OAS 2024, it will not be considered to be added until OAS 2025.

If that feature is important to your organization, then you may want to consider using OAC.

https://www.youtube.com/watch?v=a0RacJu4Skc

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acswn/index.html#GUID-CFF90F44-BCEB-49EE-B40B-8D040F02D476

[Deleted User]

In Oracle Analytics when using Workbooks with filters there is no binding required when you use normal filtering. You simply drag and drop columns into the workbook and use them. The binding happens automatically.

Steve posted the example with parameters above but the key functionality of filtering requires no binding of config.

Example: Here I'm showing products and sales numbers and have dragged the "Year" from the order date into the filter bar. Note that the "Year" is automatically deduced from the date attribute order date and doesn't even exist as a column itself:

All I did was drag the Year into the top filter bar. I don't even need to specify any filter config. You can see that option is totally empty:

The filter works as soon as you drag it onto the filter bar.

Same with a between filtering for dates:

I simply drag and drop the ship date attribute (of type date) into the filter bar and OA does the rest.

With regards to the parameters and SQL fetching there are several aspects:

1.) You can always create a data set specifically for that and store it in the data set storage using a data flow. Not sure how often this data changes for you but you'd have to watch out for stale data of course.

2.) OAS isn't a database but rather reads any data it has access to. It can store data itself as indicate above with data sets) or it can also store the data it processes via data flows in any database you have access to.

3.) So your data set can be

• transparently read from your raw data,
• it can be a stored data set inside OAS
• or it can be a stored data set you have curated in your data source.

Meaning in the latter two cases you wouldn't have to hit a 5b rows fact table to get a list of 200k distinct pharmacies for example.

4.) Having that data set would then allow you to use it as your filter and just join it to your transactional data.

5.) Lastly you do have the option of writing code to fetch a parameter list. BUT this is Logical SQL, not SQL as you understand it in a database sense. The Logical SQL hits the OAS abstraction layer (semantic layer) and hits your objects like Data Sets and Subject Areas. Heeding the explanation above that's a superfluous step when you have your pharmacies already in a data set.

Data Sets are really key concepts in how OAS works as they allow you many functional possibilities for working with and interacting with data.

You for your use case basically have no need parameters because the base product already does everything for you.

Since you're using OAS you by default also have the full semantic capabilities at your disposal rather than just using the self-service Data Visualization components.

That's a step too far for this one thread here though and I'll leave that for another day. It's like 10 time even more capable ;)

Federico Venturin

Hi @Paula McMahon ,

AFAIK this is not yet possible in DV. For this use case, I would use a subject area/semantic model instead of a dataset, so filters will be handled as WHERE conditions in the final physical queries.

Gianni Ceresa

Welcome to the forum @Paula McMahon ,

If your workbook is based on a dataset that is a SQL query on a database, and the dataset is set to be "live" (no caching), then you can define the dataset as being the full set, containing all the data, and by adding a filter to your visualization in the workbook the product will try to pass the filter down to the database as good as it can.

In this example my dataset is simply based on a query being SELECT * FROM sh.products , it is slow because it's the full table. But when I make a visualization and add a filter to it in a workbook, you can see that the filter for PROD_CATEGORY = 'Hardware' is added to the physical query executed on my database.

The dataset doesn't have any WHERE clause at all, but OAS is "smart" enough to do it, because the product will always try to push down to the data source all the filters to have to transfer and handle the least amount of data possible (because OAS isn't a database).

Of course if your query is extremely complex the generated query could be very much different, but still the product should try to push the filter to the database. In this case you can also try to use the database smartness by turning your query into a view in the database if that allows for the database to optimize the query when receiving a filter.

It's all things you should test because they depend on what your database is (you said Oracle, the optimizer is quite good at doing the least amount of work possible), what your query is like and a number of factors playing a role in how good the product can try to optimize the query.

Just pay attention at what queries are generated. Even in my super simple example you can see the query of the dataset isn't filtered directly, it's used as a subquery and then the filter is applied. But doesn't matter, the database will optimize the query anyway and the execution plan of that piece of the query shows the same behavior if a subquery is used or the table directly.

Paula McMahon

Thank you very much for the replies Federico and Gianni. You have given me a lot to go on there!

Paula McMahon

Hello again, I have another question, if that's okay. I have been using OAS for about a year but I am very much self taught, no formal training, and so maybe my understanding is limited. As a data analyst, we often need to create workbooks that the end user can use and we were able to achieve this via Discoverer workbooks.

I have attached an example of this which shows that the user can enter a pharmacy number, start date and end date (see parameters marked in yellow) and the underlying query would run on the database tables, and return a result.

discoverer_qry.png

I would like to achieve the same thing with OAS. Can this be done? I know that I can create a dataset (or view) with every possible pharmacy number, start and end date and supply this to the end user who can then use filters to subset the data, but this dataset would be very large.

Is it possible to create a dataset, through SQL, that uses parameters to fetch the data dynamically. It seems not, as Gianni has said that "OAS is not a database".

Many thanks for the help so far. Regards,

Paula.