FIM 2010 R2 Reporting Custom Reports and Extensibility

FIM 2010 R2 Reporting – Custom Reports and Extensibility

FIM 2010 R2 provides reports based on the default FIM schema, however you can also extend FIM Reporting to accommodate custom resources and attributes that you have created in the FIM schema, or customize the way that reports are displayed. The following is possible with the Extensibility portion of the FIM 2010 R2 Reporting feature.

  1. Extending the Data Warehouse Schema

  2. Extend the built-in reports

  3. Create new Reports

The first thing that must be addressed is what are you trying to do. That is are you trying to:

  1. Add single-value attributes to an object in the Data Warehouse such as User or Group

  2. Create an entirely new object in the Data Warehouse, such as Computer

  3. Create a multi-valued reference attribute, such as Computer Administrators

Remember, the default FIM Schema already exists in the Data Warehouse once FIM 2010 R2 Reporting has been successfully deployed. But any new resource or attribute that is created in FIM must be brought over into the Data Warehouse in order to report on it. Use the following table to determine what you are attempting to do and then use the corresponding section as an example on how to accomplish this.

We want to: Example See the following for an example:

Create a new attributes on an existing object

Adding Tier and Entitlements to our Group object

Create new attributes on an existing object

Create an entirely new object

Adding a Computer object

Create New Object

Create a new multi-valued reference attribute

Adding a Computer Admins attribute to our Computer object

Create a new multi-valued reference attribute

Create a new multi-valued non-reference attribute

This is currently not supported

This is currently not supported

Prior to attempting to extend FIM 2010 R2 Reporting see the FIM 2010 R2 Reporting Data Warehouse Schema and FIM 2010 R2 Reporting Data Warehouse Binding sections later in this document. For detailed step-by-step guidance on extending FIM Reporting, see Test Lab Guide: Demonstrating Forefront Identity Manager 2010 R2 Reporting Extensibility.

Creating Database Snapshots

It is highly recommended that you take a database snapshot of the following databases before performing any extensibility testing:

  • DWDataMart

  • DWStagingAndConfig

  • DWRepository

  • ServiceManager

Doing so will allow you to restore in case of an error or failure during the extension process.

Snapshots are created using T-SQL. There is no way to create a snapshot from within SQL Server Service Manager. You will need sufficient permissions to take the snapshot. In order to create the snapshot log on to one of the following servers (depending on the database you are backing up), start SQL Server Service Manager and execute the T-SQL command provided in the table below. The information below assumes that these databases are installed to their default location. If not, adjust the T-SQL accordingly. Also, modify the T-SQL example with the correct date and time. It is recommended to name the snapshots with a date and time so that the snapshots, from the same point in time, for the SCSM database and the Data Warehouse database can be used. This is important because if they are not from the same point in time, then reliability cannot be ensured. This is due to grooming jobs that occur on the SCSM Management server. For additional information on this see FIM 2010 R2 Reporting Disaster Recovery later in this guide and the System Center Service Manager 2010 SP1 Disaster Recovery Guide

Database Name Server that corresponds to this database T-SQL Example:

DWDataMart

Data Warehouse

CREATE DATABASE DWDataMart_ds_1800_05182012 ON (NAME=SM_DATA, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DWDataMart_data_1800_05182012.ss’) AS SNAPSHOT OF DWDataMart;
GO

DWStagingandConfig

Data Warehouse

CREATE DATABASE DWStagingandConfig_ds_1800_05182012 ON (NAME=SM_DATA, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ DWStagingandConfig_data_1800_05182012.ss’) AS SNAPSHOT OF DWStagingandConfig;
GO

DWRepository

Data Warehouse

CREATE DATABASE DWRepository_ds_1800_05182012 ON (NAME=SM_DATA, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ DWRepository_data_1800_05182012.ss’) AS SNAPSHOT OF DWRepository;
GO

ServiceManager

SCSM Service Manager

CREATE DATABASE ServiceManager_ds_1800_05182012 ON (NAME=SM_DATA, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ServiceManager_data_1800_05182012.ss’) AS SNAPSHOT OF ServiceManager;
GO

