Querical Studio
Sign inStart free trial

Setup guide

Connect a Fusion pod in about 10 minutes.

Querical Studio needs one BI Publisher data model and report in each pod, and a user that can run it. Nothing is installed on Oracle's servers beyond a standard BI catalog object.

Before you start

  • A Fusion user with the BI Administrator or BI Author role to create catalog objects (for example the Application Implementation Consultant).
  • The pod URL, such as https://fa-xxxx-saasfaprod1.fa.ocs.oraclecloud.com.
  • The data model script: Download SQL_RUNNER_DM.sql

1. Create the data model

  1. Open /analytics on your pod (Navigator → Tools → Reports and Analytics → Browse Catalog).
  2. Create the folder Shared Folders › Custom › FusionSQLStudio (the folder name is kept for compatibility; you can choose another and change the path in step 4).
  3. New → Data Model. In Parameters, add P_SQL, data type String, parameter type Text.
  4. New data set → SQL Query. Name it SQL_RUNNER, data source ApplicationDB_FSCM (any ApplicationDB works; choose ApplicationDB_HCM for HCM-heavy work), type of SQL Procedure Call, and paste the contents of the downloaded script.
  5. Save the data model as SQL_RUNNER_DM in the folder.

2. Create the report

  1. New → Report → use the data model SQL_RUNNER_DM.
  2. Choose to create the report without a layout (or with a simple table), and set the default output format to Data (XML).
  3. Save it as SQL_RUNNER in the same folder. The report path is /Custom/FusionSQLStudio/SQL_RUNNER.xdo.

3. Integration user

Use a dedicated integration user rather than a person's account. It needs:

  • Permission to run the report: a role that includes BI Consumer and read access to the folder (set it under the folder's Permissions).
  • The data security of the data it should see. Querical Studio cannot see more than this user can.
  • A password that does not expire, or a reminder to rotate it. Store it only in Querical Studio.

4. Add the pod in Querical Studio

  1. Sign in to Querical Studio → Manage environments → Add environment.
  2. Enter a name (for example "UAT"), the pod URL, the report path and the integration user.
  3. For production pods, switch on Production, and choose which roles may use it, whether a business reason is required, and whether queries need a second approver.

5. Check it works

Select the pod in the top bar and run SELECT USER, SYSDATE FROM dual. Then open the catalog and run any query, or open a 360 view and paste a number you know.

Troubleshooting

Report not found / 404
Check the report path (it is case-sensitive and starts with /Custom/, without "Shared Folders").
Access denied
The integration user lacks permission on the folder or report, or its password has changed.
ORA-00942 table or view does not exist
The table is not visible to the chosen data source. Try another ApplicationDB data source in the data model.
Timeouts on very large queries
Use paging in the grid or a background extract; add filters on indexed columns such as numbers and dates.

Questions? Email engage@quericalstudio.com.