Creating custom tables

You create a custom table by merging, copying, or referencing selected tables and columns in the data module. These tables are not added to the data sources that the module is based on.

Custom tables can be used to create new custom tables.

About this task

If a custom table is based on multiple tables, the properties of the table that was selected first are applied to the custom table. For example, the Usage or Data cache properties on the custom table are inherited from the first table. The columns in the custom table also inherit their properties, such as Label or Aggregate, from the columns in the first table.

Procedure

  1. In the data tree, from the data module, table (or multiple tables), or package context menu Vertical actions menu icon, click New > Table.

    You can also start creating a custom table from the Custom tables tab by clicking Create custom table.

  2. Select the tables and columns to add to your custom table.

    You can click the add icon Icon to add a data source to add new tables, or the remove icon Icon remove table. to remove tables from the list. These options are not available for a package.

    • If you initiated the process for an individual table or multiple tables, the table names appear in the Create new table dialog box, in the Selected tables pane. The table that was selected first is at the top of the list.

    • If you initiated the process from the data module context menu Vertical actions menu icon, no table names appear in the Selected tables pane initially. Click Select tables to add tables that are already in the data module.

  3. Depending on your selection of tables in the previous step, one or more of the following options are available to create the new table. Select one option.
    View of tables
    This option is available when one or more tables are selected. This is the only option that is available for package tables. The columns in the base tables are referenced in the view. The column properties in the view are independent from the base table. After the view is created, you can select or deselect columns to include in it.
    Shortcut to a table
    This option is available when one table is selected. The shortcut is a reference to the selected table, and has the following characteristics:
    • The shortcut retains relationships of the base table. However, if you use the shortcut table in a new relationship, the new relationship overwrites the relationship inherited from the base table.
    • The shortcut column properties depend on the base table, and are read-only.
    • Calculations can be added to the shortcut.
    • The shortcut doesn’t inherit navigation paths from its base table. However, navigation paths can be added to the shortcut.
    • Shortcuts can be used to create star schema grouping presentations. For more information, see Model design and presentation.
    Alias of a table
    This option is available when one table is selected. The alias is a reference to the selected table, and has the following characteristics:
    • The alias is independent of its base table and doesn’t retain the base table relationships. You can use the alias table in a new relationship.
    • The column properties in the alias are dependent on the base table, and are read-only.
    • Calculations and embedded filters can be added to the alias.
    • The alias doesn’t inherit navigation paths from its base table. However, navigation paths can be added to the alias.
    Copy of a table
    This option is available when one table is selected. You can copy all columns from the table, or only the columns that you select. The new table is disassociated from the base table so changes in one table aren’t reflected in the other table. When you copy a non-custom table, the result is a non-custom table. When you copy a custom table, the result is a custom table.
    Joined view
    This option is available when two tables are selected. You can select or deselect columns to include in your new table.
    Proceed to create a join between the two tables. For more information, see Creating a relationship manually.
    Union of tables
    This option is available when two or more tables are selected. All selected tables must have the same number of columns. The columns must be in the same order, and their data types must be compatible. The new table includes all unique rows from all selected tables.
    Tip: The help icon in the product provides information about incompatible data types when columns with such data types are discovered.
    Intersect of tables
    This option is available when two tables are selected. Both tables must have the same number of columns. The columns must be in the same order, and their data types must match. The new table includes only the unique rows that are shared between the two tables.
    Except of tables
    This option is available when two tables are selected. Both tables must have the same number of columns. The columns must be in the same order, and their data types must match. The new table includes only the unique rows from the first table.
    Note: By default, duplicate rows of data are removed from the union, except, and intersect custom tables. To include the duplicate data, from the table context menu Vertical actions menu icon, click Properties, and under Advanced, set the Duplicates property to Preserve.
  4. Proceed with the option-specific steps, and then click Finish.

    The custom table diagram appears on the Custom tables tab.

    Also, the table name is added to the list view on the Custom tables tab, and at the top of the data tree.

    Note: Copies of non-custom tables are not considered custom tables and don't have query flow diagrams.
  5. Save the data module.

What to do next

To view the custom table diagram later, from the table context menu Vertical actions menu icon in the data tree, select View definition. The same option is available for the table from the list view on the Custom tables tab.

To edit some types of custom tables, select the related Edit table option from the table context menu Vertical actions menu icon. For more information, see Editing the view custom tables and Editing the union, except, and intersect custom tables.

To change the custom table name, open the table Properties panel, and on the General tab, modify the Label property.

To delete a custom table, locate the table in the Data module panel, and from the table context menu Vertical actions menu icon, click Remove.