Create a MDPM table

POST {{baseUrl}}/api/model/tables?showFilterTokens=false&showExpressionAs=tree

This API creates a logical table in the changeset, and returns a new logical table definition with specific field information. Normal tables, FFSQL tables, and partition mapping tables are all supported.For partition mapping tables, both Warehouse Partition Mapping (WHPM) and Metadata Partition Mapping (MDPM) are supported.

When creating a normal table or warehouse partition mapping table, the request body must include a physical table definition. If type is not specified in the physical table definition, the API creates a normal table by default. If a logical table that refers to the same warehouse physical table already exists in the project, the API creates a new logical table, copying from the existing one. If a normal logical table exists in the project with the same data source information, namespace, and table name, the API creates a normal table alias. If the caller wants to use the table alias explicitly, pass an existing physical table ID in the request body. A freeform SQL table alias can only be created by passing an existing freeform SQL physical table ID in the request body. When the changeset is committed, all the new logical tables are created under folder schema objects/tables.

Metadata partition mapping table is different from other kinds of tables. It is a group of existing normal logical tables, which are called partition base tables. It does not have physical tables, and it cannot have table alias. Each partition base table must define a partition filter which is attribute qualification. The attributes in the filter can be any standalone attribute. Partition filter is a standalone system object saved to System Objects\Partition Filters. You may use showExpressionAs and showFilterTokens parameters for partition filters. When the changeset is committed, metadata partition mapping table can be saved to any location by specifying the destination folder ID in the request body. If not given destinationFolderId, the newly created table will fallback to Schema Objects\Tables folder.

For all kinds of tables, the table name cannot be duplicated within the project.

A changeset ID is required to execute the request and can be obtained using the changeset creation API. An authorization token is required to execute the request and can be obtained using Authentication endpoints.

To add the newly created table to metadata, commit using the POST /model/{changesetId}/commit API.

Request Params

KeyDatatypeRequiredDescription
checkSecondaryDataSourceTablebooleanIf set to true, the API finds compatible tables in the project. If a compatible table is found, the compatible table object information is returned.
The caller can call PATCH /model/tables/{tableID}/ to set a secondaryDatasources for the compatible table. If no table is found, a new table is created.

If set to false, a new table is created.

This parameter has no effect on creating a freeform SQL table. | | columnMergeOption | string | | Defines a column merge option. For example, say a project includes a table named Table1 and a column named C1 whose data type is char(1). You add Table2, which has a C1 column with char(4). To be consistent, columns' data types are modified in following ways:

  • reuse_any: Updates the column data type to use the most recent column definition. In the example above, the column data type for C1 is changed to char(4) since Table2 was added after Table1.
  • reuse_compatible_data_type: Updates the column data type to use the data type with the largest precision or scale. In the example above, the column data type for C1 is changed to char(4), as defined in Table2.
  • reuse_matched_data_type: Renames the column in newly added table to allow it to have different data types. In the example above, column C1 uses char(1) for Table1. Column C1 in Table2 is defined as a separate copy of C1 and uses char(4). This option can cause unwanted schema changes and should be used only when necessary.

If this value is not set, use the option that set on DBRole catalog setting. | | tablePrefixOption | string | | The following options define the table prefix:

  • none: do not set table prefix.
  • add_default_prefix: this option applies the default prefix setting on warehouse catalog.
  • add_namespace: create a prefix same with namespace.

