SQL Server Modeling Services Schemas

Article
09/22/2010

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

SQL Server Modeling Services schemas are virtual containers for organizing entities related to a specific area or problem domain. They are implemented with one or more SQL Server schemas: namespaces containing a logical grouping of tables, views, stored procedures, and other database objects. SQL Server 2008 provides a listing of these schemas in the sys.schemas catalog view.

Modeling Services schemas contain data related to their target domain. This logical grouping of related data is a common strategy for working with complex information. For example, customers of online stores often expect the Web site to categorize products into groupings such as computers or monitors. A customer who is researching computers might want to compare relative CPU speeds, whereas a customer shopping for monitors might want to identify all wide-screen LCD monitors. In this example, computers and monitors represent different domains of interest with specific properties related to each domain. These domains might also help define a larger domain, such as Electronics or Inventory. The online application is easier to create and maintain when the underlying data is also organized by these domains.

Schemas Represent Domains

Modeling Services schemas provide organization and structure to both intrinsic and user-created metadata. Modeling Services domains install hundreds of database objects, and these objects are contained by schemas that describe their purpose. For example, the SQL Server Modeling CTP installs a schema named System_Runtime that models the common language runtime (CLR). The System_Runtime schema scopes all database objects related to the CLR, such as the Assemblies view. When referring to this view in queries, you combine the schema name with the view name as in [System_Runtime].[Assemblies]. The System_Runtime schema contains views, stored procedures, functions, and other database objects that all specifically apply to the CLR. The CLR data represents only one category of data in the Modeling Services database. The Modeling Services database is the central location for all types of data, models, and applications. Schemas provide the organization to keep this growing set of complex information manageable and understandable.

Related groups of Modeling Services schemas are sometimes connected by naming convention. For example, consider the following built-in schemas: Repository and Repository.Item. There is no inherent connection between these two schemas from the perspective of SQL Server; however, the shared base name logically groups these schemas to describe aspects of a larger problem domain. In this example, each schema name begins with Repository, associating these schemas with different aspects of the Modeling Services infrastructure. The Repository.Item schema name indicates that it more specifically focuses on the management and security of items within the Modeling Services database. Groups of related schemas combine to define a problem domain with each schema modeling a specific area within that space. Although SQL Server 2008 does not recognize or enforce these connections, tools that understand this naming convention can work with Modeling Services schemas in a more structured manner.

Note

Fully qualified names in SQL Server 2008 use a format of [server_name].[database_name].[schema_name].[object_name]. In most cases, an application specifies the server and database names when establishing a connection. Queries made on that connection already have a context for those names. This enables the queries to use a shorthand name that includes only the schema name and object name (as in System_Runtime.Assemblies). Some schema names include periods, such as Repository.Item, that require escaping with brackets. For example, to refer to the Folders view in the Repository.Item schema, you need to escape the name as in [Repository.Item].[Folders]. In this topic, database object names will always be escaped with brackets for clarity. For more information, see Using Identifiers as Object Names in the SQL Server 2008 documentation.

Schemas Are Models

Modeling Services schemas can also be seen as models. The System_Runtime schema, for instance, contains database objects that describe assemblies, namespaces, methods, and other concepts specific to the CLR domain. In this sense, the System_Runtime schema models the structure of the CLR.

A schema models a specific area of interest, and schema tables define the entities of that model. Model instances refer to the specific data that conforms to the schema. For example, a model instance of a specific version of the .NET Framework requires many items that span all of the tables in the System_Runtime schema. Entities are the individual components that structure and describe the model. The Modeling Services database uses SQL Server tables and views to represent entities and the relationship between entities. The [System_Runtime].[Assemblies] view defines the properties of a CLR assembly entity; a row in the [System_Runtime].[Assemblies] view is an entity instance, also known as a Modeling Services item.

For more information about modeling concepts, see What is a Model?

The Base Domain Library

The Base Domain Library (BDL) consists of a set of schemas that describe and configure the Modeling Services database as well as provide support for some of its more advanced features. The following table lists three important schemas in the BDL that help support Modeling Services features needed by applications and tools.

Schema	Description
Repository	Provides support for core services in the Modeling Services database. These services include error handling and support for sequence objects.
Repository.Item	Provides support for features that apply to items in the Modeling Services database. This includes security, Folder management, and change tracking.
System.Globalization	Provides localization support for Modeling Services strings and resources.

