Database schemas allow you to apply a certain schema to a table or a group of tables. You can use schemas to e.g. restrict access to tables that report designers do not need, thereby making reporting off of the data warehouse easier.

Schemas can be set on staging databases and data warehouses as well as data sources and individual tables. The schema settings are applied as follows: Table level settings take precedence over data source settings, which in turn take precedence over business unit/data warehouse settings.

Adding a Database Schema to a Data Warehouse or Business Unit

To create a database schema, follow the steps below.

1. On a data warehouse or business unit, right-click Database Schemas and click Add Database Schema.
2. In the Name box, enter a name for the new schema. In the Owner box, you can enter the owning role for the schema. The default is "dbo". Click OK to create the schema.
3. Assign a Schema Behavior by right-clicking on the newly created schema. You have the following options:
   • Main default schema: The schema will be applied to all tables and views in the region (data warehouse or staging).
   • Main Raw default schema: The schema will be applied to all Raw (_R postfix) tables in the region (data warehouse or staging).
   • Main Transformation default schema: The schema will be applied to all Transformation (_T postfix) views in the region (data warehouse or staging).
   • Main Valid default schema: The schema will be applied to all Valid (_V postfix) tables and views in the region (data warehouse or staging).
   • Main Error/Warning default schema: The schema will be applied to all Link and Message (_L and _M postfix) tables in the region (data warehouse or staging).
   • Main Securable Column default schema - The schema will be applied to all of the tables that contain the securable column setups.
   • Main Securable View default schema - The schema will be applied to all of the securable views
   • Main Key Store default schema - The schema will be applied to all key store tables, which have the key store name prefaced with “KeyStore_”.

   • 

      

4. If you want to assign a schema manually, Right-click the table and select Table Settings, and then click the Table Schemas tab. Here, you can then select a schema as Default (all instances of this table), Raw, Transformation, Valid or Error/Warning.
5. Assign user rights to the schema. This can be done through SQL Server Management Studio or T-SQL. See this article on the Microsoft website for details on how to grant user rights using T-SQL: http://msdn.microsoft.com/en-us/library/ms187940.aspx

Configuring Schemas for Tables and Data Sources

Schemas for tables and data sources are configured in the settings for the respective objects.

1. Right click a table and click Table Settings.
   OR
   Right click a data source and click Data Source Settings.

   

   
   The settings window for the table or data source appears.
2. Click the Schema tab. Here, you can choose the schemas to use for the different instances of the table(s). The lists contain the schemas added to the data warehouse or business unit the table or data source belongs to. Click Add new schema... to add a new schema for use on the table or data source.

   

    

3. Click OK.

Securable Columns are used in data level security. For more information on this, click on How to implement data level security in TimeXtender Classic

Secure Views are related to object level security. For more information on this, click on How to implement object level security in TimeXtender Classic

Key Stores are used to store Supernatural Keys. For more information on these, click on Supernatural Keys

The Raw, Transformation, Valid, and Error tables are related to the execution of tables. For more on information on this, click Execute Tables