SMML Elements: Logical Table
The SMML logical table element corresponds to the logical table schema in the logical layer of a semantic model. The logical table schema contains logical objects and elements.
logicalTable Elements
-
name(required property) — The name of the logical table. -
description— The description of the logical table. -
tags— Keywords assigned to this object. This element corresponds to the Tags field. -
type(required property) — Indicates the type of table. -
primaryKey— Indicates that the key is the logical primary key for the table. -
logicalColumns— Lists the logical column objects in the logical layer. -
logicalTableSources— References the logical table sources for this logical table. -
joins— Defines the joins for this link. It contains different child elements, depending on the type of join. -
hierarchyType— Indicates the type of hierarchy. -
levelBasedHierarchy— Indicates whether this is a level-based hierarchy. -
parentChildHierarchy— Indicates whether this is a parent-child hierarchy. -
dataFilters— Data filters defined for this object.
LevelBasedHierarchy Elements
-
defaultRootLevel— References the default root level of this dimension. When there are many root levels in a dimension, the default root level is the one that's used for drill-down. -
ragged— If set to true, indicates that this dimension hierarchy is unbalanced. An unbalanced hierarchy is one that contains levels that are not at the same depth. -
skipped— If set to true, indicates that this dimension hierarchy is a skip-level hierarchy. A skip-level hierarchy is one in which there are members that do not have a value for a particular parent level. -
logicalLevels(required property) — References the logical levels for this dimension. -
logicalHierarchies(required property) — References the logical hierarchies for this dimension.
ParentChildHierarchy Elements
-
name(required property) — The name of this parent child hierarchy. -
description— The description of this parent child hierarchy. -
logicalLevels(required property) — References the logical levels for this dimension -
relationshipTables(required property) — References the relationship table defined for the parent-child hierarchy.
LogicalHierarchy Elements
-
name(required property) — The name of this logical hierarchy. -
description— The description of this logical hierarchy. -
levels(required property) — References the child levels that have been defined for this logical level.
LogicalLevel Elements
-
name(required property) — The name of this logical level. -
grandTotalLevel— If set toTRUE, indicates that this level is the Grand Total level, which is a special level representing the grand total for a dimension. Each dimension can have just one Grand Total level. A Grand Total level doesn't contain dimensional attributes and doesn't have a level key. -
displayKey— This element references the columns designated to be used for display for this hierarchy. -
preferredDrillPath— If a drill path has been defined that's outside the normal drill path defined by the dimension level hierarchy, this element references the level users should drill to. -
primaryKey— Indicates that this key is the logical primary key for the table. -
additionalKeys— Keys that can be defined in addition to primary and display columns. -
parentKey— References the parent key for this level (for dimensions with parent-child hierarchy only). -
chronologicalKey— Indicates that this key is a chronological key for a time dimension. -
disableAggregateToHigherLevel— By default, measure values at a particular level constitute aggregated measures. Set toTRUEto disable. -
numberofElements— The number of elements that exist at this logical level.
LogicalColumn Elements
-
name(required property) — The name of this logical column. -
description— The description of this logical column. -
dataType— The datatype of the logical column. See DataType Enumerated Values. -
sortBy— If the sort order for this logical column is based on a different logical column, this element indicates the name of the logical column used for sorting. -
descriptorColumn— When multilingual columns are based on a lookup function, it's common to specify the non-translated lookup key column as the descriptor ID column of the translated column. This element references the descriptor ID column. -
writeable— When set toTRUE, indicates that write-back has been enabled for this column. This feature is typically used with ADF Business Component data sources. -
logicalColumnSource(required property) — References the source for this logical column. -
aggregation— Aggregation function applied to the data in the column. -
dataFilters— Data filters defined for this object. See DataFilter Elements. -
logicalLevel— For dimension columns, the level this column has been assigned to. For level-based measures, the level at which the column has been explicitly fixed.
LogicalColumnSource Elements
-
derivedFrom(required property) — References the logical table source that this column mapping belongs to. Each column mapping must specify only one logical table source. -
physicalMappings— Contains the expression that identifies the physical column for this column mapping. See Expression Elements. -
logicalExpression— Contains the expression that identifies the logical column for this column mapping. See Expression Elements.
PhysicalMapping Elements
-
logicalTableSource(required property) — References the logical table source for this logical table. -
physicalExpression(required property) — Contains the expression that identifies the physical column for this column mapping. See Expression Elements.
LogicalTableSource Elements
-
name(required property) — The name of the logical table source. -
description— The description of the logical table source. -
disable— If set toTRUE, used to disable the logical table source. -
priority— Indicates the priority group number of this logical table source. Logical table source priority group numbers are assigned to indicate which logical table source is used for queries where there is more than one logical table source that can satisfy the requested set of columns. -
tableMapping(required property) — Element that defines the mapping of logical table source to physical tables and the join types between the physical tables. -
dataGranularity— The level of detail of the data in the logical table source. -
dataFragmentation— Contains the expression that defines how the source is fragmented. See Expression Elements. -
combineWithOtherFragments— If set toTRUE, used to combine the data with other fragmented sources. -
enableFragmentSelection— If set toTRUE, used to enable the data driven fragment selection. -
dataFilter— Filter to limit rows for or in a logical table source. See DataFilter Elements. -
distinctValues— If set toTRUE, used if the values for the logical table source are unique.
TableMapping Elements
-
tables— Physical tables mapped to a logical table source. -
logicalTableSourceJoins— Joins between tables in a logical table source.
LogicalTableSourceJoin Elements
-
leftTable(required property) — Indicates the join's left table. -
rightTable(required property) — Indicates the join's right table. -
joinType— Indicates the type of join. See JoinType Enumerated Values. -
disable— If set toTRUE, disables the join between the physical tables.
Join Elements
-
rightTable(required property) — Indicates the join's right table. -
joinType— Indicates the type of join. See JoinType Enumerated Values. -
cardinality— Indicates the cardinality of the join. See Cardinality Enumerated Values. -
drivingTable— If a driving table has been specified for the key, this attribute references that logical table.
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
Aggregation Elements
-
rule— Aggregation rule for the column. -
dataIsDense— If set toTRUE, indicates that all sources to which this column is mapped have a row for every combination of logical hierarchy levels that they represent. Setting this option toTRUEwhen any table source used by this column doesn't contain dense data returns incorrect results. -
countDistinctOverrides— If the aggregation rule isCOUNT_DISTINCT, you can specify a different aggregation rule for each logical table sources mapped to a logical column. -
dimensionBasedRules— Dimension-specific aggregation rules. -
aggregateByLevels— Level-based measure calculated to a specific level of aggregation.
DimensionBasedRule Elements
-
dimension(required property) — Specifies the dimension for which an aggregation rule needs to be applied. -
rule(required property) — Aggregation rule for the column. -
aggregateExpression— If the rule isEXPRESSION, specify an aggregate expression for the dimension. See Expression Elements.
CountDistinctOverrides Elements
-
logicalTableSource(required property) — References the logical table source. -
rule— Aggregation rule for the column.
DerivedFrom Enumerated Values
-
PHYSICAL_COLUMNS -
LOGICAL_COLUMNS
AggregationRule Enumerated Values
-
NONE -
SUM -
AVG -
COUNT -
COUNT_DISTINCT -
MAX -
FIRST -
LAST -
MEDIAN -
STD_DEV -
STD_DEV_POP -
EVALUATE_AGGR -
BASED_ON_DIMENSION
AggregationRuleBasedOnDimension Enumerated Values
-
NONE -
SUM -
AVG -
COUNT -
COUNT_DISTINCT -
MAX -
MIN -
FIRST -
LAST -
MEDIAN -
STD_DEV -
STD_DEV_POP -
EVALUATE_AGGR -
EXPRESSION
RelationshipTable Elements
-
logicalTableSource(required property) — References the logical table source for this logical table. -
table(required property) — Indicates the name of the parent-child relationship table that the source is based on. -
memberKey(required property) — The name of the column in the parent-child relationship table that identifies the member. -
parentKey(required property) — References the parent key for this level (for dimensions with parent-child hierarchies only). -
distance(required property) — The name of the column in the parent-child relationship table that specifies the number of parent-child hierarchical levels from the member to the ancestor. -
leafNodeIdentifier(required property) — The name of the column in the parent-child relationship table that indicates if the member is a leaf member.
Syntax
{
"$schema": "http://json-schema.org/draft-04/schema#",
"$ref": "#/definitions/logicalTable",
"definitions": {
"logicalTable": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"tags": {
"type": "array",
"items": {
"type": "string"
}
},
"type": {
"type": "string",
"enum": [
"DIMENSION",
"FACT",
"LOOKUP"
]
},
"primaryKey": {
"type": "array",
"items": {
"type": "string"
}
},
"logicalColumns": {
"type": "array",
"items": {
"$ref": "#/definitions/LogicalColumn"
}
},
"logicalTableSources": {
"type": "array",
"items": {
"$ref": "#/definitions/LogicalTableSource"
}
},
"joins": {
"type": "array",
"items": {
"$ref": "#/definitions/Join"
}
},
"hierarchyType": {
"type": "string"
},
"levelBasedHierarchy": {
"$ref": "#/definitions/LevelBasedHierarchy"
},
"parentChildHierarchy": {
"$ref": "#/definitions/ParentChildHierarchy"
},
"dataFilters": {
"type": "array",
"items": {
"$ref": "common_schemas#/definitions/DataFilter"
}
}
},
"required": [
"name",
"type"
],
"title": "LogicalTable"
},
"LevelBasedHierarchy": {
"type": "object",
"additionalProperties": false,
"properties": {
"defaultRootLevel": {
"type": "string"
},
"ragged": {
"type": "boolean"
},
"skipped": {
"type": "boolean"
},
"logicalLevels": {
"type": "array",
"items": {
"$ref": "#/definitions/LogicalLevel"
}
},
"logicalHierarchies": {
"type": "array",
"items": {
"$ref": "#/definitions/LogicalHierarchy"
}
}
},
"required": [
"logicalHierarchies",
"logicalLevels"
],
"title": "LevelBasedHierarchy"
},
"ParentChildHierarchy": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"logicalLevels": {
"type": "array",
"items": {
"$ref": "#/definitions/LogicalLevel"
}
},
"relationshipTables": {
"type": "array",
"items": {
"$ref": "#/definitions/RelationshipTable"
}
}
},
"required": [
"logicalLevels",
"name",
"relationshipTables"
],
"title": "ParentChildHierarchy"
},
"LogicalHierarchy": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"levels": {
"type": "array",
"items": {
"type": "string"
}
}
},
"required": [
"levels",
"name"
],
"title": "LogicalHierarchy"
},
"LogicalLevel": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"grandTotalLevel": {
"type": "boolean"
},
"displayKey": {
"type": "string"
},
"preferredDrillPath": {
"type": "array",
"items": {
"type": "string"
}
},
"primaryKey": {
"type": "array",
"items": {
"type": "string"
}
},
"additionalKeys": {
"type": "array",
"items": {
"type": "array",
"items": {
"type": "string"
}
}
},
"parentKey": {
"type": "string"
},
"chronologicalKey": {
"type": "array"
},
"disableAggregateToHigherLevel": {
"type": "boolean"
},
"numberOfElements": {
"type": "integer"
}
},
"required": [
"name"
],
"title": "LogicalLevel"
},
"LogicalColumn": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"dataType": {
"$ref": "common_schemas#/definitions/DataType"
},
"sortBy": {
"type": "string"
},
"descriptorColumn": {
"type": "string"
},
"writeable": {
"type": "boolean"
},
"logicalColumnSource": {
"$ref": "#/definitions/LogicalColumnSource"
},
"aggregation": {
"$ref": "#/definitions/Aggregation"
},
"dataFilters": {
"type": "array",
"items": {
"$ref": "common_schemas#/definitions/DataFilter"
}
},
"logicalLevel": {
"type": "string"
}
},
"required": [
"logicalColumnSource",
"name"
],
"title": "LogicalColumn"
},
"LogicalColumnSource": {
"type": "object",
"additionalProperties": false,
"properties": {
"derivedFrom": {
"type": "string",
"enum": [
"PHYSICAL_COLUMNS",
"LOGICAL_COLUMNS"
]
},
"physicalMappings": {
"type": "array",
"items": {
"$ref": "#/definitions/PhysicalMapping"
}
},
"logicalExpression": {
"$ref": "common_schemas#/definitions/Expression"
}
},
"required": [
"derivedFrom"
],
"title": "LogicalColumnSource"
},
"PhysicalMapping": {
"type": "object",
"additionalProperties": false,
"properties": {
"logicalTableSource": {
"type": "string"
},
"physicalExpression": {
"$ref": "common_schemas#/definitions/Expression"
}
},
"required": [
"logicalTableSource",
"physicalExpression"
],
"title": "PhysicalMapping"
},
"LogicalTableSource": {
"type": "object",
"additionalProperties": false,
"properties": {
"name": {
"type": "string"
},
"description": {
"type": "string"
},
"disable": {
"type": "boolean"
},
"priority": {
"type": "integer"
},
"tableMapping": {
"$ref": "#/definitions/TableMapping"
},
"dataGranularity": {
"type": "array",
"items": {
"type": "string"
}
},
"dataFragmentation": {
"$ref": "common_schemas#/definitions/Expression"
},
"combineWithOtherFragments": {
"type": "boolean"
},
"enableFragmentSelection": {
"type": "boolean"
},
"dataFilter": {
"$ref": "common_schemas#/definitions/Expression"
},
"distinctValues": {
"type": "boolean"
}
},
"required": [
"name",
"tableMapping"
],
"title": "LogicalTableSource"
},
"TableMapping": {
"type": "object",
"additionalProperties": false,
"properties": {
"tables": {
"type": "array",
"items": {
"type": "string"
}
},
"logicalTableSourceJoins": {
"type": "array",
"items": {
"$ref": "#/definitions/LogicalTableSourceJoin"
}
}
},
"title": "TableMapping"
},
"LogicalTableSourceJoin": {
"type": "object",
"additionalProperties": false,
"properties": {
"leftTable": {
"type": "string"
},
"rightTable": {
"type": "string"
},
"joinType": {
"$ref": "common_schemas#/definitions/JoinType"
},
"disable": {
"type": "boolean"
}
},
"required": [
"leftTable",
"rightTable"
],
"title": "LogicalTableSourceJoin"
},
"Join": {
"type": "object",
"additionalProperties": false,
"properties": {
"rightTable": {
"type": "string",
"rawType": "table"
},
"joinType": {
"$ref": "common_schemas#/definitions/JoinType"
},
"cardinality": {
"$ref": "#/definitions/Cardinality"
},
"drivingTable": {
"type": "string",
"rawType": "table"
}
},
"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"
},
"Aggregation": {
"type": "object",
"additionalProperties": false,
"properties": {
"rule": {
"$ref": "#/definitions/AggregationRule"
},
"dimensionBasedRules": {
"type": "array",
"items": {
"$ref": "#/definitions/DimensionBasedRule"
}
},
"dataIsDense": {
"type": "boolean"
},
"countDistinctOverrides": {
"type": "array",
"items": {
"$ref": "#/definitions/CountDistinctOverrides"
}
},
"aggregateByLevels": {
"type": "array",
"items": {
"type": "string"
}
}
},
"title": "Aggregation"
},
"DimensionBasedRule": {
"type": "object",
"additionalProperties": false,
"properties": {
"dimension": {
"type": "string"
},
"rule": {
"$ref": "#/definitions/AggregationRuleBasedOnDimension"
},
"aggregateExpression": {
"$ref": "common_schemas#/definitions/Expression"
}
},
"required": [
"dimension",
"rule"
],
"title": "DimensionBasedRule"
},
"CountDistinctOverrides": {
"type": "object",
"additionalProperties": false,
"properties": {
"logicalTableSource": {
"type": "string"
},
"rule": {
"$ref": "#/definitions/AggregationRule"
}
},
"title": "CountDistinctOverrides"
},
"DerivedFrom": {
"type": "string",
"enum": [
"PHYSICAL_COLUMNS",
"LOGICAL_COLUMNS"
],
"title": "DerivedFrom"
},
"AggregationRule": {
"type": "string",
"enum": [
"NONE",
"SUM",
"AVG",
"COUNT",
"COUNT_DISTINCT",
"MAX",
"MIN",
"FIRST",
"LAST",
"MEDIAN",
"STD_DEV",
"STD_DEV_POP",
"EVALUATE_AGGR",
"BASED_ON_DIMENSION"
],
"title":"AggregationRule"
},
"AggregationRuleBasedOnDimension": {
"type": "string",
"enum": [
"NONE",
"SUM",
"AVG",
"COUNT",
"COUNT_DISTINCT",
"MAX",
"MIN",
"FIRST",
"LAST",
"MEDIAN",
"STD_DEV",
"STD_DEV_POP",
"EVALUATE_AGGR",
"EXPRESSION"
],
"title":"AggregationRule"
},
"RelationshipTable": {
"type": "object",
"additionalProperties": false,
"properties": {
"logicalTableSource": {
"type": "string"
},
"table": {
"type": "string"
},
"memberKey": {
"type": "string"
},
"parentKey": {
"type": "string"
},
"distance": {
"type": "string"
},
"leafNodeIdentifier": {
"type": "string"
}
},
"required": [
"distance",
"leafNodeIdentifier",
"logicalTableSource",
"memberKey",
"parentKey",
"table"
],
"title": "RelationshipTable"
}
}
}