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:
- Recreate the vw_SLA_Metrics view using the template above (adjust columns based on your schema)
- Grant SELECT permissions to all reporting identities
- Test the SLA reports to verify they’re working
- Document the view creation script and add it to your upgrade procedures
- 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.
This draft is based on general Aras Innovator knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.