How to: Create and Use SQL Server Modeling Services Folders

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.]

This tutorial demonstrates how to create and use SQL Server Modeling Services Folders. In this tutorial, you will accomplish the following objectives:

Create a Folder for a new application.
Create child Folders of the new application Folder.
Create an Modeling Services table that uses the Folders.
Query the Modeling Services database with Folder information.
Remove a Folder.
Use Mx.exe to create and remove Folders.

Note

Note that some of the information in this tutorial is covered in a two-part video on repository Folders.

The scenario for this tutorial involves a fictional company named Contoso. Contoso uses Visual Studio 2010 to create an application to manage common Human Resources tasks. In this scenario, the Contoso Modeling Services administrators use SQL Server Management Studio to directly model objects in the database related to the Human Resources application. They create a main Modeling Services Folder to hold all versions of the Human Resources application. They also create subfolders for each version of the application. Finally, they add a SQL Server schema and an EmployeesTable table that contains a Folder column. This enables Contoso to add data to the Employees table that targets a specific version of the Human Resources application. Contoso Modeling Services administrators can then give users appropriate levels of access to one or more of the Human Resources Folders.

Creating Modeling Services Folders and Subfolders

Contoso administrators decide to create a main Modeling Services Folder named HumanResourcesApp. Although the HumanResourcesApp Folder can be immediately referenced by rows in the database, Contoso administrators see benefits in using separate subfolders for different versions of the Human Resources application. In this way, instance data in the Modeling Services related to the first version of the application can be secured and managed separately from instance data for subsequent versions. The following steps show how to add Folders and subfolders by inserting rows into the [Repository.Item].[Folders] view.

To create a new Modeling Services Folder using SQL Server Management Studio

On the Start menu, click All Programs, Microsoft SQL Server 2008, and open SQL Server Management Studio.
In the Connect to Server dialog, type the name of the Modeling Services server in the Server name combo box.
Specify the Authentication properties, and then click the Connect button.

Note

Creating and managing Folders requires the caller to belong to the RepositoryAdministrator role or to belong to the SQL Server sysadmin.
On the toolbar, click the New Query button. A blank query window should open.
In the blank query window, add the following T-SQL statements. These statements insert a new Folder row into the updatable view, [Repository.Item].[Folders]. The Folder is named HumanResourcesApp.
```
use Repository
go

-- Insert a new root-level Folder named HumanResourcesApp
insert into [Repository.Item].[Folders] 
   ([Name], [Folder])
   values (N'HumanResourcesApp', null)
```
Press the F5 key to run the T-SQL query and create the new Folder for the Human Resources application.

Confirm the new Folder was added by replacing the previous statements with the following T-SQL query.

-- Confirm that a row exists for a Folder with the name 'HumanResourcesApp'.
select * from [Repository.Item].[Folders] where Name=N'HumanResourcesApp'

In the query Results pane, you should see a single row for the HumanResourcesApp Folder.

Warning

Do not directly interact with the [Repository.Item].[FoldersTable] table. Even though a database administrator might have permission to add a Folder to the [Repository.Item].[FoldersTable] table, accessing the table instead of the view is unsupported and bypasses the Modeling Services security model. It could also cause future Folder insertions into the [Repository.Item].[Folders] view to fail.

To create child Modeling Services Folders using SQL Server Management Studio

The following T-SQL statements create two new Folders, HRApp1 and HRApp2. In the SQL Server Management Studio query window, replace the previous text with the following T-SQL statements.
```
use Repository
go

-- Create subfolders for the HumanResourcesApp Folder.
-- Insert two child Folders, one for each version of the 
-- Human Resources Application.
insert into [Repository.Item].[Folders] ([Name], [Folder])
values(N'HRApp1', [Repository.Item].[PathsFolder](N'HumanResourcesApp')),
      (N'HRApp2', [Repository.Item].[PathsFolder](N'HumanResourcesApp'))
```
Note

Note that the [Repository.Item].[PathsFolder] function above returns the Folder identifier for the given Folder path. Assigning the new Folders to a Folder of HumanResourcesApp makes these new Folders children of the HumanResourcesApp Folder.
Press the F5 key to run the query.

To view Modeling Services Folder Hierarchies

As Folder hierarchies get more complex, it is useful to be able to query a root Folder and recursively view all of its children. The [Repository.Item].[SubfoldersAndFolder] and [Repository.Item].[Subfolders] functions take a target Folder identifier and return a list of Folder identifiers for all of the child Folders.

Replace the text in the query window with the following T-SQL statements.

use Repository
go

-- Declare a local variable that holds the identifier of the target parent Folder.
declare @parentFolder as [Repository.Item].[FolderId]
set @parentFolder = [Repository.Item].[PathsFolder](N'HumanResourcesApp')

-- Join with the [Repository.Item].[SubfoldersAndFolder] function to list the 
-- target Folder and a recursive list of all subfolders.
select F.Id as [Folder Id], F.Name as [Folder Name],
    F.Folder as [Parent Folder], 
    [Repository.Item].[FoldersPath](F.Id) as [Folder Path]
from [Repository.Item].[Folders] F
inner join [Repository.Item].[SubfoldersAndFolder](@parentFolder) SF on (SF.Id = F.Id)

Press the F5 key to run the query.
The Results pane should show the three Folders related to Contoso’s Human Resources application. The following table provides an example of what the results might look like, but the Folder identifiers will likely be different on your server.

Folder Id Folder Name Parent Folder Folder Path

123

HumanResourcesApp

NULL

/HumanResourcesApp

124

HRApp1

123

/HumanResourcesApp/HRApp1

125

HRApp2

123

/HumanResourcesApp/HRApp2

