SLA reporting fails in service management after database schema update, missing view or permission error

PLM Aras Innovator
, , , , , , , ,
1

After a recent schema update to our Aras 14.0 instance, our SLA reports in service management have completely stopped working. The reports were running fine before the update, but now they’re returning empty result sets even though we have active service requests with SLA data.

The error in the application logs shows:


SQL Error: Invalid object name 'innovator.vw_SLA_Metrics'
Cannot execute query for report 'SLA_Performance_Dashboard'

It appears the database view that the SLA reports depend on is missing after the schema update. I checked the database and confirmed that vw_SLA_Metrics doesn’t exist anymore. I’m also concerned about role-based permissions - our Service Desk role should have read access to SLA data, but I’m not sure if the schema update affected those permissions as well. Has anyone dealt with missing database views after a schema update? How do we restore the view and ensure proper access?

2

That makes sense. I believe vw_SLA_Metrics was created by our implementation partner as part of the custom SLA reporting package. I don’t have the original view creation script though. Is there a way to recreate the view based on what the reports are expecting? I can see the report queries reference columns like sla_target_date, actual_resolution_date, and breach_status.

3

Another thing to check - make sure your schema update process included validation steps. You should have a checklist that verifies all custom database objects (views, stored procedures, functions) still exist after the update. This should be part of your standard upgrade procedure to catch these issues before they hit production.

4

Schema updates in Aras 14.0 sometimes drop and recreate views, especially if there were changes to underlying tables. Check if there’s a database upgrade script that should have recreated the view. You might need to manually run the view creation script from the Aras installation package.

5

Here’s a comprehensive solution that addresses all three critical areas:

1. Database View Dependencies First, you need to recreate the missing vw_SLA_Metrics view. Based on standard SLA reporting requirements, here’s a template view structure:

CREATE VIEW innovator.vw_SLA_Metrics AS
SELECT
  sr.id,
  sr.item_number,
  sla.target_date as sla_target_date,
  sr.closed_on as actual_resolution_date,
  CASE WHEN sr.closed_on > sla.target_date THEN 'Breached' ELSE 'Met' END as breach_status
FROM innovator.SERVICE_REQUEST sr
LEFT JOIN innovator.SLA sla ON sr.sla_id = sla.id

Before creating the view, verify that the underlying tables and columns still exist after the schema update. Run this query to check:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'innovator'
AND TABLE_NAME IN ('SERVICE_REQUEST', 'SLA')

If column names have changed (e.g., target_date renamed to sla_target), adjust your view definition accordingly. Document any schema changes that affected your custom objects.

2. Role-Based Permissions After creating the view, you must grant appropriate permissions to the identities used by your reporting roles. Execute these SQL statements:

GRANT SELECT ON innovator.vw_SLA_Metrics TO [InnovatorServiceDesk]
GRANT SELECT ON innovator.vw_SLA_Metrics TO [InnovatorReporting]

Verify that the Service Desk role’s assigned identity has the necessary database permissions. Check the identity configuration in Administration > Identities and ensure it has either db_datareader role membership or explicit SELECT grants on all reporting views.

To test permissions, run this query as the service desk identity:

SELECT permission_name, state_desc
FROM sys.database_permissions dp
JOIN sys.database_principals dpr ON dp.grantee_principal_id = dpr.principal_id
WHERE dpr.name = 'InnovatorServiceDesk'

3. Schema Update Validation Process To prevent this issue in future updates, implement these validation procedures:

Pre-Update Inventory:

  • Script out all custom database objects (views, stored procedures, functions, triggers)
  • Document dependencies between reports and database objects
  • Store scripts in version control with the Aras instance configuration

Post-Update Validation Checklist:

  • Verify all custom views exist: SELECT name FROM sys.views WHERE schema_id = SCHEMA_ID(‘innovator’)
  • Test each critical report to ensure it returns expected data
  • Validate that all role-based permissions are intact
  • Review application logs for any SQL errors

Automated Validation Script:

Create a SQL script that runs after schema updates to verify custom objects:

IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'vw_SLA_Metrics')
  PRINT 'ERROR: vw_SLA_Metrics view is missing'
ELSE
  PRINT 'SUCCESS: vw_SLA_Metrics view exists'

For your immediate situation:

  1. Recreate the vw_SLA_Metrics view using the template above (adjust columns based on your schema)
  2. Grant SELECT permissions to all reporting identities
  3. Test the SLA reports to verify they’re working
  4. Document the view creation script and add it to your upgrade procedures
  5. Create a backup of all custom database objects before future schema updates

Going forward, always script out custom database objects before any schema update and verify they’re recreated afterward. Include this as a mandatory step in your change management process for Aras upgrades. This will prevent report failures and minimize downtime after future updates.

6

Don’t forget about the permissions aspect. After recreating the view, you need to grant SELECT permissions to the identities used by your reporting roles. Run a GRANT statement for each identity that needs access to the view. Also verify that the Service Desk role’s identity has db_datareader permissions or explicit SELECT grants on the view.

7

I’ve seen this exact issue before. The problem is that custom views created outside of Aras’s standard schema aren’t always preserved during upgrades. If vw_SLA_Metrics was a custom view created by your team, you need to have the view creation script backed up and rerun it after the schema update. Also check if the view was dependent on columns that might have been renamed or removed in the update.