Good afternoon, readers! Gabriel Taylor here with a post about an interesting issue I’ve ran into with customizations to Service Manager’s data warehouse. Read on for more!

Anyone who has spent time working with the Service Manager Data Warehouse knows that the current version can be difficult to keep stable, especially when custom data warehouse extensions, such as outriggers and relationship facts, start entering the picture. If you’ve already spent some time working with outriggers and ran into issues, then hopefully this post will be of use to you. If you’ve not yet gotten your feet wet, perhaps this information can help you avoid issues down the line.

Review – What is an Outrigger?

For those unaware, in regard to Service Manager, an outrigger is a management pack element which can be used to extend the data warehouse, creating a table (and view) which stores information about potential values for a given property. Outriggers are primarily used for storing information about Lists within the data warehouse. This is because the outrigger-created table includes information about list hierarchy – the parent/child relationships between list items. Without an outrigger, the list values would still be stored in the data warehouse, but the tree of values would be collapsed.

For example, consider the Service Request Area list (link to image). By default, it has several parent list values, each with several child list items contained within. When Service Requests are synced to the data warehouse, the list values are stored within a column on the ServiceRequestDim table by their internal name with no data about which value descends from which other. Were reports to be constructed pulling information only from that table, the user of the report would not get any insight into the list structure, which could severely impact the quality of data in the reports. Fortunately, all of the out-of-the-box lists in Service Manager also have outriggers defined which create tables like the one in the image below, showing all of the potential values of the Service Request Area list, including information about their position (ordinal) and their parent (parentId):

Due to the benefit the data stored in an outrigger provides, it is a best practice when designing custom extensions to Service Manager’s data model to include outriggers for any custom list (enum) properties, whether they be on an entirely custom class or an extension to an existing class. Fortunately, outriggers are very simple to create – all one needs to do is add a small section to a management pack defining the outrigger, seal the management pack, and import it. MPSync will take care of the rest, moving the management pack to the data warehouse and creating the resultant table (and view). For more detailed information on how outriggers work and how to make one, review this link. Rather than rehash that post, this post is meant to focus on a common issue.

Just Because it works for the Native MPs…

I wish I could say that the process was as simple as importing the management pack and calling it a day. Unfortunately, I’ve run into some consistently inconsistent results with the way MPSync handles custom outriggers in the data warehouse, at least when it comes to outriggers for custom list properties extending the Incident class. The most offensive issue I’ve run into is one that has consistently broken data warehouses, requiring a wipe and rebuild of the data warehouse management group and databases. This is not fun, and no one wins.

Scenario: Let’s say you’ve created a custom class extension for the Incident class containing a list property, as well as a separate management pack containing an outrigger and any other data warehouse extensions you need for your custom class. You’re ready to import them into Service Manager and let the fun begin. You know that whenever Service Manager is installed and MPSync first ran, there is no need for any specific order of management pack import; Service Manager knows the management pack dependencies and deploys all of the management packs in the appropriate order. So you decide to import both your class extension and data warehouse management packs at the same time, then kick off MPSync and slip out for dinner while MPSync goes ahead and deploys the management packs to the data warehouse for you.

Unfortunately, that assumption has consistently led to a broken MPSync job, management packs stuck in “Pending Association”, and the only way to fix it being a data warehouse rebuild. Why, you ask? That is a question to which I would love to know the answer, however I’ve not yet come across any logical explanation.

When I first came across this issue, my first thought was that something was wrong in the management packs. However, I compared the structure of the outriggers to the out-of-the-box outriggers for native Incident list properties, and aside from the property name, there was no difference. Odder still, if I took the exact same management packs and simply replaced all of the references to the Incident class to the Service Request class, extending the Service Request class instead, everything worked fine. Both management packs could be imported simultaneously and MPSync would handle them without any issues. The more I’ve investigated the issue, the more it has been apparent that the problem only affects extensions to the Incident class, and also only when the management pack containing the class extension and the management pack containing the outrigger are imported simultaneously.

A Solution

Fortunately, there is an easy way to avoid the problem. Quite simply, import the class extension management pack first and allow MPSync to run and successfully associate the custom management pack before importing the data warehouse management pack containing your custom outrigger. This has consistently worked for me in every situation where importing the management packs simultaneously has led to issues. Patience is key, but apparently only with Incident outriggers.

There are some other differences as well between how the data warehouse handles native outriggers, custom Incident outriggers, and custom outriggers for other classes, but while annoying at times, none of them break the data warehouse in the same way. I’ll talk more about the other differences in another blog post in the future. For now, just make sure that you allow MPSync to completely associate any custom Incident extensions with list properties before importing any data warehouse management packs with outriggers and save yourself a huge headache.

Please feel free to post comments or questions in the box below!

Gabriel Taylor