FIM 2010 R2 Reporting Data Warehouse Schema

FIM 2010 R2 Data Warehouse Schema

Service Manager uses management pack files that contain the object definitions for the various features of the product. You can customize the behavior of Service Manager and extend it by creating and modifying management packs. A management pack is an XML-based file that contains definitions for classes, workflows, views, forms, and reports. FIM 2010 R2 reporting uses a management pack to create the Data Warehouse schema.

The schema xml file, Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml, is located in C:\Program Files\Microsoft Forefront Identity Manager\2010\Reporting\Reports.

The FIM 2010 R2 schema is extensible. By creating additional management packs you can do the following:

• Extend Service Manager with new objects.

• Extend Service Manager with new behavior.

• Store new custom objects that you created, such as a form or a template.

The Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml is split into the following parts:

Section	Description
Manifest	The manifest identifies the management pack and declares any references to other management packs.
TypeDefinitions	The TypeDefinitions section of a management pack contains definitions for classes, enumerations, and relationships that are used by the management pack.
Presentation	The Presentation section of a management pack declares and defines user interface-related elements. These include forms declarations, categories, and console tasks.
Warehouse
Reporting
LanguagePacks	The LanaguagePacks section of a management pack defines string resources and mappings for management pack elements.
Resources	The Resources section of a management pack contains references to binary resources, which are contained in assemblies that are separate from the management pack.

The following is the Manifest section from the Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml. This section identifies the management pack. It also declares references to other management packs.

<Manifest>
<Identity>
<ID>Microsoft.Forefront.IdentityManager.Datawarehouse.Base</ID>
<Version>1.0.0.1</Version>
</Identity>
<Name>FIM Data Warehouse Library</Name>
<References>
<Reference Alias="DWBase">
<ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>
<Version>7.0.6555.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="System">
<ID>System.Library</ID>
<Version>7.0.6555.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="SMReport">
<ID>Microsoft.SystemCenter.Report.Library</ID>
<Version>7.0.5826.216</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
</References>
</Manifest>

The TypeDefinition section is where we define our ClassTypes and our RelationshipTypes. The code below is the ClassType FIMDW.FIMPerson from the Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml file. The properties defined for this class are bound to attributes of the Person object in FIM.

<ClassType ID="FIMDW.FIMPerson" Accessibility="Public" Abstract="false" Base="FIMDW.FIMEntity" Hosted="false" Singleton="false" Extension="false">
<Property ID="FIMPersonAccountName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonAD_UserCannotChangePassword" Type="bool" Key="false" Required="false"/>
<Property ID="FIMPersonAddress" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonAssistant" Type="string" Key="false" MaxLength="40" MinLength="0" Required="false"/>
<Property ID="FIMPersonCity" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonCompany" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonCostCenter" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonCostCenterName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonCountry" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonDepartment" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonDescription" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonDomain" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonEmail" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonEmployeeEndDate" Type="datetime" Key="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonEmployeeID" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonEmployeeStartDate" Type="datetime" Key="false" Required="false"/>
<Property ID="FIMPersonEmployeeType" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonFirstName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonFreezeCount" Type="int" Key="false" Required="false"/>
<Property ID="FIMPersonIsRASEnabled" Type="bool" Key="false" Required="false"/>
<Property ID="FIMPersonJobTitle" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonLastName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonLastResetAttemptTime" Type="datetime" Key="false" Required="false"/>
<Property ID="FIMPersonLoginName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonMailNickname" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonManager" Type="string" Key="false" CaseSensitive="false" MaxLength="40" MinLength="0" Required="false"/>
<Property ID="FIMPersonMiddleName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonMobilePhone" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonOfficeFax" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonOfficeLocation" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonOfficePhone" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonPostalCode" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>
<Property ID="FIMPersonRegister" Type="bool" Key="false" Required="false"/>
<Property ID="FIMPersonRegistrationRequired" Type="bool" Key="false" Required="false"/>
</ClassType>

The following is a section-by-section explanation of what the type definition contains.

<ClassType ID="FIMDW.FIMPerson" Accessibility="Public" Abstract="false" Base="FIMDW.FIMEntity" Hosted="false" Singleton="false" Extension="false">

ClassType element	Defines the FIMPerson class
ID attribute	The unique identifier of the class
Accessibility attribute	Defines whether other classes can derive from this class.
Abstract attribute	Defines whether instances of this class can be created, or whether the class should just be used as a parent class to other classes to derive from
Base attribute	The ID of the class from which this class derives
Hosted attribute	Defines whether this class is hosted by another class.
Singleton attribute	Used when there is one and only one instance of the class
Extension attribute	Defines whether the class is customizable

