The System Center Platform in Service Manager Part 6: The Data Warehouse – Try It!

This post is a continuation in the series which describes the System Center common platform components implemented in Service Manager. Previous posts:


The System Center Platform in Service Manager Part 1: Introduction


The System Center Platform in Service Manager Part 2: The Model-Based Database


The System Center Platform in Service Manager Part 2: The Model-Based Database – Try It!


The System Center Platform in Service Manager Part 3: The System Center Data Access Service


The System Center Platform in Service Manager Part 3: The System Center Data Access Service – Try It!


The System Center Platform in Service Manager Part 4: The System Center Management Service


The System Center Platform in Service Manager Part 4: The System Center Management Service – Try It!


The System Center Platform in Service Manager Part 5: The Management Configuration Service


The System Center Platform in Service Manager Part 6: The Data Warehouse


In the last post in this series, we learned about the Data Warehouse and Reporting Infrastructure.  In this post, I’ll show you how you can extend the data warehouse schema to store additional data we don’t store out of the box.  Then I’ll show you the quick and dirty way to write a report over that data.


For most people (including myself when I first started this project) the data warehousing terminology can absolutely sound like a foreign language.  If you are going to be working with the Service Manager data warehouse I strongly recommend that you become intimately familiar with these concepts:



Out of the box, we don’t provide a dimension or facts for installed software in Service Manager 2010.  It’s easy enough to add those and to create a report for it so I’ll show you how in this post.  Remember the purpose of this series of blog posts on the System Center common platform is just to give you an overview of each of the pieces of the architecture and a quick example you can try on your own to get some hands on experience.  There is much more that you can do with the data warehouse and reporting than what I will show in this post.


First, let’s start by creating a new management pack. Remember to save your MP so that the file name is the same as the ID of the management pack.  Here is the Manifest section. You can see that we need to make a reference to the system MP and the Data Warehouse base management packs. We also need to take a dependency on the Software Library MP because that MP contains the ClassType definition of the Software Item class we are going to create dimensions and facts for. We also need a reference to the Windows Library MP because the relationship fact we are going to create is going to store which software items are installed on each computer.


Please keep in mind that this MP will only work with Service Manager Beta 2 (recently released) or later.


<ManagementPack xmlns:xsd=http://www.w3.org/2001/XMLSchema xmlns:xsl=http://www.w3.org/1999/XSL/Transform ContentReadable=true SchemaVersion=1.1 OriginalSchemaVersion=1.1>


  <Manifest>


    <Identity>


      <ID>Microsoft.Demo.DataWarehouse.Software</ID>


      <Version>7.0.5229.0</Version>


    </Identity>


    <Name>DEMO – Data Warehouse Software Extensions</Name>


    <References>


      <Reference Alias=System>


        <ID>System.Library</ID>


        <Version>7.0.5229.0</Version>


        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>


      </Reference>


      <Reference Alias=SoftwareLibrary>


        <ID>System.Software.Library</ID>


        <Version>7.0.5229.0</Version>


        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>


      </Reference>


      <Reference Alias=DWBase>


        <ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>


        <Version>7.0.5229.0</Version>


        <PublicKeyToken>31bf3856ad364e35</PublicKeyToken>


      </Reference>


      <Reference Alias=Windows>


        <ID>Microsoft.Windows.Library</ID>


        <Version>7.0.5217.0</Version>


        <PublicKeyToken>9396306c2be7fcc4</PublicKeyToken>


      </Reference>


    </References>


  </Manifest>


Immediately after the Manifest section we are going to start declaring data warehouse elements. First we start with the Warehouse element which contains all of the other elements related to the data warehouse.


  <Warehouse>


Inside of there the first thing we are going to do is declare an Outrigger for the Publisher Name property of the Software Item class. This will essentially create another table in the data warehouse database where each unique value in the Publisher Name property of Software Item will be stored (i.e. Microsoft, Adobe, Google, etc.) We can later use this table to drive a drop down on the report parameter header so the user can choose to only show software items from specific publishers.


    <Outriggers>


      <Outrigger ID=Publisher Accessibility=Public>


        <Attribute ID=PublisherName PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/Publisher$ />


      </Outrigger>


    </Outriggers>


