I noticed recently when I was running reports, data that seems to be incorrect, or I need a better understanding on how it is calculated. In the screenshot below I'll show a number of incidents that are opened, ones breaching at least one SLO and the percentages of breaches. But maybe I'm not understanding how I can have for example 24 incidents opened, 10 breaching at least 1 SLO, but Breach % is at 100%. I've gone back to the console and looked at the data and of those 24 there have been several that have met ALL SLOs, but they still show as 0% meeting ALL SLOs and 100% breached...overall very confused.

Hi Marcel,

I would be curious to see exactly what is going on in your Data Warehouse and how the report you show above was created. Every time the status of an SLA changes, the system adds another record in the SLAInstanceInformationFactvw with a different status.  I imagine this is intentional as it allows you to see and report on when a particular Incident changes from one status to another.  As you can see below, there are duplicate records for each time the status changed.

You can run the below query to see all the related SLO information for an Incident.

select I.Id, SLAC.DisplayName, Stat.SLAInstanceStatusValue, SLAI.IsBreached, SLAI.IsCancelled
From IncidentDimvw I
left outer join WorkItemDimvw W on I.BaseManagedEntityId = W.BaseManagedEntityId
left outer join SLAInstanceInformationFactvw SLAI on W.WorkItemDimKey = SLAI.WorkItemDimKey
left outer join SLAConfigurationDimvw SLAC on SLAI.SLAConfigurationDimKey = SLAC.SLAConfigurationDimKey
left outer join SLAInstanceStatusvw Stat on SLAI.SLAInstanceStatusId = Stat.SLAInstanceStatusId
Order By I.Id Asc

- Christopher

The report is being pulled from OLAP cubes by selecting the corresponding fields in the field picker (labeled exactly as they are on the screenshot above). I am not using SQL for reports at all as we do not have anyone on staff that can effectively write SQL queries that also has time to do so (our DBA is only part time, and mostly tied up with project related work).

It just seems like the data that is being pulled from the OLAP cube is either incorrect, or I am not interpreting it properly. We are getting a lot less warnings and breaches this month after tightening processes, so I'll be interested to see if this changes at all, but from what I saw when running reports for January the data the OLAP cubes were giving me wasn't backed up by the data in Service Manager.

I appreciate the help though Chris, and perhaps as I knock out a few more SCSM related projects I can start to learn more SQL to write queries and reports better. :)

So again this month I pulled data into Excel and no matter how many ways I slice the data, SLO % for breached work items always shows 100%. I went back and verified that there are many work items that met all SLO requirements, and after instead using the field "Incidents Breaching at Least One SLO" and then calculating a % on that find that I am about 75% compliant with SLO, but yet the breach % is still showing 100%.

Am I not understanding how this field works, or is something wrong with the way is calculates?

Hi Marcel,

we do have the same 'issues' with the OLAP cubes... I see you're filtering by CalenderMonth, we're doing exactly the same. Oddly the percentage is diplayed correctly when you select multiple values for the filter. For instance, if you want to report the current month and would want to have the percentage calculated 'properly' select August and September for the calender month. Is it working then?

I was wondering if the numbers you get for "Incident Meeting All SLOs" and "Incident breaching at least one SLO" are really valid?

We get really odd number when we want to display how many Incidents have breached / met a particular SLO.

I've highlighted one of the issues... Strange thing is that we don't even have that many Incidents created for August. Additionally the Grand Total seems to be wrong.

I'd really appreciate if someone could provide some Information how the "Incident Meeting all SLOs" measure is acutally calculated and how it's supossed to be used...

I've tried to analyze the WorkItemsCube code using Business Intelligence Development Studio. This is what I've found:

CREATE MEMBER CURRENTCUBE.[Measures].[Incidents Meeting All SLOs]
AS Exists
      (        
        [IncidentDim].[DisplayName].MEMBERS,
       ,"IncidentSLAInstanceInformation"
      ).Count
       - 
      Exists
      (       
        [IncidentDim].[DisplayName].MEMBERS,
       {
          [SlaInstanceStatus].[OutriggerID].&[SLAInstance.Status.Breached]
         ,[SlaInstanceStatus].[OutriggerID].&[SLAInstance.Status.Active]
         ,[SlaInstanceStatus].[OutriggerID].&[SLAInstance.Status.Warning]
         ,[SlaInstanceStatus].[OutriggerID].&[SLAInstance.Status.NotReady]
        }
       ,"IncidentSLAInstanceInformation"
      ).Count

It seems the Incident Meeting all SLOs measure subtracts the Incidents where a SLO is/was either in Status "Not Ready", "Warning", "Breached" or "Active". Not sure if I agree with that. In my opinion everything which is not particulary in Status "Breached" (or maybe Not Ready as well... depends on how this Status is used) is meeting its SLO. Or am I misunderstanding here something?

Thanks

Alex

Has anyone found a solution to this?  I am having the same issue where it's saying 100% breach for all of my incidents when using OLAP cubes and Excel.

Thanks

Robert

try this

select

I.Id,SLAC.DisplayName,Stat.SLAInstanceStatusValue,SLAI.IsBreached,SLAI.IsCancelled

From

IncidentDimvwI

left

outerjoinWorkItemDimvwWonI.BaseManagedEntityId=W.BaseManagedEntityId

left

outerjoinSLAInstanceInformationFactvwSLAIonW.WorkItemDimKey=SLAI.WorkItemDimKeyandEndDateisnotnullandSLAI.IsCancelled<>1

left

outerjoinSLAConfigurationDimvwSLAConSLAI.SLAConfigurationDimKey=SLAC.SLAConfigurationDimKey

left

outerjoinSLAInstanceStatusvwStatonSLAI.SLAInstanceStatusId=Stat.SLAInstanceStatusId

Order

ByI.IdAsc

select

I.Id,SLAC.DisplayName,Stat.SLAInstanceStatusValue,SLAI.IsBreached,SLAI.IsCancelled

From

IncidentDimvwI

left

outerjoinWorkItemDimvwWonI.BaseManagedEntityId=W.BaseManagedEntityId

left

outerjoinSLAInstanceInformationFactvwSLAIonW.WorkItemDimKey=SLAI.WorkItemDimKeyandEndDateisnotnullandSLAI.IsCancelled<>1

left

outerjoinSLAConfigurationDimvwSLAConSLAI.SLAConfigurationDimKey=SLAC.SLAConfigurationDimKey

left

outerjoinSLAInstanceStatusvwStatonSLAI.SLAInstanceStatusId=Stat.SLAInstanceStatusId

Order

ByI.IdAsc

Thanks for the information Atul, but we've been off of SCSM for about 6 months now having moved to a different product that works better.