X

Best Practices from Oracle Development's A‑Team

Filtering Parameterized Data Sources in Oracle Analytics

Validated March 23, 2021 with OAC 5.9

Introduction

This post builds upon the elements described in Using Stored Procedures as a Source for Oracle Analytics. It is a step-by-step guide for filtering stored procedure data sets in Data Visualization and how to apply the filters and parameters before the result data sets are displayed.

Validations

March 23, 2021 with OAC 5.9 

Topics

Before You Begin

Preparing a Filter Data Set

Preparing Project Canvases

Applying a Filter Data Set

Displaying Filtered Result Sets

 Before You Begin and Assumptions 

Acronyms

OAC Oracle Analytics Cloud
SP Stored Procedure
CV Cursor Variable
DB Database
DV Data Visualization
ORDS Oracle REST Data Services
ADW Autonomous Data Warehouse
LOV List of Values
   

 

Privileges

ADW privileges to select from and execute objects in the schema hosting the stored procedure.
OAC privileges for creating data visualization data sets, projects, and data actions
HTTP server privileges for executing RESTful data services if using a SP requiring parameters

Database

The objects created in the introductory post. The original procedure has three parameters shown below. Two scenarios are covered in this post: 1The parameters are removed to allow DV to perform the filtering and formatting (SP project) and 2The procedure remains unchanged (SP_Parms Project)

OAC

The ADW connection and data set(s) created in the introductory post.

HTTP REST Services

The ORDS resource handler created in the introductory post.

Initial States

The following depicts the initial states defined by the introductory post.

Using Parameters

Without Parameters

 

 Preparing a Filter Data Set 

Note: This post assumes the SP is using scalar data types for the input parameters.

This data set is used for stored procedures with and without parameters. The SQL provides the set of selectable values.

Sign in to OAC. From the DV home page click Create > Data Set and select the ADW Connection.

Click Enter SQL
  Enter a Name e.g. SP_FILTER_DS 
  Select Live from the Data Access dropdown
  Add the following Statement and click Add

Note: This post uses the OAC process number associated with the session as the filter key. Any unique value that is accessable may be used. 

 

 Preparing Project Canvases 

Separate canvases in a DV project allows you to defer using a filterable data source until the filters have been selected or provided.

Creating Project Canvases

Create a project for each type of SP data set to host the canvases.

From the DV home page click Create and Project.
  Select a Data Set based on a stored procedure with parameters e.g. SP_RESULT_SET
     Note that Canvas 1 has been created and presented by default
        Hover over Canvas1, click the down-arrow and click Rename
           Change the Name to Filter Canvas
     Click the plus sign next to the Filter Canvas to create Canvas 2
        Hover over Canvas2, click the down-arrow and click Rename
           Change the Name to Result Canvas
Click the Save icon and give the project a Folder and Name e.g. SP_Parms

Repeat the process for the stored procedure without parameters using the SP_FUNCTION_DS dataset and naming the project SP

Note: Separate projects are used for simplicity. This could also be done with two extra canvases in a single project.

Adding the Filter Data Set to the Project

Open each project and insert the filter data set.

Select the Prepare pane

Click the + sign on the bottom row to add a new data set to the project
   Select the filter Data Set e.g. SP_FILTER_DS
     Click Add to Project

Numerical columns may be treated as measures by default and aggregated. 

When the results appear click each numerical column you do not want aggregated
In the left pane select ATTRIBUTE from the TREAT AS dropdown

Select the Data Diagram, click on the filter data set and click on the numeral on the line connecting the data sets.

SP Project

The CHANNEL_ID and CUST_ID columns must be matched.

If either is not matched, click on Add Another Match to add the match
If other columns are matched, click X to remove the match
Click OK
Save the Project

SP_PARMS Project

The SP_RS_KEY must be matched.

If it is not matched, click on Add Another Match to add the match
If other columns are matched, click X to remove the match
Click OK
Save the Project

Preparing the Project Filters

In each project select the Visualization pane, select the Filter Canvas, expand the Filter Data Set, add filter data columns to the Filter Bar or Grammar Bar and Save the project.

SP Project

Drag the CHANNEL_ID, CUST_ID and BOOLEAN_VAL columns from the Filter Data Set to the Filter Bar
Hover over the CHANNEL_ID, CUST_ID columns and click Pin to All Canvases
Click each Filter Column and from the LOVs select 0 for CHANNEL_ID, CUST_ID and TRUE for the BOOLEAN_VAL. Click the CUST_ID column, select Limit Values By from the down-arrow dropdown and select CHANNEL_ID
Save the project

