Skip to main content

Table Editor

Use the Table editor to edit tables, by adding, changing, or removing columns, keys, indexes, and fact or dimension specific objects.

Table Designer

Enter a column's name, data type, length, precision, and other properties in the column's data grid row. Drag and drop a column to create related keys, indexes, or attributes.

Table Column

Edit a table reference column's table and snowflake columns in its data grid row's details area. Drag and drop a snowflake column to create related keys, indexes, or attributes.

Table Reference Column

Edit a measure's name, SSAS type, measure format, aggregation type, and other properties in its data grid row.

Measure

Edit a dimension's attributes, hierarchies, and relationships directly in their treeviews.

Attribute, Hierarchies, and Relationships

The table editor's ribbon provides the following functions:

Table Ribbon

ButtonDescription
Table ColumnChoose and add table columns to a table. The default is table column.
Import Table AssetsDisplay the Import Tables dialog, for importing schemas and tables from a database.
KeyChoose a key to add to a table. The default is primary key; if a primary key is present, then the default is unique key.
IndexAdd an index to a table.
MeasureAdd a measure to a fact table.
AttributeAdd an attribute to a dimension.
HierarchyAdd a hierarchy to a dimension.
RelationshipAdd a relationship to a dimension.
Autogenerate MeasuresAdd a measure for each table column not already associated with a measure.
Apply Measure FormatsChoose to preserve or overwrite existing measure formats.
Generate Attribute and RelationshipsChoose to generate attributes, relationships, or both for a dimension.

Import Tables Dialog​

The Import Tables dialog reads schemas, tables, and views from a database connection and adds them to a database in your project. Open it with the Import Tables button on the Import tab of the ribbon, or with Import Tables on the right-click menu of the Tables folder in the Logical View.

Connections​

FieldDescription
Source ConnectionThe connection to import tables from.
Project DatabaseThe project database where tables will be added.

Once both are selected, the Importable Assets list shows the project database with the schemas, tables, and views the connection can read. Every item starts out checked. Clear the check box of a table or view to leave it out of the import, or clear a schema to leave out every table in it. BimlStudio matches schema names and table names separately, so a cleared table is still imported when a table of the same name is checked in another schema and its own schema has at least one checked table. Views are shown with a view icon. The list shows no assets until a source connection and a project database are both selected.

If BimlStudio cannot read the list of assets from the connection, the Import Errors grid appears below the list with one row. Its Error column shows the error message, and its Asset Name column is empty.

Global Import Options​

The options under Global Import Options apply to every table in the import.

OptionStarts asDescription
KeysCheckedImports each table's primary key and unique keys. Clear it to import the tables without primary keys or unique keys.
IndexesCheckedImports each table's indexes. Clear it to import the tables without indexes.
Column Computed ValuesCheckedWhen cleared, BimlStudio removes the computed expression from every imported column. When checked, each column's computed expression is left as the import read it.
Column DefaultsCheckedImports the default value of each column that has one. Clear it to import the columns without default values.
Group Tables By Database/SchemaCheckedSets the LogicalDisplayFolder of each imported table to the project database name and the schema name, in the form Database/Schema. Clear it to import the tables without a logical display folder.
Import Foreign Keys asReference Columns with Create and Check ConstraintSets how a column that belongs to a foreign key in the source is imported. The choices are described in the next table.
Import Views asViewsSets how a view in the source is imported. With Views, the imported table carries the view's definition, including its select statement. With Tables, the view imports as a table with its columns and no view definition.

The Import Foreign Keys as list offers these choices.

ChoiceDescription
Reference Columns with Create and Check ConstraintThe column imports as a table reference column that points to the referenced table and keeps the source constraint name. The foreign key constraint is created and checked on table changes.
Reference Columns with Create and No Check ConstraintThe column imports as a table reference column, and the foreign key constraint is created but set to not check values.
Reference Columns with Do Not Create ConstraintThe column imports as a table reference column, and no foreign key constraint is created. Use this choice to model a relationship in Biml that the database engine does not enforce.
Regular ColumnsForeign keys are not imported. The column imports as a regular table column.

A column imports as a table reference column only when the table its foreign key references is also part of the same import. When the referenced table is not part of the import, the column imports as a regular table column, whatever the choice.

A foreign key that spans more than one column imports as a multiple column table reference that keeps the source constraint name.

Clustered Keys and Indexes​

An imported index carries the clustered mark that the source catalog gives it, in its Clustered property. This applies to tables imported from SQL Server, Db2, and PostgreSQL. A primary key or unique key whose constraint is the table's clustered index is marked clustered. The key keeps that mark when the Indexes box is cleared, because BimlStudio reads it from the source catalog either way.

An index or key imported from Oracle is never marked clustered, because BimlStudio reads no clustered mark from the Oracle catalog.

Tables imported with the GetDatabaseSchema method in BimlScript carry the clustered mark in the same way.

Import Results​

Select Import to add the checked schemas and tables to the project, or Cancel to close the dialog without importing.

When every selected object is read, the import completes without a message. When the import cannot read some objects or lists, BimlStudio shows a message titled Import Tables. Its first line states how many tables the import read, for example "The import read 12 tables." The lines that follow name each problem once.

LineMeaning
The import could not start: <message>The import did not run. The message gives the reason.
The <list> list could not be read: <message>A whole list could not be read from the source. The list is one of table, schema, constraint, index, index column, view, or synonym.
These objects could not be read and were not imported:A heading followed by one line for each object, in the form <object>: <message>.
and <number> more.The message shows at most 20 problem lines. This line counts the rest.