Excel, PowerQuery, and the Cireson ServiceManagement DB
Adam_Dzyacky
Product Owner Contributor Monkey ✭✭✭✭✭
When Cireson announced the v6 functionality of building your own dashboards in the portal with no code I was thrilled because if I don't have to write SQL...then I'm not going to write SQL. But the feature has a few limitations - they certainly aren't deal breakers by any means, but limitations none the less (querying other data sources, how grouping works, etc). But if these are the trade-offs in the name of "write no code" then I can get over that pretty quickly.
So if you want something for let's say a large monitor in a public area, you'll have to do something a bit more custom. Which is why I turned to Excel, PowerQuery, and leveraging the ServiceManagement database. So I came up with this -
Now, these stats aren't anything terrifically complicated but near real time stats should be really simple to digest which is why I'm a fan of this. However I do have to concede that I decided to use SQL queries inside of PowerQuery to generate some of these results and crack some VB open for this, because what I wanted is a "stoplight" indicator of how Support Groups are performing throughout the day. So the colored boxes that show "Resolved Today" actually change color based on if they are:
...than the Created Today count. It's nothing more than a really big simple IF statement that is triggered when the workbook automatically refreshes/recalculates (which happens every 15 minutes) and then changes these text box colors. For some added effect, the inner bounds (lower and greater) change to a gradient instead of a solid color.
Now there is still a degree of required understanding of how the ServiceManagement DB is laid out. Since the schema isn't available, a few SELECT TOP 10 * queries are mandatory to guess your way through it. Then again, you could also just use PowerQuery (again, no code approach) and guess your way through that way. I'd say that the big advantage of PowerQuery is that if you understand the least bit about Excel you get all the benefits of the PowerBI platform but in a fashion that doesn't require a significant investment of time in learning how the tool works/learning a wholly new tool. That and you can combine multiple data sources into a single file so you could do something like point the workbook at Cireson's ServiceManagement DB, the stock SCSM DW, a SharePoint List, Project Server, etc. and further build out the picture of IT. But again - you can absolutely do all of what I just said with PowerBI. Otherwise, I'd say use what you know!
I'm still working on getting a stripped down, plug 'n play version of this Excel file to share. So in the interim, if this is something you're interested in I can certainly post the queries at anyone's request so you can build your own Excel dashboard!
So if you want something for let's say a large monitor in a public area, you'll have to do something a bit more custom. Which is why I turned to Excel, PowerQuery, and leveraging the ServiceManagement database. So I came up with this -
Now, these stats aren't anything terrifically complicated but near real time stats should be really simple to digest which is why I'm a fan of this. However I do have to concede that I decided to use SQL queries inside of PowerQuery to generate some of these results and crack some VB open for this, because what I wanted is a "stoplight" indicator of how Support Groups are performing throughout the day. So the colored boxes that show "Resolved Today" actually change color based on if they are:
- significantly lower (less than 90%)
- lower (90%-100%)
- equal (100%)
- greater (100%-110%)
- significantly greater (110% or greater)
...than the Created Today count. It's nothing more than a really big simple IF statement that is triggered when the workbook automatically refreshes/recalculates (which happens every 15 minutes) and then changes these text box colors. For some added effect, the inner bounds (lower and greater) change to a gradient instead of a solid color.
Now there is still a degree of required understanding of how the ServiceManagement DB is laid out. Since the schema isn't available, a few SELECT TOP 10 * queries are mandatory to guess your way through it. Then again, you could also just use PowerQuery (again, no code approach) and guess your way through that way. I'd say that the big advantage of PowerQuery is that if you understand the least bit about Excel you get all the benefits of the PowerBI platform but in a fashion that doesn't require a significant investment of time in learning how the tool works/learning a wholly new tool. That and you can combine multiple data sources into a single file so you could do something like point the workbook at Cireson's ServiceManagement DB, the stock SCSM DW, a SharePoint List, Project Server, etc. and further build out the picture of IT. But again - you can absolutely do all of what I just said with PowerBI. Otherwise, I'd say use what you know!
I'm still working on getting a stripped down, plug 'n play version of this Excel file to share. So in the interim, if this is something you're interested in I can certainly post the queries at anyone's request so you can build your own Excel dashboard!
5
Comments
I've been hoping to get some time to work on a similar style chart that would be suitable for presentation on a wall/monitor somewhere.
I went in the PowerBI direction for our Cireson Portal deployment, which we've called IRIS internally. The biggest challenge has been, and continues to be, the SQL knowledge of the SCSM database as well as the different relationships needed, i.e. relating date dimensions to work item open & resolution dates, user data to end users & analysts.
I must try and leverage the ServiceManagementDB a bit more. Might be more friendly than the ServiceManager one!
As a testament to its friendliness - all of the Work Items are on a single table.
If anyone has any ideas/experience on this, would love to see/hear more!
This is exactly what I've been looking for. Are you able to post the queries?
Regards
Anikke