Extending the Data Warehouse and Reporting for Unix Computers

System-Center-Team · ‎Feb 15 2019

First published on TECHNET on Jul 15, 2013

Setting Things Up

One of the common asks I have been hearing lately is for information on how to extend the data warehouse and reporting, most often to be able to report on Unix computers including custom properties. In this blog post, I will walk through the process of extending the data warehouse to be able to report on Unix computers. I’ll also show you how to create some custom reports using either Report Builder or the SQL Data Tools.

To begin with, we need to import the relevant MPs into SCSM to extend the data model. This is the MP that I imported for purposes of this blog post: Microsoft.Unix.Library (version: 7.4.3507.0 or higher). This is a free management pack provided by Microsoft that contains the following classes:

The main class that we are going to work with here is the Microsoft.Unix.Computer class. This class derives from System.Computer > System.Device > System.LogicalDevice > System.ConfigItem > System.Entity. Also, notice how it is an Abstract=False class. That means there can be objects of this class. The SUSE, RedHat, etc. Unix classes derive from this base class and are contained in other MPs. The discoveries for each distro of Unix computer are contained in the distro-specific MP so if you want Operations Manager to discover Unix computers for you, you need to have the distro-specific MPs imported as well. If you want those discovered objects to flow from Operations Manager to Service Manager automatically via the CI connector you need to import those distro-specific MPs into SCSM as well. You can learn more about this as well as the add-on MP for viewing Unix MPs from this blog post .

Extending the Data Model

OK, now lets extend the Microsoft.Unix.Computer class with some custom properties. You can use the SCSM Authoring Tool to do this or you can create an MP XML file and edit MP XML directly. I won’t spend any time here talking about how to use the Authoring Tool because it has been extensively covered before and is documented on TechNet . In this case, I added the following properties:

One thing to point out here especially is the IT_Vertical property which is an enum (or Enumeration) data type property. Enum data type properties are special when it comes to the data warehouse and reporting as we will see later. Notice how the authoring tool automatically created the root EnumerationValue element for the IT_Vertical enum property. All of the enumerations (or List items as they are called in the console) will be child enumerations of that root enumeration.

Now we are ready to seal the management pack XML file to convert it to a binary format as a .mp file. You can either use the Authoring Tool or the MPSeal.exe (read this if you don’t know about sealing management packs yet) command line tool. Once we have sealed the management pack we can import it into Service Manager.

Extending the User Experience

Once the Microsoft.Unix.Library MP above is imported into Service Manager, we can create a new folder and view for the Unix Computers. I created a new unsealed MP to store them in. First, I just created a folder by right clicking on the root ‘Configuration Items’ node in the Configuration Items workspace navigation pane and choosing ‘Create Folder’. When prompted, I chose to create a new MP. To create the view, I right clicked on the Unix Computers folder I just created and chose ‘Create View’. In the Create View dialog, I chose the ‘Unix/Linux Computer’ class and some properties.

Creating Enumerations

Next, let’s create some IT Vertical enumerations (otherwise known as list items) by going to the Library workspace and to the Lists view. Just double click on the List (in my case ‘IT Vertical – Unix) to open up the list editor dialog. After clicking the Add Item button a couple of times and specifying the display name for each item, I now have created two enumerations:

Creating Objects

At this point, we can now go back to the All Unix Computers view in the Configuration Items workspace and click ‘Create Unix/Linux Computer’ in the task pane. That will bring up the generic form which reflects over the data model and displays the appropriate controls on the form depending on the data type of each property. That includes the extended class properties towards the bottom of the form. You can see in the screenshot below that the enumeration values we just created are showing up in the IT Vertical drop down.

After creating a couple of computers, they start to show up in the view.

What You Get for Free with the ConfigItemDim

OK… now for the data warehouse part of this. Just with what we have done so far we could start reporting on Unix computers. That’s because the Microsoft.Unix.Computer class derives from System.ConfigItem. Therefore, all Unix computers will be included in the ConfigItemDim dimension in the data warehouse. If we wait awhile (about an hour) after creating the Unix computers in the Service Manager database they will make their way through the extract, transform, load (ETL) process to the DWDataMart DB. The next time that cube processing runs (1x/day by default) the Unix computers will be included in the ConfigItem cube and other cubes that include configuration items like the Service Catalog and WorkItems cubes. For example, if I queried the ConfigItemDimVw view in the DWDataMart I would see the Unix computers we just created:

Note: Notice how I always query the views not the tables.

However…

The ConfigItemDim dimension only has the properties of the System.ConfigItem class on it such as DisplayName, AssetStatus, ObjectStatus:

It does not include properties of the Microsoft.Unix.Computer class or our custom extended class properties. In order to get that additional data over to the data warehouse we need to create some additional management pack magic. Before we go there though, let’s just take a quick look at how the Unix computers show up in the ConfigItem cube in an Excel pivot table:

Very important note: The Unix computers will not show up in the ComputerDim dimension because the ComputerDim dimension is targeted at the Microsoft. Windows .Computer class. This is unfortunately a bad name choice. We should have called it the WindodwsComputerDim dimension. There is no way to extend the out of the box ComputerDim to include the Unix computers.

Now let’s get on with creating some custom MPs to extend the data warehouse!

Creating a Dimension

First, we’ll create an MP which defines a UnixComputerDim dimension. Creating data warehouse MPs can only be done by editing XML directly. I’ve found that the easiest way to create an MP XML file is to just start with a similar MP and start editing. For example, I’ll provide the MPs related to this blog post on the TechNet Gallery (link at the end of the post). The other thing you can do to help is to use the .xsd file that is available in the Job Aids Package. If you are using Visual Studio you can relate the .xsd file to the .xml file as a schema file. Then Visual Studio will provide real time Intellisense and schema validation.

I’m not going to explain all the details about all the concepts here. For details on all the concepts such as dimensions, relationship facts, outriggers, etc. and details about what each of the MP XML element and attributes are please see the Microsoft Virtual Academy presentation I did on this topic.

To create a dimension we need to do three things:

1) Create an MP reference to the Microsoft.Unix.Library MP where the Microsoft.Unix.Computer class is located.

<Reference Alias="Unix">
<ID>Microsoft.Unix.Library</ID>
<Version>7.4.1231.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>

2) Create the dimension itself.

Because we’ll need it later, I’m also creating a dimension for the Microsoft.Unix.OperatingSystem class.

3) Create a display name for the dimension(s).

<DisplayString ElementID="UnixComputerDim">
<Name>Unix Computer Dimension</Name>
</DisplayString>

Once we complete the XML editing, we need to seal the MP and import it into Service Manager. You can either wait for the MPSyncJob to start on schedule or you can start it on demand using the console or the PowerShell cmdlets. Once the MP has been sync’d over to the data warehouse the deployment job will immediately deploy the new schema necessary to the DWRepository and DWDataMart DBs in order to create the dimension. From that point on, the ETL job will sync the Unix computers into the UnixComputerDim dimension.

Notice how there is an additional row in the results where the BaseManagedEntityId is 0000….. It’s necessary for us to create that row in every dimension table that we create. It will show up in query results when you author reports or in the cubes when you are analyzing them. You need to exclude this row when you are doing reports. I’ll show you how later.

OK, now we have a dimension. The dimension includes all the properties of the Microsoft.Unix.Computer class including our extended properties (IT_Vertical, DCLocation, RackwisePosition, etc.) because of how we defined the Dimension (IncludeExtendedClassProperties).

Notice in the query results table below how string properties are represented as columns in the dimension and the string values are the values in that column for each of the rows. Makes sense. This is true of all property data types except enum data types. Those are tricky.

Notice how you might expect to see the values ‘Vertical 1 – Unix’ or ‘Vertical 2 – Unix’ here (see console screenshots above) in the IT_Vertical column. Instead we see some ‘Enum.6caae….’ and ‘Enum.47c11…’ values. Where did those come from? Those values are the ID/Names of the MP EnumerationValue elements that were generated by the console when we created them earlier. If we were to export out the MP that contains these enumerations this is what we would see:

OK, so where did the display strings go? The display strings for all MP elements including enumerations go on the DisplayStringDim dimension.

We’ll learn next how to get the display strings to show up in repots instead of the ‘Enum…’ IDs by joining the UnixComputerDimVw and the DisplayStringDimVw views.

Creating a Report

Now, let’s start creating a SQL Reporting Services report to list Unix computers that match certain criteria such as IT_Vertical or DisplayName. You can do this either using Report Builder (this blog post) or the SQL Server Data Tools that can be installed with SQL Server. The user experience is practically identical.

