Frappe Insights for ERPNext dashboards: when teams implement it
Finance, operations and leadership teams often need the same ERPNext numbers every week, and each new question tends to become another custom report or a request to someone who can write SQL. Frappe Insights is an open source business intelligence app from the Frappe team that builds queries through a no-SQL query builder and assembles them into drag-and-drop dashboards with filters. Teams usually implement it when recurring dashboards are the main gap, when the people asking the questions cannot or should not write queries, and when they want the dashboards to live next to the ERPNext data they describe.
The decision rests on a few questions to settle before any build starts. Confirm which databases your data will sit in, since Insights supports MySQL, PostgreSQL, DuckDB and BigQuery, and confirm whether ERPNext's own database is the source or whether data is copied elsewhere first. Decide who owns each dashboard, who may see which figures, and how those rules match the roles and permissions already set in ERPNext. Also check whether the figures you need can be expressed as joins and aggregations in the query builder, because that determines whether Insights will be enough or whether a custom report is still the better tool.
This page covers how Insights is built and what it connects to, how the query builder handles joins and charts, the points where it fits and where it does not, and the work involved in running it on your own infrastructure. The sections that follow give a fuller view of each step, so you can judge whether your reporting needs are a fit before asking for a quote.
What Frappe Insights does on ERPNext data
Frappe Insights is a BI app built on the same framework as ERPNext, so it can sit beside your ERPNext data without a separate SQL layer. The cards below separate what the documentation describes from what you should confirm against the current release before you commit to dashboards built on it.
No-SQL query builder
Insights documents a query builder that lets analysts assemble questions over connected tables without writing SQL. Analysts can still reach the data they need, but the queries they build are only as clean as the table relationships behind them. Before building, confirm which ERPNext DocTypes and child tables the builder exposes in your version, and test one sales or stock question end to end against your own data.
Drag-and-drop dashboards with filters
Dashboards in Insights are assembled from saved queries and charts, with filters that apply across the layout. This suits finance and operations reviews where the same question is asked by company, period or warehouse. Check how filters behave when a dashboard mixes queries from different sources, and who can edit a shared dashboard once it is published.
Charts built on eCharts
Visualizations are rendered with eCharts, which gives a wide set of chart types for trends, comparisons and breakdowns. Chart choice matters more than chart count: a stock ageing view and a margin trend need different axes and aggregations. Confirm which chart types your target users need and whether exports or embedded views meet your reporting obligations.
Frappe Framework and Frappe UI base
Insights is built on the Frappe Framework and Frappe UI, the same stack as ERPNext. This matters for operations: it shares the framework's release cadence, bench-based deployment and app management. Plan the Insights app into the same version pinning and upgrade testing you use for ERPNext, rather than treating it as a separate tool with its own lifecycle.
Ibis composes the SQL
Insights uses Ibis to compose SQL queries from the builder's steps. Teams that audit reporting logic should ask how generated SQL is inspected and logged, and whether a reviewer can see the exact statement behind a number that goes into a board pack. Confirm this in the current documentation before relying on it for controlled reporting.
Joins to confirm before scoping
Multi-table questions, such as sales invoices joined to payment entries and customers, depend on join support in the query builder. Describe the joins you need in a short requirements list, then check the current Insights docs for supported join types and key handling. Treat any join you cannot confirm as a build risk and test it on a copy of production data.
Which databases Frappe Insights connects to
Most ERPNext sites store their transactional data in MariaDB or MySQL, so this is the option many teams start with. Frappe Insights supports MySQL, and the ERPNext database is the source of truth for ledgers, stock, sales and purchasing records. Connecting directly avoids a copy process, but it means analysts query the production database, so the read-only account and query load need deliberate planning.
- Dedicated read-only user: Create a separate database user with SELECT privileges only, limited to the ERPNext site schema, instead of reusing the Frappe administrator account.
- Replica first: If your site runs on a replica or read-only instance, point Insights there so dashboard queries do not compete with posting transactions.
- Table naming: ERPNext stores each DocType in a
tabSales Invoice-style table, so expect joins across child tables such as item lines and payment entries. - Permissions in Insights: Database access does not apply ERPNext role permissions, so restrict who can open dashboards that expose sensitive fields such as salaries or customer bank details.
- Heavy queries: Large aggregations across general ledger entries can slow the site, so schedule refreshes outside month-end close.
Confirm your database engine and version with your administrator before connecting, and check how your hosting provider handles replicas and user creation.",
When analysts need broader data or want isolation from the ERPNext site, many teams load ERPNext data into a separate PostgreSQL database and point Insights at it. This adds an extraction step, but it keeps reporting load off the operational system and lets you combine ERPNext data with data from other applications. The trade-off is that someone owns the pipeline and its freshness.
- Separate reporting schema: Store extracted tables in their own schema so the BI user cannot see unrelated tables.
- Read-only role: Grant the Insights role only
CONNECT,USAGEandSELECTon the reporting schema, with default privileges for future tables. - Refresh design: Decide whether the pipeline runs as full reloads or incremental loads keyed on modified timestamps, and document the expected lag.
- Network path: Restrict the connection to the Insights host and enforce TLS.
- Column controls: Drop or hash personal data columns in the extract rather than relying only on dashboard filters.
Confirm with your data team who maintains the extract job and what it costs to run, since that work recurs with every ERPNext upgrade that changes table structures.",
DuckDB is an embedded analytical database stored as a local file, which suits small teams, one-off analysis, or exported snapshots that do not need a shared server. Insights can query a DuckDB file, so you can load a periodic export of ERPNext tables and build dashboards without provisioning another database service. The data is only as current as the last export, and a single file is harder to share safely than a managed server.
- Export source: Generate the export from a read-only replica or a reporting copy, not from the live site during business hours.
- File location: Keep the
.duckdbfile on a server with restricted file system permissions, and back it up like any other business record. - Snapshot naming: Include the export date in file names so dashboards and auditors can tell which period the data covers.
- Concurrency: Expect limits when many users write or refresh at once, so treat it as a read-mostly store.
- Personal data: Exported files are copies, so apply the same retention and access rules as the source system.
Confirm that the DuckDB version your Insights deployment supports matches the one that created the file, and test a restore before relying on the snapshot.",
If your organization already runs analytics in Google Cloud, you can load ERPNext data into BigQuery and connect Insights to it. This fits organizations that want to join ERPNext data with web, marketing or operational datasets already in BigQuery. Cost depends on query volume and storage, and the export pipeline and service account setup become part of the platform you operate.
- Service account scope: Create a service account with read access to the specific dataset only, and rotate its keys under your key management policy.
- Export method: Decide between scheduled extracts from ERPNext and a streaming or batch pipeline, and document the lag each one creates.
- Cost controls: Set query budgets and dataset-level alerts, because dashboards that refresh often generate recurring query charges.
- Data residency: Confirm the BigQuery region meets your data location requirements before copying personal or financial records.
- Dataset design: Keep raw ERPNext extracts separate from curated tables that dashboards use.
Confirm with your cloud team the billing owner, the region, and how the service account keys are stored, since these decisions affect both cost and audit scope.
Insights, Frappe built-in reports or Metabase: which fits
This table compares Frappe Insights with ERPNext's built-in report builder and script reports, and with a general BI tool such as Metabase or Apache Superset, across who builds, where data lives, access control and upkeep. Read each row against your own reporting owners and data sources, and use the final rows to decide which option fits a given report.
| Criterion | Frappe Insights | ERPNext report builder or script report | General BI tool (Metabase or Superset) |
|---|---|---|---|
| Who usually builds it | Finance, operations or analysts who need queries and dashboards without writing SQL | Functional consultants or developers, with report builder for simple lists and Python for script reports | Data or BI team, often with analysts working in saved questions or SQL editors |
| Where the data is read from | Connected databases such as MySQL, PostgreSQL, DuckDB or BigQuery, which you configure as data sources | The ERPNext site database, through the ERPNext document model and permissions | Any database the BI team connects, which may include ERPNext and other systems |
| Access control | Governed by the Insights setup and the database accounts behind each source. Confirm how it maps to ERPNext role permissions | Follows ERPNext role and user permissions, so users see only what their roles allow | Governed by the BI tool's own user and group model. Confirm how it maps to ERPNext permissions |
| Best fit for | Cross-document dashboards and ad hoc analysis where business users need filters and charts quickly | Operational lists, printable outputs and reports that must match ERPNext logic exactly | Enterprise reporting across several systems, with a shared semantic layer owned by data teams |
| Ownership of logic | Query logic lives in Insights objects that someone must document and review | Logic lives in the ERPNext codebase or customization, reviewed with other app changes | Logic lives in saved questions or models, managed by the BI team |
| Ongoing maintenance | Confirm upgrade and compatibility steps for the Insights app alongside ERPNext upgrades | Changes travel with ERPNext upgrades, so script reports need regression checks at each step | Separate tool to patch, back up and upgrade alongside ERPNext |
| When it beats a custom report | A question changes often, spans several sources and business users need to explore it without a developer | Not the preferred choice for changing questions, where a developer would need to rewrite the report each time | Not the preferred choice when only ERPNext data is needed and a second platform adds more upkeep than value |
| When it does not | Official statements, audited figures or printed forms that must follow ERPNext logic exactly | Exploratory analysis across many data sources or dashboards that finance changes weekly | Small teams with ERPNext data only, where the extra platform is hard to justify |
How to roll out Insights dashboards on ERPNext
This sequence moves from database access to published dashboards that finance and operations owners trust. Each step closes with a named output, so the team can see what is finished before the next step starts.
Confirm database and read access
Agree with the ERPNext administrator which database Insights will read, where it is hosted, and which account it will use. The account should have read-only rights to the ERPNext tables needed for the first questions, and the team should confirm that the database type is one Insights supports (MySQL, PostgreSQL, DuckDB or BigQuery). Confirm that the read account cannot write to production tables, and record the host, database name and account owner in the rollout notes. Done means a test connection succeeds from the Insights server and the access record is signed off by the person who owns the ERPNext database.
Connect the ERPNext data source
In Insights, add the ERPNext database as a data source using the read-only account from the previous step. Browse the tables and confirm that the ERPNext DocTypes you expect are present, such as sales invoices, journal entries, stock ledger entries and purchase orders. Check that the sample rows match what users see in ERPNext for a few known documents. Done means every table the first dashboard needs is visible, and the team has noted any tables that are missing or hidden by ERPNext customizations.
Define first questions with owners
Meet with the finance and operations owners to choose three to five business questions for the first dashboard, such as open receivables by age or stock value by warehouse. For each question, write down the metric definition, the filters users need, the time basis (posting date or transaction date) and the ERPNext report it should match. Name one owner for each metric. Done means a written metric list exists with an owner, a definition and the ERPNext report used as the reference for every item.
Build queries and joins
Using the no-SQL query builder, build each question as a query from the approved definition. Join ERPNext tables through their keys, such as invoice headers to item rows or stock ledger entries to items and warehouses, and apply the status and cancellation rules that ERPNext reports use. Keep cancelled and draft documents out unless the definition says otherwise. Done means each metric has a saved query that runs without errors and whose join logic is written down for the owner to read.
Add charts and filters
Turn each saved query into a chart suited to the question, then add dashboard filters for the dimensions users asked for, such as company, date range, cost center or warehouse. Check that filters apply to every chart on the dashboard and that defaults show the period the owners expect. Test the layout with a user who has the access rights planned for real use. Done means the dashboard loads with the agreed filters, and the test user sees the same charts and data scope the rollout plan specifies.
Reconcile numbers against ERPNext reports
For each metric, run the matching ERPNext report with the same company, period and filters, and compare totals line by line for at least one period. Investigate any difference, which often comes from posting date versus transaction date, multi-currency rounding, or cancelled documents. Record each reconciled figure and the reason for any accepted gap. Done means every dashboard number either matches the ERPNext report or has a documented explanation signed off by the metric owner.
Publish and hand over metrics
Publish the dashboard to the users who need it, and confirm that access matches the ERPNext role model so that no one sees data they could not see in ERPNext. Hand over a list of owned metrics that names each owner, the definition, the saved query, the reconciling ERPNext report and the review date agreed with the owner. Agree who changes queries when ERPNext schema or customizations change. Done means users have access, the metric register is with the owners, and the team has a named contact for future changes.
What drives the effort of an Insights rollout
This table lists the factors that expand or shrink an Insights rollout on ERPNext data, with the reason each one matters and how to keep it under control. Read it before scoping, because most overruns come from unagreed metric definitions and a first release that tries to cover every report at once.
| Driver | Why it changes effort | How to keep it down |
|---|---|---|
| Number of metrics | Each metric needs a definition, a source query, a test against known totals and a sign-off from the owner of the number. | Agree a short list of metrics that leadership will act on, and park the rest for a later release. |
| ERPNext doctypes to join | Revenue, stock and margin questions often span Sales Invoice, Payment Entry, GL Entry, Stock Ledger Entry and Item tables, and each join adds validation work. | Start from the reporting tables ERPNext already maintains, such as GL Entry for ledger figures, and confirm which joins the finance team expects. |
| Multi-company logic | Consolidated views need intercompany eliminations, company filters on every chart and a clear rule for which company owns each record. | Decide in the first workshop whether dashboards are per company, consolidated, or both, and build one pattern before copying it. |
| Multi-currency logic | Amounts recorded in transaction currency and company currency can disagree, and exchange gains and losses must be shown consistently. | Choose one reporting currency and one conversion rule, then document which field each chart reads. |
| Data quality in source records | Missing customer groups, unposted documents, duplicate items or inconsistent units produce numbers that look wrong and trigger rework. | Run a data check on the source tables before building, and agree which errors are fixed in ERPNext and which are excluded in the query. |
| Access rules for financial data | Salary, margin, bank and supplier balance data may need row or dashboard restrictions that mirror ERPNext roles, and each restriction must be tested with real user accounts. | Confirm the sensitive datasets early, then publish those dashboards to a limited group rather than building one dashboard with hidden tabs. |
| Number of dashboards with filters | Each filter multiplies the combinations users can select, and every combination needs to be checked for correct totals and empty states. | Limit filters to the dimensions people actually slice by, such as period, company and warehouse, and reuse them across dashboards. |
| Scope of the first release | A first release that covers every department turns metric debates into a long queue and delays the first dashboard people use. | Release one or two dashboards for a single function, collect use feedback, then add the next set on the same metric definitions. |
Questions teams ask before building Insights dashboards
What does a Frappe Insights rollout cost, and what drives the cost?
The cost depends on how many dashboards and data questions are in scope, how clean the ERPNext data is, and who builds and maintains the queries. Frappe Insights is open source, so the software itself carries no license fee. You pay for the hosting you run it on, the people who design metrics and build dashboards, and any work to connect and secure the databases it reads. Ask for a quote that separates one-time work (metric definitions, query build, access setup, training) from recurring work (dashboard upkeep, upgrades, user support). Ask the provider to list the number of dashboards, the data sources and the sign-off steps included. DeployOpen does not publish prices, so a quote follows from the written scope.
How long does an Insights dashboard project take?
Duration is set by the number of metrics that need agreed definitions, the state of the source data, and how many review cycles stakeholders need. A small set of sales or stock views built on clean ERPNext tables is faster than a finance pack that must reconcile against ledger entries. Before work starts, confirm which metrics are already defined in writing, who approves each one, and whether a test copy of the database is available. Those three items usually decide the timeline more than the tool does. Your engagement should state the schedule in the written scope.
What must our internal team provide for an Insights project?
Three things matter most: a named metric owner for each dashboard, read access to the database that holds the ERPNext data, and a sign-off person who checks results against the source system. Metric owners define what counts as revenue, open orders or stock on hand. Database access should be read-only and scoped to the tables needed. Sign-off protects the business from charts that look right but use the wrong filter. Also agree who handles access requests and who receives dashboards after go-live, so the work does not stall waiting for a single person.
Can our analysts build Insights dashboards without help?
Yes, for straightforward questions. Insights provides a query builder that does not require SQL, along with drag-and-drop dashboards and filters, so analysts who know the business data can build many standard views themselves. Help is more useful for joins across several ERPNext tables, for metrics that must match finance totals, and for access control on the underlying database. A practical split is to have a specialist set up data sources, permissions and the core metric definitions, then let analysts extend dashboards inside those boundaries. Confirm the exact query and dashboard features against the Frappe Insights introduction before you plan the split.
How does Frappe Insights compare with custom ERPNext reports?
Insights suits exploratory analysis and dashboards that draw from several sources, while custom ERPNext reports suit operational documents that users need to open, print or act on inside the ERP. Insights reads from supported databases such as MySQL, PostgreSQL, DuckDB and BigQuery, so it can combine ERPNext data with other data. Custom reports stay within ERPNext permissions and are often the better choice for a report that must respect document-level rules. Many teams use both: reports for transactional work, Insights for management views. Check the Frappe Insights introduction for the current list of supported databases before you decide.
Which database access and permissions should Insights use?
Insights should connect with a database account that has read-only access to the tables it needs, not the account ERPNext uses to write data. This limits what a dashboard or a misconfigured query can change. Because ERPNext permissions apply inside the application, not in the database, confirm which user roles can see which figures in Insights and restrict the data source accordingly. Ask your engineers to review network access between Insights and the database, and to record who can publish dashboards. Your security team should approve this design before the first production dashboard goes live.
What operating work follows go-live?
After go-live, someone must keep metric definitions current, check that dashboards still match ERPNext after upgrades, manage new users and retire unused views. ERPNext releases frequent minor updates, so schedule a regression check of key dashboards after each platform upgrade. Assign an owner for the Insights instance itself, including backups of its configuration and updates to the application. Ask the implementation provider whether ongoing support, upgrades and testing are covered by a retainer or billed separately.
Keeping Insights dashboards accurate after go-live
The main risk after go-live is not a broken chart but a number that quietly changes meaning. Assign a named owner to every metric definition, such as how revenue, open receivables or headcount is filtered and joined, and record that definition next to the dashboard. Insights makes it easy for anyone with access to build a new chart from ERPNext tables, so without an owner two dashboards can report different totals for the same question. Set a review rhythm with finance or HR, and require that a change to a shared metric is approved by the owner before it is saved.
ERPNext upgrades are the most likely cause of silent breakage. Queries built in Insights reference DocType tables and fields directly, so a schema change, a renamed field or a module split can alter results or return errors. Before each ERPNext upgrade, list the dashboards and queries that depend on changed DocTypes, rerun them against a copy of production, and compare totals with the last closed period. Confirm the supported Insights version for your ERPNext release in your own environment, since the compatibility matrix is not covered by the facts supplied for this page. Also confirm what happens to Insights data sources and saved queries when the database backend changes.
Dashboards that show salaries, ledger balances or disciplinary data need the same access review as the source reports. Insights runs queries against the database under its own configured connection, so confirm which database user it uses and whether that user can read tables that ERPNext role permissions would hide from a given employee. Review who can create data sources, edit queries and share dashboards at least once a quarter. Before depending on the open-source project, check the frappe/insights repository for its license, recent release activity, open security issues and the support policy for the versions you run.
How DeployOpen scopes and delivers ERPNext BI work
DeployOpen plans, implements, hardens, documents and supports self-hosted open-source platforms inside your own infrastructure, and Frappe Insights dashboards on ERPNext data follow the same pattern. We agree the data sources, metrics, access model and hosting in writing before build starts, then hand over the dashboards, queries and runbooks your team needs to maintain them.
How we scope
- Inventory the ERPNext reports, dashboards and spreadsheets your leaders use today and rank them by business value.
- Confirm which database backs the ERPNext site and whether Insights connects to it directly or to a replica.
- Agree the written scope, deliverables and timeline with you before any dashboard work starts, as all engagements are fixed-scope.
How we build
- Build Insights queries with the no-SQL query builder, documenting joins, filters and calculated fields for each metric.
- Design dashboards with agreed filters, chart types and definitions so finance and operations read the same figures.
- Test each dashboard against source ERPNext reports and sample records, and review access so users see only permitted data.
How we hand over
- Document each query, its source tables, refresh expectations and the owner responsible for every metric definition.
- Walk your team through creating new charts and filters so routine changes stay in-house after handover.
- Provide the written record of connections, roles and hosting choices so your security and IT teams can review them.
How we support
- Offer ongoing support, upgrades and testing as a retainer, covering Insights releases and ERPNext version changes.
- Re-check dashboards after ERPNext upgrades, since field names and report logic can change between versions.
- Ask for a quote built from the written scope, which states one-time build effort separately from recurring support.
