Database Schemas

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:
   • None: The schema will be applied to the tables you manually assign it to.
   • 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 Transfer default schema: The schema will be applied to all Transfer (_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).
4. If you have selected None as the Schema Behavior, you need to assign the schema manually. Right-click the table, click Table Settings and 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.