Navigation
Proposal Map
1. Executive Summary2. Business Need3. Proposed Solution4. Target Architecture5. Daily Restore & Refresh Process6. SQL Server Design7. Power BI Report Server8. Disaster-Recovery Laptop9. Security & Governance10. Validation & Control Framework11. Vendor Requirements12. Project Scope13. Implementation Phases14. Deliverables15. Risks & Mitigations16. Expected Benefits17. Recommended Next Steps
Executive Overview
Executive Summary
The City already uses Accela as its transactional platform for Building, Licensing, Code Enforcement, Planning, Zoning, and other core municipal services. The proposed project does not replace Accela and does not interfere with the production environment.
The objective is to create a daily refreshed, local analytical copy of authorized Accela data inside a City-controlled Microsoft SQL Server environment. Power BI Report Server would consume the local database in Import mode, producing fast dashboards with no recurring lakehouse or cloud-compute dependency for daily analytical use.
Local
City-controlled analytical repository
Daily
Validated data refresh
Portable
Disaster-response analytics capability
Low OPEX
No variable lakehouse compute for dashboards
Core principle: Accela remains the transactional system of record. The local SQL Server becomes a non-transactional analytical and continuity platform.
Business Context
Business Need
- Provide daily operational visibility across Building, Licenses, Code, Planning, and Zoning.
- Reduce dependence on front-end transactional reports for cross-departmental analysis.
- Allow fast, local dashboard performance using imported analytical models.
- Maintain access to the latest validated operational snapshot during a hurricane, network interruption, or cloud-service disruption.
- Support an on-premises, transferable analytics environment that can be replicated to an authorized emergency laptop.
- Avoid the recurring cost and complexity of a lakehouse architecture when current requirements do not justify it.
Proposed Approach
Proposed Solution
Deploy one virtual server on premises with Microsoft SQL Server and Power BI Report Server. Each day, an authorized Accela backup, reporting copy, or equivalent SQL Server extract is securely transferred, validated, and restored into an inactive analytical database. Once validation passes, the reporting layer switches to the newest snapshot and the Power BI datasets refresh.
Accela Cloud Transactional Platform
↓
Vendor-Authorized Daily Backup / Reporting Copy
↓
Secure Landing Folder
↓
File, Version, Encryption and Integrity Validation
↓
Restore to Inactive SQL Server Database
↓
Data Quality and Freshness Validation
↓
Controlled Switch to Latest Validated Snapshot
↓
Power BI Report Server Scheduled Refresh
↓
Building • Licenses • Code • Planning • Zoning Dashboards
Target State
Target Architecture
Primary On-Premises Virtual Server
- Windows Server virtual machine.
- Microsoft SQL Server instance.
- SQL Server Agent for automation.
- Power BI Report Server.
- Power BI Desktop optimized for Report Server.
- Secure backup landing location.
- Monitoring, logging, and alerting.
Portable Continuity Environment
- Authorized municipal laptop.
- Local SQL Server instance.
- Latest validated Accela backup.
- Restore automation script.
- Approved Power BI files.
- Emergency operating guide.
The portable laptop is designed for analytical continuity and executive access to the latest available snapshot. It does not replace the Accela transactional application.
Automation
Daily Restore & Refresh Process
| Time | Process | Control |
|---|---|---|
| 01:00 | Receive or download authorized Accela backup/extract | Secure transfer and source verification |
| 01:15 | Validate file name, size, checksum, timestamp, and encryption requirements | Reject incomplete or unexpected files |
| 01:30 | Restore to inactive database | No impact to current reporting database |
| 02:00 | Run structural and data-quality tests | Row counts, max dates, key tables, smoke tests |
| 02:20 | Switch analytical views/synonyms to validated snapshot | Controlled cutover |
| 02:30 | Refresh Power BI datasets | Scheduled Import refresh |
| 03:00 | Validate dashboards and publish execution status | Success/failure log and alert |
Database Design
Microsoft SQL Server Design
To avoid downtime and prevent users from querying a database while it is being restored, the recommended pattern uses two alternating restore databases.
Accela_Restore_A ← Active today
Accela_Restore_B ← New backup restored and validated
After validation:
Reporting layer switches from A to B
Next cycle:
A becomes the inactive restore target
Recommended Databases
Accela_Restore_AAccela_Restore_BAccela_AnalyticsAccela_ControlReportServerReportServerTempDB
Analytical Layer
- Stable reporting views.
- Business-friendly field names.
- Departmental marts.
- Historical snapshots where required.
- Reusable date, status, permit, inspection, and fee dimensions.
Business Intelligence
Power BI Report Server
Power BI Report Server would host the City’s dashboards on premises. Reports would use Import mode against the local SQL Server analytical layer, providing fast performance and eliminating daily analytical query load against the Accela production system.
Initial Dashboard Portfolio
Building
Permits, inspections, aging, workload, cycle time
Licenses
Applications, renewals, status, fees, backlog
Code
Cases, violations, inspections, resolution time
Planning
Applications, reviews, milestones, pending workload
Zoning
Requests, approvals, exceptions, geographic trends
Executive
Cross-department KPIs and operational priorities
Continuity
Disaster-Recovery Analytics Laptop
The emergency laptop would maintain the latest approved analytical snapshot, local SQL Server capability, and approved Power BI files. During a hurricane or other disaster, authorized personnel could continue to review critical municipal workload, permits, inspections, code cases, licenses, and planning status even if the primary server or cloud connection is unavailable.
Continuity equation: Latest validated backup + local SQL Server + approved Power BI models = portable analytical access during disruption.
Recommended Laptop Package
- Encrypted enterprise laptop.
- SQL Server edition aligned with licensing requirements.
- SSMS and approved restore scripts.
- Power BI Desktop for Report Server.
- Latest validated backup and PBIX copies.
- Offline operating procedure and verification checklist.
Controls
Security, Privacy & Governance
Security
- Role-based access.
- Least privilege.
- Encryption at rest and in transit.
- Secure service accounts.
- Restricted backup folders.
Governance
- Documented ownership.
- Restore and refresh audit trail.
- Versioned business rules.
- Approved data dictionary.
- Change-control process.
Continuity
- Retention policy.
- Periodic restore tests.
- Emergency laptop validation.
- Recovery documentation.
- Failure notification.
Quality Assurance
Validation & Control Framework
| Control Area | Validation | Expected Result |
|---|---|---|
| Backup file | Name, date, size, checksum | Authorized and complete file |
| SQL compatibility | Source version, destination version, compatibility level | Restorable environment |
| Encryption | TDE certificate/key availability, if applicable | Successful restore |
| Freshness | Maximum transaction/update date | Expected daily cutoff |
| Completeness | Critical table row counts | Within approved tolerance |
| Integrity | Key records, relationships, views, stored procedures | No critical failures |
| Reporting | Power BI refresh and dashboard smoke test | Successful refresh |
Dependency Confirmation
Required Information from Accela / Hosting Vendor
- Is a native SQL Server backup available?
- Is an Enhanced Reporting Database or read-only reporting copy available?
- What SQL Server version and compatibility level are used?
- Is the backup encrypted or protected by TDE?
- Will the City receive the required certificate and private key for restore?
- Does the delivered data include all required operational tables?
- Are documents included, or only document metadata?
- Are deleted and corrected records represented?
- Is daily local restoration contractually permitted?
- What is the backup/export SLA?
- Is a data dictionary available?
- Are there licensing restrictions on local retention and reporting?
Project Boundaries
Proposed Project Scope
Included
- Architecture and technical design.
- SQL Server installation and configuration.
- Automated daily restore process.
- Validation and logging framework.
- Analytical views and marts.
- Power BI Report Server configuration.
- Initial dashboard portfolio.
- Emergency laptop package.
- Documentation and knowledge transfer.
Not Included Unless Added
- Replacement of Accela transactional functions.
- Modification of Accela production data.
- Cloud lakehouse deployment.
- Application disaster recovery for the full Accela platform.
- Document-attachment migration unless separately scoped.
- 24/7 managed-service support unless separately contracted.
Delivery Model
Implementation Phases
| Phase | Objective | Primary Output |
|---|---|---|
| 1. Discovery | Confirm Accela source, backup method, version, licensing, encryption, and required modules | Validated technical assumptions |
| 2. Infrastructure | Provision VM, SQL Server, storage, accounts, and security controls | Ready on-premises environment |
| 3. Restore Automation | Build secure transfer, validation, alternating restore, logging, and alerts | Daily automated refresh pipeline |
| 4. Analytical Layer | Create stable views, marts, and business logic | BI-ready SQL model |
| 5. Power BI | Deploy initial dashboards and scheduled refresh | Operational and executive reports |
| 6. Continuity | Prepare and test emergency laptop | Portable analytical recovery package |
| 7. Handover | Documentation, training, testing, and acceptance | Operational ownership transferred |
Outputs
Project Deliverables
- Solution architecture document.
- SQL Server configuration and database design.
- Automated backup intake and restore scripts.
- Validation, audit, and notification framework.
- Accela analytical data model.
- Power BI Report Server deployment.
- Initial Building, Licensing, Code, Planning, Zoning, and Executive dashboards.
- Disaster-recovery laptop configuration.
- Runbooks for daily operations, failure recovery, and emergency use.
- Administrative and end-user knowledge transfer.
Risk Management
Key Risks & Mitigations
| Risk | Impact | Mitigation |
|---|---|---|
| Vendor does not provide native SQL backup | Restore architecture may not be possible as designed | Use authorized reporting replica or structured export |
| Source version newer than destination | Restore failure | Match or exceed source SQL Server version |
| TDE-protected backup without certificate | Backup cannot be restored | Require certificate and private key in advance |
| Backup arrives late or incomplete | Stale analytics | Validation gate and previous-snapshot fallback |
| Schema changes | Dashboard or ETL failure | Automated schema checks and change-control process |
| Unauthorized laptop access | Data exposure | Encryption, MFA, role-based access, physical controls |
Business Value
Expected Benefits
Fast
Local dashboard response using Import mode
Independent
No analytical dependency on live Accela reporting
Resilient
Latest snapshot available during disruption
Governed
City-controlled access, validation, and audit trail
Pragmatic
Avoids unnecessary lakehouse complexity
Transferable
Replicable to an emergency analytics laptop
Commercial positioning: This is not simply a backup server. It is a Municipal Operational Data Continuity and Business Intelligence Platform.
Decision Point
Recommended Next Steps
- Confirm Accela hosting model and exact SQL Server delivery option.
- Obtain written authorization for local analytical retention and restoration.
- Confirm SQL Server version, encryption, database size, and daily backup SLA.
- Request a sample backup or reporting extract for a proof of concept.
- Complete an infrastructure sizing assessment.
- Restore the sample into a controlled lab environment.
- Validate key modules: Building, Licensing, Code, Planning, and Zoning.
- Build one pilot dashboard and one disaster-recovery test.
- Use pilot results to finalize schedule, pricing, and production scope.
Recommended decision: Proceed with a technical discovery and proof-of-concept phase before committing to full production implementation.
Professional Note
Assumptions & Disclaimer
This proposal is a conceptual solution design prepared under the assumption that Accela data can be delivered in a Microsoft SQL Server–compatible and vendor-authorized form. Final architecture, licensing, restore procedures, security controls, and project pricing must be confirmed after technical discovery with the City, Accela, and the hosting provider.