ERP & CRM systems
How to Integrate Power BI Software with FileMaker, Supabase and Bespoke ERPs
Installing Power BI Desktop and pointing it at your data is only the beginning. The real work sits in data modelling, row-level security, embedding inside your existing application, and automating refreshes so reports stay current without manual effort. Each step has specific requirements depending on whether your source system is FileMaker, Supabase or a custom-built ERP.
Key takeaways
- Edition choice matters: Desktop is free and local; Pro adds publishing and collaboration; Premium Per User adds advanced capabilities for power users.
- FileMaker has two paths: ODBC suits simple read-only reporting against stable schemas; the Data API suits flexible, scheduled extracts.
- Supabase uses the native connector: Import mode for most SME workloads; DirectQuery only where real-time data is essential.
- Row-level security scales cleanly: a dynamic RLS security-mapping table handles multi-site and multi-tenant data without one role per site.
- Embedding needs the Embed API: production integrations require service principal authentication, not an iFrame shortcut.
- Schema preparation is everything: poor source modelling is one of the most frequent causes of slow, unreliable reports.
What is Power BI software and which edition does your business need?
Microsoft Power BI is a business intelligence platform that connects to data sources, transforms data through Power Query, builds relational models using DAX (Data Analysis Expressions) measures, and renders interactive reports and dashboards. In practice it is the reporting and analytics layer on top of your existing data — a FileMaker Server, a Supabase Postgres database, or a custom ERP. Choosing the wrong tier early costs time in licence migrations later:
| Edition | Primary purpose | Sharing | Best suited for |
|---|---|---|---|
| Power BI Desktop | Build reports and models locally (free) | Limited on its own | Individual report authors and prototyping |
| Power BI Pro | Publish, share, collaborate and manage reports in the Power BI service | All licensed Pro users | Small and medium teams |
| Premium Per User (PPU) | Pro features plus larger models and paginated reports | All PPU-licensed users | Power users needing Premium features without dedicated capacity |
Power BI Pro is priced at US$14/user/month according to Microsoft's pricing page. Premium Per User is available at R404.90/user/month on a 12-month contract through Vodacom Cloud. Desktop remains free for local report authoring regardless of tier.
Which licence makes practical sense for an SME?
Most SMEs land on Desktop for report authors and Pro for anyone who publishes or shares. Premium capacity is worth the overhead only when viewer numbers are large: at F64 Fabric capacity, viewers can consume content without individual paid licences, while creators still need authoring licences. For a team of five to fifty people, Pro is the right default.
How does Power BI software connect to a FileMaker database?
The two main integration paths between Power BI and a FileMaker Pro or FileMaker Server deployment are ODBC and the FileMaker Data API. ODBC uses the FileMaker client driver installed on the same Windows machine as Power BI Desktop, communicating with the hosted file over TCP port 2399. The Data API is a REST interface returning JSON, suited to scheduled or event-driven extracts where you need finer control over exposed fields and layouts.
ODBC driver vs FileMaker Data API
ODBC is faster to set up for read-only reporting against stable schemas: install the 64-bit FileMaker ODBC driver, create a 64-bit System DSN in Windows, then connect through Power BI's ODBC connector. The driver's bitness must match the application, not just the operating system. The Data API is the better choice when you need incremental extracts, want to limit which data is exposed, or are building server-side automation that drives the refresh cycle.
Modelling FileMaker schemas in Power Query
FileMaker's relational structure does not map directly to the star schema Power BI works best with. Portals, value lists and multi-key relationships need flattening before you build visuals: remove calculated portal columns, set correct data types, and resolve many-to-many relationships through bridge tables. This is the step most often skipped in rushed integrations — and the one that saves the most rework.
Row-level security for multi-site FileMaker deployments
When one FileMaker layout returns records from several sites or practices, Power BI row-level security (RLS) enforces data boundaries at the report layer. A dynamic approach uses a security-mapping table with one row per user and permitted site, related to the fact and dimension tables, filtered by a single DAX expression:
UserSecurity[UserKey] = USERPRINCIPALNAME()A practice manager in Cape Town sees only their site's data, even though the underlying FileMaker query returns a wider result set.
How do you integrate Power BI with Supabase and Postgres?
Supabase exposes a standard Postgres database, so Power BI connects through its built-in PostgreSQL connector. Copy the direct connection string from the Supabase dashboard and enter the host, port (5432) and database name separately — Power BI does not accept a full PostgreSQL URI. Supabase enforces SSL, so enable the encrypted connection option when prompted.
Import mode vs DirectQuery
For most SME reporting, Import mode is right: data loads into Power BI's in-memory model, refreshes run on a schedule and reports stay fast. DirectQuery is worth it only where near-real-time data is genuinely non-negotiable, such as a live logistics dispatch board, because every interaction sends a live query. Supabase direct connections use IPv6 by default; on an IPv4-only network enable the IPv4 add-on or use the Session Pooler connection string.
Schema design and pgvector considerations
Schemas built for AI features — pgvector columns and edge function outputs — need preparation. Exclude raw vector columns from the semantic model; they carry no meaning in a tabular report. Expand JSONB fields into typed columns, and surface any semantic search results as plain fields in a Postgres view before connecting Power BI.
Scheduled refresh and credentials
Use a dedicated read-only Postgres role for Power BI rather than a broad default role, limiting the impact if credentials are ever compromised. Align the refresh schedule with any automation that updates the data, and replicate credentials and SSL configuration exactly on any on-premises gateway machine.
Embedding Power BI software in a bespoke ERP or CRM
Embedding moves reports out of the Power BI service and into the application your users already work in. The main patterns are a simple iFrame (quick, limited control), the Power BI Embed API with service principal authentication (full control over security and token scoping), and publish-to-web for non-sensitive public data only.
Why the Embed API with a service principal
For bespoke ERP and CRM projects, the Embed API is almost always right. The host application enforces row-level security and single sign-on, and the server generates a short-lived embed token using a service principal registered in Microsoft Entra ID, sending only that token to the browser. The client secret stays server-side at all times.
Sector examples
- Medical practices: patient throughput and billing dashboards inside a FileMaker patient administration system.
- Pathology labs: instrument result trends inside a custom LIMS built on Supabase.
- Logistics: live consignment and fleet data within a dispatch ERP.
- Financial services: automated monthly management reporting from a bespoke CRM pipeline.
How Blue Line Solutions builds Power BI into custom systems
We build custom FileMaker Data API connectors, model FileMaker and Postgres schemas into clean star schemas, and embed Power BI via the Embed API inside the custom ERPs and CRMs we build for clients in healthcare, finance, logistics and engineering. Because we build the underlying system and the integration layer, there are no third-party handoffs — a schema change in FileMaker or Supabase propagates cleanly through to the Power BI model.
The integration checklist: from source modelling to automation
- Audit the source schema: identify fact tables (transactions, events) and dimensions (customers, products, locations, dates).
- Choose the right connector — ODBC or Data API for FileMaker, the Postgres connector for Supabase.
- Validate data types and confirm key fields join cleanly before any Power Query work.
- Model a star schema and configure dynamic row-level security.
- Choose Import or DirectQuery, then set up scheduled or incremental refresh.
- Embed with the Embed API and a service principal; automate delivery.
Performance tuning for live operational data
The main levers are Import vs DirectQuery, aggregation tables for high-volume fact tables, incremental refresh policies, and query folding to push transformations back to the source database. As a practitioner rule of thumb, a scheduled refresh that consistently takes more than ten minutes signals the model needs attention. Give incremental refresh a window wide enough to capture backdated ERP transactions.
Automating report delivery
Scheduled refresh handles most needs. For event-driven scenarios, the Power BI REST API lets an ERP event — a month-end close or a lab instrument upload — trigger a dataset refresh without manual intervention.
Bringing it all together
Power BI software is a capable business intelligence layer for FileMaker, Supabase and bespoke ERP environments. The value comes from clean source modelling, the right connector, properly configured row-level security, and embedding that fits the way your users already work. Most SMEs start with Desktop and Pro, build on their existing data infrastructure, and automate once the model is stable and trusted. Blue Line Solutions has been building custom FileMaker, Supabase, ERP and CRM systems for over two decades — talk to us about where a Power BI integration fits into yours.
FAQs