The following sections describe these infrastructure-focused schemas in greater detail.

The Repository Schema

The Repository schema provides tables and stored procedures for managing core services, such as error-handling routines and Modeling Services sequence objects. Sequence objects provide an optional Modeling Services-supplied mechanism for obtaining groups of unique row identifiers to use during inserts. This can improve performance by managing the sequences manually in the Modeling Services database. For large numbers of insertions, this can be faster than relying on the database to automatically increment an identity column in the target tables. For more information, see Identifier Design Patterns.

The Repository.Item Schema

The Repository.Item schema helps control organization and security within the Modeling Services database. There are two Modeling Services concepts associated with this schema: items and Modeling Services Folders.

Items are individual rows within tables that define instances of a logical entity. For example, an Addresses table can be seen as a collection of address items. In this example, an address is the logical entity that the table describes. Each row in the table is an item that describes an address instance.

Modeling Services Folders provide a hierarchical basis for organizing Modeling Services items and domains. They act in a similar manner to file system folders in the operating system—they group items and other Folders for containment and manageability. Each item belongs to a specific Folder, defined in the [Repository.Item].[Folders] view. Each Modeling Services view contains a column named Folder of type [Repository.Item].[FolderId]. This Folder column identifies each row’s associated Modeling Services Folder, linking to the Id column of the [Repository.Item].[Folders] view.

Folders can contain child Folders. The [Repository.Item].[Folders] view also has a column named Folder that reference an optional parent Folder. A Folder that has a NULL parent Folder value does not have a parent folder and resides at the top of the Folder hierarchy. Modeling Services installs one top-level Folder named Repository. Instead of putting data in this system-provided Folder, put any custom data in your own Folders and Folder hierarchies.

Folders provide the basis for versioning in the Modeling Services database. For example, consider again the System_Runtime domain. The [System_Runtime].[Assemblies] view models the characteristics of an individual .NET Framework assembly. However, there are multiple versions of the same assembly that correspond to multiple versions of the .NET Framework. To address this situation, you could add a Folder to the [Repository.Item].[Folders] view named .Net Framework. This Folder would use the Folder column to reference the top-level Frameworks Folder, meaning that the .Net Framework Folder is a subfolder of the Frameworks Folder. Additional Folders could be added for the various .NET Framework versions, such as 2.0 and 3.0. These version-specific Folders would use the .Net Framework Folder as their parent. Assembly items in the [System_Runtime].[Assemblies] could then view use their Folder column to refer to the to the Folder of the .NET Framework version associated with each assembly.

Folders also provide a natural security boundary. Modeling Services administrators can grant users the appropriate level of access to specific Folders. This is a finer-grained level of security than granting users access based on tables, which would give the users access to all instances in a given table. The Repository.Item schema contains tables, functions, and stored procedures for controlling access to Folders. Updatable views use the security functions in the Repository.Item schema to filter the results for a query to return only those rows that the user has access to. These views also provide a mechanism to securely control updates to the underlying base tables. For more information about this process, see SQL Server Modeling Services Security. For more information about how to use Folders, see How to: Create and Use SQL Server Modeling Services Folders.

The Repository.Item schema also supports a variety of administrative tasks including:

Lifetime services—supports the creation of policies that schedule the removal of old rows in Modeling Services tables. For more information, see SQL Server Modeling Services Lifetime Services.
Change Tracking—supports applications and tools that need to synchronize with changes to the data in Modeling Services tables. For more information, see SQL Server Metadata Services Change Tracking.
Change Data Capture—supports tracking and reporting on all changes to rows in targeted Modeling Services tables. For more information, see SQL Server Metadata Services Change Tracking.

The System.Globalization Schema

The System.Globalization schema supports localization of strings and resources in the Modeling Services database. The [System.Globalization].[Locales] view provides a listing of the available languages for strings and resources in the Modeling Services database. Localized strings and resources are accessible from the [System.Globalization].[Strings] and [System.Globalization].[Resources] views, respectively. You can query these views directly to obtain a localized string or resource, or you can leverage functions in the System.Globalization schema by passing a string or resource identifier, a Folder identifier, and the desired locale identifier.

For more information about the System.Globalization schema, see SQL Server Modeling Services Localization Support. Guidelines for adding localizable data to the Modeling Services database can be found in Localization Design Pattern. For more information about using the localization features of the Modeling Services database, see Localization Tasks (Modeling Services).