SMML Elements: Database
The SMML database element corresponds to the database schema which is part of the physical layer of a semantic model. The database schema contains database objects and elements.
Database Elements
-
name
(required property) — The name of the database. -
description
— The description of the database. -
tags
— The keywords assigned to this object. This element corresponds to the Tags field. -
databaseType
(required property) — The type of data source. For example, Oracle Database, SQL Server, or DB2. See DataBase Type Enumerated Values. -
persistConnectionPool
— References the connection pool used as the persist connection pool, if one has been assigned. A persist connection pool is a database property that's used for specific types of queries (typically used to support Marketing queries). -
connectionPools
— References the connection pools for this database object. -
featureOverrides
— Lists the SQL features for this database. -
joins
— Defines the joins for this link. It contains different child elements, depending on the type of join. -
queryLimits
— When selected, allows all users to runPOPULATE SQL
. If you want most, but not all, users to be able to runPOPULATE SQL
, select this option and then limit queries for specific users or groups. See Set Query Limits in Building Semantic Models in Oracle Analytics Cloud. -
virtualPrivateDatabase
— If set toTRUE
, identifies the physical database source as a virtual private database (VPD). When a VPD is used, returned data results are contingent on the user's authorization credentials. This option is used with the Security Sensitive option for session variables. -
crmMetadataTables
— For legacy Siebel Systems sources only. If set toTRUE
, Oracle Analytics looks for the table definition in Oracle's Siebel CRM-specific tables. -
allowDirectDatabaseRequests
— If set toTRUE
, allows all users to run physical queries. The Oracle Analytics query engine sends unprocessed, user-entered, physical SQL directly to an underlying database. If you want most, but not all, users to be able to run physical queries, select this option and then limit queries for specific users or groups. See Set Query Limits in Building Semantic Models in Oracle Analytics Cloud. -
allowPopulateQueries
— If set toTRUE
, allows all users to runPOPULATE SQL
. If you want most, but not all, users to be able to runPOPULATE SQL
, select this option and then limit queries for specific users or groups. See Set Query Limits in Building Semantic Models in Oracle Analytics Cloud.
ConnectionPool Elements
-
name
(required property) — The name of the connection. -
description
— The description of the connection. -
connection
(required property) — References the connection defined in Oracle Analytics Cloud. -
callInterface
— The call interface type. -
maxConnections
— The maximum number of total connections allowed to this connection pool for a given user. -
requiresFullyQualifiedTableNames
— If set toTRUE
, indicates that this database requires fully qualified table names. The fully qualified names are based on the physical object names in the semantic model. -
connectionTimeOut
— The amount of time that a connection to the data source remains open after a request completes. During this time, new requests use this connection rather than open a new one (up to the number specified for the maximum connections). The time is reset after each completed connection request. If you set the timeout to 0 (the default), connection pooling is disabled. In other words, each connection to the data source terminates immediately when the request completes. Any new connections either use another connection pool or open a new connection. connectionTimeoutUnit
— The unit of measure such asMINUTES
.-
multithreaded
— If set toTRUE
, indicates that the connection pool supports multi-threading, or in other words, that one connection pool can support multiple queries. If this option is set to false, each query is tied to a single database connection. -
supportParams
— Indicates whether the data source supports parameters. If set tofalse
and the database features table supports parameters, a special code runs that enables the Oracle Analytics query engine to push filters (or calculations) with parameters to the database. -
isolationLevel
— For ODBC and DB2 gateways. Sets the transaction isolation level on each connection to the back-end database. The isolation level setting controls the default transaction locking behavior for all statements issued by a connection. Options are as follows:default
uses the default transaction locking behavior of the data source.readCommitted
specifies that shared locks are held while the data is read to avoid dirty reads.readUncommitted
implements dirty read (isolation level 0 locking). When this option is set, it is possible to read uncommitted or dirty data, change values in the data, and have rows appear or disappear in the data set before the end of the transaction.repeatableRead
places locks on all data that is used in a query, preventing other users from updating the data.serializable
places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.
-
runOnConnectScripts
— Contains a connection script that is run before the connection is established. -
runBeforeQueryScripts
— Contains a connection script that is run before the query is run. -
runAfterQueryScripts
— Contains a connection script that is run after the query is run. -
runOnDisconnectScripts
— Contains a connection script that is run after the connection is closed. -
writeBackConfig
— Indicates the data source's write back properties. -
permissions
— Lists the users and application roles and permissions to access the connection pool. See Permission Elements.
RunScript Elements
-
script
(required property) — Contains the connection script. disable
— If set toTRUE
, used to disable the connection script.
WriteBackConfig Elements
-
dbSupportsUnicode
— This attribute is set toTRUE
when working with columns of an explicit Unicode data type, such asNCHAR
, in an Unicode database. -
bulkInsertBufferSize
— Used for limiting the amount of data in kilobytes, each time data is inserted into a database table. -
transactionBoundary
— Controls the batch size for an insert in a database table. -
tempTablePrefix
— The first two characters in a temporary table name. The default value isTT
. -
tempTableOwner
— The table owner name used to qualify a temporary table name in a SQL statement, for example to create the table,owner.tablename
. -
tempTableDatabase
— Database where the temporary table is created. This property applies only to IBM OS/390, because IBM OS/390 requires the database name qualifier to be part of theCREATE TABLE
statement. -
tempTableSpace
— Tablespace where the temporary table will be created. This property applies only to IBM OS/390, because IBM OS/390 requires the tablespace name qualifier to be part of theCREATE TABLE
statement.
Feature Elements
-
name
(required property) — Indicates the name of the feature, such asLEFT_OUTER_JOIN_SUPPORTED
. -
value
(required property) — Indicates whether this feature is supported by the database, or provides the actual value of the feature (such as "0
" forMAX_COLUMNS_IN_SELECT
).
QueryLimit Elements
-
accessor
(required property) — Lists the application role to assign query limits to. -
maxRowSetting
— Specifies the status of the maximum number of rows limit. Valid values are:- Inherit — Inherits the
maxRows
limit from the parent application role. If there is no limit to inherit, then no limit is enforced. - Enable — Limits the number of rows to the value specified in
maxRows
. If the number of rows exceeds themaxRows
value, then the query is terminated. - Disable — Disables any limits set in
maxRows
. - Warn — Doesn't enforce limits set in
maxRows
, but logs any queries that exceed the set limit in the query log.
- Inherit — Inherits the
maxRows
— Indicates the maximum number of rows the accessor can retrieve from the database.-
maxTimeSetting
— Specifies the status of the maximum time limit. Valid values are:- Inherit — Inherits the
maxTime
limit from the parent application role. If there is no limit to inherit, then no limit is enforced. - Enable — Limits the number of minutes to the value specified in
maxTime
. - Disable — Disables any limits set in
maxTime
. - Warn — Doesn't enforce limits set in
maxTime
, but logs any queries that exceed the set limit in the query log.
- Inherit — Inherits the
-
maxTime
— The maximum number of minutes queries can run on a database. -
logicalQueryMaxTime
— The maximum time in seconds a logical query can run. -
directDatabaseRequests
— When selected, allows all users to run physical queries. The Oracle Analytics query engine will send unprocessed, user-entered, physical SQL directly to an underlying database. If you want most, but not all, users to be able to run physical queries, select this option and then limit queries for specific users or groups. restrictions
— Indicates restrictions in place for accessing the database.
Restriction Elements
type
— Indicates the type of restriction, whether the restriction isALLOW
orDENY
.day
— The day specified for a restriction.from
— Indicates the restriction start time.to
— Indicates the restriction end time.
Syntax
{
"$schema": "http://json-schema.org/draft-04/schema#",
"$ref": "#/definitions/database",
"definitions": {
"database": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"tags": {
"type": "array",
"items": {
"type": "string"
}
},
"databaseType": {
"$ref": "common_schemas#/definitions/DatabaseType"
},
"persistConnectionPool": {
"type": "string"
},
"connectionPools": {
"type": "array",
"items": {
"$ref": "#/definitions/ConnectionPool"
}
},
"featureOverrides": {
"type": "array",
"items": {
"$ref": "#/definitions/Feature"
}
},
"queryLimits": {
"type": "array",
"items": {
"$ref": "#/definitions/QueryLimit"
}
},
"virtualPrivateDatabase": {
"type": "boolean"
},
"crmMetadataTables": {
"type": "boolean"
},
"allowDirectDatabaseRequests": {
"type": "boolean"
},
"allowPopulateQueries": {
"type": "boolean"
}
},
"required": [
"databaseType",
"name"
],
"title": "Database"
},
"ConnectionPool": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"connection": {
"type": "string"
},
"remoteConnection": {
"type": "boolean"
},
"maxConnections": {
"type": "integer"
},
"requiresFullyQualifedTableNames": {
"type": "boolean"
},
"connectionTimeout": {
"type": "integer"
},
"connectionTimeoutUnit": {
"type": "string",
"enum": ["WHEN_QUERY_COMPLETES", "DAYS", "HOURS", "MINUTES", "SECONDS", "NEVER"]
},
"multithreaded": {
"type": "boolean"
},
"supportParams": {
"type": "boolean"
},
"isolationLevel": {
"type": "string"
},
"runOnConnectScripts": {
"type": "array",
"items": {
"$ref": "#/definitions/RunScript"
}
},
"runBeforeQueryScripts": {
"type": "array",
"items": {
"$ref": "#/definitions/RunScript"
}
},
"runAfterQueryScripts": {
"type": "array",
"items": {
"$ref": "#/definitions/RunScript"
}
},
"runOnDisconnectScripts": {
"type": "array",
"items": {
"$ref": "#/definitions/RunScript"
}
},
"writeBackConfig": {
"$ref": "#/definitions/WriteBackConfig"
},
"permissions": {
"type": "array",
"items": {
"$ref": "common_schemas#/definitions/Permission"
}
}
},
"required": [
"connection",
"name"
],
"title": "ConnectionPool"
},
"RunScript": {
"type": "object",
"additionalProperties": false,
"properties": {
"script": {
"type": "string"
},
"disable": {
"type": "boolean"
}
},
"required": [
"script"
],
"title": "RunScript"
},
"WriteBackConfig": {
"type": "object",
"additionalProperties": false,
"properties": {
"dbSupportsUnicode": {
"type": "boolean"
},
"bulkInsertBufferSize": {
"type": "integer"
},
"transactionBoundary": {
"type": "integer"
},
"tempTablePrefix": {
"type": "string"
},
"tempTableOwner": {
"type": "string"
},
"tempTableDatabase": {
"type": "string"
},
"tempTableSpace": {
"type": "string"
}
},
"title": "WriteBackConfig"
},
"Feature": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"value": {
"oneOf": [
{"type": "string"},
{"type": "boolean"},
{"type": "integer"}
]
}
},
"required": [
"name",
"value"
],
"title": "Feature"
},
"QueryLimit": {
"type": "object",
"additionalProperties": false,
"properties": {
"accessor": {
"type": "string"
},
"maxRowSetting": {
"type": "string",
"enum": ["INHERIT", "ENABLE", "DISABLE", "WARN"]
},
"maxRows": {
"type": "integer"
},
"maxTimeSetting": {
"type": "string",
"enum": ["INHERIT", "ENABLE", "DISABLE", "WARN"]
},
"maxTime": {
"type": "integer"
},
"logicalQueryMaxTime": {
"type": "integer"
},
"directDatabaseRequests": {
"type": "string",
"enum": ["INHERIT", "ALLOW", "DISALLOW"]
},
"restrictions": {
"type": "array",
"items": {
"$ref": "#/definitions/Restriction"
}
}
},
"required": [
"accessor"
],
"title": "QueryLimit"
},
"Restriction": {
"type": "object",
"additionalProperties": false,
"properties": {
"type": {
"type": "string",
"enum": ["ALLOW", "DENY"]
},
"day": {
"type": "string",
"enum": ["SUNDAY", "MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY"]
},
"from": {
"type": "integer"
},
"to": {
"type": "integer"
}
},
"required": [
"day",
"from",
"to",
"type"
],
"title": "Restriction"
}
}
}