1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Build a Semantic Model's Physical Layer
  4. Modify a Database's Data Source Properties and Supported Query Features

Modify a Database's Data Source Properties and Supported Query Features

This topic provides information about how to modify a database object's properties.

Topics:

Add or Modify a Database's Data Source Properties

Use this topic to understand and specify a database's data source properties.

These are the data source properties that you can assign to a database:

  • Virtual Private Database - Select to identify the database source as a virtual private database (VPD). When a VPD is used, returned data results are contingent on the user's authorization credentials. Therefore, it's important to identify these sources. These data results affect the validity of the query result set that's used with caching. See About Row-Level Security.

    If you select this option, then you also should select the Security Sensitive option in the session variable's Variables tab.

  • Siebel CRM Database - Select to indicate that the definition of physical tables and columns for Siebel CRM tables was derived from the Siebel metadata dictionary.

  • Allow direct database requests by default - If this property is configured incorrectly, it can expose sensitive data to an unintended audience.

    Select to allow all users to run physical queries. The Oracle Analytics query engine sends unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set can be rendered in the Oracle Analytics query engine and then charted, rendered, and treated as an Oracle Analytics request.

    If you want most but not all users to be able to run physical queries, select this option and use the Query Limits tab to limit queries for specific application roles.

  • Allow populate queries by default - Select to allow everyone to run POPULATE SQL. If you want most, but not all, users to be able to run POPULATE SQL, select this option and then limit queries for specific users or groups.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer and locate and double-click a database.
  4. In the database's tab, click Advanced.
  5. Go to the Data Source Properties section of the Features table and specify the database's data source properties.
  6. Click Save.

What Are Supported Query Features?

The Oracle Analytics query engine uses the specified query features settings to determine how to query the data source. The supported query features are automatically populated with values appropriate for your semantic model's data source.

Query features are the SQL expressions, statements, function, operations, and other features that you can run against the data source such as a query that uses an ISDESCENDANT statement. Operations such as ADD or SQRT (square root) operations are supported.

When a supported query feature is selected or a value is specified, the data source supports the feature and the Oracle Analytics query engine pushes the function or calculation to the data source for improved performance.

When a supported query feature is deselected or no value is specified, then it isn't supported in the data source and the calculation or processing is performed in the Oracle Analytics query engine.

In most cases, you should keep the default selections and values. If you enable or change query features that the data source doesn't support, your query may return errors and unexpected results. If you disable supported SQL features, the server could issue less efficient SQL to the data source. Before you change any of the defaults, confirm that the query feature is supported by the data source.

See Modify a Database's Supported Query Features.

These are some reasons why you would update a database's query feature settings:

  • If you're upgrading to a newer version of a data source. In this case, you can tailor the query features for the data source to see if the updated feature is reflected in the Oracle Analytics query engine defaults.

  • If a data source supports a particular feature such as left outer join queries but you want to prohibit the Oracle Analytics query engine from sending such queries to a particular data source.

  • If you have federated data sources that run functions differently. In this case, you can disable the appropriate functions so that Oracle Analytics query engine performs calculations consistently and produce correct query results.

  • If you're troubleshooting a query or other operation that isn't working as expected.

Modify a Database's Supported Query Features

You can view and modify how the database's supported query features are set. The data source determines how the default query feature values are set.

In most cases, you should keep the default selections and values. If you enable or change query features that the data source doesn't support, your query may return errors and unexpected results. If you disable supported SQL features, the server could issue less efficient SQL to the data source. Before you change any of the defaults, confirm that the query feature is supported by the data source. For more information, see What Are Supported Query Features?

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer and locate and double-click a database.
  4. In the database's tab, click Advanced.
  5. Go to the Supported Query Features section of the Features table and modify the database's query features as needed.
  6. Click Save.