Folder Id	Folder Name	Parent Folder	Folder Path
123	HumanResourcesApp	NULL	/HumanResourcesApp
124	HRApp1	123	/HumanResourcesApp/HRApp1
125	HRApp2	123	/HumanResourcesApp/HRApp2

Using Modeling Services Folders in Row Instances

After creating Modeling Services Folders for the Human Resources application, Contoso administrators add rows into Modeling Services tables that reference these new Folders.

To create a table that supports Modeling Services Folder references

Modeling Services data models use SQL Server schemas to group related database objects that describe the same problem domain. Before creating a table that supports Modeling Services Folder references, first create a SQL Server schema named Contoso.HumanResources.

In SQL Server Management Studio, replace the previous text in the Query window with the following T-SQL statements to create a new SQL Server schema.
```
use Repository
go

-- Create a schema to model Human Resources data.
create schema [Contoso.HumanResources] authorization [RepositoryOwner]
```
The next step creates a table named EmployeesTable. This table is owned by the Contoso.HumanResources schema. The [Contoso.HumanResources].[EmployeesTable] table contains employee data for Contoso’s Human Resources application. A foreign key constraint on the Folder column references the Id column of the [Repository.Item].[FoldersTable].

Replace the text in the query window with the following T-SQL statements to create a table that holds Employee data.
```
-- Create a table with a Folder column .
if (OBJECT_ID('[Contoso.HumanResources].[EmployeesTable]') is not null)
drop table [Contoso.HumanResources].[EmployeesTable]

create table [Contoso.HumanResources].[EmployeesTable](
   [Id] bigint NOT NULL IDENTITY,
   [Folder] [Repository.Item].[FolderId] NOT NULL,
   [FullName] nvarchar(max) NOT NULL,
   constraint [PK_Employees] primary key clustered ([Id]),
   constraint [FK_Employees_Folder_RepositoryItem_Folders] foreign key ([Folder])
      references [Repository.Item].[FoldersTable] ([Id])
      on delete cascade   
)
```
Note

Note that the FK_Employees_Folder_RepositoryItem_Folders foreign key constraint uses cascading deletes to maintain referential integrity. This means that when a Folder is deleted from the [Repository.Item].[FoldersTable], any rows that reference that Folder are deleted as well. This is a logical choice for Folders. It is similar to file system folders. When file system folders are deleted, all files in those folders are implicitly deleted as well.
Press the F5 key to run the query.

To insert data that references specific Modeling Services Folders

After creating the [Contoso.HumanResources].[EmployeesTable] table with support for Folders, add rows to this table that reference specific Folders.

In SQL Server Management Studio, replace the previous text in the Query window with the following T-SQL statements. This adds two employee rows that belong to the HRApp1 Folder and two employee rows that belong to the HRApp2 Folder.

use Repository
go

-- Obtain Folder identifiers for version 1.0 and version 2.0
-- of the Human Resources application.
declare @v1Folder as int = [Repository.Item].[PathsFolder](N'HumanResourcesApp/HRApp1')
declare @v2Folder as int = [Repository.Item].[PathsFolder](N'HumanResourcesApp/HRApp2')

-- Insert Employee data for version 1.0 of the application.
insert [Contoso.HumanResources].[EmployeesTable] (Folder, FullName)
   values(@v1Folder, 'David Campbell'),
         (@v1Folder, 'Molly Clark')

-- Insert Employee data for version 2.0 of the application.
insert [Contoso.HumanResources].[EmployeesTable] (Folder, FullName)
   values(@v2Folder, 'Allison Brown'),
         (@v2Folder, 'Carlos Lacerda')

Press the F5 key to run the query.
Run the following query to view the inserted rows in the [Contoso.HumanResources].[EmployeesTable] table.
```
select * from [Contoso.HumanResources].[EmployeesTable]
order by [Folder],[FullName]
```

Deleting Modeling Services Folders

There are scenarios that require removing Folders and the data owned by those Folders. For example, Contoso successfully migrated to version 2.0 of the Human Resources application. Contoso administrators decide to remove the data associated with version 1.0 of the Human Resources application.

To remove a Modeling Services Folder and associated rows

In SQL Server Management Studio, replace the previous text in the Query window with the following T-SQL statement to delete the HRApp1 Folder.
```
use Repository
go

delete [Repository.Item].[Folders] where Name = 'HRApp1'
```
Press the F5 key to run the query. The [Contoso.HumanResources].[EmployeesTable] table contains a foreign key reference to the [Repository.Item].[FoldersTable] table with cascading deletes. Deleting the HRApp1 Folder also deletes the rows in the [Contoso.HumanResources].[EmployeesTable] table that referenced that Folder.

Replace the text in the query window with the following T-SQL statement to view the rows remaining in the [Contoso.HumanResources].[EmployeesTable].

select E.FullName, E.Folder,
   F.Name as FolderName   
from [Contoso.HumanResources].[EmployeesTable] E
inner join [Repository.Item].[Folders] F on F.Id = E.Folder
order by E.[Folder],E.[FullName]

Press the F5 key to run the query. Note that only rows that reference the HRApp2 Folder remain in the table.

To remove a Modeling Services Folder using Mx.exe

On the Start Menu, click All Programs, Microsoft SQL Server Modeling CTP, and open the Microsoft SQL Server Modeling CTP Command Prompt.
Type the following command and press ENTER. This command removes a Folder named PayrollApp that is a subfolder of the system-provided Applications Folder.
```
mx dropFolder Repository/PayrollApp /database:Repository
```
Note

Note that this procedure demonstrates an alternative method for removing Folders. Users and applications have a choice of using Mx.exe or direct data access routines for managing Folders.

Share via