<Property ID="FIMPersonAccountName" Type="string" Key="false" CaseSensitive="false" MaxLength="448" MinLength="0" Required="false"/>

Property element	Contains the following attributes
ID attribute	Designates the unique identifier of the property.
Type attribute	Indicates the data type of the property
Key attribute	Determines whether this property is to be used to uniquely identify the class.
CaseSensitive attribute	Indicates whether the property is case sensitive.
MaxLength attribute	Indicates the maximum length of the property.
MinLength attribute	Indicates the minimum length of the property.
Required	Indicates whether this property is required.

Relationships are defined between classes to indicate an association between a particular instance of one class and the particular instance of another. RelationshipTypes are used to represent these. All of the RelationshipTypes in Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml are reference relationships. The reference relationship is the most general relationship type. A reference relationship is used when the parent and child classes are not dependent on one another

<RelationshipTypes>
<RelationshipType ID="FIMDW.FIMGroupHasComputedMembers" Accessibility="Public" Abstract="false" Base="System!System.Reference">
<Property ID="FIMCreatedDate" Type="datetime" Key="false" Required="false"/>
<Property ID="FIMDeletedDate" Type="datetime" Key="false" Required="false"/>
<Property ID="FIMCreatedRequestID" Type="string" Key="false" MaxLength="40" MinLength="0" Required="false"/>
<Property ID="FIMDeletedRequestID" Type="string" Key="false" MaxLength="40" MinLength="0" Required="false"/>
<Source ID="FIMGroup" MinCardinality="0" MaxCardinality="2147483647" Type="FIMDW.FIMGroup"/>
<Target ID="FIMGroupComputedMember" MinCardinality="0" MaxCardinality="2147483647" Type="FIMDW.FIMEntity"/>
</RelationshipType>

The following is a section-by-section explanation of what the type definition contains.

<RelationshipType ID="FIMDW.FIMGroupHasComputedMembers" Accessibility="Public" Abstract="false" Base="System!System.Reference">

RelationshipType element	Defines the relationship class
ID attribute	Designates the unique identifier of the relationship.
Accessibility attribute	Defines whether other classes can derive from this class.
Abstract attribute	Defines whether instances of this class can be created, or whether the class should just be used as a parent class to other classes to derive from.
Base attribute	The ID of the class from which this class derives

The property values for the RelationshipType are similar to the ones above.

<Source ID="FIMGroup" MinCardinality="0" MaxCardinality="2147483647" Type="FIMDW.FIMGroup"/>

Source element	Represents the origination point of the defined relationship
ID attribute	Designates the unique identifier.
MinCardinality attribute	Minimum cardinality
MaxCardinality attribute	Maximum cardinaltiy
Type attribute	Indicates the data type of the relationship

<Target ID="FIMGroupComputedMember" MinCardinality="0" MaxCardinality="2147483647" Type="FIMDW.FIMEntity"/>

Target element	Represents the object of the defined relationship.
ID attribute	Designates the unique identifier.
MinCardinality attribute	Minimum cardinality
MaxCardinality attribute	Maximum cardinaltiy
Type attribute	Indicates the data type of the relationship

The Presentation section of a management pack declares and defines user interface-related elements. The following is the Presentation section from the Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml.

<Presentation>
<Folders>
<Folder ID="Forefront.IdentityManager.Reporting" Accessibility="Public" ParentFolder="SMReport!ServiceManager"/>
</Folders>
<FolderItems/>
</Presentation>

The following is a section-by-section explanation of what the type definition contains.

<Folder ID="Forefront.IdentityManager.Reporting" Accessibility="Public" ParentFolder="SMReport!ServiceManager"/>

Folder element	Determines the location in the navigation tree in which the view is displayed.
ID attribute	Designates the unique identifier of the folder.
Accessibility attribute	Defines whether other classes can derive from this class.
ParentFolder attribute	Identifies the parent folder.

The LanaguagePacks section of a management pack defines string resources and mappings for management pack elements. The following is the LanguagePacks section from the Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml.

