Data Visualizer

Retail Analytics Platform implementations largely involve processing large volumes of data through several application modules, so it is important to know how to access the database to review settings, monitor load progress, and validate data tables. Database access is provided through the Oracle Data Visualization (DV) tool, which is included with all Retail Analytics Platform environments. The URL to access the DV application will be similar to the below URL:

https://{service}.retail.{region}ocs.oraclecloud.com/{solution-customer-env}/dv/?pageid=home

Note:

The best way to write ad hoc SQL against the database is through APEX. However, Data Visualizer can be used to create reusable datasets based on SQL that can be built into reports for longer term usage.

The RAP database comprises several areas for the individual application modules, but the majority of objects are exposed in DV as a connection to the RAFEDM01 database user. This user has read-only access to the majority of database objects which are involved in RI and Science implementations, as well as the tables involved in publishing data to the Planning modules. Follow the steps below to verify access to this database connection:

1. Log in to the DV application with a user that includes the RIApplicationAdministrator_JOB or DVContentAuthor groups in IDCS or OCI IAM (in non-production environments, use RIApplicationAdministrator_JOB_PREPROD or DVContentAuthor).

2. Expand the navigation panel using the Navigator icon in the upper left corner.

   

3. Click Data and, once the screen loads, click Connections. Confirm that you have a connection already available for RAFEDM (Retail Analytics Front End Data Mart).

   

4. Click the connection. The Add Data Set screen will load using the selected connection. A list of database users are displayed in the center panel.

    If any errors are displayed or a password is requested, contact Oracle Support for assistance.

   

5. Click the RAFEDM01 user.

6. Click C_ODI_PARAM on the following list of database tables.

7. Click the Add All button to select all columns in the table. In the bottom panel, click the Refresh icon to receive sample data. Confirm that one or more rows of data are displayed.

   

8. If you are performing a one-time query that does not need to be repeated or reused, you can stop at this point. You can also switch to the Enter SQL option in the upper right corner, to write simple queries on the database. However, if you want to create a reusable dataset, or expose the data for multiple users, proceed to the next steps.

   Note:

   The Enter SQL option does NOT allow for the full range of Oracle SQL commands. You cannot join multiple tables or perform complex operations such as pivots and partition-by clauses. The primary purpose is to select columns to add to a dataset and do basic manipulations of them.

9. Click the last icon in the data flow at the top of the screen.

10. Change the value of the Data Access setting to Live. 

11. Click Add to complete the dataset definition and start the dataset formatting process.

    

12. You can format the dataset on this screen for use in DV projects. You may rename the columns, change the datatype between Measure and Attribute, create new columns based on calculated values, and extract values from existing columns (such as getting the month from a date). Refer to Oracle Analytics documentation on Dataset creation for full details. When finished, click Create Project in the upper right corner to save the dataset and open a new project with it.

    

Once you have verified database connectivity, you may continue on to creating more datasets and projects as needed. Datasets will be saved for your user and can be reused at later dates without having to re-query the database. Saved datasets can be accessed using the Data screen from the Navigator panel.