Create new attributes on an existing object

In order to create new attributes on an object in the Data Warehouse let us first look at the default binding. In the diagram below, the FIM Group resource and it’s attributes are mapped to the Data Warehouse FIMGroup object.

New Extensibility 1

Now, let’s say we have added two attributes to our Group object in FIM. Our mapping would look like the diagram below:

New Extensibility 2

In order to report on these two new attributes, we need to extend the FIMGroup object in the Data Warehouse to include these two attributes.

New Extensibility 3

In order to extend FIM 2010 R2 reporting by adding new attributes to existing objects in the Data Warehouse use the following:

To create new attributes on an existing object

  1. Create Schema in Data Warehouse. 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. This is the base data warehouse schema. This file is not extendable and editing it is prohibited. In order to extend the data warehouse schema, a new xml file with the same format as the Base.xml file must be created. Below is an entry from an example file that extends the data warehouse schema class FIMGroup (this is Group in FIM) by adding two new attributes, FIMGroupTier and FIMGroupEntitlements. The following will break this xml file down to help better explain it. For additional information on the Data Warehouse schema, see the FIM 2010 R2 Reporting Data Warehouse Schema section later in this document.

    <ManagementPack ContentReadable="true" SchemaVersion="1.1" OriginalSchemaVersion="1.1">
    <!--
     (C) 2011 Microsoft Corporation. All rights reserved. 
    --> 
    <Manifest>
    <Identity>
    <ID>Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility</ID>
    <Version>1.0.0.1</Version>
    </Identity>
    <Name>FIM Data Warehouse Extensibility Test</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="FIMDW">
    <ID>Microsoft.Forefront.IdentityManager.Datawarehouse.Base</ID>
    <Version>1.0.0.1</Version>
    <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
    </References>
    </Manifest>
    <TypeDefinitions>
    <EntityTypes>
    <ClassTypes>
    <ClassType ID="FIMDW.FIMGroupExtensionTest" Accessibility="Public" Abstract="false" Base="FIMDW!FIMDW.FIMGroup" Hosted="false" Singleton="false" Extension="true">
    <Property ID="FIMGroupTier" Type="decimal" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="25" MinLength="0" Required="false"/>
    <Property ID="FIMGroupEntitlements" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="488" MinLength="0" Required="false"/>
    </ClassType>
    </ClassTypes>
    </EntityTypes>
    </TypeDefinitions>
    <Warehouse>
    <Dimensions>
    <Dimension ID="FIMGroupExtensionTestDim" Accessibility="Public" InferredDimension="true" Target="FIMDW.FIMGroupExtensionTest" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false"/>
    </Dimensions>
    </Warehouse>
    <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
    <DisplayStrings>
    <DisplayString ElementID="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility">
    <Name>FIM Data Warehouse Extensibilty Test Library</Name>
    <Description>This management pack extends Group to include two new attributes.</Description>
    </DisplayString>
    </DisplayStrings>
    </LanguagePack>
    </LanguagePacks>
    </ManagementPack>
    
    1. Below, an extended class is created using the following:

      • ID="FIMDW.FIMGroupExtensionTest – name of our new extended class in the data warehouse

      • Base="FIMDW!FIMDW.FIMGroup" – specifies the class being extended

      • Extension=”true” – states that this class is extending the FIMGroup class

      Extensibility 1

    2. Below are two examples of the properties that are being added to our new class. Note that they are of differing types. One is a decimal and one is a string.

      Extensibility 1

      Extensibility 3

    3. Finally, the class is added as a dimension using the following:

      • ID="FIMGroupExtensionTestDim" – the name of the new dimension

      • Target="FIMDW.FIMGroupExtensionTest" – refers to the class being defined as a dimension

      • HierarchySupport="IncludeExtendedClassProperties" – specifies to the data warehouse to make all the extended properties dimensions

      Extensibility 7

  2. Create FIM to Data Warehouse Binding. The schema for the FIM Reporting Store is based on data warehousing best practices including the utilization of traditional data warehousing schema. This schema differs significantly from the Entity-Attribute-Value schema used in the FIMService. The schema for the FIM Reporting Store is based on data warehousing design techniques with dimensions and facts. Because the schema’s are significantly different, the ability to map FIM objects to the Data warehouse objects needs to be addressed. The new reporting feature in Forefront Identity Manager 2010 R2 introduces the DataWarehouseBinding object to FIM. This object is used to define the mapping between the FIM Schema and the Data Warehouse schema and is represented by an xml file. Below is a copy of the Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility.xml file that binds our new dimension with FIM. The following will break this xml file down to help better explain it. For additional information on the Data Warehouse schema, see the FIM 2010 R2 Reporting Data Warehouse Binding section later in this document.

    <DataWarehouseBindingObject>
    <DisplayName>Test Extensibility</DisplayName>
    <DataWarehouseBindingIdentity>Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility</DataWarehouseBindingIdentity>
    <DataWarehouseMapping>
    <Mapping>
    <ClassBindings>
    <!-- Group --> 
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="Group" AttributeName="Tier"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMGroupExtensionTest" PropertyIdentity="FIMGroupTier" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility" ManagementPackVersion="1.0.0.1"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="Group" AttributeName="Entitlements"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMGroupExtensionTest" PropertyIdentity="FIMGroupEntitlements" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility" ManagementPackVersion="1.0.0.1"/>
    </ClassBinding>
    </ClassBindings>
    </Mapping>
    </DataWarehouseMapping>
    </DataWarehouseBindingObject>
    
    1. Below, we bind our DataWarehouseBindingObject to FIM with the following:

      • ObjectTypeName=”Group” – FIM Object type

      • AttributeName=”Tier” – FIM Attribute Name

      • ClassTypeIdentity=”FIMDW.FIMGroupExtensionTest” – Data Warehouse class name

      • PropertyIdentity=”FIMGroupTier” – Data Warehouse property name

      • ManagementPackIdentity=”Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility” - Name of the schema management pack that defines this class and properties.

      • ManagmentPackVersion=”1.0.0.1” – Version of the schema management pack that defines this class and properties.

      •   Extensibility 5

  3. Import into testing environment. At this point, two xml files have been created. Both are named Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility.xml. The first one, which was created in step one, is the schema file and the second one, created in step two is the binding file. Now that we have these files, we must import them into our environment. This is done with the use of the Import- FIMReportingSchemaDefinition PowerShell cmdlets. This can be done using the following procedure.

    To Run the Import- FIMReportingSchemaDefinition PowerShell Cmdlet

    1. Open Windows PowerShell.

    2. On the command line type the following and hit return Set-ExecutionPolicy unrestricted. This will bring up a message about the execution policy. Click Y.

    3. Type Add-PSSnapIn FimReportingAdministration and hit enter.

    4. Enter the following for the Import-FIMReportingSchemaDefinition cmdlet and hit enter. The following assumes our xml files are in directories called C:\ReportingExt\binding and C:\ReportingExt\schema.

      Import-FIMReportingSchemaDefinition -BindingXmlFile C:\ReportingExt\binding\Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility.xml -CompanyName "Contoso" -ManagementPackFile C:\ReportingExt\schema\Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility.xml -ServiceManagerServer APP2 -Verbose -SealManagementPack -SealingKeyFile C:\ReportingExt\TestFimReportingKeyPair.snk
      

      Import-FIMReportingSchemaDefinition

    5. Once this is finished, the synchronization job must be started to move the data from FIM into the data warehouse. The SCSM Data Warehouse Management Pack Synchronization Job instructs the Data Warehouse to look for any new Management Packs that have been added, create any new schema, and deploy any reports defined within them. Use the following procedure to start the Data Warehouse Management Pack Synchronization.

      To run the Data Warehouse Management Pack Synchronization Job

      1. On the command line type the following and hit return Add-PSSnapIn SMCmdletSnapIn. This will load the SCSM PowerShell Cmdlets into your current PowerShell session.

      2. On the command line type the following and hit return Start-SCDWJob –JobName MPSyncJob –ComputerName APP3. This will begin the synchronization job. This may take up to 5 minutes to complete.

      3. Now check the status of the MPSyncJob. On the command line type the following and hit return Get-SCDWJob –JobName MPSyncJob –ComputerName APP3. This will return information about the job. Note the Status.

        Get MPSync

      4. You need to wait until this job completes before moving to the next part. If the status says running check it again in a minute or so. Once the status says Not Started move on to the next part.

    6. Once this has finished, we will synchronize the FIM data with the Data Warehouse by running the Start-FIMReportingInitialSync. This will extract our new baseline data from FIM and moves it over to the System Center staging tables. To run Start-FIMReportingInitialSync use the following procedure.

      To Run Start-FIMReportingInitialSync PowerShell Cmdlet

      1. On the command line type the following and hit return Start-FIMReportingInitialSync.

        FIMReportingInitialSync

        Important

        If the script is not in the users PATH, you can access it by navigating to C:\Program Files\Microsoft Forefront Identity Manager\2010\Reporting\PowerShell

      2. To check the status of our job, go back into the FIM Portal.

      3. On the left, at the bottom click Administration.

      4. On the Administration page click All Resources.

      5. On the All Resources page, scroll down and double-click Reporting Job. On the Reporting Job screen you will see our job with a Reporting Job Type of Initial.

      6. Double-click on Reporting Job under Display Name. This will bring up the attributes of this job. At the top click the Extended Attributes tab.

      7. On the Extended Attributes, note the Reporting Job Status. If this says Running then wait. Otherwise, if it says Completed close the Reporting Job attributes.

        Check Initial Status

      8. Minimize Internet Explorer.

    7. Once this has finished, we will do an incremental synchronization to move FIM data with the Data Warehouse by running the Start-FIMReportingIncrementalSync. This will get the data from the ExportLog table and move this to the data warehouse. To run Start-FIMReportingIncrementalSync use the following procedure.

      To Run Start-FIMReportingIncrementalSync PowerShell Cmdlet

      1. Back in Windows PowerShell, on the command line type the following and hit return Start-FIMReportingIncrementalSync. . It will briefly flash an Importing change 1 message.

        Start-FIMReportingIncrementalSync

      2. To check the status of our job, go back into the FIM Portal.

      3. On the left, at the bottom click Administration.

      4. On the Administration page click All Resources.

      5. On the All Resources page, scroll down and double-click Reporting Job. On the Reporting Job screen you will see our job with a Reporting Job Type of Incremental.

        Reporting Jobs

      6. Double-click on Reporting Job under Display Name. This will bring up the attributes of this job. At the top click the Extended Attributes tab.

      7. On the Extended Attributes, note the Reporting Job Status. If this says Running then wait. Otherwise, if it says Completed close the Reporting Job attributes. You will have to close and re-open the job in the FIM Portal to see the status change. It will not change automatically.

        Incremental Status

      8. Minimize Internet Explorer.

    8. After the synchronization jobs are completed the ETL process can be kicked off. The Export, Transform, Load – (ETL) process that runs on the SCSM server takes that data that was exported during the processes above and loads it into the Data Warehouse. This process runs at a specific interval on the SCSM Data Warehouse. This process can be kicked off by using a script. See the FIM 2010 R2 Reporting ETL Process section for a copy of the script. To use the script to kick-off the ETL process use the following procedure.

      To Create and Run ETLScript PowerShell Script

      1. On the PowerShell command line type the following and hit return Add-PSSnapin SMCmdletSnapIn.

      2. On the command line type the following and hit return [environment]::SetEnvironmentVariable(“IMT.DataWarehouse”, “APP3”).

        Warning

        Change APP3 to the name of your data warehouse server.

      3. On the command line type the following and hit return C:\ETL\ETLScript.ps1.

        ETLScript

        Warning

        This will take a while to run. It will take about 30-35 minutes and information will populate the PowerShell window.

  4. Create a report in SQL. Now that the new schema has been created, it has been bound to FIM, and the data from FIM has been brought over into the data warehouse, reports can be created. Creating a SQL report is outside the scope of this document, but this step is provided for informational purposes.

  5. Create a new report Management Pack. Once you have a report created you can now create a new report Management Pack. Creating report management packs is outside the scope of this document. For information on management pack authoring see the System Center Service Manager 2010 SP1 Authoring Guide.

