Parameterized SQL statement for Data set using external data source

Wilson Yeung

Description

Currently, we are using ADW as data source of our data set used by Data Visualizer. It is great to have ability to use exiting data in databases (ADW in our case) to allow users to enjoy the benefit of OAC rather than waiting the process to create a workable RPD.

Yet, without the ability to use parameters/bind variables in SQL causing us performance problems and render few visualization failed to show data.
It is because OACS only apply user filters or data-level security (workaround of not having RPD as per support doc 2396406.1) after the SQL in data set return ALL data.

Hence, it will be useful for OACS to allow SQL statements to use parameters/bind variables when it use external data source instead of Subject Area from RPD.

 

 

Use Case and Business Need

A data set using SQL statement with connection to external source (Oracle Databases/ADW) need to return ALL data before OACS can filter them based on either user filters / data-level security causing performance issues and preventing few visualization to show data.

In order to study business trending, a SQL is created to returns 4 years of data (11 Millions of records after grouping) from ADW. OACS only reduce the result to 4 months that user want to see after ALL records in the SQL return instead of pass this condition to database. Hence, it is simply not working as it took too long or exceeding total records limit.

With a bind variables/parameters in the SQL will reduce the actual data to user requirement and much more efficient.

More details

In order to study business trending, a SQL is created to returns 4 years of data (11 Millions of records after grouping) from ADW. OACS only reduce the result to 4 months that user want to see after ALL records in the SQL return instead of pass this condition to database. Hence, it is simply not working as it took too long or exceeding total records limit. The filter only if data set is using Subject Area which defeat the benefit of quick adoption for OAC using external source.

Original Idea Number: 230b953aee

Gabby Rubin-Oracle

The assumption that "OACS only apply user filters or data-level security after the SQL in data set returns ALL data" is not correct, especially for ADW (assuming your dataset is set to live). When possible, OAC will push down filters to the physical source. The specific caveat concerning the attached support doc is that it uses an OAC variable to resolve the user name, which the physical source might not understand, and therefore, that filter will not be pushed down. (please note that the method in the support note is no longer relevant since role-based filters were introduced to datasets some time ago).

I would say the following:

1. Do not consider client tool filters a data security feature. While they are a good option for enforcing filters based on roles, they will not provide real protection if someone wants to spend the time to bypass them. This is why the OAC feature called "role-based filters" does not include the word "security."
2. For datasets, the secured solution here is to define user-based data security at the database level. The Analytics tool cannot push down the filters related to 'user name' as the database might not understand those.

This does not mean that dataset role-based filters are not an important and useful tool for many enterprise scenarios; we need to be careful when creating a perception of security when something is not designed to withstand malicious access.

My options on idea statuses are a bit limited (between decline or delivered), so given that we did deliver dataset role-based filters that do a better job compared to the old workaround, I'll go on the positive side and mark this one as delivered.