To begin – navigate to your SQL Reporting Services browser UI (typically something like http://<reporting server>/Reports) and click on Report Builder:

That will launch a Click Once app. Click on the Run button in the dialog that pops up.

At this dialog, choose Table or Matrix Wizard

At this prompt choose ‘Create a Data Set’ and click Next:

On this screen click New to create a new connection to a SQL DB:

On this screen give your connection a name and click the Build button.

On this screen enter the SQL Server name where your DWDataMart DB is located and then select the DWDataMart DB from the drop down. Then click OK and OK to get back to the wizard and click Next.

On the next screen we are going to design our query. If all we had to do is display columns of properties that are not enumeration data type properties we could just select them from the list under the UnixComputerDimVw view and move on through the wizard to finish things up.

Remember though that if we choose the IT_Vertical column here it will display the values ‘Enum…’ instead of the Display Name of the enumeration (like ‘IT Vertical 1 – Unix’). So – we need to do something a little smarter here in the query designer. Click the ‘Edit as Text’ button so that we can enter the SQL Query as text instead of clicking off the checkboxes.

Enter the following query and then click the exclamation point button to test the query.

This is how we join the UnixComputerDimvw to the DisplayStringDimvw to display the display name of the enumerations instead of the IDs. Notice how I specified which language to use in the report. Add other columns from the UnixComputerDimvw view if you want. Click Next.

Drag the available fields to the Values area and click Next.

Click Next on the Choose Layout screen and click Finish on the Choose a Style screen.

Give your report a title and resize the columns:

Click the Run button to test it.

Sweet! Now we just need to publish it. Click the Save button in the upper left. Create a folder (if you want) and save the report.

Note: If you create the report in a folder under the Service Manager folder the report will be available via the Service Manager console Reporting workspace.

That’s it! Now let’s get a little fancier and add some parameters to our report.

Let’s say we want to add a parameter that will let the report user choose an IT_Vertical and/or specify all or part of a computer name.

In Report Builder , click the Design button to go back into Design mode if needed.

Let’s do the computer name parameter first since it is easiest. Right click the Parameters node in the tree on the left and choose Add Parameter.

Give the parameter a name and a user prompt and then click OK.

Expand Datasets and right click DataSet1 and choose Properties. Rename it to UnixComputers.

Click the Parameters tab. Click the Add button. Enter a name for the Parameter like ‘ComputerName’ and select [@ComputerName] from the drop down. This is how we map the value provided by the user (if any) into the data set query.

Click the Filters tab. Click the Add button. Select [DisplayName] from the Expression drop down. Leave the = operator in the Operator drop down.

Click the Function (fx) button. Click Parameters. Double click the ComputerName in the Parameters column. Click OK. Click OK.

Now when you run the report it will expose the new parameter and you can use it to look up a specific computer by name:

Now if we want to add a drop down where the user can select the IT Vertical(s) where are we going to get the data to populate that drop down?? That’s one reason why we would want to create what is called an ‘outrigger’.

Creating an Outrigger

An outrigger in the context of the Service Manager data warehouse is just a way to declare a table to store enumeration values on. For example, out of the box we ship outriggers for enumeration values (lists) for things like incident impact, urgency, classification, and so on. IT Vertical in this scenario is another enum data type property for which we want to create an outrigger.

Creating an outrigger is a really easy process similar to creating a dimension. Three steps:

1) Declare a management pack reference that points to the management pack that contains the class. property for which you want to create an outrigger. In this case, the property is the extended class property called ‘IT_Vertical’ we defined at the beginning of this process.

<Reference Alias="RHUCI">
<ID>RHI.UNIXComputer.CI.ClassExtension</ID>
<Version>1.0.0.2</Version>
<PublicKeyToken>9f73c350be1db249</PublicKeyToken>
</Reference>

2) Declare the outrigger:

Now, we just need to seal the management pack and import it. After MP Sync runs a new table will be created in the DWDataMart DB:

As you can see the outrigger table will contain the English display string by default on the table itself. The English display string and all other languages (if they are defined) are stored on the DisplayStringDim table.

Now we are ready to use the outrigger table to create the IT Vertical parameter in the report.

To do that, we first need to create a new data set that will query the IT Verticals from the new outrigger table. Right click on the Datasets node in the Report Builder tree and choose ‘Add Dataset’. In the Dataset Properties dialog give the new dataset a name like ‘ITVerticals’ and choose the ‘Use a dataset embedded in my report’ option. Select the same data source as the UnixComputers dataset. Then enter a query similar to the following:

If you just need the English display strings you can just use a simple SELECT ID, ITVerticalUnixValue FROM ITVerticalUnixvw query. The ITVerticalUnixID ensures that we don’t display the 0000-00… row that is automatically created during the table creation process (because that row has an ID of 0) or the root enumeration row (ID = 1).

Click OK.

Now, as before, right click on Parameters and choose Add Parameter. In the Parameter Properties dialog give the parameter a name like ‘ITVerticalUnix’. Provide a prompt such as ‘Select an IT vertical:’. Select ‘Allow multiple values’.

