How would you describe your current experience with SCSM Data Warehouse reporting?

pzerger

How is the SCSM data warehouse working out for your org? We would like to hear what you think! We will discuss results in a future blog post or Cireson webinar!

Candice_Yeudall

I actually found it easier to learn some basic SQL and go directly from the Database than to use the data warehouse. At least for some of what I am doing.

And speaking of "We do not use the data warehouse (because it is too difficult to maintain / keep healthy)" is there away that you know of to keep all the SQL data together so that we can actually report on our complete history instead of dealing fragments?

Josh_Crew

I utilize a lot of SQL for reporting, as customizing the data cubes is very difficult to perform and the lack of documentation doesn't help.

David_Sebba

We're somewhere between 2 and 3.  We use the data warehouse (using SQL queries) and it's ok, but there is missing information that means we can't do some kinds of reports.

Adam_Dzyacky

I've started to turn to the ServiceManagement DB since the data is a bit more flat and thus easily consumable. Otherwise PowerBI all the way for stock DW.

With regards to IR/SR - I think the challenge faced a lot is around dates within SCSM. Getting things like items created or resolved by themselves are easy tasks. But when you want to chart IR created against IR resolved regardless of the day (i.e. not the created or resolved date) you have to join to DateDim which is only possible within SQL and not cubes (well not yet at least, cubes seem to be getting a much needed date update in scsm 2016)

With regards to SR/RA/MA - I think the challenge faced from a management perspective is first digesting these new concepts if you're coming out of a "classic" ticketing system where everything is...well just a ticket. SRs seem to be conceptually harder to understand from a reporting perspective since the activities contained therein more times than not, do not pertain to the group (let's say IT management) wants information on. A perfect example being employee onboarding - the SR doesn't have a clear Support Group, because the Activities that make up the SR really define where the work goes (HR, IT, Accounting ,etc.) I would argue that visualizing and charting this is challenging, again when you're coming off of a "classic" ticketing system.

Leigh_Kilday

We are creating our own cubes from the DW. The DW has some advantages, such as its MPSync job and we don't have to intimately understand object model. The OOTB cubes are not user-friendly and seem to require more hours effort in maintenance than hours of productive use.

Conner_Wood

We registered a Data Warehouse with SCSM.
The Data Warehouse is very tricky to maintain due to unforeseen circumstances and lack of developer testing && support.

Getting comments archived was a pain.
Adding DateSlicers to Cubes was a pain.
Cubes randomly remove dateslicer info, running dateslicer PS script every once in a while is a pain.
Jobs getting stuck is a pain.
Cubes not processing is a pain.
Setting Retention Time correctly (both DWRepository and DWDataMart should be the same big number) was a pain.
Learning the differences in schema of SCSM Live Database vs SCSM DW Databases was a pain.
Developing a solution for Service Desk to search Archived Tickets efficiently was a pain.
Reporting a DW Corruption bug to MS (whom since have done nothing) was a pain.

TLDR - Pain Awaits all who enter the SCSM Data Warehouse Gates.

Jon_Runheim

We only use the cubes as of now. There native reports doesn't cut it and the effort to create new reports is too high.

The cubes enables excel-people (which most are capable of managing) to at leastt with some creativity get the job done. Even though the dateslicers are a pre-req for even the simplest of reports.

Due to the non-existing documentation in the area the cubes require lots of understanding before using.

This area has LOTS of improvement capabilities..

Fredrik_Borchsenius

I tend to create custom reports for customers, based on SQL + SRSS or PowerBI. Click and shoot reports, which we integrate straight into the portal as custom pages seems to be the solution most find elegant in my experience.

Josh_Gilliam

I love using the ServiceManagement db for reporting, however, we are still trying to figure out how to query the custom created fields.  We can obviously see them within the portal via enumeration, but cannot figure out how to query them via sql. 

Fredrik_Borchsenius

Josh_Gilliam said:

I love using the ServiceManagement db for reporting, however, we are still trying to figure out how to query the custom created fields.  We can obviously see them within the portal via enumeration, but cannot figure out how to query them via sql. 

unless you edited the XML after creating the custom class/field in the authoring tools you will need to use the rather non-intuituve ID from SCSM. By default, an enumeration for incident status resolved for example would be:

IncidentStatusEnum.Resolved

But for a cutom value, the same ID is something like Enum.29e2bd691f984134a0e158945142974a unless you edited the XML.

Also, remember to Join the enumeration on DisplayStringView to get a readable value, and filter to the language of your choice. Something like this (extremely simplified):

SELECT MTV_System$WorkItem$Incident.DisplayName AS [Display Name]
     , DisplayStringView.DisplayName AS Status
FROM
  dbo.MTV_System$WorkItem$Incident
  INNER JOIN dbo.DisplayStringView
    ON MTV_System$WorkItem$Incident.Status_785407A9_729D_3A74_A383_575DB0CD50ED = DisplayStringView.LTStringId
WHERE
  DisplayStringView.LanguageCode = 'ENU'

Josh_Gilliam

We did edit the XML and create a new enumeration.  For instance, it's called Ticket Type for additional tracking.  Creating it was against my recommendation, but I got trumped..

pzerger

Josh_Gilliam said:

I love using the ServiceManagement db for reporting, however, we are still trying to figure out how to query the custom created fields.  We can obviously see them within the portal via enumeration, but cannot figure out how to query them via sql. 

John, we are not caching your custom fields currently, but we can point you to where to get that data from the SCSM DB.

Germaine_Overman

We do various pivot table reporting but not everything is available, so than we get our reporting services people involved.   It is fragile and sometimes locks up and has issues during upgrades.

Amanda_Hore

We are really struggling with reporting.  We have BI people that want to help, we give them the cubes and they are struggling to get the data as not all of it is in the cubes, especially custom fields.  We have got the custom fields into the DW, but not into the cubes.  you would expect some more simple reporting from a service management product. 

Josh_Gilliam

pzerger said:

Josh_Gilliam said:

I love using the ServiceManagement db for reporting, however, we are still trying to figure out how to query the custom created fields.  We can obviously see them within the portal via enumeration, but cannot figure out how to query them via sql. 

John, we are not caching your custom fields currently, but we can point you to where to get that data from the SCSM DB.

Yes could you point me in the right direction please. 

seth_coussens

Some of the information you are looking for can be found here: https://sethcoussens.com/2016/01/22/power-bi-desktop-scsm-2012/

Basically, you can get all the data you want out of the datawarhouse without the cubes and power bi makes it pretty easy to do so. It takes a bit of work to get it setup the first time, but then works very well after that.