Using SLA KPI Instance Table to Build KPIs

In my last post, we reviewed each of the four Dataverse tables that exist and work together to deliver SLA solutions to organizations. In this post, we will focus on the SLA KPI Instance table for two things: important columns and sample KPIs.

SLA KPI Instance Columns

Like all Dataverse tables, this one comes with a list of pre-configured columns to use in the SLA solution. A complete list of all columns that are included can be found here on Microsoft Learn.

Here are some of the key columns you should know before building out your KPIs:

• Name: Descriptive label of the KPI instance.
• ApplicableFromValue: Timestamp when the KPI measurement starts.
• ComputedWarningTime: System-calculated date/time warning should trigger.
• ComputedFailureTime: System-calculated date/time when failure is due.
• FailureTime: The actual timestamp when the KPI failed.
• ElapsedTime: Total time elapsed in minutes.
• PausedOn & LastResumeTime: For SLA pauses/resumes.
• Status: default options are:
  • 0: In Progress
  • 1: Noncompliant
  • 2: Nearing Noncompliance
  • 3: Paused
  • 4: Succeeded
  • 5: Canceled
• SucceededOn: Timestamp when KPI succeeded.

5 KPIs to Get Started

Now, let’s talk about some KPIs you can build using the SLA KPI Instance. These are just suggestions to get you started, not a complete solution. You will likely need to build additional columns at the parent level (example: Account or Contact) to support the specific metric you’re looking for. Rollup columns will be your friend in getting a lot of these to dashboard and visualization form. Let’s dive in – some of these are simple, others are more complex.

Time to Warning

Create a formula column to calculate when a warning should be issued. This may be handy for a Customer Service Rep to see how much time they have left before the warning is initiated.

ComputedWarningTime – ApplicableFromValue

Time to Failure

An even better one! Once you create this column, it’s a great one to add to a form or view for your reps and managers. Create a formula column to calculate how much time is left before SLA breach.

ComputedFailureTime – ApplicableFromValue

Actual Time to Success or Failure

This metric is great to show trends over time, case type and sales rep within a visual. It’s an excellent one to incorporate into dashboards and visualizations.

For successful instances, create a formula column using: SucceededOn – ApplicableFromValue

For failed instances, create a formula column using: FailureTime – ApplicableFromValue

On-Time Success Rate

This is a great metric that can create powerful, simple visuals. You could do this simply in PowerBI with a measure that calculates SucceededOn <= ComputedFailureTime.

You could also do this in Dataverse by building some rollup columns and a new column on the SLA KPI Instance table. I’d suggest:

• New column called “On Time Success” as a Yes/No column.
• Rollup fields on Account:
  • Total KPI instances: Count of related SLAKPIInstance rows
  • On Time Success Count: Count where OnTimeSuccess = Yes

Then, it’s a formula column on the Account for the calculation that gives you a percentage of SLA KPI Instances that succeeded before their failure deadline.

Failure Rate

This can help identify Customer Service Reps who might need additional coaching, case trends where process changes may be needed and identify opportunities for improvement.

You’ll need to build some rollup fields to support it at the parent level:

• Total KPI instances: Count of related SLAKPIInstance rows
• Failed KPI Instances: Count of related SLAKPIInstance rows where Status = Noncompliant (1)

You can also do the same for Warnings & Successes to get rates for those as well.

After you have those rollups, you can make a formula column on the parent (Account, Case or other) to get the rates.

Average Elapsed Time

This would be a great one to build in Power BI using Elapsed Time across all KPI instances to find average SLA duration usage.

ElapsedTime is a column included directly in the SLA KPI Instance table, making this an easy metric to build off.

Use PowerBI to show averages and trends or use Dataverse columns to calculate averages across parent tables (Account, Case, etc).

Final Thoughts

Starting with these KPIs will get you moving in the right direction. Where it really gets powerful is when you extend these KPIs with Power BI for trend analysis.

---