Home General Discussion

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

pzergerpzerger Partner Adept IT Monkey ✭✭
edited May 2016 in General Discussion
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!

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

We use the data warehouse. It meets all our reporting needs and is easy enough to use.
4% 2 votes
We use the data warehouse. It is sometimes too technical for our users who need reporting.
76% 32 votes
We do not use the data warehouse (because it is too difficult to maintain / keep healthy).
16% 7 votes
We do not use the data warehouse (because we do not need historical reporting)
2% 1 vote

Comments

  • Candice_YeudallCandice_Yeudall Customer Advanced IT Monkey ✭✭✭

    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_CrewJosh_Crew Customer IT Monkey ✭
    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_SebbaDavid_Sebba Customer IT Monkey ✭
    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_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭
    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_KildayLeigh_Kilday Member Ninja IT Monkey ✭✭✭✭

    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_WoodConner_Wood Customer Ninja IT Monkey ✭✭✭✭
    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_RunheimJon_Runheim Customer Adept IT Monkey ✭✭
    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_BorchseniusFredrik_Borchsenius Customer IT Monkey ✭
    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_GilliamJosh_Gilliam Customer IT Monkey ✭
    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_BorchseniusFredrik_Borchsenius Customer IT Monkey ✭
    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_GilliamJosh_Gilliam Customer IT Monkey ✭
    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.. :smile:

  • pzergerpzerger Partner Adept IT Monkey ✭✭
    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_OvermanGermaine_Overman Customer Adept IT Monkey ✭✭
    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_HoreAmanda_Hore Customer Adept IT Monkey ✭✭
    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_GilliamJosh_Gilliam Customer IT Monkey ✭
    pzerger 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_coussensseth_coussens Member Ninja IT Monkey ✭✭✭✭
    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.
Sign In or Register to comment.