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.
.png "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.
.png "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.
.png "Entitiy Relationship Model")
FIM 2010 R2 Reporting Class Model
The following diagram shows the FIM 2010 R2 Reporting Class Model.
.png "Class Model")