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 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. See Set Query Limits.

• virtualPrivateDatabase — If set to TRUE, 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 to TRUE, Oracle Analytics looks for the table definition in Oracle's Siebel CRM-specific tables.

• allowDirectDatabaseRequests — If set to TRUE, 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.

• allowPopulateQueries — If set to TRUE, allows all users 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. See Set Query Limits.

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.

• callInterface — The call interface type.

• maxConnections— The maximum number of total connections allowed to this connection pool for a given user.

• requiresFullyQualifiedTableNames — If set to TRUE, 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 as MINUTES.

• multithreaded — If set to TRUE, 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 to false 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 to TRUE, used to disable the connection script.

WriteBackConfig Elements

• dbSupportsUnicode — This attribute is set to TRUE when working with columns of an explicit Unicode data type, such as NCHAR, 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 is TT.

• 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 the CREATE 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 the CREATE TABLE statement.

Feature Elements

• name (required property) — Indicates the name of the feature, such as LEFT_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" for MAX_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 the maxRows 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.
• 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.

• 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 is ALLOW or DENY.
• 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"
        }
    }
}