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.

Extensibility 4

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.

Extensibility 5

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.

Entitiy Relationship Model

FIM 2010 R2 Reporting Class Model

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

Class Model

Show: