AX2679

About table types

Tables can be assigned to a table type in order to group related tables and control access to the tables as a group. For each table type, users can be granted full access or filtered access to the tables in the table type.

You can create any number of table types to meet your needs. When you create a table type, you specify the table classification for the table type, such as Data or Reference. All tables assigned to the table type must be the same table classification.

Each table type can have a set of required columns that are common to each table that belongs to the table type—for example, ACCT and DEPT columns for GL actuals tables. Tables in the table type can have other key columns, and other non-key columns, in addition to the common columns for the table type. However, the table type filters in Security should only use the common key columns.

Table types are optional. You do not need to assign tables to a table type in order to apply security or query filters, but in certain cases it may be easier to do so.

Required columns for table types

Table types can have any number of required columns, key or non-key. Table types can also have no required columns. However, if you want to define security filters for the table type, it is recommended to define required columns as follows:

The table type should have at least one required key column. Table type filters should only use key columns, and the key columns must be common to all tables in the table type.
The required key column must use a lookup column. For example, if the required key column is ACCT, it should look up to ACCT.ACCT. In order for the filter to work across all tables in the table type, it must be constructed using a common reference table.

If you define required columns for a table type, then any security filter for the table type must be based on those required columns. This ensures that any filter defined for the table type will be valid on all tables within the table type.

If you do not define required columns for a table type, then it is not possible for Axiom to validate the security filter for use with all tables in the table type. In this case, it is the system administrator's responsibility to verify that any security filter defined is valid on all tables within the table type.

Security and table types

In security, you can define access rights at the table type level and/or the individual table level. If a table belongs to a table type, that table inherits the security permissions set at the table type level. In most cases, you will want to assign related tables to table types and set permissions at the table type level to streamline the process of defining security settings.

Because most data tables can be easily grouped into table types with shared key columns, it makes sense to use table types and define access rights at the table type level. For example, imagine that your GL actuals data is stored in individual tables by year (GL2020, GL2021, etc.). Most likely, you want to apply the same security filters to each table. Rather than defining the filters for each individual table, you can assign the tables to a GL table type, and then set the filters once. Additionally, as you create new GL actuals tables and assign them to the GL table type, the existing security will automatically be applied.

Reference tables (including picklist tables and KPI tables) can also be assigned to table types if desired, however, these tables do not have shared key columns. Therefore, if you want to set security permissions at the table type level for reference tables, this permission must be limited to full read and/or write access—filters cannot be used. If you need to use security filters with reference tables, then those tables should not belong to a table type.

Table types do not apply to document reference tables. By default, all users have access to the data in a document reference table, via the Everyone role. If you need to change this access, you would do so at the individual table level.

The following table summarizes how table types apply to different table classifications:

Classification	Assign to Table Type?	Security
Data	Yes. In the majority of cases you will want to group related data tables into a table type, to streamline the process of defining security settings, including filters that use shared key columns. However, it is not required to assign data tables to a table type if it is not necessary for certain tables.	By default, users do not have access to the data in data tables. You must grant access to users and/or roles on a per table type and/or table basis.
Reference (including picklist and KPI tables)	Maybe. If you have multiple related reference tables where you can set full read and/or write access at the table type level, using table types can streamline the process of defining security settings for those tables. But if security filters are needed, then there is no benefit to using a table type with those tables, because the filters can only be set per table.	By default, users do not have access to the data in reference tables. You must grant access to users and/or roles on a per table type and/or table basis.
Document Reference	No. Document reference tables cannot be assigned to a table type.	By default, all users have access to the data in document reference tables, via the Everyone role. If you do not want all users to have access to this data, you must modify the Everyone role to remove access, and then grant access to the specific users and roles that need it.

Sheet filters and table types

Table types are also useful for defining sheet filters in Axiom files. Sheet filters can be used to filter all of the data queries on a particular sheet. Instead of setting a filter on each individual Axiom query or GetData function, you can set the filter at the sheet level.

NOTE: Table types can only be used with sheet filters when all tables in the table type share common validated key columns. Effectively this means that table type sheet filters are only for use with table types that group data tables.

Sheet filters can be set at the individual table level, or at the table type level. If you set a filter at the table type level, the filter applies to all tables in the table type. This is useful if you are:

Querying data from several tables, such as to compare actuals to plan data, or to compare data from different years. In this case you can define one sheet filter that applies to all data tables in the query, assuming that all the tables belong to the same table type.
Using aliases to query data. In this case you may be updating the aliases from year to year so that they point to different tables. If the tables belong to the same table type, then you can define one sheet filter that continues to apply as you change which data table the aliases point to.

For example, imagine that you have a report that queries data from the GL2020 and GL2021 tables, and you want to filter the report by a specific region using a sheet filter. If the tables do not belong to a table type, then you need to define the filter twice, once for each table. If the tables belong to the same table type, you can define the filter once, at the table type level.