Next, we’ll declare a Dimension for the Software Item class:


    <Dimensions>


      <Dimension ID=SoftwareItemDim


Accessibility=Public


InferredDimension=true Target=SoftwareLibrary!System.SoftwareItem HierarchySupport=Exact


Reconcile=false>


<InclusionAttribute   ID=IsVirtualApplication PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/IsVirtualApplication$


SlowlyChangingAttribute=false />


<InclusionAttribute   ID=LocaleID PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/LocaleID$ SlowlyChangingAttribute=false />


<InclusionAttribute   ID=MajorVersion PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/MajorVersion$ SlowlyChangingAttribute=false />


<InclusionAttribute   ID=MinorVersion PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/MinorVersion$ SlowlyChangingAttribute=false />


<InclusionAttribute   ID=ProductName PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/ProductName$ SlowlyChangingAttribute=false />


<InclusionAttribute   ID=Publisher PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/Publisher$ SlowlyChangingAttribute=false />


<InclusionAttribute   ID=VersionString PropertyPath=$Context/Property[Type=’SoftwareLibrary!System.SoftwareItem’]/VersionString$ SlowlyChangingAttribute=false />


      </Dimension>     


    </Dimensions>


You can see that this maps exactly to the ClassType definition of Software Item in the System.Software.Library MP:


Note: don’t include ClassType definition this in your MP! This is just to show you what the ClassType definition looks like (in the System.Software.Library MP) that you are referencing from your dimension.


        <ClassType ID=System.SoftwareItem Accessibility=Public Base=System!System.LogicalEntity>


          <Property ID=IsVirtualApplication Key=true Type=bool />


          <Property ID=LocaleID Key=true Type=int />


          <Property ID=MajorVersion Type=string />


          <Property ID=MinorVersion Type=string />


          <Property ID=ProductName Key=true Type=string />


          <Property ID=Publisher Key=true Type=string />


          <Property ID=VersionString Key=true Type=string />


        </ClassType>


Next we want to declare the Relationship Fact about the relationship of software being installed on Windows Computers:


    <Facts>


      <RelationshipFact       ID=ComputerHasSoftwareItemInstalled Accessibility=Public Domain=DWBase!Domain.ConfigurationManagement TimeGrain=Daily SourceType=Windows!Microsoft.Windows.Computer SourceDimension=DWBase!ComputerDim>


        <Relationships RelationshipType=SoftwareLibrary!System.DeviceHasSoftwareItemInstalled TargetDimension=SoftwareItemDim />


      </RelationshipFact>


    </Facts>


  </Warehouse>


I’ts important to note here that the relationship type is defined as between SoftwareItem and Device (again this is from the System.Software.Library MP not the MP you are creating):


        <RelationshipType ID=System.DeviceHasSoftwareItemInstalled Base=System!System.Reference Accessibility=Public>


          <Source ID=Device Type=System!System.Device />


          <Target ID=SoftwareItem Type=System.SoftwareItem />


        </RelationshipType>


Because Microsoft.Windows.Computer inherits from System.Device and a dimension is already defined for Microsoft.Windows.Computer we can just point to that dimension (SourceDimension=”DWBase!ComputerDim” above) instead of creating a new Device dimension.


Now we just need to include the usual LanguagePack section and we are done!


<LanguagePacks>


    <LanguagePack ID=ENU IsDefault=true>


      <DisplayStrings>


        <DisplayString ElementID=Microsoft.Demo.DataWarehouse.Software>


          <Name>DEMO – Data Warehouse Software Extensions</Name>


          <Description>This management pack adds a Software dimension and a Software installed on Computer fact for demo purposes.  Provided by Microsoft.  No warranty expressed or implied.</Description>


        </DisplayString>


        <DisplayString ElementID=Publisher>


          <Name>Publisher</Name>


          <Description>A list of all unique publishers of software.</Description>


        </DisplayString>


        <DisplayString ElementID=SoftwareItemDim>


          <Name>Software Item Dimension</Name>


          <Description>All software items.</Description>


        </DisplayString>


        <DisplayString ElementID=ComputerHasSoftwareItemInstalled>


          <Name>Computer Has Software Item Installed Fact</Name>


          <Description>All software items installed on computers.</Description>


        </DisplayString>


      </DisplayStrings>


    </LanguagePack>


  </LanguagePacks>


