How to connect Power BI to TOTVS Protheus
Short answer
Most Protheus sites connect Power BI to the underlying SQL Server, Oracle or PostgreSQL database through views and an on-premises data gateway, ideally into a small warehouse layer. REST is an option for smaller volumes, and natural-language Q&A can sit on top of the same model.
Protheus stores data in alias-named tables like SC5, SD2, SE1 and SB2, with a filial column for branches and a deletion flag in D_E_L_E_T_. Power BI can read them, but a model built directly on these tables is hard to maintain and produces wrong totals if the filters are missed.
The usual pattern is to build views or a warehouse that renames columns, applies branch and deletion filters, joins headers to items, and handles currency and fiscal fields. Power BI then reads clean fact and dimension tables through a data gateway when the database is on-prem.
TOTVS also offers its own analytics tooling in some product lines, so the first question is whether that already covers your reporting. Power BI makes sense when you need to combine Protheus with other sources or want the wider Microsoft ecosystem.
What teams use it for
Sales and margin by customer and product
Facts from SD2 and SF2 joined to customers and items, with cost from the stock movement tables, showing margin trends by region and family.
Purchasing and supplier performance
On-time delivery from SC7 promised dates against SD1 receipts, price variance against last price, and spend by category.
Inventory and coverage
Balances from SB2 with consumption history, days of coverage, obsolete stock and slow movers by warehouse.
Production and OEE-adjacent views
Planned versus produced quantities from SC2 and the related appointment tables, scrap, and orders delayed against due date.
Cash and receivables aging
SE1 and SE2 aging buckets, overdue by customer, and expected inflows by week for finance.
Fiscal and tax reconciliation
Comparison of invoice totals and tax lines between SF1/SF2 and the books, to spot gaps before closing.
TOTVS Protheus integration surface
- Direct database access to the Protheus schema on SQL Server, Oracle or PostgreSQL, using read-only views, with the Power BI on-premises data gateway for scheduled refresh.
- REST services from the Protheus application server (standard or custom TLPP), useful for smaller datasets or when direct DB access is not allowed.
- A staging database or warehouse populated nightly, which isolates reporting load from the transactional system.
- The SX3 data dictionary, used to generate readable column names and to find custom fields for the model.
- TOTVS analytics offerings in your release, which may cover standard reports without extra tooling; availability varies.
How it works
- 1
Decide the data path
Direct views for a quick start, a staging or warehouse layer for scale and history, REST only for light needs. Confirm that your DBA allows reporting queries on the production database.
- 2
Build clean views
For each business entity create a view that filters D_E_L_E_T_, includes the filial, renames columns using SX3 titles, and formats dates stored as text strings.
- 3
Install the on-premises data gateway
Install it on a server that can reach the database, register it with your Power BI tenant, and use a read-only database login.
- 4
Model the data
Create a star schema with date, customer, item, supplier and branch dimensions. Set row-level security by branch where users should not see all companies.
- 5
Schedule refresh
Use incremental refresh on large tables such as movements and invoice items, and refresh outside peak transaction windows.
- 6
Add natural-language questions
Use Power BI's Q&A or Copilot features where licensed, or add a separate assistant that queries the same views so the answers match the dashboards.
Example flows
Nightly sales model refresh
Trigger: Gateway runs the scheduled refresh at 02:00.
- Views return new and changed SD2 rows by date.
- Power BI appends to the sales fact table.
- Margin measures recalculate with updated costs.
- Morning dashboards show yesterday's close.
Result: Sales managers see trusted numbers without exports from Protheus.
Late supplier alert for buyers
Trigger: Dashboard data flags lines past the promised date.
- Model compares SC7 dates with received quantities from SD1.
- A report page lists late lines by supplier.
- A subscription emails buyers the page each Monday.
Result: Buyers start with a ranked list, and the same logic can later feed an assistant.
Keeping data on-prem
For databases that must stay inside your network, use the on-premises data gateway so Power BI Service only receives the imported model, or consider Power BI Report Server if policy forbids the cloud service entirely. Keep the warehouse on your own servers. For natural-language questions without cloud processing, add a local assistant that queries the same views through a read-only role and uses an open-weight model served on local GPUs, so questions and answers never leave the site. Feature differences between Power BI Service and Report Server apply, so confirm what you need.
Security checklist
- Use a read-only database login for the gateway and grant access only to reporting views.
- Apply row-level security in the model by filial and by cost center where required.
- Restrict workspace access and sensitivity labels for reports with payroll, pricing or personal data.
- Keep gateway servers patched and monitor refresh logs for unexpected queries.
Ways to get it done
Direct views plus gateway to the Protheus database
Effort: One to three weeks for a first model.
Quick for a few dashboards when the DBA allows read load.
Staging database or warehouse with a proper star schema
Effort: Several weeks to a few months.
Best for history, multiple companies and combining other data sources.
Partner delivery, including natural-language Q&A, such as Netray
Effort: A pilot in a few weeks.
Fits when you want dashboards plus an on-prem assistant over the same model.
Pitfalls
- !Importing base tables without D_E_L_E_T_ and filial filters, which double-counts and mixes companies.
- !Dates stored as text in many Protheus columns, causing time-intelligence failures until converted.
- !Heavy refresh queries during invoicing or closing hours, slowing transactional users.
- !Custom fields missed because the model used standard column lists only.
- !Dashboards whose totals differ from Protheus reports because cost and tax logic was not replicated; reconcile before launch.
Frequently asked questions
Does Power BI connect directly to Protheus?
Power BI connects to the Protheus database (SQL Server, Oracle or PostgreSQL) or to REST services. There is no single built-in Protheus connector assumed here. Most sites use views plus a data gateway, which is dependable and keeps logic in one place.
Do we need a data warehouse?
Not for a first set of dashboards, but it helps as you grow. A staging layer isolates reporting load, stores history, and gives you a single place to apply branch filters, currency rules and naming, which saves repeated work in every report.
Can we use REST instead of the database?
Yes for small to medium datasets, or when direct database access is not permitted. REST services pass through Protheus logic and permissions, but large history loads are slower and need paging, so many sites combine both.
How do we handle multiple companies and branches?
Keep company and filial as dimensions, apply them in the views, and use row-level security so each user sees only their entities. Test with real users, because cross-branch leaks usually come from missing filters in a single table.
Can users ask questions in plain language?
Power BI offers natural-language features where licensed, and quality depends on your model naming. An alternative is an assistant that queries the same views, which keeps answers consistent with dashboards and can run on-prem with an open model.
Want Power BI working on your TOTVS Protheus?
A free 30 minute assessment: we map the integration path for your version and setup, and where it should run.
Get a free assessment