On the Available Values tab and choose ‘Get values from query’. Select ‘ITVertical's’ from the Dataset drop down, ‘ID’ (this is the MP enumeration ID) from the value field drop down, and ‘DisplayName’ from the Label field dropdown. Then click OK.

Lastly, we need to modify the UnixComputers dataset query so that it takes the ITVerticalUnix parameter. Right click on the UnixCompuers dataset in the tree and choose ‘Query…’. Then add the last line in the screenshot below.

Save the report and then run it.

Sweet! Now, on to the final phase: cube authoring!

Creating a Relationship Fact

In order to create a cube for Unix Computers, we need to define a relationship fact between the Microsoft.Unix.Computer class and the Microsoft.Unix.OperatingSystem class based on the Microsoft.Unix.ComputerHostsOperatingSystem relationship type.

Creating a relationship fact is very similar to creating a dimension or an outrigger. Two easy steps:

1) Create a management pack reference(s) to the management pack(s) that contain the source class of the relationship type, the relationship type, the source class dimension, and the Microsoft.SystemCenter.Datawarehouse.Base management pack.

<Reference Alias="DWBase">
<ID>Microsoft.SystemCenter.Datawarehouse.Base</ID>
<Version>7.5.2905.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="Unix">
<ID>Microsoft.Unix.Library</ID>
<Version>7.4.1231.0</Version>
<PublicKeyToken>31bf3856ad364e35</PublicKeyToken>
</Reference>
<Reference Alias="UnixDim">
<ID>RHI.UnixComputer.DWExtensions.Dim</ID>
<Version>1.0.0.0</Version>
<PublicKeyToken>9f73c350be1db249</PublicKeyToken>
</Reference>

2) Create the relationship fact

3) Create a display string

<DisplayString ElementID="UnixComputerHostsOperatingSystem">
<Name>Unix Computer Hosts Operating System Fact</Name>
<Description></Description>
</DisplayString>

Now, we seal the MP and import it. MP Sync job runs. And now we have a RelationshipFact table in the DWDataMart DB.

Now, you could use this additional relationship fact table via the UnixComputerHostsOperatingSystemvw view in your report if you wanted to. For purposes of this blog post though we’ll skip that and go straight to creating a cube based on this relationship fact.

Creating a Cube

Creating a cube is pretty similar to creating a dimension, relationship fact or an outrigger. It’s just a bit of XML.

1) First we need to create an MP reference to the MP that contains the relationship fact we are going to create a measure group from.

<Reference Alias="UnixRelFact">
<ID>RHI.UnixComputer.DWExtensions.RelationshipFact</ID>
<Version>1.0.0.0</Version>
<PublicKeyToken>9f73c350be1db249</PublicKeyToken>
</Reference>

2) Next we need to declare the cube:

3) Create a display string:

<DisplayString ElementID="UnixComputerCube2">
<Name>Unix Computer Cube 2</Name>
<Description>Unix Computer Cube 2</Description>
</DisplayString>

Now we just seal the MP. Import it. MP Sync job runs. The cube is created. Then we need to process it by selecting the cube in the Cubes view and clicking the Process Now link in the details pane.

Now the cube can be analyzed in Excel. We can see that just by including the one measure group that Service Manager intelligently picks up all the outriggers like Asset Status, Object Status, IT Vertical and the measures associated with the dimensions that are related to the relationship fact.

If I add in the UnixComputerDimCount measure and the ITVerticalUnixValue outrigger value I can slice the Unix computers by IT Vertical and it will automatically use the display string from the appropriate language. This is another reason to create outriggers for enumeration data type properties.

Notice how it shows 1 computer with a blank IT Vertical. This is the effect in a cube of the 0000-000… that is created in each dimension or outrigger table when we create the table. We can filter these out of the report by simply pulling down the down arrow next to the column header and unchecking the blank (the 0000-000…) and the IT Verticals – Unix (represents the root of the enumeration hierarchy).

Perfect!

Conclusion

Hopefully, this has been a useful example of how to extend the data warehouse to store custom data model class properties, create dimensions, outriggers, relationship facts, and cubes as well as learn some techniques for creating custom reports. You can find all the source files on the TechNet Gallery. This isn’t really intended to be a canned solution for everybody. It’s just an example that you can use as a starting point and for learning how to do this kind of thing.

http://gallery.technet.microsoft.com/Service-Manager-SCSM-Data-4d16a687

Lastly, please check out the TechNet wiki page for data warehouse and reporting to dive into more details.

http://social.technet.microsoft.com/wiki/contents/articles/15608.system-center-service-manager-...

Products (49)

Special Topics (28)

Video Hub (462)

Most Active Hubs