Home Analyst Portal

Documentation or training on the SQL databases contents

DSVellaDSVella Member IT Monkey ✭

Good afternoon all;

I have been tasked with pulling in data from Service Manager into our companies Data Warehouse. I have had some success with the analytics database and some tables in the management database. However management have asked for some more complex metrics (for example Incidents that have changed level or number of related items to a given work item).

My goal is to create some comprehensive materialised views in our Data warehouse to serve management their reports.

I could really use some documentation or training on the SQL schema of the system so I can get more useful information out and into managements hands. I have looked around the site but I cannot find anything.

Best Answer


  • DSVellaDSVella Member IT Monkey ✭


    Thanks for your reply, I have been busy with the DWDataMart database as i didn't even know it existed!

    While I have been able to get 90% of my current requirements by querying the various tables and views in the DW, I'm short one last thing, the history tab. I am looking to query this to answer the business question "How many incidents changed level (positively or negatively) during its life. My research suggests that SCSM does not move the ticket's history to the DW by default.

    I will create a separate thread about capturing the history data.

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited February 2020

    No problem. There is a lot of power to be found there especially when you add SCOM and SCCM into the mix.

    History can mean a couple things in SCSM (although I get the one you're after) -

    • The Action Log? There is actually a community MP for that. Requires a very small edit if you want it to pull Problems.
    • The actual History (Entity Change Log) table when you view the History tab on an object in SCSM? You're right, that doesn't make it to the DW. ECL only exists in the operational ServiceManager DB and is responsible for recording all of the changes made to an object in SCSM.

    But since you keep coming back to "how many times an Incident changed level" and I can only assume you mean Incident Tier Queue (commonly referred to as Support Group) the good news is that there is a post from Marcel on this very topic that I was alluding to earlier.

  • Carol_LeeCarol_Lee Customer IT Monkey ✭
    edited September 2020

    Hi @Adam_Dzyacky, I have checked the SCSM DW structure that you posted above, and I have download the Visio file from that Microsoft website. Do you happen to know where in the data warehouse database we can find the Change Request's "Requested By" field/column? It is not stated in the "Change" tab of the Visio file. Please advise.

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    Hey there @Carol_Lee,

    The Created By is from the User Class (UserDim table). So it's a relationship not a direct property you'll find on the Change Request class (ChangeRequestDim table) like the Change Request Title or Description.

    This is just off the top of my head, but I believe the SQL joins you'll have to make are to the effect of:

    ChangeRequestDim -> EntityDim -> WorkItemDim -> WorkItemCreatedbyUserFactvw -> UserDim

  • Carol_LeeCarol_Lee Customer IT Monkey ✭

    Hi @Adam_Dzyacky , sorry it is the "Requested By" that we need, not "Created By". Sorry for the typo that I had earlier.

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    edited September 2020

    Change Requests have this relationship, but they don't expose it by default in the GUI so it's something you'd have to be setting with either Runbooks or Advanced Request Offering. But in either case, it's almost the same exact thing:

    ChangeRequestDim -> EntityDim -> WorkItemDim -> WorkItemAffectedUserFactvw -> UserDim

  • Carol_LeeCarol_Lee Customer IT Monkey ✭

    Thanks @Adam_Dzyacky. Let me update our report developer.

Sign In or Register to comment.