Connect Power BI and Excel to SyteLine safely

Short answer

Don't point Power BI or Excel straight at SyteLine's tables with an admin login. Create a read-only login, put a small set of reporting views in their own schema that join the _mst tables correctly (with site_ref), give the login access to that schema only, and schedule refreshes outside busy hours. Better still, read from a reporting copy of the database if you have one. You get one trusted definition of "open orders," no accidental writes, and no BI refresh blocking users at 9 a.m.

Why not connect directly to the tables

Risk. A login that can read everything can usually do more than read. Excel and BI tools shouldn't be holding powerful credentials.
Wrong numbers. Every analyst writes their own joins. Forget site_ref and you double rows in a multi-site database. Use the site-filtered views without a site context and you get nothing.
Load. A large refresh at the wrong time competes with users and background tasks, and can hold locks. See SyteLine freezing for everyone.
Upgrades. Reports built on raw tables break silently when the schema changes. Views give you one place to fix.

The setup

Create a SQL login, or better a Windows (Active Directory) group, just for reporting. It gets read access and nothing that allows writing. Store any password somewhere managed, not in a spreadsheet. The full script, and how to prove it can't write, is in Set up a read-only SQL Server login for SyteLine reporting.

2. Decide where the views live

You have two sensible options:

	Separate reporting database (recommended)	`rpt` schema inside the SyteLine database
Upgrades	Untouched by SyteLine upgrades and Infor tooling	Your objects sit in the vendor's database, so re-check them after every upgrade
Permissions	The login needs `SELECT` on the views and on the SyteLine tables they read (cross-database)	`SELECT` on the schema is enough, through ownership chaining
Clarity	Obviously yours; nothing to explain to Infor support	Mixed in with standard objects

The separate database is the cleaner long-term choice: create a database such as SyteLineReporting on the same SQL Server, put the rpt schema there, and reference the SyteLine tables with three-part names (SyteLine_App.dbo.co_mst). Don't switch on cross-database ownership chaining to avoid the extra grants. Grant SELECT on the specific tables instead (the read-only login guide shows how).

If you'd rather keep everything in one database, the schema-only approach below works:

CREATE SCHEMA rpt AUTHORIZATION dbo;
GO
GRANT SELECT ON SCHEMA::rpt TO [svc_reporting];

Grant SELECT on the schema, not on the underlying tables. The login then sees your views and nothing else. Within one database, views owned by dbo over dbo tables work through ownership chaining, so no extra grants on the tables are needed. In a separate reporting database, use three-part names in the FROM clauses and grant the table-level SELECT described above.

3. Views that get the joins right

An example that powers most sales dashboards. It's written for this article, so check status codes and columns against your own data before relying on it:

CREATE OR ALTER VIEW rpt.open_order_lines AS
SELECT  co.site_ref,
        co.co_num,
        co.cust_num,
        ca.name             AS customer_name,
        ci.co_line,
        ci.co_release,
        ci.item,
        ci.qty_ordered,
        ci.qty_shipped,
        ci.qty_ordered - ci.qty_shipped AS qty_open,
        ci.due_date
FROM    dbo.co_mst       AS co
JOIN    dbo.coitem_mst   AS ci ON ci.co_num   = co.co_num
                              AND ci.site_ref = co.site_ref
JOIN    dbo.custaddr_mst AS ca ON ca.cust_num = co.cust_num
                              AND ca.cust_seq = 0
                              AND ca.site_ref = co.site_ref
WHERE   ci.stat = 'O';

Keep site_ref in every view's output, so reports can filter or group by site. List the columns explicitly rather than SELECT *, so a column added in an upgrade can't change what reports receive.

About WITH (NOLOCK): you'll often see it recommended to stop reports blocking users. It does reduce blocking, but it can also read rows twice, miss rows, or read changes that are later rolled back. It's acceptable for rough operational lists. Avoid it for anything financial, and fix slow queries instead.

Good first views for most sites: open order lines, shipments by day, open PO lines with vendor names, on-hand by warehouse, AR open items, and job status. Table names for each are in the SQL table cheat sheet.

4. Connect Power BI and Excel

Power BI: connect with the SQL Server connector, using the reporting login, and select views from the rpt schema. Use Import mode with a scheduled refresh for most dashboards. DirectQuery sends a query to SyteLine every time someone clicks.
Excel: use Data > Get Data > From SQL Server, sign in with the reporting login, and pick a view. Users refresh the data, and the definition stays the same for everyone.

5. Refresh at the right time

Schedule BI refreshes early in the morning or overnight, away from month-end utilities and the morning rush. If a refresh takes minutes, look at the view's query plan before adding more refreshes. See Why your SyteLine report is slow.

Even better: a reporting copy

If you have the infrastructure, point reporting at a copy of the database instead of production: a restored nightly copy, a log-shipped secondary, or a readable replica. Reports can never slow down users, and analysts can query freely. The trade-off is that the data is as old as the last copy, which is fine for most dashboards.

Keep it maintainable

Name views for what they mean (rpt.open_order_lines, not vw1).
Document each view with what it shows, its filters and who uses it.
Re-test views after every upgrade. Run EXEC sp_refreshview 'rpt.open_order_lines'; for each view so it re-reads the table definitions (a view that errors tells you exactly what changed), then select a few rows and compare totals with before the upgrade.
Never write through the views. Keep the login read-only. Changes to SyteLine data go through SyteLine.
Don't put business logic in Excel. If a calculation matters, put it in the view so everyone gets the same answer.

FAQ

Can I connect Power BI directly to SyteLine?

On-premises, yes, through SQL Server. Use a read-only login and reporting views rather than raw tables, and schedule refreshes outside busy hours.

How do I connect Excel to SyteLine data?

Use Get Data > From SQL Server with a read-only reporting login, and select a reporting view.

Should reporting views go in the SyteLine database or a separate one?

A separate reporting database is cleaner: SyteLine upgrades don't touch it and it's clearly yours. It needs SELECT granted on the underlying tables. A schema inside the SyteLine database is simpler to permission but needs re-checking after each upgrade.

Will Power BI slow down SyteLine?

It can, if large refreshes run against production at busy times. Use Import mode, schedule refreshes early, and ideally read from a reporting copy.

Does this work in CloudSuite Industrial multi-tenant?

No. There's no direct SQL access in multi-tenant. Use the Data Lake or the IDO REST API instead. See Reporting in CloudSuite Industrial without direct SQL.