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

Question

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 · Accepted Answer

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 · Accepted Answer

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 · Accepted Answer

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] · Accepted Answer

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