<LanguagePacks>
<LanguagePack ID="ENU" IsDefault="true">
<DisplayStrings>
<DisplayString ElementID="Microsoft.Forefront.IdentityManager.Datawarehouse.Base">
<Name>FIM Data Warehouse Library</Name>
<Description>This management pack adds types to represent base out of box classes for the Forefront Identity Manager default reports</Description>
</DisplayString>
<DisplayString ElementID="Forefront.IdentityManager.Reporting">
<Name>Forefront Identity Manager Reporting</Name>
<Description>Contains all default Forefront Identity Manager reports</Description>
</DisplayString>
<!--
 Add DisplayStrings for ALL Facts and Dimension Elements 
--> 
</DisplayStrings>
</LanguagePack>
</LanguagePacks>
 

The Resources section of a management pack contains references to binary resources, which are contained in assemblies that are separate from the management pack. The following is the Resource section from the Microsoft.Forefront.IdentityManager.Datawarehouse.Base.xml.

<Resources>
<Resource ID="TransformFIMRequestFactResource" Accessibility="Public" FileName="TransformFIMRequestFact.sql"/>
<Resource ID="UninstallFIMRequestFactResource" Accessibility="Public" FileName="UninstallFIMRequestFact.sql"/>
<Resource ID="TransformFIMRequestTargetDetailFactResource" Accessibility="Public" FileName="TransformFIMRequestTargetDetailFact.sql"/>
<Resource ID="UninstallFIMRequestTargetDetailFactResource" Accessibility="Public" FileName="UninstallFIMRequestTargetDetailFact.sql"/>
<Resource ID="TransformFIMApprovalFactResource" Accessibility="Public" FileName="TransformFIMApprovalFact.sql"/>
<Resource ID="UninstallFIMApprovalFactResource" Accessibility="Public" FileName="UninstallFIMApprovalFact.sql"/>
<Resource ID="TransformFIMApprovalResponseFactResource" Accessibility="Public" FileName="TransformFIMApprovalResponseFact.sql"/>
<Resource ID="UninstallFIMApprovalResponseFactResource" Accessibility="Public" FileName="UninstallFIMApprovalResponseFact.sql"/>
<Resource ID="TransformFIMSequenceDimResource" Accessibility="Public" FileName="TransformFIMSequenceDim.sql"/>
<Resource ID="UninstallFIMSequenceDimResource" Accessibility="Public" FileName="UninstallFIMSequenceDim.sql"/>
</Resources>

For additional information on authoring management packs see System Center Service Manager 2010 SP1 Authoring Guide

Understanding Facts and Dimensions

The following information is some basic data warehousing concepts that will help with understanding the FIM 2010 R2 data warehouse. By better understanding the data warehouse, the extensibility portion becomes easier to explain and demonstrate.

Fact and Fact Tables

With regard to data warehousing, a fact is a value or measurement. It would be considered historical data, for example, FIMCreatedDate, is a fact that is part of the FIMDW.FIMGroupHasComputerdMembersFact table. It is a specific date in time.

Each data warehouse or data mart includes one or more fact tables. Central to a star or snowflake schema, a fact table captures the data that measures the organization's business operations. A fact table might contain business sales events such as cash register transactions or the contributions and expenditures of a nonprofit organization. Fact tables usually contain large numbers of rows, sometimes in the hundreds of millions of records when they contain one or more years of history for a large organization.

A key characteristic of a fact table is that it contains numerical data (facts) that can be summarized to provide information about the history of the operation of the organization. Each fact table also includes a multipart index that contains as foreign keys the primary keys of related dimension tables, which contain the attributes of the fact records. Fact tables should not contain descriptive information or any data other than the numerical measurement fields and the index fields that relate the facts to corresponding entries in the dimension tables.

The following is the FIMDW.FIMGroupHasComputerdMembersFact table. This is a fact table for FIM 2010 R2 Reporting.

Dimension and Dimension Tables

Dimension tables contain attributes that describe fact records in the fact table. Some of these attributes provide descriptive information; others are used to specify how fact table data should be summarized to provide useful information to the analyst. Dimension tables contain hierarchies of attributes that aid in summarization. For example, a dimension containing product information would often contain a hierarchy that separates products into categories such as food, drink, and nonconsumable items, with each of these categories further subdivided a number of times until the individual product SKU is reached at the lowest level.

The following is the FIMDW.FIMGroupDim table. This is a dimension table for FIM 2010 R2 Reporting.

FIM 2010 R2 Reporting Entity-Relationship Model

The following diagram shows the FIM 2010 R2 Reporting Entity-Relationship Model between FIM and the Data Warehouse.

FIM 2010 R2 Reporting Class Model

The following diagram shows the FIM 2010 R2 Reporting Class Model.