SCSM–Using PowerShell to Create DW Outriggers and Dimensions

image

Update (Dec,3rd 2015): Found a noob/doingthislateatnight bug in the script. Fixed it. Now the option to create dimension or not works. Nothing like eating your own dog food…

Certain technologies have such complexity when we first look at them that, as Carl Sagan said before, they are undistinguishable from magic. However, as history has proven, if you spend time to study and understand, these phenomena won’t be so mystifying anymore. For me, although working wit SCSM for a while, send custom information to the Data Warehouse was always a point I would avoid as much as I could. However, often enough, there is no way around it.

So, I have decided to face the challenge and not only produce one-offs. I wanted something I could use multiple times.

With that said, I’m not covering all possibilities here. Service Manager has endless opportunities for expansion, which won’t always be easy to implement. Here’s the scenario I wanted to have ready, since it happens often enough in SCSM Implementations:

– Customer needs a new field in a form, typically Incidents or Service Request. Something like a list of customers, internal departments,etc.

– You use the Authoring Console to create the new fields and lists

– Once the new (sealed) MP is imported, you can see text fields in the warehouse DB, but all the lists (enumerations) contain GUIDs.

To fix this issue, Service Manager requires you to create what is called an Outrigger, very well described here. It is however a dry subject and it takes a while to really take off.

Let’s look at an example:

Here’s my new class property, on top of incidents. I actually have a couple more, but I’m focusing on the Clients one:

image

If you look at the database (data warehouse):

image

Notice Clients is there, but:

image

Not too helpful. Enters the outrigger:

image

It is actually simpler than it looks, but can be confusing. The key part is the line:

<Attribute ID=”Clients” PropertyPath=”$Context/Property[Type=’ref1!ClassExtension_ced5b84f_54a9_4ff5_b681_0d071e879d94′]/Clients$” />

To compose that line, you need to make sure you have a reference (call ref1 here) to the MP that you used to extend the class.

image

You need to know the PublicKeyToken as well to write this manually.

You also need to know the name of the extension of the (incident) class from your original MP:

image

Fortunately, you can get all this information using PowerShell and that’s exactly what I did in my script.

The script also allows you to generate a Dimension, which is some sort of new compose class in the DW. There is a flag in the beginning of the script.

Once generated, the script will also try to seal the MP according to the configuration in the header of the script:

image

Once you run it, it will prompt you for the class you need to create the outrigger from:

image

It looks a bit messy, but PowerShell gridview allows you to quickly search, so, note the Identifier on the right:

image

Once you select and click ok, you’ll be prompted for the field or fields you want to add as outriggers:

image

Again, a bit messy to look, but effective. Smile

And there you have it.

Now you have a new table in the database (DWDatamart):

image

That you can relate by the EnumTypeId field:

image

And you can use for parameters in Reporting Services. Neat, huh?

You can find the script here!

 

Points for improvement – there are a lot more things that can be done, including facts tables, for relationships, more options, error control and Cube data (next version), but this is version 1.0 and I think it will help with a common/simple task.

A few tips: once you import your new class, make sure you synchronize the DW once before importing the Outrigger extensions. You can use this script from Travis Wright to speed up the process. Also make sure you run it on a Service Manager Posh window, as administrator. If fastseal fails for some reason, you HAVE to fix it in order to seal the MP. You can’t use unsealed MPs in the Data Warehouse.

 

Hope this helps!