Managing tables

This section describes how to manage existing tables.

Summary

To access the details of a particular tables go to Databases ‣ Select a database on the Tables & views screen pick a table (notice the Type column).

The table summary screen should appear:

Summary

The following table summarizes the visible properties:

Parameter Description
Virtual table name The name of the table visible to TDS clients.
Full table name Same as above but database and schema qualified
Physical table name Database and schema qualified name of the table in the underlying data source.
Filter An arbitrary SQL WHERE condition that will be applied to all queries related to this table, e.g. AccountKey = ‘777’*
Row count Number of rows stored in the physical table.
Comment The comment text.

Clicking Edit enables the “Edit table” screen where most of above values can be set:

Edit table

Clicking Delete brings up the table removal screen.

Preview data

In this screen, you can preview the data from the view. You can select the Limit of returning rows. The default value is 100, but you can select among a number of rows:

  • 10
  • 100
  • 1000

Available actions:

  • SHOW DATA - runs the query and shows the results on the screen.
  • CANCEL - cancels the running query.
Preview data

Columns

Clicking this tab will bring up the Columns list screen:

Columns

The following buttons can be used:

Add column

Add column opens a screen for mapping an existing physical column to a virtual column:

Add column

The following table summarizes the properties:

Parameter Description
Virtual column name The name of the column visible to TDS clients.
Physical column name The name of the physical column in the underlying data source.
Virtual column type The data type of the column visible to TDS clients.
Physical column type The data type of the underlying physical column.
Comment The comment text.
Data masking formula See below.
Nullable Defines whether the virtual column allows NULLS.

Data masking formula

This field allows to arbitrarily change the data returned for this column. Basically, this can be any valid SQL expression, e.g. a function call, CASE expression etc. Any column from the containing table can be referenced as well.

As an example, the following expression can be used to limit the [Salary] column visibility only to HR managers:

case when is_rolemember('HR manager')=1 then [Salary] else null end

Warning

Please keep in mind that the is_rolemember function call is evaluated in the context of the current user. This might lead to problems when the [Salary] column would be used in a cached view because caching is performed by the System user. Is such case the resulting table might have 0 rows.

Add calculated

Add calculated opens a screen for a defining a new calculated column.

Add calculated

The following table summarizes the properties:

Parameter Description
Virtual column name The name of the column visible to TDS clients.
Virtual column type The data type of the column deduced from the Read Formula.
Comment The comment text.
Read Formula A SQL expression that will be evaluated for every row to return the final value.

After filling the Read Formula field, click Parse to validate the expression and deduce the column type. Use CAST if necessary. E.g. the following expression returns decimal ones:

cast (1 as decimal)

Column preview

When on clicking a given column on the columns list a preview screen appears:

Column preview

The first section contains a read-only preview of properties as described in above. Scrolling down allows to access the following screens:

Preview data

Preview data

Similarly to table data preview, data only for a given column can be viewed. The advantage here is that previewing distinctive data (and with count) is also possible.

Access rights

Please go to the Access rights section for general information about access rights. Table column access rights are listed below:

Access right Description
Select Confers to the grantee the ability to select the data.
Update Confers to the grantee the ability to update the data.
View definition Enables the grantee to access column metadata.

Business terms

Please refer to the Business terms section.

Primary and Foreign keys

Please refer to the Primary and Foreign Keys chapter of the manual.

Dependent views

When selecting this section, a list of all views that depend on this object appears. Clicking one of them opens the Dependency graph screen, with the object being high-lightened.

Dependent views

Clicking the bar expands the list of columns. Clicking a column highlights all source columns in other objects.

Access rights

Please go to the Access rights section for general information about access rights. View access rights are listed below:

Access right Description
Alter Confers to the grantee the ability to alter table.
Alter table Confers to the grantee the ability to change table attributes.
Control Confers to the grantee the ability to table definition and control database access rights.
Control table Confers to the grantee the ability to table definition, alter, control table and create table access rights.
Delete Confers to the grantee the ability to delete the data.
Insert Confers to the grantee the ability to insert the data.
Select Confers to the grantee the ability to select the data.
Update Confers to the grantee the ability to update the data.
View definition Enables the grantee to access database metadata.

Statistics

Shows the statistics for the table. This screen is in read only mode. When you want to adjust the statistics manually please click EDIT button. On the screen, you can see the Use approximate distinct count option to improve the performance of statistics calculations with negligible deviation from the exact result. View row count shows how many rows are present in the table. For each column, you can examine the statistics in the following table:

Column name Description
Column name The virtual column name.
Distinct count The number of the distinct values in the virtual column.
Average length bytes The average length of columns where the bytes length can be calculated, for instance, character columns.
Calculate statistics Specifies if the calculation of the column statistics should be performed and how.

To refresh the statistics you can use the Management tasks section.

Statistics

Available actions:

  • EDIT - goes to the edit mode screen.

Edit statistics

This screen allows you to modify statistics related values. It is the same screen as described above, but with editing capabilities.

Statistics edit

Management tasks

Please refer to the Management Tasks section.

Business terms

Please refer to the Business terms section.