</ManagementPack>


Note: The complete management pack is attached to this post. 


Now, what you want to do is import this into Service Manager either using the UI or the Import-SCSMManagementPack PowerShell cmdlet. After the MP Sync process kicks off the Management Pack will by synchronized automatically to the data warehouse.


Following that the Deployment process will kick in and automtically create the schema for you in the DWRepository and DWDataMart databases.


In the end, you should see tables like this in both the DWRepository and DWDataMart databases:


Publisher Outrigger:


clip_image002


SoftwareItemDim Dimension:


clip_image004


ComputerHasSoftwareItemInstalled Fact:


clip_image006


There are also database views that are created automatically:


clip_image008


clip_image010


clip_image012


OK, now we are ready to create a report! For this example, I’ll show you how to use the SQL Server Business Intelligence Design Studio (“BIDS”) to create the report. This is a feature of SQL Server that can be installed using the SQL Server installation setup.exe. Please re-run setup and add it if you did not add it when you first installed SQL Server. You can install this either on your server or on a remote location like your desktop as long as you have network connectivity and permissions to publish the reports to the SQL Server Reporting Services server.


After you launch BIDS, you need to create a new project:


clip_image014


Provide a name for your project like ‘SoftwareReport’ and click OK:


clip_image016


In the Solution Explorer right click on ‘Shared Data Sources’ and choose ‘Add New Data Source’:


clip_image018


Provide a name for your Data Source like ‘DWDataMart’ ….


clip_image020


… and then click the Edit button to get to this dialog where you can enter your server name and select the DWDataMart database from the drop down.


clip_image022


Make sure you click the Test Connection to verify connectivity! Then click the OK button on the Connection Properties dialog and then on the Shared Data Source Properties dialog.


Now you should have a data source shown in the tree in the Solution Explorer:


clip_image024


Now, right click on the Reports folder in the Solution Explorer and choose ‘Add New Report’


clip_image026


Click Next on the Report Wizard welcome screen:


clip_image028


Make sure the new data source you just created (‘DWDataMart’) is selected and click Next


clip_image030


Click the Query Builder button:


clip_image032


Click the Add Table button in the toolbar:


clip_image034


In the Add Table dialog, select the Views tab and select the ‘SoftwareItemDimvw’ view and click Add and then click Close:


clip_image036


Select the properties you care about showing in the report:


clip_image038


And then click OK.


clip_image040


Now that you are back in the Report Wizard, click Next on this ‘Design the Query’ dialog:


Select Tabular and Click Next:


clip_image042


Add the fields to the report something like this:


clip_image044


Leave this screen as is and click Next:


clip_image046


Choose a style:


clip_image048


Give your report a name like ‘Software Items’ and click Finish:


clip_image050


Now you should have your report in the Solution Explorer:


clip_image052


And in the middle you should see the report design area:


clip_image054


You can play around with column widths, colors, and all kinds of other formatting options here to make it look how you would like, but for now let’s just go ahead and publish this report as is.


Right click on the SoftwareReport project node in the Solution Explorer and choose Properties:


clip_image056


In the Properties enter the path to the SQL Server Reporting Services reportserver URL and click OK.


clip_image058


Choose Build –> Deploy Software Report from the menu:


clip_image060


Now you should be able to view your report in the browser or the Service Manager console. To view in the browser navigate to the URL of your SQL Reporting Services server and click on the SoftwareReport folder:


clip_image062


And then click on the Software Items link:


clip_image064


And you’ll see a report like this:


clip_image066


In order to see this report in the Service Manager console you need to move this report folder to the System Center\Service Manager folder. You can do that by going back to the Home, and then clicking the Show Details button on the right. Then you’ll see all the folders in a list:


clip_image068


Select the SoftwareReport folder’s checkbox and then click the Move button on the toolbar. Then select the /System Center/Service Manager folder in the tree and click OK:


clip_image070


Now if you launch the Service Manager console you should see this report folder and its report:


clip_image072


And you can get the same report by running the report from there:


clip_image074


In future blog posts, we’ll describe some of the fancier things you can do to extend the data warehouse and create reports.


 


 


 


 


 

Microsoft.Demo.DataWarehouse.Software.xml