This param has no effect on a free form SQL table. | | fields | null | | A comma-separated, top-level field whitelist that allows the client to selectively retrieve part of the response model. By default, all fields are returned. If specified, extra filtering is applied, and for the top-level object (if the root model is an array, each array element), only the listed fields are kept in the response. For example, "information,logicalSize" keeps only the "information" and "logicalSize" fields, omitting all other fields in the top-level response model. | | showFilterTokens | boolean | | Specify whether "qualification" is returned in "tokens" format, along with "text" and "tree" formats. A filter's "qualification" is presented in the following formats:

  • "text": A human readable, but non-parsable text, describing a filter's qualification.

  • "tree": A tree data structure fully defining the filter's qualification.

  • "tokens": A list of parsed tokens that define a filter's qualification. Be aware that generating tokens requires additional time. If omitted or false, only "text" and "tree" formats are returned. If true, all "text", "tree" and "tokens" formats are returned. | | showExpressionAs | string | | This parameter specifies the format in which the expressions are returned in response. This parameter applies to the expressions in a fact or attribute and custom expressions used in a filter, prompt, security filter, custom group, etc. Expressions are presented in the following formats:

  • "text": A human readable, but non-parsable text, describing the expression. This is the default format that is always returned in the response.

  • "tree": A tree data structure fully defining the expression. This format can be used if you want to examine and modify the expression programmatically.

  • "tokens": A list of parsed tokens. This format can be used if you want to examine and modify the expression using the parser component. Note that generating tokens requires additional time.

    Tokens are a semi-structured representation of MicroStrategy expression text that includes object references. For example, let’s say a filter expression is "Revenue > Cost". When the filter expression is represented as tokens, the text is broken down into pieces (tokens) with information about what these pieces represent in the metadata: ("Revenue", Revenue_ID), (">", GreaterThan_ID), ("Cost", Cost_ID). If omitted, the expression is returned in "text" format. If tree, the expression is returned in "text" and "tree" formats. If tokens, the expression is returned in "text" and "tokens" formats. |

Request Body

{"information"=>{"subType"=>"table_partition_md", "name"=>"New MDPM Table", "destinationFolderId"=>"CC582EC35C0C481E826E6D6B40BE4905"}, "logicalSize"=>0, "isLogicalSizeLocked"=>true, "partitionTables"=>[{"information"=>{"objectId"=>"1108A1684C55EC5AFB8DC19C15470B30", "subType"=>"logical_table", "name"=>"inventory_q1_2014"}, "partitionFilter"=>{"qualification"=>{"text"=>"Quarter (ID) = 20141", "tree"=>{"type"=>"predicate_form_qualification", "predicateText"=>"Quarter (ID) = 20141", "predicateTree"=>{"function"=>"equals", "parameters"=>[{"parameterType"=>"constant", "constant"=>{"type"=>"double", "value"=>"20141.0"}}], "attribute"=>{"objectId"=>"8D679D4A11D3E4981000E787EC6DE8A4", "subType"=>"attribute", "name"=>"Quarter"}, "form"=>{"objectId"=>"45C11FA478E745FEA08D781CEA190FE5", "subType"=>"attribute_form_system", "name"=>"ID"}, "dataLocale"=>""}}}}}, {"information"=>{"objectId"=>"54B36C794B9FEFD92AAC4FB1C4F34B4E", "subType"=>"logical_table", "name"=>"inventory_q2_2014"}, "partitionFilter"=>{"qualification"=>{"text"=>"Quarter (ID) = 20142", "tree"=>{"type"=>"predicate_form_qualification", "predicateText"=>"Quarter (ID) = 20142", "predicateTree"=>{"function"=>"equals", "parameters"=>[{"parameterType"=>"constant", "constant"=>{"type"=>"double", "value"=>"20142.0"}}], "attribute"=>{"objectId"=>"8D679D4A11D3E4981000E787EC6DE8A4", "subType"=>"attribute", "name"=>"Quarter"}, "form"=>{"objectId"=>"45C11FA478E745FEA08D781CEA190FE5", "subType"=>"attribute_form_system", "name"=>"ID"}, "dataLocale"=>""}}}}}, {"information"=>{"objectId"=>"62318B2346C8D9BBC7CB2582D5591401", "subType"=>"logical_table", "name"=>"inventory_q3_2014"}, "partitionFilter"=>{"qualification"=>{"text"=>"Quarter (ID) = 20143", "tree"=>{"type"=>"predicate_form_qualification", "predicateText"=>"Quarter (ID) = 20143", "predicateTree"=>{"function"=>"equals", "parameters"=>[{"parameterType"=>"constant", "constant"=>{"type"=>"double", "value"=>"20143.0"}}], "attribute"=>{"objectId"=>"8D679D4A11D3E4981000E787EC6DE8A4", "subType"=>"attribute", "name"=>"Quarter"}, "form"=>{"objectId"=>"45C11FA478E745FEA08D781CEA190FE5", "subType"=>"attribute_form_system", "name"=>"ID"}, "dataLocale"=>""}}}}}]}

HEADERS

KeyDatatypeRequiredDescription
X-MSTR-AuthTokenstring(Required) Authorization Token
X-MSTR-MS-Changesetstring(Required) Changeset ID