Build a Semantic Model from Analytic Views in Oracle Autonomous Data Warehouse

In Oracle Analytics you can model Analytic Views in Autonomous Data Warehouse into Oracle Analytics and visualize the data.

Overview to Connecting to Analytic Views

In Oracle Analytics, you can model analytic views from Autonomous Data Warehouse and visualize the data. Analytic views accelerate analytical queries against data stored in Oracle tables and views. Analytic views allow you to easily add aggregations and calculations to data that can be queried with relatively simple SQL.

You must include the analytics views in a semantic model, and then upload the semantic model to Oracle Analytics. After you upload the semantic model, you can visualize the dimensions, measures, and custom calculated measures in Oracle Analytics visualization workbooks.

What You Need

  • Oracle Autonomous Data Warehouse, 18c or later
  • Oracle SQL Developer, 19.x or later
  • Oracle Analytics Cloud, latest update
  • Oracle Analytics Client Tools (Model Administration Tool), latest update (minimum version 6.0)

Create and Upload a Semantic Model based on an Analytic View

Create and upload a semantic model based on an analytic view in a Oracle Autonomous Data Warehouse so that you can visualize the data.

Before you start, make sure that you have the required components, see Overview to Connecting to Analytic Views.
  1. Optional: Use SQL Developer to identify and validate the analytic views in your Oracle Autonomous Data Warehouse data source that you'd like to visualize in Oracle Analytics.
    For example, if you're using SQL Developer, right-click the analytic view and select Validate Analytic View. Make sure that the analytic view is valid before you proceed.
  2. To include your analytic views in a new semantic model that you can upload to Oracle Analytics:
    1. In Model Administration Tool, click File, then New Repository.
    2. At the Repository Information page, specify a Name and Password.
    3. At the Select Data Source page:
      For Connection Type, select Oracle Analytic Views.
      For Data Source Name, copy in the Oracle Autonomous Data Warehouse connection URL from the tnsnames.ora file. In your Oracle Autonomous Data Warehouse instance, extract the tnsnames.ora file from the file. Copy the text description string, including the brackets as shown.

      For User name and Password, specify the credentials for your Oracle Autonomous Data Warehouse instance.
    4. At the Select Metadata Objects page, move the analytic views you want to visualize from the Data source view list to the Repository View list.
  3. To include analytic views in an existing semantic model in Oracle Analytics Cloud:
    1. In Model Administration Tool, open the semantic model using the In the Cloud mode.
    2. Click File, then Import Metadata.
    3. Follow the inline instructions to connect to Oracle Autonomous Data Warehouse and import the metadata.
  4. In the Physical pane, verify that you can see the analytic views that you want to visualize.
  5. Drag the analytic views from the Physical pane to the Presentation pane.
  6. Upload the semantic model containing the analytic views to Oracle Analytics Cloud.

    To upload the semantic model .rpd file, overwriting the existing semantic model (if any):

    1. In Oracle Analytics, click Console, then Snapshots.
    2. From the Page menu, select Replace Data Model.
    3. In the Replace Data Model page, select your new semantic model file, then click Replace.

    To upload changes to an existing semantic model that you opened using the In the Cloud mode, publish the semantic model to Oracle Analytics Cloud. In Model Administration Tool, click File, then Cloud, then Publish.

    You're now ready to visualize the data from the analytic views.

Connect to Analytic Views in Oracle Autonomous Data Warehouse

Connect to analytic views to visualize data in Oracle Autonomous Data Warehouse.

Before you start, ask your Oracle Analytics administrator to make analytic views available through a local subject area (semantic model).
  1. In Oracle Analytics, on the Home page, click Create and then click Dataset.
  2. Click Local Subject Area.
  3. Select a subject area that's based on an analytic view.
  4. Select the facts and measures that you want to analyze and add to a new dataset.
    You can now visualize data in this dataset.