Documentation or training on the SQL databases contents
DSVella
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
-
Adam_Dzyacky
Product Owner Contributor Monkey ✭✭✭✭✭
...example Incidents that have changed level...
When it comes to things like "number of times X occurred per Work Item" I tend to default to a Class Extension to said Work Item class followed by creating a Workflow that monitors for the event to occur and then writes a value into that class extension. since the value is written to the native SCSM DW you just grab it like any other SELECT style SQL query.
number of related items to a given work item
Also a pretty easy maneuver through the SCSM DW. I think its maybe 3 or 4 joins? Either way, the schema diagrams that you're looking for can be found at the bottom of this post. But obviously this assuming you are using the native SCSM DW. What DB you choose to query has its pros and cons that I'd 100% be echoing from the following thread.
5
Answers
...example Incidents that have changed level...
When it comes to things like "number of times X occurred per Work Item" I tend to default to a Class Extension to said Work Item class followed by creating a Workflow that monitors for the event to occur and then writes a value into that class extension. since the value is written to the native SCSM DW you just grab it like any other SELECT style SQL query.
number of related items to a given work item
Also a pretty easy maneuver through the SCSM DW. I think its maybe 3 or 4 joins? Either way, the schema diagrams that you're looking for can be found at the bottom of this post. But obviously this assuming you are using the native SCSM DW. What DB you choose to query has its pros and cons that I'd 100% be echoing from the following thread.
Adam,
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.
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) -
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.
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.
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
Hi @Adam_Dzyacky , sorry it is the "Requested By" that we need, not "Created By". Sorry for the typo that I had earlier.
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
Thanks @Adam_Dzyacky. Let me update our report developer.