SMML Elements: Physical Table
The SMML physical table element corresponds to the physical table schema which is part of the physical layer in a semantic model. The physical table schema contains physical table objects and elements.
Physical Table Elements
-
name(required property) — The name of the physical table. -
description— The description of the physical table. -
tags— Keywords assigned to this object. This element corresponds to the Tags field. -
sourceType— Indicates the type of physical table. -
sourceTable— For alias tables only. References the physical table that is being used as the source for this alias table. -
additionalKeys— Keys that can be defined in addition to primary and display columns. -
joins— Defines the joins for this link. It contains different child elements, depending on the type of join. -
dynamicName— If a session variable is being used to specify the name of this physical table, this element references that session variable. -
sqlHints— For Oracle Databases only. Lists any database hints, such as index hints or leading hints, that were specified for this physical table. -
overrideSourceCacheSetting— For alias tables only. If set toTRUE, indicates that the alias table has its own cache properties that override the cache properties of the source table. -
caching— If set to true, indicates that this table is included in the Oracle Analytics query engine query cache. See Caching Elements. -
eventPollingFrequency— The polling frequency, in seconds. Only applies if this table is anOracle Analytics query engine event polling table. The default value is 3600 seconds. -
selectStatements— List of select statements that can be defined for different databases. -
physicalColumns— Specifies the physical columns that belong to this physical table.
SelectStatement Elements
-
databaseType— The type of data source. See DataBase Type Enumerated Values. -
query— Data source specific query for the physical table.
PhysicalColumn Elements
-
name(required property) — The name of the physical column. -
description— The description of the physical column. -
dataType— The data type of the physical column, such asVARCHAR. See DataType Enumerated Values. -
length— The length of the physical column. -
nullable— If set toTRUE, indicates that null values are allowed for the column. This allows null values to be returned to the user, which is expected with certain functions and with outer joins.
Join Elements
-
joinType— Indicates the type of join. See JoinType Enumerated Values. -
useJoinExpression— If set toTRUE, then specify the join expression. -
rightTable(required property) — Indicates the join's right table. -
cardinality— Indicates the cardinality of the join. See Cardinality Enumerated Values. -
hint— For Oracle Databases only. Lists any database hints, such as index hints or leading hints, that were specified for this complex join. -
joinConditions— A list of matching columns in a join between two tables. A join condition defines a relationship between two tables. It involves columns that relate the two tables. A join condition may have more than one column in a table related to columns in another table. -
joinExpression— A join condition defined as an expression instead of simply matching columns from two tables. For example,Table1.Column1 = Table2.Column1 AND Table1.Column2 = <value>. See Expression Elements.
Cardinality Enumerated Values
-
ONE_TO_ONE -
ZERO_OR_ONE_TO_ONE -
ONE_TO_ZERO_OR_ONE -
ZERO_OR_ONE_TO_ZERO_OR_ONE -
ONE_TO_MANY -
ZERO_OR_ONE_TO_MANY -
MANY_TO_ONE -
MANY_TO_ZERO_OR_ONE -
MANY_TO_MANY -
UNKNOWN
ComplexJoinCondition Elements
-
expressionTemplate(required property) — Defines and stores the join's expression. -
expressionObjects— The objects referenced in the join expression.
JoinCondition Elements
-
leftColumn(required property) — Indicates columns in a join condition from the left side of a table that is in a join. rightColumn(required property) — Indicates columns in a join condition from the right side of a table that is in a join.
Syntax
{
"$schema": "http://json-schema.org/draft-04/schema#",
"$ref": "#/definitions/physicalTable",
"definitions": {
"physicalTable": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"tags": {
"type": "array",
"items": {
"type": "string"
}
},
"sourceType": {
"type": "string",
"enum": [
"TABLE",
"STORED_PROCEDURE",
"SELECT"
]
},
"sourceTable": {
"type": "string"
},
"additionalKeys": {
"type": "array",
"items": {
"type": "array",
"items": {
"type": "string"
}
}
},
"joins": {
"type": "array",
"items": {
"$ref": "#/definitions/Join"
}
},
"dynamicName": {
"type": "string"
},
"sqlHints": {
"type": "string"
},
"caching": {
"$ref": "common_schemas#/definitions/Caching"
},
"overrideSourceCacheSetting": {
"type": "boolean"
},
"eventPollingFrequency": {
"type": "string"
},
"selectStatements": {
"type": "array",
"items": {
"$ref": "#/definitions/SelectStatement"
}
},
"physicalColumns": {
"type": "array",
"items": {
"$ref": "#/definitions/PhysicalColumn"
}
}
},
"required": [
"name"
],
"title": "PhysicalTable"
},
"SelectStatement": {
"type": "object",
"additionalProperties": false,
"properties": {
"databaseType": {
"$ref": "common_schemas#/definitions/DatabaseType"
},
"query": {
"type": "string"
}
},
"title": "SelectStatement"
},
"PhysicalColumn": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"dataType": {
"$ref": "common_schemas#/definitions/DataType"
},
"length": {
"type": "integer"
},
"nullable": {
"type": "boolean"
}
},
"required": [
"name"
],
"title": "PhysicalColumn"
},
"Join": {
"type": "object",
"additionalProperties": false,
"properties": {
"joinType": {
"$ref": "common_schemas#/definitions/JoinType"
},
"useJoinExpression": {
"type": "boolean"
},
"rightTable": {
"type": "string",
"rawType": "table"
},
"cardinality": {
"$ref": "#/definitions/Cardinality"
},
"hint": {
"type": "string"
},
"joinConditions": {
"type": "array",
"items": {
"$ref": "#/definitions/JoinCondition"
}
},
"joinExpression": {
"$ref": "common_schemas#/definitions/Expression"
}
},
"required": [
"rightTable"
],
"title": "Join"
},
"Cardinality": {
"type": "string",
"enum": [
"ONE_TO_ONE",
"ZERO_OR_ONE_TO_ONE",
"ONE_TO_ZERO_OR_ONE",
"ZERO_OR_ONE_TO_ZERO_OR_ONE",
"ONE_TO_MANY",
"ZERO_OR_ONE_TO_MANY",
"MANY_TO_ONE",
"MANY_TO_ZERO_OR_ONE",
"MANY_TO_MANY",
"UNKNOWN"
],
"title": "Cardinality"
},
"ComplexJoinCondition": {
"type": "object",
"additionalProperties": false,
"properties": {
"expressionTemplate": {
"type": "string"
},
"expressionObjects": {
"type": "array",
"items": {
"type": "string"
}
}
},
"required": [
"expressionTemplate"
],
"title": "ComplexJoinCondition"
},
"JoinCondition": {
"type": "object",
"additionalProperties": false,
"properties": {
"leftColumn": {
"type": "string",
"rawType": "column"
},
"rightColumn": {
"type": "string",
"rawType": "column"
}
},
"required": [
"leftColumn",
"rightColumn"
],
"title": "JoinCondition"
}
}
}