Schemas

Database schemas in Jet Analytics Data Integration allow developers to apply a specific schema to a table or group of tables. Schemas can be used to restrict access to tables that are not relevant for report designers, simplifying the creation of reports based on the available schemas.

Schemas can be set for either data areas or individual tables. Schema settings applied at the table level take precedence over those applied at the data area level.

Data areas and schemas

When a data area is created, the name provided is set as the default schema for all objects in that data area.

Using the data area name as the schema allows Direct Read to work correctly with tables across different data areas. When multiple data areas exist in the same database, each is differentiated by its schema name.

Edit an existing schema

1. Right-click the default schema and select Edit Schema.
2. Enter a new name in the Name field.
3. A prompt appears advising that a refresh is needed to display the new schema name in the data area tree. Click Yes.
4. Deploy all tables in the data area to apply the new schema name.

Troubleshooting: duplicate schema names

Add a database schema to a data area

1. In the data area, right-click the Database Schemas folder and select Add Database Schema.
2. Enter a name in the Name box and an owner in the Owner box. The default owner is dbo. Click OK.
3. To configure the schema behaviour, right-click the new schema in the Database Schemas folder and select the appropriate behaviour:
   • None: The schema will not be applied unless manually assigned to a table.
   • Main default schema: The schema will be applied to all tables and views in the data area.
   • Main Raw default schema: The schema will be applied to all Raw tables (_R postfix) in the data area.
   • Main Transfer default schema: The schema will be applied to all Transfer views (_T postfix) in the data area.
   • Main Valid default schema: The schema will be applied to all Valid tables and views (_V postfix) in the data area.
   • Main Error/Warning default schema: The schema will be applied to all Link and Message tables (_L and _M postfix) in the data area.
   • Main Securable Column default schema: The schema will be applied to all secured columns created.
   • Main Securable View default schema: The schema will be applied to all secured views created.
   • Main Key Store default schema: The schema will be applied to all key stores created.
4. If None is selected as the schema behaviour, assign the schema to tables manually: right-click a table, select Table Settings, click the Table Schemas tab, and select the appropriate schema for each object type (raw, valid, etc.).
5. To assign user rights to the schema, use SQL Server Management Studio or T-SQL. See GRANT Schema Permissions (Transact-SQL) on Microsoft Learn for instructions.

Configure schemas for tables

1. Right-click a table and select Table Settings.
2. Select the Table Schemas tab, which lists all objects that comprise the table along with their current schemas. Select the appropriate schema for each object type. The list includes all schemas added to the data area. If needed, click Add new schema… to add a schema for use with the table.
3. Click OK.

Troubleshooting

If a new schema is added to a data area or table and deployment is run with the Only deploy modified tables and views option, the deployment may fail with an error similar to:

Error:Create failed for table 'schemaname.tablename_r'. The specific schema name "schemaname" does not exist or you do not have permission to use it.

The Only deploy modified tables and views option excludes the deployment of new schemas. The schema must be deployed before any table that uses it can be deployed successfully.

To deploy the schema, right-click the schema in the Database Schemas folder and select Deploy Schema.