Synapse Software
Power BI vs. Purpose-Built Archive: What You Lose When You Flatten Cherwell Data
The Plan That Sounds Reasonable (Until You Try It)
I’ve been hearing a new version of the “we’ll handle the historical data ourselves” plan from Cherwell admins lately. It goes like this: “We’ll just point Power BI at the Cherwell SQL database and build some dashboards. That’s our archive.”
I understand the logic. Power BI is already in most Microsoft enterprise environments. It connects to SQL Server natively. The Cherwell database is just a SQL Server database at the end of the day. So why not?
Because Power BI is a reporting tool. It’s designed to visualize aggregate data, build charts, and surface trends. It was never built to serve as a record-level archive for a platform with complex form configurations, dynamic expressions, and multi-layered object relationships.
Let me explain what actually happens when you try to use Power BI as your Cherwell data retention strategy.
The Cherwell Power BI Connector Is Already Dead
Before we get to the SQL database approach, let’s address the obvious path. Cherwell shipped an official Power BI Data Connector that connected through the Cherwell REST API. You could pull saved searches directly into Power BI and build reports.
That connector required a running Cherwell instance with an active REST API. After December 31, 2026, the Cherwell application shuts down. The REST API goes with it. The connector stops working.
Even while it was running, this connector had well-documented problems. The maximum scheduled refresh token lifespan for Cherwell REST API client IDs is 1,440 minutes (24 hours). That means even daily scheduled refreshes could fail if the timing drifted by a few minutes. The Power BI community forums are full of posts about authentication failures, token expiry, and daily refresh jobs that silently broke overnight. Multiple threads end with users abandoning the connector and going straight to SQL queries instead.
So the real scenario most teams will face is: take a SQL backup of the Cherwell database, keep it on a server somewhere, and connect Power BI directly to it.
What Power BI Sees When You Connect to a Cherwell Database
Here’s where things get technical. Cherwell’s SQL Server database doesn’t store data the way most people expect. The database tables don’t map cleanly to the business objects you see in the Cherwell interface.
Cherwell uses a metadata-driven architecture. The platform reads configuration data from the database to determine how to display forms, which fields to show, how relationships between objects work, and how expressions compute. The application layer does the heavy lifting. The database is the raw material.
When you point Power BI at that database, you see raw SQL tables. Not business objects. Not form layouts. Not the relationships your team configured over the past decade. Just tables and columns with internal naming conventions that make sense to the Cherwell application but not to a human looking at them in Power BI.
Five Things Power BI Cannot Reconstruct
1. Cherwell Form Views
When someone opens an incident record in Cherwell, they see a carefully arranged form: fields in specific positions, tabs grouping related data, journal entries displayed chronologically, related items linked inline. All of that is defined in Cherwell’s configuration metadata.
Power BI doesn’t read Cherwell metadata. It reads SQL tables. You can build a Power BI report that shows columns from the incident table, but that’s not the same thing as seeing the record in context. When your compliance team needs to review a specific HR case from 2023, they don’t want a bar chart. They want to see the record the same way the original handler saw it.
2. Expression-Driven Fields
Cherwell forms frequently include fields whose values are computed dynamically by the application layer through “expressions.” These combine data from multiple underlying fields to produce a display value: SLA countdowns, composite status indicators, calculated dates.
Power BI can’t execute Cherwell expressions. It can read whatever static value was last written to the database, but the computational logic is gone. If your team relied on those computed fields to interpret records, that interpretation layer doesn’t exist in Power BI.
3. Complex Object Relationships
This is the most significant gap. Cherwell’s relationship model connects business objects through multi-table links, embedded object references, and nested hierarchies. An incident might be linked to a customer, a configuration item, a problem, a change request, journal entries, approval chains, and tasks.
In Power BI, you’d need to manually model every one of those relationships by writing SQL joins across dozens of tables. But Cherwell’s internal relationship definitions aren’t straightforward foreign key references. They’re stored in configuration metadata that the Cherwell application interprets at runtime. Rebuilding those joins from scratch requires deep knowledge of Cherwell’s internal data model.
I worked inside that codebase for three years as a senior software engineer at Cherwell. I can tell you firsthand: the relationship model is not something you reverse-engineer over a weekend with Power BI Desktop.
4. Attachments
Power BI doesn’t surface file attachments. Full stop. Cherwell stores attachments in the database in a way that’s designed to be read by the Cherwell application. You can’t click on an incident in a Power BI dashboard and open the screenshot that was attached to it, or the signed approval PDF, or the configuration export file.
For many compliance scenarios, the attachment is the evidence. Without it, the record is incomplete.
5. Record-Level Lookups
Power BI is built for aggregate analysis: trends over time, volume by category, SLA performance across teams. It’s excellent at that.
But the primary use case for archived ITSM data isn’t trend analysis. It’s: “Pull up incident #47,231 because an auditor wants to see the full record.” Or: “A customer called about an issue from 2022 and we need the complete history.”
Power BI isn’t designed for single-record retrieval in original form context. You’d be using a business intelligence tool as a glorified record viewer, without the actual record context.
The Maintenance Problem Nobody Accounts For
Even if you accept all of those limitations, there’s a practical problem. Someone has to build and maintain this.
Building useful Power BI reports against a Cherwell database requires mapping internal table names to business objects, understanding which columns correspond to which form fields, writing SQL queries that reconstruct at least some of the object relationships, and testing all of this against your specific Cherwell configuration.
That’s a project. And it’s a project that requires Cherwell-specific expertise. The number of people who understand Cherwell’s internal database schema is getting smaller every month. The ones who are left are busy migrating their own organizations to ServiceNow or Jira Service Management.
Who builds your Power BI reports? Who maintains them when something breaks? Who documents the table mappings for the next person?
What a Purpose-Built Archive Does Differently
A purpose-built archive for Cherwell data doesn’t try to reinterpret the database through an external tool. It reads Cherwell’s own configuration metadata directly from the database and uses it to reconstruct a familiar form view, with relationships, expressions, and attachment references intact. Minimal configuration. No SQL mapping required. No custom Power BI development. No ongoing maintenance of a reporting layer on top of a dead platform.
The archive reads the database. The database tells it how to display the data. That’s it.
When Power BI Still Makes Sense
Power BI is a great tool for aggregate reporting and data visualization. If you want to analyze historical ticket volumes, SLA trends, or workload distribution before you decommission Cherwell, go for it. Run those reports while the platform is still active and the REST API connector still works.
But if your goal is long-term data retention with record-level access, compliance readiness, and attachment preservation, Power BI isn’t an archival solution. It’s a reporting tool pointed at a database it wasn’t designed to interpret.
The question to ask is: “What will an auditor, a compliance reviewer, or a service desk agent actually need from this data two years from now?” If the answer is individual records in original context, Power BI isn’t the answer.
Frequently Asked Questions
Can Power BI connect directly to a Cherwell SQL database?
Yes. Power BI connects to SQL Server natively, and the Cherwell database is a SQL Server database. However, Power BI sees raw database tables, not Cherwell business objects. It cannot interpret Cherwell’s metadata configuration to reconstruct form views, relationships, or expression-driven fields.
Does the Cherwell Power BI Data Connector still work after EOL?
No. The official Cherwell Power BI Data Connector connects through the Cherwell REST API, which requires a running Cherwell instance. After the December 31, 2026 end-of-life date, the Cherwell application and its REST API stop functioning. The connector stops working.
Can Power BI show Cherwell attachments?
No. Power BI is a data visualization tool and cannot surface file attachments stored in the Cherwell database. Attachments like screenshots, signed documents, and uploaded files require a tool that understands Cherwell’s attachment storage format.
Is Power BI sufficient for Cherwell data compliance and audit readiness?
In most regulated environments, no. Compliance reviews typically require records in their original format with full context: form layout, related objects, attachments, and audit history. Power BI provides aggregate reporting and data visualization but cannot reproduce the original record context.
What is Cherwell’s metadata-driven architecture?
Cherwell uses a configuration layer stored in the database that defines how business objects are displayed, how fields are calculated, and how objects relate to each other. The Cherwell application reads this metadata to render form views. External tools like Power BI do not read this metadata, so they cannot reconstruct the original record presentation.
What does a purpose-built Cherwell archive preserve that Power BI does not?
A purpose-built archive reads Cherwell’s configuration metadata to automatically reconstruct familiar form views, object relationships, expression-driven fields, and attachment access. Records display in their original context with minimal configuration, without custom SQL development or ongoing maintenance.
Can I use Power BI for Cherwell data analysis before EOL?
Yes. While Cherwell is still running, the Power BI Data Connector or direct SQL queries work for aggregate analysis and reporting. This is a valid use case for trend analysis and workload reporting during the migration period.