Create New Object

In order to create a new object in the Data Warehouse let us first see how an object might look in FIM. Below is a diagram of the ComputerReportObject resource and how it looks in FIM.

New Extensibility 4

In order to report on this object, we need to create a new object in the Data Warehouse and map the attributes to this object. All new objects created in the Data Warehouse will extend from FIMEntity.

New Extensibility 5a

In order to extend FIM 2010 R2 reporting by adding a new object use the following:

To create a new object

  1. Create Schema in Data Warehouse. Below is an entry from an example file that extends the data warehouse schema class FIMEntity to create the FIMComputerReportObject. The FIMComputerReportObject has 9 single-valued attributes and 1 multi-valued reference attribute associated with it. The following will break this xml file down to help better explain it. For additional information on the Data Warehouse schema, see the FIM 2010 R2 Reporting Data Warehouse Schema section later in this document.

    <ManagementPack ContentReadable="true" SchemaVersion="1.1" OriginalSchemaVersion="1.1">
    <!--
     (C) 2012 Microsoft Corporation. All rights reserved. 
    --> 
    <Manifest>
    <Identity>
    <ID>Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer</ID>
    <Version>1.0.0.0</Version>
    </Identity>
    <Name>FIM Data Warehouse Computer Test</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="FIMDW">
    <ID>Microsoft.Forefront.IdentityManager.Datawarehouse.Base</ID>
    <Version>1.0.0.1</Version>
    <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
    </Reference>
    </References>
    </Manifest>
    <TypeDefinitions>
    <EntityTypes>
    <ClassTypes>
    <ClassType ID="FIMDW.FIMComputerReportObject" Accessibility="Public" Abstract="false" Base="FIMDW!FIMDW.FIMEntity" Hosted="false" Singleton="false" Extension="false">
    <Property ID="FIMComputerReportObjectHasPower" Type="bool" AutoIncrement="false" Key="false" CaseSensitive="false" Required="false"/>
    <Property ID="FIMComputerReportObjectServiceTime" Type="datetime" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="488" MinLength="0" Required="false"/>
    <Property ID="FIMComputerReportObjectAssetTag" Type="decimal" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="25" MinLength="0" Required="false"/>
    <Property ID="FIMComputerReportObjectDescription" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="488" MinLength="0" Required="false"/>
    <Property ID="FIMComputerReportObjectMachineRole" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="488" MinLength="0" Required="false"/>
    <Property ID="FIMComputerReportObjectCostCenter" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="488" MinLength="0" Required="false"/>
    <Property ID="FIMComputerReportObjectDomain" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="488" MinLength="0" Required="false"/>
    <Property ID="FIMComputerReportObjectCustodian" Type="string" AutoIncrement="false" Key="false" CaseSensitive="false" MaxLength="40" MinLength="0" Required="false"/>
    </ClassType>
    </ClassTypes>
    <RelationshipTypes>
    <RelationshipType ID="FIMDW.FIMComputerReportObjectHasAdministrators" Accessibility="Public" Abstract="false" Base="System!System.Reference">
    <Property ID="FIMCreatedDate" Type="datetime" AutoIncrement="false" Key="false" Required="false"/>
    <Property ID="FIMDeletedDate" Type="datetime" AutoIncrement="false" 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="FIMComputerReportObject" MinCardinality="0" MaxCardinality="2147483647" Type="FIMDW.FIMComputerReportObject"/>
    <Target ID="FIMComputerAdministrators" MinCardinality="1" MaxCardinality="1" Type="FIMDW!FIMDW.FIMEntity"/>
    </RelationshipType>
    </RelationshipTypes>
    </EntityTypes>
    </TypeDefinitions>
    <Warehouse>
    <Dimensions>
    <Dimension ID="FIMComputerReportObjectDim" Accessibility="Public" InferredDimension="true" Target="FIMDW.FIMComputerReportObject" HierarchySupport="IncludeExtendedClassProperties" Reconcile="false"/>
    </Dimensions>
    <Facts>
    <RelationshipFact ID="FIMComputerReportObjectHasAdministratorsFact" Accessibility="Public" Domain="DWBase!Domain.ActivityManagement" TimeGrain="Daily" SourceType="FIMDW.FIMComputerReportObject" SourceDimension="FIMComputerReportObjectDim">
    <Relationships RelationshipType="FIMDW.FIMComputerReportObjectHasAdministrators" TargetDimension="FIMDW!FIMEntityDim"/>
    </RelationshipFact>
    </Facts>
    </Warehouse>
    <LanguagePacks>
    <LanguagePack ID="ENU" IsDefault="true">
    <DisplayStrings>
    <DisplayString ElementID="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer">
    <Name>FIM Data Warehouse Extensibilty Test Library</Name>
    <Description>This management pack adds Computer</Description>
    </DisplayString>
    </DisplayStrings>
    </LanguagePack>
    </LanguagePacks>
    </ManagementPack>
    
    1. Below, a new class (which is actually an extended class of FIMEntity) is created using the following:

      • ID="FIMDW.FIMComputerReportObject” – name of our new extended class in the data warehouse

      • Base="FIMDW!FIMDW.FIMEntity" – specifies the class being extended

      New Extensibility 6

    2. Below is an example of the properties that are being added to our new class.

      New Extensibility 7

    3. Because we have a multi-valued reference attribute we need to add a RelationshipType. Below the RelationshipType is created using the following:

      • ID="FIMDW.FIMComputerReportObjectHasAdministrators" – Designates the unique identifier of the relationship.

      • Base="System!System.Reference"– The ID of the class from which this class derives

      • Source ID="FIMComputerReportObject" - Represents the origination point of the defined relationship

      • Type="FIMDW.FIMComputerReportObject" - Indicates the data type of the relationship

      • Target ID="FIMComputerAdministrators" - Designates the unique identifier.

      • Type="FIMDW!FIMDW.FIMEntity" - Indicates the data type of the relationship

      New Extensibility 8

    4. Finally, the class is added as a dimension using the following:

      • ID=" FIMComputerReportObjectDim"– the name of the new dimension

      • Target="FIMDW.FIMComputerReportObject" – refers to the class being defined as a dimension

      • HierarchySupport="IncludeExtendedClassProperties" – specifies to the data warehouse to make all the extended properties dimensions

      New Extensibility 9

  2. Create FIM to Data Warehouse Binding. The schema for the FIM Reporting Store is based on data warehousing best practices including the utilization of traditional data warehousing schema. This schema differs significantly from the Entity-Attribute-Value schema used in the FIMService. The schema for the FIM Reporting Store is based on data warehousing design techniques with dimensions and facts. Because the schema’s are significantly different, the ability to map FIM objects to the Data warehouse objects needs to be addressed. The new reporting feature in Forefront Identity Manager 2010 R2 introduces the DataWarehouseBinding object to FIM. This object is used to define the mapping between the FIM Schema and the Data Warehouse schema and is represented by an xml file. Below is a copy of the Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Extensibility.xml file that binds our new dimension with FIM. The following will break this xml file down to help better explain it. For additional information on the Data Warehouse schema, see the FIM 2010 R2 Reporting Data Warehouse Binding section later in this document.

    <DataWarehouseBindingObject>
    <DisplayName>Test Computer</DisplayName>
    <DataWarehouseBindingIdentity>Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer</DataWarehouseBindingIdentity>
    <DataWarehouseMapping>
    <Mapping>
    <ClassBindings>
    <!--
     ComputerReportObject 
    --> 
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="HasPower"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectHasPower" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="ServiceDate"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectServiceTime" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="AssetTag"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectAssetTag" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="Description"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectDescription" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="DisplayName"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMDisplayName" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="MachineRole"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectMachineRole" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="CostCenter"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectCostCenter" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="Domain"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectDomain" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    <ClassBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="Custodian"/>
    <DataWarehouseClassProperty ClassTypeIdentity="FIMDW.FIMComputerReportObject" PropertyIdentity="FIMComputerReportObjectCustodian" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </ClassBinding>
    </ClassBindings>
    <RelationshipBindings>
    <!--
    ComputerReportObject
    --> 
    <RelationshipBinding>
    <SystemObjectAttribute ObjectTypeName="ComputerReportObject" AttributeName="Administrators"/>
    <DataWarehouseRelationshipProperty RelationshipTypeIdentity="FIMDW.FIMComputerReportObjectHasAdministrators" ManagementPackIdentity="Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer" ManagementPackVersion="1.0.0.0"/>
    </RelationshipBinding>
    </RelationshipBindings>
    </Mapping>
    </DataWarehouseMapping>
    </DataWarehouseBindingObject>
    
    1. Below, is an example of how we bind our DataWarehouseBindingObject (a single-valued attribute) to FIM with the following:

      • ObjectTypeName=”ComputerReportObject” – FIM Object type

      • AttributeName=”HasPower” – FIM Attribute Name

      • ClassTypeIdentity=”FIMDW.FIMComputerReportObject” – Data Warehouse class name

      • PropertyIdentity=”FIMComputerReportObjectHasPower” – Data Warehouse property name

      • ManagementPackIdentity=”Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer” - Name of the schema management pack that defines this class and properties.

      • ManagmentPackVersion=”1.0.0.1” – Version of the schema management pack that defines this class and properties.

      •   New Extensibility 10

    2. Now below we bind of the multi-valued reference attribute to FIM with the following:

      • ObjectTypeName=”ComputerReportObject” – FIM Object type

      • AttributeName=”Administrators” – FIM Attribute Name

      • RelationshipTypeIdentity=”FIMDW.FIMComputerReportObjectHasAdministrators” – Defines which relationship the FIM object type will map into

      • ManagementPackIdentity=”Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer” - Name of the schema management pack that defines this class and properties.

      • ManagmentPackVersion=”1.0.0.1” – Version of the schema management pack that defines this class and properties.

      •   New Extensibility 11

  3. Import into testing environment. At this point, two xml files have been created. Both are named Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer.xml. The first one, which was created in step one, is the schema file and the second one, created in step two is the binding file. Now that we have these files, we must import them into our environment. This is done with the use of the Import- FIMReportingSchemaDefinition PowerShell cmdlets. This can be done using the following procedure.

    To Run the Import- FIMReportingSchemaDefinition PowerShell Cmdlet

    1. Open Windows PowerShell.

    2. On the command line type the following and hit return Set-ExecutionPolicy unrestricted. This will bring up a message about the execution policy. Click Y.

    3. Type Add-PSSnapIn FimReportingAdministration and hit enter.

    4. Enter the following for the Import-FIMReportingSchemaDefinition cmdlet and hit enter. The following assumes our xml files are in directories called C:\ReportingExt\binding and C:\ReportingExt\schema.

      Import-FIMReportingSchemaDefinition -BindingXmlFile C:\ReportingExt\binding\Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer.xml -CompanyName "Contoso" -ManagementPackFile C:\ReportingExt\schema\Microsoft.Forefront.IdentityManager.Datawarehouse.Test.Computer.xml -ServiceManagerServer APP2 -Verbose -SealManagementPack -SealingKeyFile C:\ReportingExt\TestFimReportingKeyPair.snk
      
    5. Once this is finished, the synchronization job must be started to move the data from FIM into the data warehouse. The SCSM Data Warehouse Management Pack Synchronization Job instructs the Data Warehouse to look for any new Management Packs that have been added, create any new schema, and deploy any reports defined within them. Use the following procedure to start the Data Warehouse Management Pack Synchronization.

      To run the Data Warehouse Management Pack Synchronization Job

      1. On the command line type the following and hit return Add-PSSnapIn SMCmdletSnapIn. This will load the SCSM PowerShell Cmdlets into your current PowerShell session.

      2. On the command line type the following and hit return Start-SCDWJob –JobName MPSyncJob –ComputerName APP3. This will begin the synchronization job. This may take up to 5 minutes to complete.

      3. Now check the status of the MPSyncJob. On the command line type the following and hit return Get-SCDWJob –JobName MPSyncJob –ComputerName APP3. This will return information about the job. Note the Status.

        Get MPSync

      4. You need to wait until this job completes before moving to the next part. If the status says running check it again in a minute or so. Once the status says Not Started move on to the next part.

    6. Once this has finished, we will synchronize the FIM data with the Data Warehouse by running the Start-FIMReportingInitialSync. This will extract our new baseline data from FIM and moves it over to the System Center staging tables. To run Start-FIMReportingInitialSync use the following procedure.

      To Run Start-FIMReportingInitialSync PowerShell Cmdlet

      1. On the command line type the following and hit return Start-FIMReportingInitialSync.

        FIMReportingInitialSync

        Important

        If the script is not in the users PATH, you can access it by navigating to C:\Program Files\Microsoft Forefront Identity Manager\2010\Reporting\PowerShell

      2. To check the status of our job, go back into the FIM Portal.

      3. On the left, at the bottom click Administration.

      4. On the Administration page click All Resources.

      5. On the All Resources page, scroll down and double-click Reporting Job. On the Reporting Job screen you will see our job with a Reporting Job Type of Initial.

      6. Double-click on Reporting Job under Display Name. This will bring up the attributes of this job. At the top click the Extended Attributes tab.

      7. On the Extended Attributes, note the Reporting Job Status. If this says Running then wait. Otherwise, if it says Completed close the Reporting Job attributes.

        Check Initial Status

      8. Minimize Internet Explorer.

    7. Once this has finished, we will do an incremental synchronization to move FIM data with the Data Warehouse by running the Start-FIMReportingIncrementalSync. This will get the data from the ExportLog table and move this to the data warehouse. To run Start-FIMReportingIncrementalSync use the following procedure.

      To Run Start-FIMReportingIncrementalSync PowerShell Cmdlet

      1. Back in Windows PowerShell, on the command line type the following and hit return Start-FIMReportingIncrementalSync. . It will briefly flash an Importing change 1 message.

        Start-FIMReportingIncrementalSync

      2. To check the status of our job, go back into the FIM Portal.

      3. On the left, at the bottom click Administration.

      4. On the Administration page click All Resources.

      5. On the All Resources page, scroll down and double-click Reporting Job. On the Reporting Job screen you will see our job with a Reporting Job Type of Incremental.

        Reporting Jobs

      6. Double-click on Reporting Job under Display Name. This will bring up the attributes of this job. At the top click the Extended Attributes tab.

      7. On the Extended Attributes, note the Reporting Job Status. If this says Running then wait. Otherwise, if it says Completed close the Reporting Job attributes. You will have to close and re-open the job in the FIM Portal to see the status change. It will not change automatically.

        Incremental Status

      8. Minimize Internet Explorer.

    8. After the synchronization jobs are completed the ETL process can be kicked off. The Export, Transform, Load – (ETL) process that runs on the SCSM server takes that data that was exported during the processes above and loads it into the Data Warehouse. This process runs at a specific interval on the SCSM Data Warehouse. This process can be kicked off by using a script. See the FIM 2010 R2 Reporting ETL Process section for a copy of the script. To use the script to kick-off the ETL process use the following procedure.

      To Create and Run ETLScript PowerShell Script

      1. On the PowerShell command line type the following and hit return Add-PSSnapin SMCmdletSnapIn.

      2. On the command line type the following and hit return [environment]::SetEnvironmentVariable(“IMT.DataWarehouse”, “APP3”).

        Warning

        Change APP3 to the name of your data warehouse server.

      3. On the command line type the following and hit return C:\ETL\ETLScript.ps1.

        ETLScript

        Warning

        This will take a while to run. It will take about 30-35 minutes and information will populate the PowerShell window.

  4. Create a report in SQL. Now that the new schema has been created, it has been bound to FIM, and the data from FIM has been brought over into the data warehouse, reports can be created. Creating a SQL report is outside the scope of this document, but this step is provided for informational purposes.

  5. Create a new report Management Pack. Once you have a report created you can now create a new report Management Pack. Creating report management packs is outside the scope of this document. For information on management pack authoring see the System Center Service Manager 2010 SP1 Authoring Guide.

See Also

Other Resources

System Center Service Manager Team Blog