SP_PARMS Project

Drag the SP_RS_KEY from the Filter Data Set to the Filter Bar. Hover over the Pin in the filter column and click Pin to All Canvases.
Drag the remaining columns to the Filter area in the Grammar Bar
Click each Filter Column in both bars and from the LOVs select 0 for CHANNEL_ID, CUST_ID, NULL for SP_RS_KEY, and TRUE for the BOOLEAN_VAL. In the SP_RS_KEY filter click Exclude Selections from the menu action dropdown.
Save the project

Adding the Filter Data Set to the Filter Canvas

In each project add Filter Data Set columns to create a table visualization.

SP Project

Drag the CHANNEL_ID, CUST_ID and BOOLEAN_VAL columns from the Filter Data Set to create a Table Visualization.
Save the project.

SP_PARMS Project

Drag all the columns from the Filter Data Set to create a Table Visualization.
Save the project.

Adding the Result Data Set to the Result Canvas

In each project select the Result Canvas and add all the Result Data Set columns to create a table visualization.

SP Project

Drag all the columns from the Result Data Set to create a Table Visualization.
Save the project

SP_PARMS Project

Drag all the columns except SP_RS_KEY from the Result Data Set to create a Table Visualization.
Save the project

 

℘ Applying Filter Data Sets 

SP Project

The two filtering columns, CHANNEL_ID and CUST_ID, are applied via the join between the filter and result data sets. 

The formatting column, BOOLEAN_VALUE, determines if the product name is masked. It is applied via a calculated column based on the product name.

From the Data Panel, right-click My Calculations and click Add Calculation
  Enter a Name i.e. PROD_NAME_CALC
  Expand Expressions and double-click Case (if) to add it to the calculation definition 
     Replace the CONDITION by double clicking the BOOLEAN_VALUE column from the filter data set and adding = 'TRUE' 
     Replace the THEN and ELSE expressions by double-clicking the PROD_NAME column from the result data set
     Double-click the THEN expression and Mask the PROD_NAME by replacing it with LEFT(PROD_NAME,5)
From the Result Canvas replace the PROD_NAME column with the new calculated column. Click Save.

SP_Parms Project

Important: Before using a REST API navigate from the DV home to Console > Safe Domains, click + to add a new domain and enter the REST API hostname e.g. https://<REST API FQDN>

The filter values are passed to the stored procedure via a Data Action in DV. Refer here or search for Visualizing Data and Building Reports in Oracle Analytics Cloud for the documentation.

From the Menu Action under the user icon select Data Actions
   Click + to create a new action
      Enter a Name e.g. Run Procedure
      Select HTTP API from the Type dropdown
      Select POST from the HTTP Method dropdown
      Enter the REST API URL created in the introductory post 
         i.e. https://<FQDN>/ords/spws/dv/sp/
      Enter the POST Parameters. Enter a filter value for each API bind variable on a separate line:


channel_id=${keyValuesForColumn:XSA('dayne.carley@oracle.com'.'SP_FILTER_DS')."Columns"."CHANNEL_ID"}
cust_id=${keyValuesForColumn:XSA('dayne.carley@oracle.com'.'SP_FILTER_DS')."Columns"."CUST_ID"}
boolean_value=${keyValuesForColumn:XSA('dayne.carley@oracle.com'.'SP_FILTER_DS')."Columns"."BOOLEAN_VAL"}
sp_rs_key=${keyValuesForColumn:XSA('dayne.carley@oracle.com'.'SP_FILTER_DS')."Columns"."SP_RS_KEY"}

Click Save

 

℘ Displaying Filtered Result Sets​ 

SP Project

A user navigates to the project's filter canvas bar and selects a single value for each of the three filter columns. Then changes to the result canvas and views the filtered result set and the masked product name.

SP_Parms Project

A user navigates to the project's filter canvas bar and selects the value for the SP_RS_KEY. Then from the filter visualization pane, selects a single value for each of the three other filter columns.

They then right-click on any filter column and click Run Procedure, wait until the Success message appears and change to the result canvas to view the filtered result set and the masked product name.

 

 Filter Flows 

The filter flow for each project is shown below.

SP Project

 

 

SP_Parms Project

 

 Summary 

This post provided a step-by-step guide for filtering stored procedure data sets in Data Visualization and how to apply the filters and parameters before the result data sets are displayed

For other posts relating to analytics and data integration visit http://www.ateam-oracle.com/dayne-carley

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha